Open navigation

Snowflake: Authentication guide

This guide will walk you through how to authenticate or reauthenticate Snowflake in Supermetrics with Google Sheets, Data Studio, and Excel.


Before you begin

There are 2 ways to authenticate Snowflake: with OAuth, or with a username and password.


To connect with OAuth, you need an account with the Account admin role, plus a client ID and client secret. 


How to create an OAuth client ID and client secret
  1. Log in to Snowflake and click SYSADMIN

  2. Click Change, and select the ACCOUNTADMIN role. 
  3. Run the following 2 commands in a worksheet:
    First command:
    CREATE SECURITY INTEGRATION
      "Supermetrics App"
      TYPE = OAUTH
      ENABLED = TRUE
      OAUTH_CLIENT = CUSTOM
      OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
      OAUTH_REDIRECT_URI = 'https://supermetrics.com/login-complete'
    ;

    Second command:
    SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('Supermetrics App');

    The output from these queries will look like this:
  4. Click on row 1 in the output.
  5. A popup will open with 3 key-value pairs in JSON format. 
  6. Copy the values after the keys OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET. These are your client ID and client secret respectively.


To connect with a username and password, you need a database username and secret key with username, password, hostname, warehouse name, database name, and database schema.


How to find your hostname, database name, and database schema

You can find your hostname in the URL when you are logged in to Snowflake. It should end in "snowflakecomputing.com".


The Warehouse, Database name, and Database schema can be found in Snowflake at the top right of the query editor.


Allowlist IP

To enable Supermetrics for Snowflake, your database needs to accept connections from this IP address:

  • 23.20.234.176


Query types

  • Database table-based report: Select a table from a schema, and use the columns of that table as the fields in Data Studio.
  • Database based report: Build an SQL query and use it to create report fields. With the SQL query, combine data from several tables, including those that are in different schemas.

Note that SQL comments and Snowflake functions aren't supported in SQL-based reports.


Instructions

Google Sheets Data Studio  Excel Data integrations
Before you can authenticate the data source in Google Sheets, you need to install the Supermetrics add-on.
  1. Open a new Google Sheets file. 
  2. Navigate to ExtensionsSupermetricsLaunch to open the Supermetrics sidebar.
  3. Under Data source, select Snowflake.
  4. For OAuth login:
    Enter the client ID, client secret, warehouse, database name, and database schema. Click Start.

    For username and password login: 
    Enter the database username, database password, hostname, warehouse, database name, and database schema. Click Start.
  5. Navigate to Database query.
  6. Under Query SQL, create an SQL query.
  7. Under Tables, select countries.
  8. Click Get data to table.  

Troubleshooting

  • If you encounter an authentication error such as “authentication failed”, “you need to reauthenticate” or similar, see this guide
  • If you're missing an account from the drop-down list, follow these steps.
  • If you encounter a permission error such as “user permission denied” or similar, follow this guide.

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.