Skip to main content

Snowflake

Snowflake is an enterprise-ready cloud Data Warehouse. It can be used as a downstream resource in your Turbine data apps by using the write function to a select table in a database.

Setup​

Create Snowflake User​

Meroxa uses Key Pair Authentication to access your Snowflake account. The following steps guide you through creating a private key, public key, a meroxa_user user, and associating the public key appropriately.

  1. Generate a private key:
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

For more details, see: Snowflake Docs: Generate the Private Key

  1. Generate a public key:
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

For more details, see: Snowflake Docs: Generate the Private Key

  1. Create a new user within Snowflake with the username meroxa_user.

  2. Using a Snowflake Worksheet, assign the public key to the user: 

-- Set public key for authentication to user
ALTER USER meroxa_user SET RSA_PUBLIC_KEY = "$SNOWFLAKE_PUBLIC_KEY"

$SNOWFLAKE_PUBLIC_KEY is the public key you created in step #2.

When adding the public key, exclude the public key delimiters (-----BEGIN PRIVATE KEY|-----END PRIVATE KEY) in the SQL statement. For example:

ALTER USER meroxa_user SET RSA_PUBLIC_KEY ='MIIBIjANBgkqh...';

Create and Configure Snowflake Database​

Next, using Snowflake Worksheet, we can run the following commands:

-- Use a role that can create databases
USE ROLE accountadmin;
-- Create new database 
CREATE DATABASE meroxa_db;
USE meroxa_db;
CREATE SCHEMA stream_data;
-- Create a Snowflake role with the privileges to work with the connector.
CREATE ROLE meroxa_platform_role;
-- Grant privileges on the database.
GRANT USAGE ON DATABASE meroxa_db TO ROLE meroxa_platform_role;
GRANT USAGE ON SCHEMA stream_data TO ROLE meroxa_platform_role;
GRANT CREATE TABLE ON SCHEMA stream_data TO ROLE meroxa_platform_role;
-- Grant access to stage data
GRANT CREATE STAGE ON SCHEMA stream_data TO ROLE meroxa_platform_role;
GRANT CREATE PIPE ON SCHEMA stream_data TO ROLE meroxa_platform_role;
-- Grant the custom role to an existing user.
GRANT ROLE meroxa_platform_role TO USER meroxa_user;
ALTER USER meroxa_user SET DEFAULT_ROLE = meroxa_platform_role;

The above does the following:

  • Creates a new Snowflake Database called meroxa_db.
  • Creates a new Snowflake Schema called stream_data.
  • Creates a new Snowflake Role called meroxa_platform_role.
  • Grants privileges to the above for meroxa_platform_role.
  • Set's the role of meroxa_user to be meroxa_platform_role.

Resource Configuration​

Use the meroxa resource create command to configure your Snowflake resource.

First, save your private key in a variable so you could use it below in the CLI:

SNOWFLAKE_PRIVATE_KEY=$(cat rsa_key.p8)

The following example depicts how this command is used to create a Snowflake resource named snowflake with the minimum configuration required.

meroxa resource create snowflake \ 
  --type snowflakedb \
  --url snowflake://$SNOWFLAKE_URL/meroxa_db/stream_data \
  --username meroxa_user \
  --password $SNOWFLAKE_PRIVATE_KEY

In the command above, replace the following variables with valid credentials from your PostgreSQL environment:

Or simply visit our Meroxa Dashboard to do that on https://dashboard.meroxa.io/resources/new.