Snowflake

Varicent ELT Help Center

Snowflake

Abstract

Connect your Varicent ELT and Snowflake accounts to export your data to your Snowflake account.

Connect your Varicent ELT and Snowflake accounts to export your data to your Snowflake account. This connection allows Varicent ELT to write data to Snowflake.

Set up the connector to be available for use with the Export tool. You only need to set up the connection once. After the connection is set up, Varicent ELT can write updates and inserts into Snowflake.

Before you begin

Ensure that the necessary privileges are granted to the role to write to the table. If the role is omitted in the Snowflake connection, the default role of the user is used. For more information, see Configuring access control.

	New table in new schema	New table in existing schema	Update existing table
`USAGE` on warehouse	Yes	Yes	Yes
`USAGE` on database	Yes	Yes	Yes
`USAGE` on schema	No	Yes	Yes
`USAGE` on file format	Yes	Yes	Yes
`CREATE SCHEMA` on database	Yes	No	No
`CREATE TABLE` on schema	No	Yes	No
`OWNERSHIP` on table	No	No	Yes

Snowflake file format

To load data into a Snowflake table, you need to use file format. File format is the Snowflake object that describes the format of a file. A file format object requires the following properties:

type = csv
escape = \
field_optionally_enclosed_by = "

Create file format

Ensure that the user has the necessary privileges to create file format:
- USAGE on warehouse
- USAGE on database
- USAGE on schema
- CREATE FILE FORMAT on schema
Note
You don't need to create the file format object each time, but ensure that there is one valid file format object and the role has access to it within the database used for the connection.
Create the file format in Snowflake for a .csv file:
CREATE FILE FORMAT {database}.{schema}.{file_format_name}
TYPE = 'CSV' ESCAPE='\\' FIELD_OPTIONALLY_ENCLOSED_BY='“';
Example 1. Create a schema for Varicent ELT
For example, if you wanted to create a schema for Varicent ELT to use, use the following file format:
CREATE OR REPLACE FILE FORMAT VARICENT_ELT_CSV ESCAPE = '\\' FIELD_OPTIONALLY_ENCLOSED_BY = '\"';

Create and update tables

Varicent ELT only supports creating and updating tables with a primary key. For duplicate rows with the same primary key value, the latest row record is used. Any rows that have been updated during an export that fails, cannot be reverted. For more information, see Snowflake primary keys.

Using OAuth authentication

If you want to use the OAuth authentication method for connecting Varicent ELT to your Snowflake account, you must first create a security integration object.

Important

Only users with the ACCOUNTADMIN role can create a security integration object. Snowflake does not allow the following roles to log in through OAuth: ACCOUNTADMIN, SECURITYADMIN, and ORGADMIN. Learn more...

Creating a security integration object

Open your Snowflake instance.

In Snowflake, enter the following query to create the security integration object:

CREATE OR REPLACE SECURITY INTEGRATION <name>
  TYPE=OAUTH
  OAUTH_CLIENT=CUSTOM
  OAUTH_REDIRECT_URI='<redirect URI>' 
  OAUTH_CLIENT_TYPE='CONFIDENTIAL'
  OAUTH_REFRESH_TOKEN_VALIDITY = <1 ~ 90 days in seconds>
  OAUTH_ISSUE_REFRESH_TOKENS=TRUE
  ENABLED=TRUE;

Replace the following variables with the correct information for your instance:

Table 29. Snowflake OAuth variables

Variable	Description
Name	Enter the name of the security integration.
Redirect URI	Enter the import redirect URI of the Varicent ELT application. For NA: `https://app.symon.ai/importRedirect` For EU: `https://eu.symon.ai/importRedirect`
OAUTH_REFRESH_TOKEN_VALIDITY	Enter the integer that specifies how long the refresh token is valid for in seconds. This value is used to expire the refresh token periodically.

Variable

Description

Name

Enter the name of the security integration.

Redirect URI

Enter the import redirect URI of the Varicent ELT application.

For NA: https://app.symon.ai/importRedirect

For EU: https://eu.symon.ai/importRedirect

OAUTH_REFRESH_TOKEN_VALIDITY

Enter the integer that specifies how long the refresh token is valid for in seconds. This value is used to expire the refresh token periodically.

Learn more about creating a security integration.

Caution

Re-running the query with the same security integration name will replace the existing security object. This action will rotate the existing client id and client secret. Any connections tied to the security integration object are no longer valid and the user must edit the Snowflake connection tied to the security integration with a new client id and client secret pair.

