Open navigation

Snowflake: Authentication and reauthentication guide

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


IP Whitelist

To enable Supermetrics for Snowflake, your database needs to accept connections from the following IP: 23.20.234.176

Before you begin

To connect Supermetrics to Snowflake with oAuth, you need:

  •  Valid Accountadmin and you need to create a Client ID and Client Secret


To connect Supermetrics to Snowflake with username and password, you need:

  • Database username and secret key with username, password, hostname, warehouse name, database name, and database schema.


To create a client ID and client secret, follow these steps:

How to create an OAuth client ID and client secret

To connect Supermetrics to Snowflake, you will first need the create the following parameters:


Client ID, Client Secret, Warehouse, Database name, and Database schema.


For this, an Accountadmin role and the security integration steps are required. Please do the following:


1. Login to Snowflake and go to the user icon. Click on change and select the ACCOUNTADMIN role. 




2. Run the following two 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 as below:





4. Click on the output row 1.





5. A popup will open with 3 key-value pairs in JSON format. You will need to copy the values after the keys  “OAUTH_CLIENT_ID” and 


“OAUTH_CLIENT_SECRET”.


You have created your Client ID and Client Secret. 


Hostname can be found in the URL when you are logged in to Snowflake. It should end in snowflakecomputing.com.

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



You have completed all the steps for security integration.


Data protection agreement 

If you have any questions about how Supermetrics handles and secures your data, read our policy on data privacy and security.


Query types

  • Database table based report: where you select a table from a schema and you can use the columns of that table as the fields in Data Studio.
  • Database based report: build a SQL query that will be used to create report fields. With the SQL query, you can combine data from several tables and even from tables that are in different schemas.

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. Click Add-ons → Supermetrics → Launch to open the sidebar.
  3. Under Data source, select the data source.
  4. You have two options to authenticate the data source:
    -OAuth login. Enter the Client ID, Client Secret, Warehouse, Database name, and Database schema and click Start.
    - API username and secret key. Enter the Database username, Database password, Hostname, Warehouse, Database name, Database schema, and click Start.
  5. Navigate to Database query,
    Under Query SQL, create a SQL query.
    Under Tables, select the countries.
    Under Columns in countries, select how you want your results to be (Id, name, etc.) 
  6. Click Get data to table.  

Reauthenticate 
Learn how to reauthenticate the data source with Supermetrics Google Sheets.


Troubleshooting

  • SQL comments are not supported in SQL based reports.
  • Snowflake functions are not supported in SQL based reports.
  • 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.