Accessing Data in SELECT

Export to CSV

Users can easily export data from any chart or table they see in the SELECT UI. Just hover over the data you want to export and you'll see a menu with 4 options:

1. Download to CSV
2. Copy CSV to Clipboard
3. Copy TSV to Clipboard (use this option to paste directly in Google Sheets!)
4. View SQL (if you have a SELECT data share setup, learn more here

Data Sharing Feature Overview

Once the data share has been enabled, you can hover over any chart or table in SELECT and you will see a little code icon appear in the top left. Copy the SQL code and run it directly in your Snowflake account. You’ll see the exact data used to populate the UI in SELECT!

Setup Instructions

Using the accountadmin role, run the show shares SQL command in your account:

1use role accountadmin;
2show shares;

You'll see an output that looks like this:

Look for the row where owner_account is something like SCWXHOB.SELECT_AWS_US_WEST_2_PROVIDER (the name will vary depending on the cloud/region you are using, but it will have the similar structure of SCWXHOB.SELECT_<cloud provider>_<cloud region>_PROVIDER). The share name will look like select_scwxhob_ad38017 i.e. select_<your account uuid>.

Copy and paste the account name and share name from the output above, then create a database in your account for the share:

1-- modify using the owner_account and name from the output of the show shares query
2-- keep the database named select_datashare if possible
3create database select_datashare 
4from share scwxhob.select_aws_us_west_2_provider.select_scwxhob_ad38017
5;

By default, this shared database is only accessible to users with the accountadmin role. You can grant access to other users using the following command:

1grant imported privileges on database select_datashare to role <enter_role_name_here>;

Querying the data with Python

If you are looking to automate spend reporting within your organization using the data available in SELECT, you can easily do so once the data share has been setup. Once you have copied & modified the relevant SQL query you want to run from the SELECT UI, you can programmatically execute that query and retrieve the results using the Snowflake Python connector.

Example Python Script

Here is an example Python script that shows the end-to-end flow for connecting to Snowflake, executing the query, and retrieving the results as a list of dictionaries:

1import os
2
3import snowflake.connector
4
5# Replace this with your Snowflake account identifier. 
6# See our docs on how to find this if you don't know it.
7# https://select.dev/docs/connect-snowflake-account#using-snowsight-recommended
8account_identifier = 'your_account_identifier'
9
10# Ensure this is a service user account that doesn't require MFA
11user = os.environ['SNOWFLAKE_USERNAME']
12password = os.environ['SNOWFLAKE_PASSWORD']
13
14# Example SQL query. Update this!
15sql = """

Here's an example of the result_list output from above:

1>>> result_list
2[{'QUERY_ID': '01b17a56-0000-48b5-0000-3de90684bc82'}, {'QUERY_ID': '01b17a56-0000-48b5-0000-3de90684bc86'}, {'QUERY_ID': '01b17a56-0000-48b5-0000-3de90684bc8a'}, {'QUERY_ID': '01b17a56-0000-4892-0000-3de90684c922'}, {'QUERY_ID': '01b17a56-0000-48c1-0000-3de90684e48a'}, {'QUERY_ID': '01b17a56-0000-48c1-0000-3de90684e48e'}, {'QUERY_ID': '01b17a54-0000-4892-0000-3de90684c91a'}, {'QUERY_ID': '01b17a54-0000-48c1-0000-3de90684e482'}, {'QUERY_ID': '01b17a53-0000-48b5-0000-3de90684bc7e'}, {'QUERY_ID': '01b17a53-0000-4892-0000-3de90684c916'}]
3>>>

Generating a custom date range

Let's imagine you want to report on spend by Snowflake service, and you grab the corresponding query from SELECT's UI:

In the generated SQL, a date range is shown for the period selected in the web UI. For your custom reporting, you'll likely want to change this.

1select
2    to_char(date, 'YYYY-MM-DD') as day,
3    service as service,
4    sum(spend_net_cloud_services) as sum_spend_net_cloud_services
5from select_datashare.transformed.daily_spend_select
6where
7    to_char(date, 'YYYY-MM-DD') >= '2023-11-07' -- dates from the SELECT web UI
8    and to_char(date, 'YYYY-MM-DD') <= '2024-01-05'
9group by all

We can accomplish this using the datetime library from Python

1from datetime import datetime, timedelta
2
3period_end = datetime.now() - timedelta(days=1)
4period_start = period_end - timedelta(days=30)
5
6sql = f"""
7select
8    to_char(date, 'YYYY-MM-DD') as day,
9    service as service,
10    sum(spend_net_cloud_services) as sum_spend_net_cloud_services
11from select_datashare.transformed.daily_spend_select
12where
13    to_char(date, 'YYYY-MM-DD') >= '{period_start.strftime('%Y-%m-%d')}'
14    and to_char(date, 'YYYY-MM-DD') <= '{period_end.strftime('%Y-%m-%d')}'
15group by all

Limitations

• Not all SELECT datasets have been exposed to the data share. Please reach out if you see a dataset that is not present and we will expedite the process of adding it.
• Not all UI elements have the code snippet available as shown in the demo above. If you see a chart/table and would like to access to underlying data but cannot see the corresponding query, please reach out to us.