Snowflake – Setup

You need the following information to set up a Snowflake Connector within Cyclr:

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

To obtain those details, follow the instructions below.

Remote Setup in Snowflake

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 Snowflake’s 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 an account identifier to authenticate the Snowflake Connector in Cyclr. The account identifier is the subdomain in the 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“.

Partner Setup in Cyclr Console

To set up Snowflake 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 entry.
  4. Select the Pencil button.
  5. Select the Settings tab.
  6. Enter the below values:
    • Client ID – The client ID of the Snowflake account.
    • Client Secret – The client secret of the Snowflake account.

      Leaving these values blank will allow you to use different settings each time you install a Snowflake Connector.
  7. Select Save Changes.

NOTE: If you leave these values blank, they must be provided each time the Connector is installed.

Cyclr Connector Installation

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

PropertyDescription
Account IdentifierThe account identifier of the 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 Connector Methods within the “Utilities” Method Category to list accessible warehouses, databases, and schemas.

Additional Information

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” Method

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.