Snowflake

Snowflake setup

You need the following information to set up the Snowflake connector in Cyclr:

The client ID and client secret obtained by creating a security integration in Snowflake.
The account identifier associated with your Snowflake account.

Create a security integration

You need to create a security integration in Snowflake to get the client ID and client secret. For more information., see Snowflake's guide on how to create a security integration in the Snowflake console. The following example creates a security integration called cyclr_oauth that issues a refresh token once every 90 days:

CODE

create or replace security integration
    cyclr_oauth
    TYPE = OAUTH
    ENABLED = TRUE
    OAUTH_CLIENT = CUSTOM
    OAUTH_CLIENT_TYPE = CONFIDENTIAL
    OAUTH_REDIRECT_URI = 'https://example.cyclr.com/connector/callback'
    OAUTH_ISSUE_REFRESH_TOKENS = TRUE
    OAUTH_REFRESH_TOKEN_VALIDITY = 7776000;

Warning: Snowflake does not allow you to refresh tokens with the API. The user needs to log in again once the refresh token expires. To extend the duration of the refresh token, you can contact your Snowflake account administrator. For more information, see Snowflakes documentation on OAuth.

Note: The OAUTH_REDIRECT_URI field needs to point to the OAuth redirect URL of your Cyclr account. The URL has the format: https://{Your Cyclr service domain e.g. app-h.cyclr.com}/connector/callback.

Get the client ID and client secret

You need a client ID and client secret to authenticate the Snowflake connector in Cyclr. Before you can get these credentials, you need to create a security integration. For more information on how to get the client ID and client secret, see the Snowflake documentation. The following example gets the client ID and client secret for the security integration created in the previous section:

CODE

select system$show_oauth_client_secrets('CYCLR_OAUTH');

Note: The integration name cyclr_oauth is converted to upper case and you need to enter CYCLR_OAUTH instead for this request.

Getting the account identifier

You need your account identifier to authenticate the Snowflake connector in Cyclr. Your account identifier is the subdomain in your Snowflake account URL provided on account creation. This is the same URL that you use to log in to Snowflake. For example, the account URL https://AB12345.europe-west1.gcp.snowflakecomputing.com has the account identifier AB12345.europe-west1.gcp.

Cyclr setup

To set up your Snowflake connector within your Cyclr console:

Go to your Cyclr Console.
Select Connectors > Application Connector Library at the top of the page.
Use the search box to find the Snowflake connector.
Select the Setup Required icon.
Enter the below values, omitting this step will allow you to use different settings for each account on installation:ValueDescriptionClient IDThe client ID of your Snowflake account.Client SecretThe client secret of your Snowflake account.
Select Save Changes.

Account setup

Cyclr asks for the following values when you install the Snowflake connector within an account:

Value	Description
Client ID	The client ID of your Snowflake account, if you did not enter this in step 5 above.
Client Secret	The client secret of your Snowflake account, if you did not enter this in step 5 above.
Account Identifier	The account identifier of your Snowflake account.
Warehouse	The Snowflake warehouse to process queries with.
Database	The Snowflake database to access data in.
Schema	The Snowflake schema to access data in.

Note: You can install the connector without providing a warehouse, database, and schema and use methods in the Utilities category to list accessible warehouses, databases, and schemas.

Create custom Table Rows objects

You can create custom Table Rows objects to have methods specific to a Snowflake table. These objects can automatically populate request and response fields for methods within the custom object. To create a custom object:

Go to the Edit Connector page for the Snowflake connector.
Under the Methods & Fields heading, locate the Table Rows category and select it to expand.
Select the red Copy this Category to create a Custom Object Category icon.
Use the dropdown menu to select the Snowflake table name.
Select Copy.

Use last successful run date in a Table Rows > List Rows inline merge field

The Where parameter can normally include the {{LastSuccessfulRunDate}} inline merge field to include the last successful run date:

CODE

CREATED_AT > '{{LastSuccessfulRunDate}}'

Due to how the Snowflake API paginates data, you can't use the {{LastSuccessfulRunDate}} inline merge field in this way. To work around this, you can use the Table Rows > List Rows method in a cycle:

Select Step setup.
Set Skip Pre-POST Request? to True.
Set Where to Ignore.

Enter the following script:

CODE

function before_action() {
    prePostRequest();
    return true;
}

function prePostRequest() {
    if (!prePostRequestCalledTrue()) {
        setRole();
        method_request_headers.where = `CREATED_AT > '${last_successful_run_date}'`;
        handlePrePostRequest();
        setIndex();
        action_data.prePostRequestCalled = true;
    }
}

function prePostRequestCalledTrue() {
    return action_data != null && action_data.prePostRequestCalled != null && action_data.prePostRequestCalled === true;
}

Update line 9 of the script with the appropriate where clause. last_successful_run_date can be used to include the last successful run date.
Start the cycle.

Use table joining in a Table Rows > List Rows

When joining tables using snowflake, using the List Rows method, 3 parameters must be used; these are: joinType, joinTable and joinOn. The Table Rows category must also be correctly copied before carrying out this function.

Documentation for join functions in snowflake can be found here.

joinType - This parameter dictates the type of table join to use. Please refer to the documentation above to get a more detailed description of each joining method.
joinTable - This is the target table of the join. Select the table you would like to join with the table currently selected in the copyable category.
joinOn - This is the conditional expression to select which rows from the two sides of the join that are considered to match.

There is also a joinFields parameter. If selectAllFields is set to blank or false, this parameter must be used to select the fields you would like to retrieve from the joinTable selected. This should be in a comma-separated list format.

Any fields you would like to retrieve from the joinTable must be manually mapped by the user, regardless of the selectAllFields and joinFields values.