Querying Data Using Worksheets¶

This topic describes how to write and execute queries using worksheets in Snowsight, the Snowflake web interface.

In this Topic:

  • Using Worksheets

    • Keyboard shortcuts

    • Exploring Worksheets and Database Objects

      • Exploring Worksheets

      • Exploring Databases

    • Searching Worksheets and Databases

    • Previewing Worksheets

    • Switching Worksheet Versions

  • Writing and Executing Queries

    • Change the Session Context

    • Write Queries

    • Append a SQL script to an Existing Worksheet

    • Resuming and Resizing Warehouses

    • Executing Queries

  • Exploring the Results Table

    • Automatic Contextual Statistics

    • View Query Details

    • View the Query Profile

    • Download Results

    • Stored Results for Prior Worksheet Versions

  • Using Filters

  • Custom Filters

    • Granting Permission to Create Custom Filters

    • Creating Custom Filters

Using Worksheets¶

Snowflake provides several methods in Snowsight for navigating to and interacting with your worksheets.

This section gives a brief overview of how to navigate and use worksheets. This section includes examples and descriptions of the following interface elements:

  • Query editor

  • Database selector

  • Worksheet menu

  • Worksheet and database explorer

  • Search worksheets and databases

  • Preview worksheet SQL

  • Version history

  • Show and hide filters

  • Context selector

  • Share worksheet

  • Run query

  • Results table

  • Query details

  • Show and hide sections

  • Search and download results

Interface

Description

The text area for editing queries.

Query editor

The query editor, located at the center of the worksheet, is where you create and modify your SQL queries.

To learn more, see Writing and Executing Queries.

For assistive technology, the database selector is a button labeled with the currently-selected database and schema. The label changes based on the selected database and schema.

Database selector

The database selector, located at the top of the query editor, lets you search and choose a database and schema for your query. When you select a database and schema, you can reference objects in the schema without fully qualifying the object names in your query.

For assistive technology, the worksheet menu is a button labeled with the name of the currently-selected worksheet. By default, the name of the worksheet is the date and time when the worksheet was created.

Worksheet menu

The worksheet menu, located in the upper-left corner of the worksheet, lets you:

  • Worksheet name

    Rename your worksheet.

  • Import SQL from File

    Import SQL from a file on your device.

  • Move to

    Move your worksheet to a folder or dashboard.

  • Duplicate

    Duplicate your worksheet.

  • Format query

    Format the query text for readability.

  • Delete worksheet

    Delete your worksheet.

  • Show shortcuts

    Show the worksheet keyboard shortcuts.

For assistive technology, the worksheet and database explorer contains the worksheet and database tabs.

Worksheet and database explorer

The worksheet and database explorer, located on the left side of the worksheet, lets you:

  • Explore folders and open worksheets.

  • Explore databases and add object names and columns directly to your SQL query.

For assistive technology, text inputs for search are included on both tabs. On the databases tab, the search input has the button role.

Search worksheets and databases

The worksheet and database explorer includes a search box that lets you:

  • Search the names and content of your worksheets.

  • Search the names of your databases and objects.

To learn more, see Searching Worksheets and Databases.

Because the sql preview feature requires you to hover over other worksheets in the explorer, the feature might not be usable by some assistive technology.

Preview worksheet SQL

When a worksheet is open, hover over other worksheets in the explorer to preview and copy the SQL from other worksheets without leaving the open worksheet.

To learn more, see Previewing Worksheets.

For assistive technology, the history button is usually labeled with a date range description, such as updated 3 days ago.

Version history

When you make changes to a worksheet and run the query, Snowsight preserves the previous version of the worksheet. You can switch to historical versions of the worksheet in order to view and modify the checkpoints.

To learn more, see Switching Worksheet Versions.

For assistive technology, the filter button is labeled show and hide filters.

Show and hide filters

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

To learn more, see Custom Filters.

For assistive technology, the context selector button is usually labeled with the currently-selected role and warehouse.

Context selector

The context selector, located in the upper-right corner of the page, lets you set roles and warehouses for your worksheet session. Worksheets preserve the context you select for future sessions.

To learn more, see Change the Session Context.

Use Share, located in the upper-right corner of the page, to share query results with other Snowflake users. To run the shared worksheet, a user must be granted the role used for the worksheet session context.

For assistive technology, use the button labeled run-button to run your query.

Run query

The run button, located in the upper-right corner of the page, lets you run your queries. If your script changed, a checkpoint is saved in the worksheet's version history.

To learn more, see Executing Queries.

Due to way the results table is rendered, the feature might not be usable by some assistive technology. To explore the results locally, download the results as a .csv file.

Results table

The results table, located at the bottom of your worksheet, appears after you run a query.

To learn more, see Exploring the Results Table.

The query details contain various metrics.

Query details

The Query Details, located on the right side of the results table, provide statistics about the execution of the query.

To learn more, see View Query Details.

Use the buttons labeled objects, query, results, and chart to show and hide worksheet sections.

Show and hide sections

Use Objects, Query, Results, and Chart, located at the bottom of the query editor, to show and hide the corresponding sections of the worksheet.

