MENU
    Snowflake
    • 07 Jan 2025
    • 4 Minutes to read

    Snowflake


    Article summary

    Overview

    In this article we will discuss how to connect a Snowflake database to Preset. So let's get started!


    Allowlist Preset IPs

    Before starting, validate your Snowflake Network Policies configuration, to ensure that Preset IP addresses are allowed to access Snowflake. Below IPs should be allowed (according to the workspace region):

    us-west-2 (us1a)us-east-1 (us2a)eu-north-1 (eu5a)ap-northeast-1 (ap1a)
    35.161.45.1144.193.153.19613.48.95.335.74.159.67
    54.244.23.8552.70.123.5213.51.212.16535.75.171.157
    52.32.136.3454.83.88.9316.170.49.2452.193.196.211

    If you are not sure where your Preset workspace is located, you can refer to the URL on your browser when accessing Preset. It should look like this: https://xxxxxxxx.us2a.app.preset.io/superset..., where us2a means it is in us-east-1.


    Connect Database to Preset

    Let's start by selecting + Database — have a look at Connecting your Data if you need help with this step.

    Select Snowflake.

    Snowflake_test_image.png

    The Connect a database panel appears. There are three supported authentication methods to connect to Snowflake:


    Connect using username and password

    Check Snowflake User Used

    When a new user is created on Snowflake, a password reset might be required on the initial login or use. Make sure the account that will be used to create the connection has a definitive password (it is not pending a password update).

    Fill the form with information from Snowflake:

    • Database Name with name of the database that you are connecting to
    • Username and Password with the credentials for the user
    • Display Name with the name to be used on Preset for this connection
    • Account with the account identifier. Refer to the Snowflake documentation to identify the structure to your Cloud Provider and Region.
    • Warehouse and Role with the relevant warehouse (e.g., COMPUTE_WH) and role (e.g., ACCOUNTADMIN) that should be used when running queries.

    Snowflake Dynamic Form.png

    Click on Connect to create the connection.

    At this stage, you can optionally configure Advanced options, if needed. To learn more, please see Advanced Connection Settings.

    GSheet_Public_Advanced_Step_3_of_3

    To finalize the connection, select Finish. Great job!


    Connect using an encrypted key pair

    It's also possible to connect to Snowflake authenticating with an encripted key pair instead of a password, using a SQLAlchemy String URI.
    image.png

    In the Display Name field, enter a meaningful name for the new connection to Snowflake.
    In the SQLAlchemy URI field, customize your URI connection string using the following syntax:

    snowflake://<Username>@<Account>/<Database>?role=<Role>&warehouse=<Warehouse>
    Plain text

    image.png

    Then, navigate to the ADVANCED tab, expand the Security section and use add below JSON structure to the SECURE EXTRA field:

    {
        "auth_method": "keypair",
        "auth_params": {
            "privatekey_body": "Your Private Key",
            "privatekey_pass":"Your Private Key Password"
        }
    }
    JSON

    Note that the privatekey_body should receive a single line value, so replace line breaks with \n. For example:

    {
         "auth_method": "keypair",
         "auth_params": {
             "privatekey_body": "-----BEGIN ENCRYPTED PRIVATE KEY-----\n...\n...\n-----END ENCRYPTED PRIVATE KEY-----",
             "privatekey_pass":"my_password"
        }
     }
    JSON

    Navigate back to the Basic tab, and click on Test Connection to validate the information is correct. Lastly, click on Connect to save the connection.


    Connect using OAuth

    The OAuth integration allows Preset to run queries while impersonating users. When implemented, users are prompted to grant consent to the integration, which generates a token to be used for each user. Refer to the Snowflake documentation for additional context.

    Setting up the OAuth integration in Snowflake

    Below SQL query can be used to set up an OAuth integration in Snowflake named PRESET:

    CREATE SECURITY INTEGRATION PRESET
        TYPE = OAUTH
        ENABLED = TRUE
        OAUTH_CLIENT = CUSTOM
        OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
        OAUTH_USE_SECONDARY_ROLES = 'IMPLICIT'
        OAUTH_ALLOW_NON_TLS_REDIRECT_URI = TRUE
        OAUTH_REDIRECT_URI = 'https://${WORKSPACE}/api/v1/database/oauth2/';
    SQL

    Replace ${WORKSPACE} with the Workspace domain URL you'll connect to Snowflake. The structure is https://${WorksapceSlug}.${WorkspaceRegion}.app.preset.io.

    Once the integration is created, run below SQL query to get the required information:

    SELECT
    JSON_EXTRACT_PATH_TEXT(SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('PRESET'), 'OAUTH_CLIENT_ID') as "Client ID",
    JSON_EXTRACT_PATH_TEXT(SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('PRESET'), 'OAUTH_CLIENT_SECRET') as "Client Secret";
    SQL

    Fill the Snowflake connection form in Preset:

    • Database Name with name of the database that you are connecting to
    • Display Name with the name to be used on Preset for this connection
    • Account with the account identifier. Refer to the Snowflake documentation to identify the structure to your Cloud Provider and Region.
    • Warehouse with the warehouse that should be used for the connection.

    Then, expand the OAuth2 client information section and provide:

    • The Client ID and Client Secret values retrieved with the SQL query
    • Use https://${ACCOUNT-URL}/oauth/authorize as the Authorization Request URI
    • Use https://${ACCOUNT-URL}/oauth/token-request as the Token Request URI
    • Use refresh_token in the Scope field. Note that you can also optionally limit the role that's used by the integration.

    Snowflake OAuth form.png

    Click on the Connect button. If the information is correct, the connection is created and you are redirected to the Advanced tab. Expand the Security section and enable the Impersonate logged in user option, and finally click on FINISH.

    To test the integration:

    1. Navigate to SQL > SQL Lab.
    2. Select the Snowflake connection in the Database dropdown.
    3. You should see an Authorization needed error. Click on See more.
    4. Click on the provide authorization hyperlink to be reditected to Snowflake. Authenticate using your Snowflake credentials and grant consent for the integration.
    5. After authentication, you're redirected back to Preset. Refresh the page.

    Queries should now be properly authenticated with your OAuth token. Note that this Authorization needed prompt will be displayed to all users the first time they interact with this database (either in SQL Lab or via charts).


    Was this article helpful?