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.
- 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
- 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
Create a new user within Snowflake with the username
meroxa_user
.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 bemeroxa_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:
$SNOWFLAKE_URL
- Snowflake Account URL$SNOWFLAKE_PRIVATE_KEY
- The private key created in the previous step.
Or simply visit our Meroxa Dashboard to do that on https://dashboard.meroxa.io/resources/new.