# Accessing Data in SELECT

URL: https://select.dev/docs/reference/usage-guide/accessing-select-data
Index of all SELECT docs: https://select.dev/docs/llms.txt

> Export your SELECT data

## 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](https://select.dev/docs/reference/usage-guide/accessing-select-data#data-sharing-feature-overview))

[Video](https://storage.googleapis.com/prod_select_images/docs/download_csv.mp4)

## Data Sharing Feature Overview

> [!NOTE]
> Enabling data sharing for your SELECT account is an add-on feature. Please contact SELECT to determine your eligibility and receive a quote.

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!

[Video](https://storage.googleapis.com/prod_select_images/docs/view_sql.mp4)

### Setup Instructions

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

```sql
use role accountadmin;
show shares;
```

You'll see an output that looks like this:

![SELECT data share setup part 1](https://select.dev/cdn-cgi/imagedelivery/1zmOcgV1p520E4lLTrYjjg/docs/dataShare1.png/width=2030,quality=75)

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_DATA_SHARE_ORG_ABC123` i.e. `SELECT_DATA_SHARE_<your org id>`.

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

```sql
-- modify using the owner_account and name from the output of the show shares query
-- keep the database named select_datashare if possible
create database select_datashare 
from share scwxhob.select_aws_us_west_2_provider.select_data_share_org_abc123
;
```

> [!WARNING]
> The generated SQL shown in the demo video above assumes that the database you have created is named `select_datashare`. You can name it something else, but the SQL code we generate will not work directly as a result.

By default, this shared database is only accessible to users with the [`accountadmin`](https://select.dev/posts/snowflake-roles#default-snowflake-account-roles) [role](https://select.dev/posts/snowflake-roles#default-snowflake-account-roles). You can grant access to other users using the following command:

```sql
grant 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](https://select.dev/posts/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:

```python
import os

import snowflake.connector

# Replace this with your Snowflake account identifier. 
# See our docs on how to find this if you don't know it.
# https://select.dev/docs/connect-snowflake-account#using-snowsight-recommended
account_identifier = 'your_account_identifier'

# Ensure this is a service user account that doesn't require MFA
user = os.environ['SNOWFLAKE_USERNAME']
password = os.environ['SNOWFLAKE_PASSWORD']

# Example SQL query. Update this!
sql = """
select
    query_id
from snowflake.account_usage.query_history
where
    start_time > current_date - 1
limit 10
"""

# Establish a connection to Snowflake
conn = snowflake.connector.connect(
    user=user,
    password=password,
    account=account_identifier,
)
# Create a cursor object
cursor = conn.cursor()

try:
    # Execute the SQL query
    cursor.execute(sql)
    # Fetch the results
    results = cursor.fetchall()
    # Get column names
    column_names = [desc[0] for desc in cursor.description]
    # Convert the results to a list of dictionaries
    result_list = [dict(zip(column_names, row)) for row in results]
finally:
    # Close the connection
    conn.close()
```

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

```markdown
>>> result_list
[{'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'}]
>>>
```

#### 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:

![SELECT data share example query](https://select.dev/cdn-cgi/imagedelivery/1zmOcgV1p520E4lLTrYjjg/adfdb401-afe4-4c3f-3af0-4909db5f8b00/width=2598,quality=75)

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.

```sql
select
    to_char(date, 'YYYY-MM-DD') as day,
    service as service,
    sum(spend) as sum_spend
from select_datashare.consolidated.consolidated_daily_spend
where
    to_char(date, 'YYYY-MM-DD') >= '2023-11-07' -- dates from the SELECT web UI
    and to_char(date, 'YYYY-MM-DD') <= '2024-01-05'
group by all
```

We can accomplish this using the `datetime` library from Python

```python
from datetime import datetime, timedelta

period_end = datetime.now() - timedelta(days=1)
period_start = period_end - timedelta(days=30)

sql = f"""
select
    to_char(date, 'YYYY-MM-DD') as day,
    service as service,
    sum(spend) as sum_spend
from select_datashare.consolidated.consolidated_daily_spend
where
    to_char(date, 'YYYY-MM-DD') >= '{period_start.strftime('%Y-%m-%d')}'
    and to_char(date, 'YYYY-MM-DD') <= '{period_end.strftime('%Y-%m-%d')}'
group 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.