What Is Snowflake?

Snowflake is a cloud-based data storage and analytics service that provides rapid, easy-to-work-with access to extensive amounts of data. Clients can easily send data to data to one location for storage or additional evaluation.

What are the benefits of integrating MetaRouter with Snowflake?

With the Snowflake integration clients can easily send any data/ payload they want to the data storage company. This integration allows clients to move and store their data in whichever data storage company they wish.

Connecting to Snowflake S3

NOTE: This is for the Snowflake S3 setup. For GCS setup, scroll down for GCS specific setup instructions.

Getting Started - From Your Snowflake Account

Snowflake dashboard

Go to Worksheets, create a new SQL worksheet, and add the following:

USE ROLE SECURITYADMIN;

CREATE USER mr_user;
ALTER USER mr_user SET PASSWORD='ADD_NEW_PASSWORD_HERE';
ALTER USER mr_user SET RSA_PUBLIC_KEY='ADD_RSA_PUBLIC_KEY_HERE';

USE ROLE SYSADMIN;

CREATE DATABASE METAROUTER_MR_STARTER_KIT_TESTING;
USE DATABASE METAROUTER_MR_STARTER_KIT_TESTING;

CREATE SCHEMA metarouter;
USE SCHEMA METAROUTER_MR_STARTER_KIT_TESTING.metarouter;

CREATE TABLE METAROUTER_MR_STARTER_KIT_TESTING.metarouter.events(raw VARIANT);

CREATE WAREHOUSE METAROUTER_MR_STARTER_KIT_TESTING_WAREHOUSE WAREHOUSE_SIZE=xsmall AUTO_SUSPEND=300;

CREATE STAGE events_stage
URL='s3://ADD_BUCKET_NAME_HERE/'
CREDENTIALS=(AWS_KEY_ID='ADD_AWS_KEY_ID_HERE' AWS_SECRET_KEY='ADD_AWS_SECRET_KEY_HERE');

CREATE PIPE METAROUTER_MR_STARTER_KIT_TESTING.metarouter.events_pipe
AS COPY INTO METAROUTER_MR_STARTER_KIT_TESTING.metarouter.events
FROM @METAROUTER_MR_STARTER_KIT_TESTING.metarouter.events_stage
FILE_FORMAT=(type='JSON');

USE ROLE SECURITYADMIN;

CREATE ROLE MR_USER;
GRANT ROLE MR_USER TO USER mr_user;

USE ROLE SYSADMIN;

GRANT ALL PRIVILEGES ON PIPE METAROUTER_MR_STARTER_KIT_TESTING.metarouter.events_pipe
TO ROLE MR_USER;
GRANT ALL PRIVILEGES ON SCHEMA METAROUTER_MR_STARTER_KIT_TESTING.metarouter
TO ROLE MR_USER;
GRANT USAGE ON DATABASE METAROUTER_MR_STARTER_KIT_TESTING
TO ROLE MR_USER; 

USE ROLE SECURITYADMIN;

ALTER USER mr_user SET
DEFAULT_WAREHOUSE='METAROUTER_MR_STARTER_KIT_TESTING_WAREHOUSE'
DEFAULT_ROLE='MR_USER';

Make sure to replace all ADD_XXXXXXXXX_HERE values with your values:

ADD_NEW_PASSWORD_HERE — set a password for the newly created user
ADD_RSA_PUBLIC_KEY_HERE — follow this link to generate your private and public keys
ADD_BUCKET_NAME_HERE — S3 bucket name
ADD_AWS_KEY_ID_HERE — your AWS Key ID
ADD_AWS_SECRET_KEY_HERE — your AWS Secret Key

Run All commands above.

Grabbing “account” value for the MetaRouter connection parameter:

account
The account value is defined by your Snowflake <account id>-<organisation id> in lowercase.
This information can be found
1. Under the account selector (bottom left)
2. Your org URL
  
  For this example, the account value will be rnisshj-mz94630.

Getting Started - From Your MetaRouter Account

Adding a Snowflake S3 integration.

From the integration library, add a Snowflake integration. Then, fill out the Connection Parameters:

Connection Parameter	Description
user	The user name from your Snowflake `CREATE USER` command.
password	The password from your Snowflake `.. SET PASSWORD`.. command.
account	The `account` value is defined by your Snowflake `<account id>-<organisation id>` in lowercase.
database	The user name from your Snowflake `CREATE DATABASE` command.
warehouse	The user name from your Snowflake `CREATE WAREHOUSE` command.
schema	The schema from your Snowflake `CREATE SCHEMA` command.
snowpipe	The snowpipe from your Snowflake `CREATE PIPE` `[warehouse].[schema].[snowpipe]`
privateKey	The private key you generated while creating the public key from the previous step.
stage - s3	`region` - The region of your AWS bucket. `bucket` - Your S3 bucket name. `access key` - Your AWS Access Key ID. `secret_key` - Your AWS Secret Key ID.

Connecting to Snowflake GCS