To learn more about charts, see Visualizing Worksheet Data.

Use the search input to explore results. Use the download as csv button to download the results as a .csv file. Use the toggle query statistics button to hide or show query details.

Search and download results

On the right side of the worksheet, search, download your results, and hide or show the Query Details.

Keyboard shortcuts¶

Snowsight also provides keyboard shortcuts for faster navigation and editing of worksheets. To view the list of supported keyboard shortcuts, press [CMD]+[SHIFT]+[?] (Mac) or [CTRL]+[SHIFT]+[?] (Windows).

Exploring Worksheets and Database Objects¶

This section describes using the worksheet and database explorer to work with worksheets and databases.

Exploring Worksheets¶

To open a worksheet, in the worksheet and database explorer, select the worksheet that you want to open.

Each worksheet in the worksheet and database explorer has a corresponding options menu (For assistive technology, the options button.). The options menu lets you:

  • Rename a worksheet.

  • Duplicate a worksheet.

  • Delete a worksheet.

  • Move a worksheet to a folder or dashboard.

Exploring Databases¶

To view database objects, in the worksheet and database explorer, select a database.

You can:

  • Drill down through your databases to explore your database objects.

  • Hover over tables and views to preview and pin (For assistive technology, the pin button.) them.

Each object in the worksheet and database explorer has a corresponding options menu (For assistive technology, the options button.). The options menu lets you:

  • Set the worksheet context (databases and schemas only).

  • Place the name of the object in your SQL.

  • Add columns from the object to your SQL (tables and views only).

  • View the object definition (tables and views only).

Searching Worksheets and Databases¶

To search your worksheets and databases, in the worksheet and database explorer, enter your term in the search box.

Previewing Worksheets¶

To preview a worksheet, in the worksheet and database explorer, hover over a worksheet.

To copy the worksheet SQL, hover over the preview and select Copy (For assistive technology, the copy button.).

Switching Worksheet Versions¶

To switch to a different version of a worksheet, at the top of the worksheet, select the timestamp, and then the worksheet version that you want to open.

Writing and Executing Queries¶

Snowsight makes writing queries as simple as possible, giving you help when you need it but generally trying to stay out of your way.

Change the Session Context¶

To select or change the role and warehouse for the worksheet session, use the context selector. The current role determines which operations can be performed on Snowflake objects based on the access control privileges granted to the role. First select a role, then select a warehouse that the role has privileges to use.

Note that you cannot choose multiple roles. That is, the context dropdown does not support secondary roles.

To select (or change) the current database for a worksheet, click the database dropdown in the upper-left corner of the query editor.

Note

Each worksheet is a unique session and can use roles different from the role you select on the user menu (your active role). Changing your active role does not change the role assigned to the worksheet with the context selector.

Write Queries¶

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¶

To append a SQL script to an existing worksheet, on the worksheet menu, select Import SQL from File.

Resuming and Resizing Warehouses¶

In the upper-right corner of your worksheet, use the context selector to view your warehouses.

To change the size and status of your active warehouse, in the context selector, select Show Warehouse Details (For assistive technology, the show warehouse details button.).

Executing Queries¶

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

To run a single query, in the query editor, place your cursor in the query, and then select the run button (For assistive technology, use the button labeled run-button to run your query.).

To run the entire worksheet, select all queries in the query editor, and then select the run button (For assistive technology, use the button labeled run-button to run your query.).

Exploring the Results Table¶

The results table supports interactive features including generated statistics that display contextual information for any selection.

Use the arrow keys on your keyboard to navigate selections, just as you would in a spreadsheet.

Select columns, cells, rows, or ranges in the results table. All selections can be copy and pasted.

Up to 10,000 rows can be displayed in the results. If your query returns more than 10,000 rows, the statistics still reflect the entire result set.

Note

When you download your results as a .csv file, the complete results are included, even if the results exceed 10,000 rows.

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 include information about the execution of the query, including:

  • The duration of the query execution.

  • The number of rows.

  • 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 available for only 14 days.

View the Query Profile¶

To access a detailed profile of your query, on the Query Details options menu (For assistive technology, the options button.), select View Query Profile.

Download Results¶

To download your results as a .csv file, in the upper-right corner of the results table, select Download as CSV (For assistive technology, the download as csv button.).

Snowflake does not enforce a size limit of CSV files generated for results. The size of your CSV file is based on the amount of data returned by your query.

Stored Results for Prior Worksheet Versions¶

Results for queries executed in worksheets are cached for up to 1 year. This cache is included in the data storage usage for your account.

Using Filters¶

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

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.

For assistive technology, 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.

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. Select the filter button, and then Manage Filters.

    The Filters window opens.

  2. In the upper-right corner, select Edit Permission.

    The Filter Permissions window opens.

  3. Select the roles you want to grant permission to create filters.

  4. Select Save.

Creating Custom Filters¶

To create a custom filter:

  1. Select the filter button, and then Manage Filters.

    The Filters window opens.

  2. In the upper-right corner, select Filter.

    The Add Filter window opens.

  3. 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.

  4. Add options to the filter.

  5. Select Save.