Querying Data Using Worksheets

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

Home (wksts1) » 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.

Schema browser

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

Schema browser

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.

Writing and Executing Queries

Navigation

Home (wksts1) » 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, warehouse, and database for the user session, click the Session Context dropdown in the upper-left corner of the query editor. 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 and database that the role has privileges to use.

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.

Smart autocomplete

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.

Execute Queries

To run your query, position your cursor above or within the query text in the query editor, and click the Run (wksts2) button in the head or type [CMD]+[RETURN] (Mac) or [CTRL]+[ENTER] (Windows).

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

Current Limitations

Currently, the Run (wksts2) button in the query editor executes a single query only. Either write one query per worksheet, or reposition your cursor to specify which query to execute in a multi-query worksheet.

Viewing 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 History tab.

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.

Custom Filters

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

Granting Permission to Create Custom Filters

Navigation

Home (wksts1) » 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

Home (wksts1) » 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.

Toggle filter

To view the SQL values for your filters, click the Show query without filters (wksts3) 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.

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.