Snowflake Connector

Query information from Snowflake to use with Tulip apps

The Snowflake connector streamlines how to query information from a Snowflake Data Cloud. Snowflake SQL REST API documentation can be found here.

Support Contact

This connector was created by collaboration with Spaulding Ridge. for more information or questions, please reach out to Spaulding Ridge at info@spauldingridge.com

Connector Notes

The Snowflake connector can manipulate single records relatively easily. Custom widgets may be necessary for querying multiple rows of data or manipulating outputs in Tulip (interactive table, etc.).

Snowflake OAuth uses Snowflake’s built-in OAuth service, and supports Tulip as a custom client. Snowflake OAuth documentation can be found here. Custom client configuration details can be found here.

Setup

This section provides the procedure for configuring an OAuth token from Snowflake's OAuth server to establish connectivity with Tulip as a client, as well as the steps to set up the Snowflake connector in Tulip.

Register Tulip as a client application in Snowflake

1. Log in to your Snowflake instance with a user that has the role/privilege to create and manage Security Integrations (ACCOUNTADMIN).

2. Create a new SQL worksheet in the COMPUTE_WH warehouse.

3. Run the following script to create a Security Integration of type OAuth. Do not forget to update values for INTEGRATION_NAME, TULIP_INSTANCE_NAME, and TIME_IN_SECONDS.

CREATE OR REPLACE SECURITY INTEGRATION <INTEGRATION_NAME>
TYPE = OAUTH
ENABLED = TRUE
OAUTH_CLIENT = CUSTOM
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
OAUTH_REDIRECT_URI = 'https://<TULIP_INSTANCE_NAME>.tulip.co/oauth-redirect-handler'
OAUTH_ISSUE_REFRESH_TOKENS = TRUE
OAUTH_ISSUE_REFRESH_TOKEN_VALIDITY = <TIME_IN_SECONDS>;

Note: The Refresh Token validity can be as long as 1 day (86400 seconds).

Gather details for the OAuth configuration in Tulip

1. Run the following commands to describe the Security Integration you just created and
   DESCRIBE SECURITY INTEGRATION <INTEGRATION_NAME>;

2. Write down the below details:

OAUTH_CLIENT_ID
OAUTH_REDIRECT_URI
OAUTH_AUTHORIZATION_ENDPOINT
OAUTH_TOKEN_ENDPOINT

3. Run:

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('<INTEGRATION_NAME>');

<INTEGRATION_NAME> must be fully capitilised
e.g not tulip_intergation but TULIP_INTEGRATION

4. Write down:

OAUTH_CLIENT_SECRET

Configure your Snowflake connector

1. Log into Tulip and add the Snowflake Connector from the Tulip Library here (Web or Embedded).
2. Click into the production environment
3. Set the following settings:

Running On: Cloud Connector Host
Server address: Your snowflake account server url in format <account_identifier>.snowflakecomputing.com
Base path: api/v2
Authentication
Select either service or user OAuth
Authorization code URL - OAUTH_AUTHORIZATION_ENDPOINT from step 2
Access token URL - OAUTH_TOKEN_ENDPOINT from step 2
Client ID - OAUTH_CLIENT_ID from step 2
Client Secret - OAUTH_CLIENT_SECRET from step 2
Select check boxes for
Send authentication header
Encode client ID during authentication

4. Click Test. You will be taken to the Login Window of your Snowflake account.

A user must login. Note that its default role must not be ACCOUNTADMIN, SECURITYADMIN, or ORGADMIN. You can alter users’s default role for OAuth, as detailed in this Snowflake resource: Managing user consent for OAuth.

5. After successful authentication, a message will appear for consent. Please read it carefully to understand the operation that is being performed, and then click Allow.

You are now able to connect securely to Snowflake from Tulip!

Usage

You have two options to begin using the connector:

Start by customizing the connector template functions to suit your specific requirements.

Follow the Snowflake Unit Test instructions for a practical demonstration of how to use the connector for tasks including: retrieving all table records from a Snowflake Table in Tulip, creating a new record, updating an existing record, and deleting a record.

Template Functions

The Library connector includes the following template functions:
Template: Query Database
Description: Allows you to submit one or more SQL statements for execution.
Method: POST
Endpoint: /api/v2/statements
Reference: https://docs.snowflake.com/en/developer-guide/sql-api/reference#post-api-v2-statements
Template: Check Query Status
Description: Enables you to check the status of the execution of a statement.
Method: GET
Endpoint: /api/v2/statements/{statementHandle}
Reference: https://docs.snowflake.com/en/developer-guide/sql-api/reference#get-api-v2-statements-statementhandle
Template: Cancel Query
Description: Allows you to cancel the execution of a statement.
Method: POST
Endpoint: /api/v2/statements/{statementHandle}/cancel
Reference: https://docs.snowflake.com/en/developer-guide/sql-api/reference#post-api-v2-statements-statementhandle-cancel

Additional References

This Snowflake Community guide provides additional context to the Snowflake integration