Querying Data Using Worksheets

After you create or open a worksheet, you can manage the worksheet, write and execute queries, explore query results and history, and set up filters using Snowsight.

Writing Queries in Worksheets

After you open a worksheet, you can write SQL queries and statements.

Set Worksheet Context

When you set a database and optionally, a database schema, as the worksheet context, you can reference objects in the schema without fully qualifying the object names in your query.

Write Queries with Autocomplete

As you enter your script in the query editor, the autocomplete feature suggests:

  • Query syntax keywords such as SQL functions or aliases.

  • Values that match table or column names within a schema.

Select a function to view its syntax and a brief description.

Snowflake tracks table aliases and suggests them as autocomplete options. For example, if you execute a query using posts as p or posts p as an alias, the next time you type p, the autocomplete feature suggests the alias as an option.

Append a SQL Script to an Existing Worksheet

If you have a SQL script in a file, you can append it to an existing worksheet by doing the following:

  1. Sign into Snowsight.

  2. Select Worksheets to open the list of worksheets.

  3. Open a worksheet.

  4. Hover over the tab for the worksheet and select more actions for worksheet, then choose Import SQL from File.

  5. Browse to the SQL file on your computer.

    The file contents are appended to your worksheet.

Refer to Database Object Names in Worksheets

While you write queries in your worksheet, refer to the database objects relevant to the queries in the Databases explorer. You can drill down to specific database objects, or use search to locate a database, schema, or object that you have access to.

Using the Databases explorer, you can pin databases and database objects for quick reference. When you hover over a database object, select the Pin icon to pin them. Pinned objects appear at the top of the Databases explorer in the Pinned section. You might need to expand the section to view of all your pinned objects.

After you locate a database object, you can place the name of the object in the worksheet that you’re editing:

  1. Sign into Snowsight.

  2. Select Worksheets to open the list of worksheets.

  3. Open a worksheet.

  4. Locate the database object in the Databases explorer.

  5. Hover over the object name and select … more menu » Place Name in Editor.

    The fully qualified object name appears after your cursor location in the worksheet.

For database tables and views, you can also add the column names to the worksheet that you’re editing:

  1. Sign into Snowsight.

  2. Select Worksheets to open the list of worksheets.

  3. Open a worksheet.

  4. Locate the database object in the Databases explorer.

  5. Hover over the object name and select … more menu » Add Columns in Editor.

    The comma-separated column names appear after your cursor location in the worksheet.

Format Your Queries

When a worksheet is open, you can select the name of the worksheet to format the queries in your worksheet, and view the keyboard shortcuts.

  1. Sign into Snowsight.

  2. Select Worksheets to open the list of worksheets.

  3. Open a worksheet.

  4. Hover over the tab for the worksheet and select more actions for worksheet.

  5. In the drop-down list, select Format query to format the query text for readability.

Load Data to a Table

If you’re using a worksheet and want to add some data to work with, you can load data into a table without leaving your worksheet:

  1. Sign in to Snowsight.

  2. In the navigation menu, select Worksheets

  3. Open a worksheet.

  4. Select Objects to view the object explorer.

  5. Locate a specific table using search or browsing.

  6. Hover over a specific table name and select More options » Load Data.

  7. Follow the prompts to upload one or more structured or unstructured files of 50MB or less.

Refer to Loading Data Using the Web Interface for more details.

Executing and Running Queries

You can run a single query or multiple queries sequentially in the same worksheet.

  • To run a single query, in the query editor link, place your cursor in the query, and then select the Run button.

  • To run the entire worksheet, from the More options dropdown menu next to the Run button, select Run All.

Running Worksheets in Folders

Folders no longer have a role assigned to them. An owner or editor of a worksheet in a folder can change the worksheet to run as any role. You can also add USE ROLE to a worksheet in a folder to run different statements in the worksheet as different roles.

When you create a worksheet inside a folder, the worksheet is created with the role of your current session.

Note

To run a worksheet in a folder that was shared with you, even if you have View and Run or Edit permissions on the folder, you must use the same role as the worksheet. If you do not have the same role, duplicate the worksheet and run it as one of your own roles.

Exploring the Worksheet Results

When you run one query or all queries in a worksheet, you see the query results.

The query results display as a table of up to 10,000 rows. If your query returns more than 10,000 rows, use the Download results option to view all the results.

You can navigate the query results with the arrow keys on your keyboard, like you might with a spreadsheet. You can select columns, cells, rows, or ranges in the results table. Any selection can be copied and pasted.

If you want to view your results as a chart, select Chart. For more about charts, refer to Visualizing Worksheet Data.

The results also include generated statistics that display contextual information for any selection, as well as overall statistics for all results. Even if your query returns more than 10,000 rows, the statistics reflect the entire result set.

