Documentation

Snowflake

Suggest Edits

📘

This integration is currently in beta. Contact your MetaRouter support team to get started.

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

  1. 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';
  1. 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
  1. Run All commands above.
  1. 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`.

      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 ParameterDescription
userThe user name from your Snowflake CREATE USER command.
passwordThe password from your Snowflake .. SET PASSWORD.. command.
accountThe account value is defined by your Snowflake <account id>-<organisation id> in lowercase.
databaseThe user name from your Snowflake CREATE DATABASE command.
warehouseThe user name from your Snowflake CREATE WAREHOUSE command.
schemaThe schema from your Snowflake CREATE SCHEMA command.
snowpipeThe snowpipe from your Snowflake CREATE PIPE [warehouse].[schema].[snowpipe]
privateKeyThe private key you generated while creating the public key from the previous step.
stage - s3region - 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

  1. 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';
  1. 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
  1. Run All commands above.
  1. 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`.

      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 ParameterDescription
userThe user name from your Snowflake CREATE USER command.
passwordThe password from your Snowflake .. SET PASSWORD.. command.
accountThe account value is defined by your Snowflake <account id>-<organisation id> in lowercase.
databaseThe user name from your Snowflake CREATE DATABASE command.
warehouseThe user name from your Snowflake CREATE WAREHOUSE command.
schemaThe schema from your Snowflake CREATE SCHEMA command.
snowpipeThe snowpipe from your Snowflake CREATE PIPE [warehouse].[schema].[snowpipe]
privateKeyThe private key you generated while creating the public key from the previous step.
stage - gcscredentialJson - The credentialJson for your GCS bucket.
bucket - Your GCS bucket name.
compression - Your GCS bucket file’s extension

Additional Snowflake Notes

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

DESC STORAGE INTEGRATION <integration_name>;

Updated 5 months ago