home User Guide Getting Started Help Center Documentation Community Training Certification
menu
close
settings
Looker keyboard_arrow_down
language keyboard_arrow_down
English
Français
Deutsch
日本語
search
print
Snowflake

add

New in Looker 7.10, Looker can use connection pools on Snowflake to improve query performance.

Follow these steps to connect Looker to Snowflake:

  1. Create a Looker user on Snowflake and provision access.
  2. Set up a database connection in Looker.

Create a Looker User on Snowflake

We recommend the following commands for creating the Looker user. Make sure to run each line individually.

Optionally, add in the ON FUTURE keyword to persist GRANT statements on newly created objects. We recommend running this for tables in all schemas that Looker will use so you are not required to re-run GRANT statements as new tables are created.

-- change role to ACCOUNTADMIN use role ACCOUNTADMIN; -- create role for looker create role if not exists looker_role; grant role looker_role to role SYSADMIN; -- Note that we are not making the looker_role a SYSADMIN, -- but rather granting users with the SYSADMIN role to modify the looker_role -- create a user for looker create user if not exists looker_user password = '<enter password here>'; grant role looker_role to user looker_user; alter user looker_user set default_role = looker_role default_warehouse = 'looker_wh'; -- change role use role SYSADMIN; -- create a warehouse for looker (optional) create warehouse if not exists looker_wh -- set the size based on your dataset warehouse_size = medium warehouse_type = standard auto_suspend = 1800 auto_resume = true initially_suspended = true; grant all privileges on warehouse looker_wh to role looker_role; -- grant read only database access (repeat for all database/schemas) grant usage on database <database> to role looker_role; grant usage on schema <database>.<schema> to role looker_role; -- rerun the following any time a table is added to the schema grant select on all tables in schema <database>.<schema> to role looker_role; -- or grant select on future tables in schema <database>.<schema> to role looker_role; -- create schema for looker to write back to use database <database>; create schema if not exists looker_scratch; use role ACCOUNTADMIN; grant ownership on schema looker_scratch to role SYSADMIN revoke current grants; grant all on schema looker_scratch to role looker_role;

If you paste the above commands as a batch into the Snowflake connection panel, check the All Queries box to ensure all lines are run. By default, Snowflake runs only lines that are selected:

Adding the Connection

Follow the instructions for connecting Looker to your database, noting a few special settings:

Here is an example setup:

Designating Snowflake Warehouses on a Per-Group or Per-User Basis

You can use Looker user attributes to assign separate Snowflake warehouses to individual Looker users or groups. This is useful, for example, if you have users who require different levels of computing power. You can assign a warehouse with greater computing resources to just those users who need it, while assigning a warehouse with lesser resources to users with lesser needs.

To designate warehouses on a per-group or per-user basis:

  1. Add the groups or users in Looker.

  2. Define a user attribute to store the Snowflake warehouse names:

  3. In the user attribute you just defined, assign the warehouse name values to the groups or users:

  4. In the Additional Params field on the Connection Settings page, add the following, replacing snowflake_warehouse with the name of the user attribute you defined:

    warehouse={{ _user_attributes['snowflake_warehouse'] }}

    For example:

  5. To test the individual connection settings, you can sudo as a user to whom you assigned a warehouse name value.

You can see more detailed instructions for this procedure on the Red Pill Analytics blog.

Snowflake’s Autosuspend Feature

Snowflake warehouses have an autosuspend feature that is enabled by default. After a specified period, the warehouse will autosuspend. If the warehouse is suspended, all queries produce an error. This error is not visible via dashboards (no data is normally shown), but it is visible when querying with the Explore page.

Snowflake also has an autoresume feature that will resume the warehouse when it is queried. However, resuming the warehouse can take up to five minutes, causing queries to hang for five minutes before returning. These features can be configured on the Warehouses tab:

PDT Support