For more details about worksheet results and version history, see Managing Worksheet History and Versions.

Automatic Contextual Statistics

Select columns, cells, rows, or ranges in the results table to view relevant information about the selected data in the inspector pane (to the right of the results table). Contextual statistics are automatically generated for all column types. The statistics are intended to help you make sense of your data at a glance.

The column overview displays a preview of the statistics for each column. Select a column from the inspector or the column header to view detailed column statistics.

The statistics pane generates different metrics for different types of columns. You can interact with and filter using the items in the statistics pane.

Filled/empty meters

All columns show how many rows are filled and empty. Columns displaying some data types, such as email and JSON, also indicate the number of invalid rows.

Histograms

Displayed for all date, time, and numeric columns.

The histogram indicates the rows that fall into a particular range. Click a bar or drag over the histogram to select a range. You can fine tune your selection by clicking the value labels above the histogram to input specific values.

Frequency distributions

Displayed for all categorical columns. Categorical columns are text columns where the same values are used more than once.

Email domain distributions

Displayed for email columns. The email domain distribution shows the frequency distribution of domain name occurrences.

Key distributions

Displayed for JSON columns. The key distribution shows the frequency of the top keys present in the result set if all the rows contain JSON objects. If the column includes JSON arrays, the key distribution shows the relative types of JSON values in the column.

View Query Details

The Query Details includes information about the execution of the query, including:

  • The duration of the query execution.

  • The number of rows in the results.

  • When the execution completed.

  • The quantity of data scanned by the query.

  • The role used to execute the query.

  • The warehouse used to execute the query.

Some query details are only available for only 14 days.

View the Query Profile

To access a detailed profile of your query, on the Query Details pane select the … more menu » View Query Profile.

The query profile opens in a new browser tab.

Download Your Query Results

To download your query results as a CSV-formatted or TSV-formatted file, select Download results.

The size of your file depends on the amount of data returned by your query. Snowflake does not limit the size of files exported for query results.

Using Filters in Worksheets

The following system filters are available to all roles:

  • :daterange

    Lets you apply a date range to columns.

  • :datebucket

    Lets you group aggregate data by a unit of time (for example, by day).

In addition, administrators can create custom filters for use in your account. To learn more, see Custom Filters.

Consider the following example:

SELECT
    COUNT(O_ORDERDATE) as orders, O_ORDERDATE as date
FROM
    SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
WHERE
    O_ORDERDATE = :daterange
GROUP BY
    :datebucket(O_ORDERDATE), O_ORDERDATE
ORDER BY
    O_ORDERDATE
Copy

In this example, you:

  • Include the :daterange system filter in your WHERE clause.

  • Include the :datebucket system filter in your GROUP BY clause.

When you include the filters in your query, corresponding filter buttons appear at the top of your worksheet.

When the filter buttons appear, they follow the show and hide filters button in the tab order.

In this example, assume you set the Date bucket filter to day, and the Date range to All time.

When you run the query, the results are grouped by day. You can select a new date range to further filter the results.

The results table in this example includes two columns, orders and date. The orders column contains the aggregated number of orders on a given date.

Custom Filters

Custom filters let you change the results of a query or visualization without directly editing the query. Filters are implemented as special keywords that resolve as a subquery or list of values, which are used in the execution of your query.

The Show or Hide Filter button, located in the upper-left corner of the page, lets you show, hide, and manage your custom filters.

Granting Permission to Create Custom Filters

An account administrator (a user with the ACCOUNTADMIN role) must grant each role, including the ACCOUNTADMIN role itself, the permission to create custom filters.

To grant a role permission to create custom filters:

  1. Sign into Snowsight.

  2. Select Worksheets to open the list of worksheets.

  3. Open a worksheet.

  4. Select the Show or Hide Filter button and select Manage Filters.

  5. In the dialog that appears, select Edit Permission.

  6. In the Filter Permissions dialog, select the roles you want to grant permission to create filters.

  7. Select Save.

Creating Custom Filters

To create a custom filter:

  1. Sign into Snowsight.

  2. Select Worksheets to open the list of worksheets.

  3. Open a worksheet.

  4. Select the Show or Hide Filter button and select Manage Filters.

  5. In the dialog that appears, select + Filter.

  6. To add a filter, complete the following:

    Display Name

    Name of the filter that other users see displayed internally.

    SQL Keyword

    Unique keyword to insert into queries. Use the format :<string>, without spaces. For example: :myfilter.

    Description

    Description of the filter.

    Role

    Role used when refreshing filter values derived from a query.

    Warehouse

    Warehouse used when refreshing filter values derived from a query.

    Options via

    Specifies whether the filter values are derived from a query or an itemized list.

  7. Add options to the filter.

  8. Select Save.

Language: English