Search docs.molo17.com

    Snowflake logo Snowflake agent

    Target Snowflake

    Prerequisites

    To have Gluesync working on your Snowflake instance you will need to have:

    • Valid user credentials with permission to read, and write to the target tables and respective database;

      • A valid RSA key pair for the user;

    • In case of MFA enabled, the user must have generated a Programmatic Access Token, this must be then used as user password;

    • Warehouse name.

    Generating a Programmatic Access Token

    To generate a Programmatic Access Token, follow the instructions in the Snowflake documentation by looking for Programmatic Access Token or go to the user Authentication settings in the Snowflake console and generate a Programmatic Access Token.

    Programmatic Access Token

    Creating a Snowflake user with the required permissions

    If you don’t have a user with the required permissions, you can create one by running the following SQL commands:

    USE ACCOUNTADMIN;
    
    CREATE WAREHOUSE IF NOT EXISTS INGEST;
    CREATE ROLE IF NOT EXISTS INGEST;
    GRANT USAGE ON WAREHOUSE INGEST TO ROLE INGEST;
    GRANT OPERATE ON WAREHOUSE INGEST TO ROLE INGEST;
    CREATE DATABASE IF NOT EXISTS INGEST;
    USE DATABASE INGEST;
    CREATE SCHEMA IF NOT EXISTS INGEST;
    USE SCHEMA INGEST;
    GRANT OWNERSHIP ON DATABASE INGEST TO ROLE INGEST;
    GRANT OWNERSHIP ON SCHEMA INGEST.INGEST TO ROLE INGEST;
    
    CREATE USER INGEST PASSWORD='<REDACTED>' LOGIN_NAME='INGEST' MUST_CHANGE_PASSWORD=FALSE, DISABLED=FALSE, DEFAULT_WAREHOUSE='INGEST', DEFAULT_NAMESPACE='INGEST.INGEST', DEFAULT_ROLE='INGEST';
    GRANT ROLE INGEST TO USER INGEST;
    SET USERNAME=CURRENT_USER();
    GRANT ROLE INGEST TO USER IDENTIFIER($USERNAME);

    Assign the RSA key pair to the user:

    openssl genrsa 4096 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
    openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
    echo "ALTER USER INGEST SET RSA_PUBLIC_KEY='`cat ./rsa_key.pub`';"

    Run the SQL from the output to set the RSA_PUBLIC_KEY for the INGEST user, like:

    ALTER USER INGEST SET RSA_PUBLIC_KEY='xyz-----BEGIN PUBLIC KEY-----
    xyz==
    -----END PUBLIC KEY-----';

    Use the private key, rsa_key.p8, for the user in the agent configuration.

    Setup via Web UI

    • Hostname / IP Address: DNS record or IP Address of your Snowflake tenant;

    • Database name: Name of your target database;

    • Username: Username with read & write access to the target tables;

    • Password: Password belonging to the given username;

    • RSA key: the RSA .p8 private key.

    Custom properties

    • Warehouse: (defaults to COMPUTE_WH) The warehouse name where your Snowflake tenant belongs to;

    Specific configuration

    This agent has no specific configuration properties.

    Updates with bulk operations during CDC

    The Snowflake agent writes the changes of a single-table entity through a staging table, whatever the bulk operations settings of the entity. An update is not applied as an UPDATE statement: the agent deletes the existing row from the target table and inserts the new version of it. Multi-table entities are written with UPDATE statements.

    For the target table this has the following effects:

    • A Snowflake stream on the target table reports a delete and an insert instead of an update;

    • Columns that Gluesync does not write, such as a column with a default value, are set again as for a new row.

    Setup via Rest APIs

    Here following an example of calling the Core Hub’s Rest API via curl to setup the connection for this Agent.

    Connect the agent

    curl --location --request PUT 'http://core-hub-ip-address/pipelines/{pipelineId}/agents/{agentId}/config/credentials' \
    --header 'Content-Type: application/json' \
    --header 'Authorization: ••••••' \
    --data '{
            "hostCredentials": {
            "connectionName": "myAgentNickName",
            "host": "XYZ-123.snowflakecomputing.com",
            "port": 443,
            "databaseName": "db_name",
            "username": "",
            "password": "",
            "maxConnectionsCount": 100,
            "enableTls": true,
            "certificatePath": "/myPath/cert.p8"
          },
          "customHostCredentials": {
            "warehouse": "COMPUTE_WH"
          }
    }'