How to connect Snowflake with Rows

In this article we'll walk through how to set up the Snowflake integration in Rows.

The Snowflake integration allows you to query your databases, schemas and views and import the results directly inside a Rows spreadsheet.

Connecting the Snowflake integration requires 3 inputs:

• The Account identifier
• The Client ID
• The Client Secret

You can generate them inside the Snowflake UI following this guide. The Snowflake integration uses OAuth 2 to authenticate your Snowflake account with Rows.

Before we start

Snowflake's requirements are a little technical and require a certain level of permission to set up, so if you are not a SYSADMIN in Snowflake please reach out to your company's Snowflake SYSADMIN.

Due to security permissions, Snowflake restricts the user roles that are allowed to connect the Rows integration. Snowflake blocks the following roles from authenticating using OAuth 2: ACCOUNTADMIN, ORGADMIN, and SECURITYADMIN.

If you are part of any of these roles, you'll need to create an additional user inside Snowflake with a different role in order to authenticate and use the integration.

See here on how to create a new role or follow Snowflake documentation.

Create the Rows security integration in Snowflake

The first step is to create a security integration inside Snowflake.

To do that, create a new Worksheet in Snowflake and run the following code inside a warehouse:

1create or replace security integration ROWS_SI
2type=oauth
3enabled=true
4oauth_client=CUSTOM
5oauth_client_type='CONFIDENTIAL'
6oauth_redirect_uri='https://rows.com/dashboard/integrations/snowflake'
7oauth_issue_refresh_tokens=true
8oauth_refresh_token_validity=7776000; /* 90 days */

Note: All properties included in the code are mandatory. Read more about Security integrations in the Snowflake documentation.

To confirm that the security integration was created, this command in a specific warehouse to list the existing security integrations:

1SHOW SECURITY INTEGRATIONS;

💡 Use the replace command in case you need to update the integration along with all secrets. If you don't want to overwrite the secrets, use the update command, for ex:

1alter security integration ROWS_SI
2SET oauth_redirect_uri='https://rows.com/dashboard/integrations/snowflake';

Get the Account identifier

Once the integration is created, you can retrieve the inputs to connect the integration.

Run this command in the Snowflake worksheet:

1DESC SECURITY INTEGRATION ROWS_SI;

This command will output the security integration, including the endpoints we need OAUTH_AUTHORIZATION_ENDPOINT and OAUTH_TOKEN_ENDPOINT.

You'll need to extract the account identifier from the endpoint. For example, in:

1OAUTH_AUTHORIZATION_ENDPOINT = https://si99999.europe-west4.gcp.snowflakecomputing.com/oauth/authorize

The account identifier is si99999.europe-west4.gcp.

This is what you'll use for the Account indentifier parameter in the Rows/Snowflake integration page.

Get the Client ID and Client Secret

In the same worksheet, run the following command to reveal the ClientID and Client Secret

1select SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('ROWS_SI')

This will reveal the client id and secret in a JSON object.

1{
2    "OAUTH_CLIENT_SECRET_2":"Bt7pjuW9MMw+R/iXXXXXretkI8b5eP+oztucFj6REyc=",
3    "OAUTH_CLIENT_SECRET":"sv2Q5X+W4gQtQQ9gQtHmfnXXXXXeIQ78CT2oy+A95Bk=",
4    "OAUTH_CLIENT_ID":"cCkydz9HxeamWN5VXXXXXhf7Mw4="
5}

Parse the OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET. These will be the Client ID and Client Secret parameters in the Rows/Snowflake integration page.

The OAUTH_CLIENT_SECRET_2 is only used for rotation purposes and can be ignored.

Connecting the integration

Once you have followed these steps, you can connect the Snowflake integration.

Add the Account identifier, Client ID and Client Secret and click to Connect.

A pop-up window will appear that will prompt you to login to your Snowflake account. Once that is done and you grant access to Rows to query the data inside Snowflake, the integration is connected 🎉

How to create a new user and role

Follow these steps to create a new role and use it to connect the Rows integration.

Inside a new worksheet:

1) Create a new role and name it READONLY

1CREATE OR REPLACE ROLE READONLY

2) Grant the role permissions to query data inside a data warehouse (replace COMPUTE_WH with your current warehouse).

1GRANT OPERATE ON WAREHOUSE COMPUTE_WH TO ROLE READONLY:
2GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE READONLY:

3) Grant the role permissions to query tables (replace "DATABASE"."SCHEMA" with the database and schema you want to access).

1GRANT SELECT ON FUTURE TABLES IN SCHEMA "DATABASE"."SCHEMA" to ROLE READONLY;
2GRANT SELECT ON ALL TABLES IN SCHEMA "DATABASE"."SCHEMA" to ROLE READONLY;

4) Create a new user with the role

If you already have additional roles on your Snowflake account, you can use them as the DEFAULT_ROLE for the user.

In this example we're creating a user kevin with the 'p@assword' Password.

1CREATE or replace USER kevin PASSWORD='p@assword' DEFAULT_ROLE = READONLY DEFAULT_SECONDARY_ROLES = ('ALL') MUST_CHANGE_PASSWORD = FALSE;

5) Add the user to the role:

1GRANT ROLE READONLY TO USER kevin;

You can check if the user was successfully create and confirm the grants of the user user role with the following commands:

See list of all users:

1SHOW USERS;

Check the grants to the READONLY role:

1SHOW GRANTS TO ROLE READONLY