Snowflake Connector
  • 31 Jan 2024
  • 2 Minutes to read
  • Contributors

Snowflake Connector


Article Summary

Query information from Snowflake to use with Tulip apps

Audience

Please note, the Snowflake is technical and may require your IT support to create the appropriate roles and permissions.

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

Purpose

The snowflake connector streamlines how to query information from a Snowflake Data Cloud.

Connector Notes

  • The Snowflake connector is used to query information from Snowflake; currently, there is not functionality to write directly to snowflake. That can be accomplished with other tools such as the Tables API
  • The Snowflake connect can output single rows relatively easily as variables. For querying multiple rows of data, custom widgets to manipulate arrays will be required.
  • Snowflake documentation can be found here.

Setup

The following is required for correct usage:

  • Ability to create security credentials using the ACCOUNTADMIN role
  • Ability to use Refresh Tokens to generate additional Access Tokens (Security preference for most companies)

To enable this connectivity, you will need to enable OAUTH on your snowflake instance.

  1. Log in to your Snowflake instance with a user that has ACCOUNTADMIN role.
  2. Run the following script to create an OAUTH security integration
CREATE OR REPLACE SECURITY INTEGRATION oauth_tulip_int
TYPE = OAUTH
ENABLED = TRUE
OAUTH_CLIENT = CUSTOM
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
OAUTH_REDIRECT_URI = 'https://[instance].tulip.co'
OAUTH_ISSUE_REFRESH_TOKEN_VALIDITY = 7776000
BLOCKED_ROLES_LIST = ('SYSADMIN')

NOTE: Refresh Token Validity can be as long as 90 days. Access Tokens are programmatically generated, but refresh tokens are manually created
Snowflake has additional documentation on security credentials

Ensure you update the OAUTH_REDIRECT_URI to your tulip instance URL

  1. Run the describe statement below to get the following values:
  • OAUTH_CLIENT_ID
  • OAUTH_REDIRECT_URI
  • OAUTH_AUTHORIZATION_ENDPOINT
  • OAUTH_TOKEN_ENDPOINT
DESC SECURITY INTEGRATION oauth_tulip_int;
  1. Run the following select statement to get OAUTH client secrets:
  • OAUTH_CLIENT_SECRET2
  • OAUTH_CLIENT_SECRET
 select SYSTEM$SHOW_OAUTH_CLIENT_SECRETS(  'OAUTH_TULIP_INT' );
  1. Request an Authorization code by navigating to URL:
https://<yoursnowflakeinstance>.snowflakecomputing.com/oauth/authorize?response_type=code&client_id=<OAUTH_CLIENT_ID>&redirect_uri=<encoded value ofOAUTH_REDIRECT_URI>
  • This will take you to a login window, login and Allow ‘oauth_tulip_int’ to access your
    snowflake account.
  • Redirected to a page where URL will contain code=’xxxxxxxx’. This is your Auth Code
    Grant, save this for connecting within Tulip.
    Please Note:* All query parameters need to be encoded. Use a tool such as urlencoder.io to encode the text correctly
  1. Log into Tulip and add the Snowflake Connector from the Tulip Library (Web or Embedded)
    image.png

  2. Click on Production & testing & development in the environment settings section on the left hand side of the Tulip UI

  3. Update the Host to be your snowflake instance URL:

image.png

Go to your Snowflake instance and copy your account url located in the bottom right corner of your screen (There will be a button that you can click to copy it).

  1. You are now able to connect securely to Snowflake from Tulip. Some sample functions are included in the library content including authorization and obtaining an OAUTH token.
    Example: Use the Auth Code generated in Step 5 as the value for refresh_token in the template “Get OAuth2 Token”.

Usage

Once the Access Token has been generated, it can be used as a Bearer Token in a SQL request to Snowflake.

Make sure the queries are set to POST request, and ensure /api/v2/statements is added to the end of the host url for database queries

Additional References

This Snowflake Community guide also provides additional context to the Snowflake integration


Was this article helpful?