NOTE: This is for the Snowflake GCS setup. For S3 setup, scroll up for S3 specific setup instructions.

Getting Started - From Your Snowflake Account

Snowflake dashboard

Go to Worksheets, create a new SQL worksheet, and add the following:

USE ROLE SECURITYADMIN;

CREATE USER mr_user;
ALTER USER mr_user SET PASSWORD='ADD_NEW_PASSWORD_HERE';
ALTER USER mr_user SET RSA_PUBLIC_KEY='ADD_RSA_PUBLIC_KEY_HERE';

USE ROLE SYSADMIN;

CREATE DATABASE METAROUTER_MR_STARTER_KIT_TESTING;
USE DATABASE METAROUTER_MR_STARTER_KIT_TESTING;

CREATE SCHEMA metarouter;
USE SCHEMA METAROUTER_MR_STARTER_KIT_TESTING.metarouter;

CREATE TABLE METAROUTER_MR_STARTER_KIT_TESTING.metarouter.events(raw VARIANT);

CREATE WAREHOUSE METAROUTER_MR_STARTER_KIT_TESTING_WAREHOUSE WAREHOUSE_SIZE=xsmall AUTO_SUSPEND=300;

USE ROLE ACCOUNTADMIN;
CREATE STORAGE INTEGRATION gcs_integration
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'GCS'
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ('gcs://ADD_BUCKET_NAME_HERE/');

-- USE ROLE ACCOUNTADMIN;
-- DESC STORAGE INTEGRATION gcs_integration;

USE ROLE ACCOUNTADMIN;
CREATE STAGE my_gcs_stage
  URL = 'gcs://ADD_BUCKET_NAME_HERE/'
  STORAGE_INTEGRATION = gcs_integration
  FILE_FORMAT = (type='JSON');

create or replace pipe METAROUTER_MR_STARTER_KIT_TESTING.metarouter.events_pipe auto_ingest=false as COPY INTO METAROUTER_MR_STARTER_KIT_TESTING.metarouter.events
FROM @METAROUTER_MR_STARTER_KIT_TESTING.metarouter.my_gcs_stage
FILE_FORMAT=(type='JSON')
ON_ERROR = 'skip_file';

USE ROLE SECURITYADMIN;

CREATE ROLE MR_USER;
GRANT ROLE MR_USER TO USER mr_user;

USE ROLE ACCOUNTADMIN;
GRANT ALL PRIVILEGES ON PIPE METAROUTER_MR_STARTER_KIT_TESTING.metarouter.events_pipe
TO ROLE MR_USER;
GRANT ALL PRIVILEGES ON SCHEMA METAROUTER_MR_STARTER_KIT_TESTING.metarouter
TO ROLE MR_USER;
GRANT USAGE ON DATABASE METAROUTER_MR_STARTER_KIT_TESTING
TO ROLE MR_USER; 

USE ROLE SECURITYADMIN;

ALTER USER mr_user SET
DEFAULT_WAREHOUSE='METAROUTER_MR_STARTER_KIT_TESTING_WAREHOUSE'
DEFAULT_ROLE='MR_USER';

Make sure to replace all ADD_XXXXXXXXX_HERE values with your values:

ADD_NEW_PASSWORD_HERE — set a password for the newly created user
ADD_RSA_PUBLIC_KEY_HERE — follow this link to generate your private and public keys
ADD_BUCKET_NAME_HERE — GCS bucket name

Run All commands above.

Grabbing “account” value for the MetaRouter connection parameter:

account
The account value is defined by your Snowflake <account id>-<organisation id> in lowercase.
This information can be found
1. Under the account selector (bottom left)
2. Your org URL
  
  For this example, the account value will be rnisshj-mz94630.

Getting Started - From Your MetaRouter Account

Adding a Snowflake GCS integration.

From the integration library, add a Snowflake integration. Then, fill out the Connection Parameters:

Connection Parameter	Description
user	The user name from your Snowflake `CREATE USER` command.
password	The password from your Snowflake `.. SET PASSWORD`.. command.
account	The `account` value is defined by your Snowflake `<account id>-<organisation id>` in lowercase.
database	The user name from your Snowflake `CREATE DATABASE` command.
warehouse	The user name from your Snowflake `CREATE WAREHOUSE` command.
schema	The schema from your Snowflake `CREATE SCHEMA` command.
snowpipe	The snowpipe from your Snowflake `CREATE PIPE` `[warehouse].[schema].[snowpipe]`
privateKey	The private key you generated while creating the public key from the previous step.
stage - gcs	`credentialJson` - The credentialJson for your GCS bucket. `bucket` - Your GCS bucket name. `compression` - Your GCS bucket file’s extension

Additional Snowflake Notes

How to generate the private key

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

How to get the private key

cat rsa_key.p8

How to get public key

cat rsa_key.pub

Retrieve the Cloud Storage Service Account for your Snowflake Account and Grant the Service Account Permissions to Access Bucket Objects

DESC STORAGE INTEGRATION <integration_name>;