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
-
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 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
-
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>;
Updated 2 months ago