Querying Data Using Worksheets

Preview Feature — Open

Available to all accounts.

Currently, this feature is not supported for Snowflake accounts in government regions.

This topic describes how to write and execute queries using Snowsight worksheets.

In this Topic:

Managing Worksheets

Create and Open Worksheets

To create a new worksheet, click the + Worksheet button (or the New worksheet (+) button when an existing worksheet is open).

To open an existing worksheet, navigate to one of the following tabs and click on the name of the worksheet:

Recent

Displays the most recent worksheets you’ve opened.

Shared with me

Displays worksheets that your colleagues have explicitly shared with you.

My worksheets

Displays worksheets that you created and own.

Folders

Displays folders that you created or that your colleagues have explicitly shared with you.

Exploring Schemas

Navigation

Worksheets » My Worksheets (or open a local worksheet under Recent or in Folders) » <worksheet_name> » Schema tab. The schema browser opens in the right pane in the bottom part of the window.

The schema browser enables users to explore the schemas, tables, views, and other objects in the current database that are accessible by the current session role.

Search table and column names by entering a text string in the search field. For example, enter station to search all objects that include that string in the object name.

Click the name of a table to pin it to the main schema browser pane.

The schema browser displays details of each table including column names and data types and the number of rows. Click the Preview button at the top of a table details area to open a preview of the table data. To remove the table from the main pane, click the pin icon in the table details header.

Refreshing the Schema Metadata

Schema metadata in Snowsight is refreshed automatically every 24 hours. When a new table or view is created in a database, roles with the required permissions can query the object; however, the schema browser does not list the new object immediately, and the new object cannot be searched.

To manually refresh the metadata of a schema, click … (options) » Refresh schema in the schema browser. Snowsight quickly refreshes the metadata so that the new object is listed and searchable.

Writing and Executing Queries

Navigation

Worksheets » My Worksheets (or open a local worksheet under Recent or in Folders) » <worksheet_name>.

Snowsight make 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 current role or warehouse for the user session, click the session context dropdown in the upper-right corner of Snowsight. 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.

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

Write Queries

As you type in the query editor, the smart autocomplete feature suggests query syntax keywords such as SQL functions or aliases and lists values that match table or column names within a schema. This feature enables you to write queries quickly. Click on the name of a function to view its syntax and a brief description; the description links to the Snowflake documentation for more detailed information.

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

Resume and Resize Warehouses

The session context dropdown for warehouses is located in the upper-right corner of Snowsight. The dropdown includes an options menu for the selected warehouse. Click the Show warehouse details menu to display the status, size, and other details of the warehouse. If desired, you can resume or suspend the warehouse, or increase or decrease its size to meet the performance demands of your queries.

Execute Queries

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

Single query

Position your cursor above or within the query in the query editor.

Multiple queries

Highlight the queries to run.

Click the Run () button or press [CMD]+[RETURN] (Mac) or [CTRL]+[ENTER] (Windows).

After you run your query or queries, you can explore the results table. See Exploring the results table.

View the Query History

Currently, Snowsight does not provide a visual display of the query history.

You can view the query history in the classic web interface or via SQL:

Web Interface

Click History .

SQL

ACCOUNT_USAGE

Query the QUERY_HISTORY view.

Information Schema

Query the QUERY_HISTORY family of table functions.

Query Earlier Worksheet Versions

Each time a query in a worksheet is executed, a version of the worksheet is saved in the query history. Click Updated <time> in the header to view past queries. You can re-run past queries from here.

The query history displays either a preview of the query or the error message returned by each query. This preview does not consume compute resources.

Custom Filters

Custom filters are special keywords that resolve as a subquery or list of values.

Granting Permission to Create Custom Filters

Navigation

Admin » Settings » Privacy.

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

All custom filters are available to all users in your account.

1. Click the Add role button.

2. Search for each role that you want to grant permissions to. Select the role.

3. Click the Add (<n>) button, where <n> is the number of roles that are selected.

Creating Custom Filters

Navigation

Admin » Settings.

1. Click the + Filter button. The Configure filter dialog opens.

2. Complete the following fields:

   Display name

   Name of the filter displayed internally on the Settings page.

   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. Click the Edit button to edit the query or list.

   When the filter values are made available via a query, choose whether the query should be refreshed hourly, daily, or never.

   Value type

   Text

   String values.

   Number

   Numeric values.

   Multiple values can be selected

   Allow worksheet owners to select multiple values for the filter.

   Include an “All” option

   Include an All option to allow specifying any value, or any value from an approved list.

   Include an “Other” option

   Include an Other option to allow specifying any value.

Use Filters

The following system filters are available to all roles:

:daterange

:datebucket

In addition, administrators can create custom filters for use in your account. For instructions, see Custom Filters (in this topic).

Include filters in WHERE clauses to filter the query results. You can then choose the filter values to apply to the results from the filter bar.

For example, include the :daterange filter in a WHERE clause. After querying your data, click the All time selector above the query editor and select a different range of time. The query results automatically update based on the new filter value.

To view the SQL values for your filters, click the Show query without filters () button.

Keyboard Shortcuts

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

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.

A limit of 10,000 rows are displayed in the results. If your query returns more than 10,000 rows, the statistics still reflect the entire result set. Filter your results to bring them within the 10,000 row limit. Note that the entire result set is included in downloads to CSV (comma-separated values) format.

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 inspector pane generates different statistics for different types of columns. All elements in column statistics are clickable, so you can filter by any of these elements.

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.

Download Results

Use the download button in the inspector to download your results as a CSV file.

Note that Snowsight downloads have no upper size limit.