Enter the following query to get the client id and secret:
```
SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('<security integration name in capital letters>');
```
This query returns values for OAUTH_CLIENT_ID, OAUTH_CLIENT_SECRET, OAUTH_CLIENT_SECRET_2. You can use either OAUTH_CLIENT_SECRET or OAUTH_CLIENT_SERCRET_2 as your client secret.
Note
If the refresh token expires, you must edit the connection and reauthorize. The Client ID and Client Secret can be retrieved with the query in Step 3.

Create the connection

Create the connection between Varicent ELT and Snowflake. Generate a new connection or use an existing one.

Creating a new connection

In Varicent ELT, go to your pipe.
Add the Export tool.
In the Export tool configuration section, click the Connection drop-down.
From the Connection drop-down, click + New connection.

Create the new Snowflake connection for the Export tool.

On the Connect to your Data page, enter the following information:

Field	Description
Connector name	The name of the connection.
Snowflake URL	The URL to connect to Snowflake, such as `https://<variable>.snowflakecomputing.com`.
Snowflake role	The role in Snowflake.
Warehouse	The name of the Snowflake warehouse your data runs out of.
Database name	The name of the Snowflake database.
Authentication method	Select either the Basic standard authentication method or OAuth authentication method
Basic standard authentication method	Enter your Snowflake username, such as `admin` and password.
OAuth authentication method	Enter your OAuth Client ID and Client Secret to connect to your Snowflake connector. Note Prior to connecting with OAuth, set up your Snowflake environment with the Security Integration Object.
Use Storage integration	Click the checkbox to use Storage integration. For more information, see Step 7.

Click Next.
If you want use Storage integration, you must create or replace your storage integration in Snowflake. For more information, see: Snowflake storage integration.

In the Export tool configuration panel, enter the following information:

Field	Description
Table name	The name of the table to create or update.
Schema	The schema where the updated table belongs to, or the intended schema for new tables.
File format	The drop-down list of valid file formats where the user can choose from. This list only displays the valid formats that the role has access to. Note If you encounter an error, ensure that you have the file format set up correctly. For more information, see Create file format.
Key columns	The drop-down list of columns to select primary key columns from.

Click Export now.

Snowflake storage integration

If you enable to use the storage integration in Snowflake during configuration, you must create or replace the integration within Snowflake.

Important

Only users with the ACCOUNTADMIN role or a role with the global CREATE INTEGRATION privilege can execute this SQL command can create a storage integration object. For more information on Snowflake storage integration, see: Configuring a Snowflake storage integration to access Amazon S3.

Creating storage integration object

Open your Snowflake instance.

In Snowflake, enter and run the following query to create the storage integration object:

CREATE STORAGE INTEGRATION <integration_name>
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = '<iam_role_arn>'
  STORAGE_ALLOWED_LOCATIONS = ('<protocol>://<bucket>/<path>/', '<protocol>://<bucket>/<path>/')

Run the following query to grant USAGE privilege on storage integration to the Snowflake role used for this connection:
```
GRANT USAGE ON INTEGRATION <INTEGRATION NAME> TO ROLE <ROLE NAME>
```
Run the following query to retrieve the STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID:
```
DESCRIBE INTEGRATION <INTEGRATION_NAME>
```
In Varicent ELT, enter your information on the Connect to your Database page, in the following input fields:
Table 30. Snowflake input table fields
Field
Description
Principal
Enter the name of the STORAGE_AWS_IAM_USER_ARN.
External ID
Enter the name of the STORAGE_AWS_EXTERNAL_ID.
Storage integration name
Enter the name of your storage integration.

Field	Description
Principal	Enter the name of the `STORAGE_AWS_IAM_USER_ARN`.
External ID	Enter the name of the `STORAGE_AWS_EXTERNAL_ID`.
Storage integration name	Enter the name of your storage integration.

Using your existing Snowflake connection

In Varicent ELT, go to your pipe.
Add the Export tool.
In the Export tool configuration, click the Connection drop-down.
Search for and select your existing Snowflake export connection.

In the Export tool configuration panel, enter the following information:

Field	Description
Table name	The name of the table to create or update.
Schema	The schema where the updated table belongs to, or the intended schema for new tables.
File format	The drop-down list of valid file formats where the user can choose from. This list only displays the valid formats that the role has access to.
Key columns	The drop-down list of columns to select primary key columns from.

Click Export now.

Was this helpful?

Would you like to provide feedback? Just click here to suggest edits.

Varicent ELT Help Center

Snowflake

Before you begin

Snowflake file format

Note

Create and update tables

Using OAuth authentication

Important

Caution

Note

Create the connection

Note

Note

Snowflake storage integration

Important

Search results