Skip to main content

Symon.AI Help Center

Snowflake

Abstract

Connect your Symon.AI and Snowflake accounts to export your data to your Snowflake account.

Connect your Symon.AI and Snowflake accounts to export your data to your Snowflake account. This connection allows Symon.AI 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, Symon.AI 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

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

  2. 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 Symon.AI

    For example, if you wanted to create a schema for Symon.AI to use, use the following file format:

    CREATE OR REPLACE FILE FORMAT VARICENT_ELT_CSV ESCAPE = '\\' FIELD_OPTIONALLY_ENCLOSED_BY = '\"';



Create and update tables

Symon.AI 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 Symon.AI 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

  1. Open your Snowflake instance.

  2. 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;

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

      Table 27. Snowflake OAuth variables

      Variable

      Description

      Name

      Enter the name of the security integration.

      Redirect URI

      Enter the import redirect URI of the Symon.AI 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.

  3. 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 Symon.AI and Snowflake. Generate a new connection or use an existing one.

Creating a new connection

  1. In Symon.AI, go to your pipe.

  2. Add the Export tool.

  3. In the Export tool configuration section, click the Connection drop-down.

  4. From the Connection drop-down, click + New connection.

  5. Create the new Snowflake connection for the Export tool.

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

  6. Click Next.

    You are sent back to your pipe page and the Export tool configuration is opened.

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

  8. Click Export now.

Using your existing Snowflake connection

  1. In Symon.AI, go to your pipe.

  2. Add the Export tool.

  3. In the Export tool configuration, click the Connection drop-down.

  4. Search for and select your existing Snowflake export connection.

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

  6. Click Export now.