Snowflake
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
- 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 userADD_RSA_PUBLIC_KEY_HERE
— follow this link to generate your private and public keysADD_BUCKET_NAME_HERE
— S3 bucket nameADD_AWS_KEY_ID_HERE
— your AWS Key IDADD_AWS_SECRET_KEY_HERE
— your AWS Secret Key
- Run All commands above.

- Grabbing “account” value for the MetaRouter connection parameter:
-
account
Theaccount
value is defined by your Snowflake<account id>-<organisation id>
in lowercase.
This information can be found-
Under the account selector (bottom left)
-
Your org URL
For this example, the
account
value will bernisshj-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 |
password | The password from your Snowflake |
account | The |
database | The user name from your Snowflake |
warehouse | The user name from your Snowflake |
schema | The schema from your Snowflake |
snowpipe | The snowpipe from your Snowflake |
privateKey | The private key you generated while creating the public key from the previous step. |
stage - s3 |
|
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 userADD_RSA_PUBLIC_KEY_HERE
— follow this link to generate your private and public keysADD_BUCKET_NAME_HERE
— GCS bucket name
- Run All commands above.

- Grabbing “account” value for the MetaRouter connection parameter:
-
account
Theaccount
value is defined by your Snowflake<account id>-<organisation id>
in lowercase.
This information can be found-
Under the account selector (bottom left)
-
Your org URL
For this example, the
account
value will bernisshj-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 |
password | The password from your Snowflake |
account | The |
database | The user name from your Snowflake |
warehouse | The user name from your Snowflake |
schema | The schema from your Snowflake |
snowpipe | The snowpipe from your Snowflake |
privateKey | The private key you generated while creating the public key from the previous step. |
stage - gcs |
|
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>;
Updated 20 days ago