Snowflake – Setup

Introduction

This guide explains how to obtain authentication details from Snowflake, as well as how to install a Snowflake Connector.

Setup & Authentication

Overview

Cyclr Partner setup

• Leave the Snowflake entry in your Cyclr Console’s Application Connector Library blank, as the Client ID and Client Secret values should be provided by each of your customers when installing the Connector.

Your customer’s setup

• Obtain the Snowflake Account Identifier for the data source being connected to.
• Decide which type of authentication to use, as Cyclr’s Snowflake Connector supports the following:
  • OAuth 2.0 Authorization Code
  • Key pair authentication

Remote Setup in Snowflake – performed by your customer

Within Snowflake, your customer must:

1. Obtain their Account Identifier.
2. Perform appropriate setup depending on the authentication type chosen:
   • OAuth 2.0 Authorization Code
   • Key pair authentication

Obtaining the Account Identifier

The account identifer is the subdomain of the Snowflake account/server URL which is provided when the account is created. Snowflake’s documentation on account identifiers can be found here.

As an example, if this were the Snowflake account/server URL:
PXYHYKG-OX56300.snowflakecomputing.com
The account identifier for it would be:
PXYHYKG-OX56300

OAuth 2.0 Authorization Code Setup

To install a Snowflake Connector using OAuth 2.0 Authorization Code authentication, follow these steps to obtain a Client ID and Client Secret from within Snowflake:

1. Create a Security Integration

For additional information, see Snowflake’s guide on how to create a security integration.

The following Snowflake command 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;

The OAUTH_REDIRECT_URI value above must be set to your Cyclr Partner Console’s Callback URL.

Warning: Snowflake does not allow you to refresh tokens through its API. The user must manually 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 FAQ on OAuth.

2. Obtain a Client ID and Client Secret

Having created a Snowflake Security Integration in the previous step, execute this Snowflake command to obtain a Client ID and Client Secret:

select system$show_oauth_client_secrets('CYCLR_OAUTH');

The security integration name provided previously of “cyclr_oauth” is then converted to uppercase by Snowflake so you must enter “CYCLR_OAUTH” for this command.

For more information on how to obtain the client ID and client secret, see the Snowflake documentation.

Key pair authentication Setup

To install a Snowflake Connector using Key pair authentication, the following values are required:

• The public key fingerprint and private key assigned to a Snowflake user.

Snowflake’s documentation on key pair authentication can be found here.

Partner Setup in Cyclr Console

Ensure the Snowflake entry in your Cyclr Console’s Application Connector Library is left blank, as the Client ID and Client Secret values should be provided by each of your customers when installing the Connector.

The Client ID and Client Secret values relate to each customer’s Snowflake setup so should be left blank within your Console.

Cyclr Connector Installation

When installing a Snowflake Connector, the first option displayed is the Authentication Type to use, as described previously in this guide:

• OAuth 2.0 Authorization Code
• Key pair authentication

OAuth 2.0 Authorization Code Installation

Provide the following values when installing a Snowflake Connector:

Property	Description
Client ID	The client ID of the Snowflake account. Required
Client Secret	The client secret of the Snowflake account. Required
Account Identifier	The Account Identifier of the Snowflake account. Required
Warehouse	The Snowflake warehouse to process queries with.
Database	The Snowflake database to access data in.
Schema	The Snowflake schema to access data in.
Role	Role to use when executing the statement. The value in this field is case-sensitive. If you omit this field, the SQL API uses the value of the DEFAULT_ROLE property of the user.

You can install the Connector without providing a Warehouse, Database, and Schema and use Methods in the “Utilities” Category to list the accessible warehouses, databases, and schemas.

Key pair authentication Installation

Provide the following values when installing a Snowflake Connector:

Property	Description
Account Identifier	The Account Identifier of your Snowflake account. Required
Warehouse	The Snowflake warehouse to process queries with.
Database	The Snowflake database to access data in.
Schema	The Snowflake schema to access data in.
Role	Role to use when executing the statement. The value in this field is case-sensitive. If you omit this field, the SQL API uses the value of the DEFAULT_ROLE property of the user.
User	The user to authenticate with. Required
Public Key Fingerprint	The public key fingerprint of the key pair. This should NOT include the hash function prefix, such as “SHA256:“. Required
Private Key	The private key of the key pair. Required

You can install the Connector without providing a Warehouse, Database, and Schema and use Methods in the “Utilities” Category to list the 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;
}

5. 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.
6. 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.