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.

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.

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:1717/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"
      }
}'