For persistent derived table support, create a Snowflake user account for PDTs that has write access to your database and the default schema. On the Looker Connections Settings page, check the Persistent Derived Tables box. Then, in the PDT Overrides column, enter the username and password of the PDT user. See the Connecting Looker to Your Database documentation page for more information.

PDTs are not supported for Snowflake connections that use OAuth.

add

When you update to Looker 7.20, Snowflake connections will now be made with the session parameter AUTOCOMMIT set to TRUE.

For Snowflake connections, Looker sets the value for the AUTOCOMMIT parameter to TRUE (which is Snowflake’s default value). AUTOCOMMIT is required for SQL commands that Looker runs to maintain its PDT registration system.

Configuring OAuth for Snowflake Connections

Looker supports OAuth for Snowflake connections, meaning that each Looker user authenticates in to Snowflake and authorizes Looker to run queries on the database with their own Snowflake user account.

OAuth allows database administrators to:

With Snowflake connections that use OAuth, users must log in again periodically when their OAuth tokens expire. The duration of validity for Snowflake OAuth tokens is set through Snowflake itself.

Note the following for Snowflake with OAuth:

Configuring a Snowflake Database for OAuth with Looker

To create a Snowflake connection to Looker using OAuth, you must set up the OAuth integration in Snowflake. This requires a Snowflake user account with ACCOUNTADMIN permission.

You will need a different security integration for each Looker instance you have.

  1. Run the following command in Snowflake:

    CREATE SECURITY INTEGRATION LOOKER TYPE = OAUTH ENABLED = TRUE OAUTH_CLIENT = LOOKER OAUTH_REDIRECT_URI = 'https://<looker_hostname>/external_oauth/redirect';

    Where <looker_hostname> is the hostname of your Looker instance.

  2. Get the OAuth client ID and secret by running the following command:

    SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('LOOKER');

    The response will have an OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET that you will need later in this procedure.

  3. In Looker, create a new connection to your Snowflake warehouse, as described on the Connecting Looker to Your Database documentation page. When creating the new connection, check the Use OAuth checkbox. When you select Use OAuth, you will see the OAuth Client ID and OAuth Client Secret fields:

  4. Paste in the OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET values from Step 2.

  5. Complete the rest of the procedure for Connecting Looker to Your Database.

Once you configure Looker’s connection to your database, you can test the connection itself by doing either of the following:

Beyond that, you can test the connection and deploy it on a model by doing the following:

  1. In Looker, go into Development Mode.
  2. Navigate to the project files for a Looker project that uses your Snowflake connection.
  3. Open a model file and replace the model’s connection value with the name of the new Snowflake connection, then save the model file.
  4. Open one of the model’s Explores or dashboards and run a query. When you try to run a query, Looker will prompt you to log in to Snowflake.
  5. Follow the login prompts for Snowflake and enter your Snowflake credentials.

Once you successfully log in to Snowflake, Looker will return you back to your query. If your query runs correctly, you can commit the new connection value and deploy your changes to production.

Signing In to Snowflake to Run Queries

Once the Snowflake connection is set up for OAuth, users will be prompted to log in to Snowflake before running queries. This includes queries from Explores, dashboards, Looks, and SQL Runner. Here’s an example Explore that uses a Snowflake connection for which the user must log in:

Users can also log in to Snowflake from the OAuth Connection Credentials section on their Account page.

To log in to your Snowflake account via Looker:

  1. Select Account from the user menu.
  2. Scroll down to the OAuth Connection Credentials section and click the Log In button for the desired Snowflake database.
  3. Enter your Snowflake credentials through Snowflake’s interface and click Log In.
  4. Click Allow to give Looker access to your Snowflake account.

Once you log in to Snowflake through Looker, you can log out or reauthorize your credentials at any time through your Account page, as described on the Personalizing Your User Account documentation page.

Feature Support

Looker’s ability to provide some features depends on whether the database dialect can support them.

In the current Looker release, Snowflake supports the following Looker features:

Next Steps

After you have connected your database to Looker, you’re ready to configure sign-in options for your users.

Top