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.

To obtain those details, follow the instructions below.

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:

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 Partner Console’s Callback URL.

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:

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:

  1. Go to your Cyclr Console.
  2. Select Connectors > Application Connector Library at the top of the page.
  3. Use the search box to find the Snowflake connector.
  4. Select the Setup Required icon.
  5. Enter the below values, omitting this step will allow you to use different settings for each account on installation:
    • Client ID – The client ID of your Snowflake account.
    • Client Secret – The client secret of your Snowflake account.
  6. Select Save Changes.

Account setup

Cyclr asks for the following values when you install a Snowflake Connector:

PropertyDescription
Client IDThe client ID of your Snowflake account, if you did not enter this in step 5 above.
Client SecretThe client secret of your Snowflake account, if you did not enter this in step 5 above.
Account IdentifierThe account identifier of your Snowflake account.
WarehouseThe Snowflake warehouse to process queries with.
DatabaseThe Snowflake database to access data in.
SchemaThe 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:

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

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

Typically within Cyclr, parameters such as the Where parameter would normally be able to be set to include the {{LastSuccessfulRunDate}} Cyclr inline Mergefield to be able to pass in the last successful run date, e.g.:

CREATED_AT > '{{LastSuccessfulRunDate}}'

Becuase of how the Snowflake API performs paging, you can’t use the {{LastSuccessfulRunDate}} inline Mergefield in this way.

To workaround this, you can use the Table Rows > List Rows method in a cycle:

  1. Select Step Setup.
  2. Set Skip Pre-POST Request? to True.
  3. Set Where to Ignore.
  4. Enter the following script on the Step’s Advanced Settings > Script area:
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;
}
  1. Update line 9 of the script with the appropriate where clause.
    “last_successful_run_date” can be used to include the Cyclr Step’s last successful run date value.
  2. 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.

  1. 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.
  2. 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.
  3. 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.