Snowflake

Snowflake is a fully-managed cloud-based data platform for storing and processing data. Snowflake uses its own SQL query engine to provide its customers all of the functionality of an enterprise analytic database, along with many additional special features and unique capabilities.

This document walks through setting up Snowflake to be used as a data Store in DeltaStream.

Setting up Snowflake Account

Prerequisites

Sign up for a Snowflake account using AWS.
Unix-like environment (Linux, Mac OS X).
Download SnowSQL

Add Snowflake Warehouse

In Snowflake, in the lefthand panel under Admin, select Warehouses to view a list of existing warehouses in your Snowflake account. Snowflake creates a starter warehouse for you called COMPUTE_WH.
Listing Snowflake Warehouses
To create a new warehouse, in the top right of the screen click + Warehouse. To edit an existing warehouse, to the right of the warehouse you want click the 3 horizontal dots. Then click Edit.
Configure your warehouse with your preferred specifications. To learn more about configuring your warehouse, review the Snowflake warehouse documentation.

Create Snowflake Key Pair Authentication

In a terminal window, generate a new private key:
1. Without encryption:
  $ openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
  A new file called rsa_key.p8 should be generated which contains the private key in PEM format.
2. With encryption:
  $ openssl genrsa 2048 | openssl pkcs8 -topk8 -traditional -inform PEM -out rsa_key.p8
  When prompted, enter a password for this new private key. It's important to remember this password, as subsequent steps require it.
  A new file called rsa_key.p8 is generated that contains the encrypted private key in PEM format.
Generate a public key
```
$ openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
```
A new file called rsa_key.pub is generated that contains the public key in PEM format.
Ensure these new private and public keys are stored securely. If these files are kept on local disk, then consider restricting read and write access to these files or securing them behind a password.

Assign the public key to a Snowflake user. To do this:

Start the snowsql REPL in your terminal. Here's an example of starting snowsql for account ABCDEF-ABC12345 with the user jsmith and warehouse COMPUTE_WH. For more information about using snowsql, see Snowflake's documentation.
```
$ snowSQL -a ABCDEF-ABC12345 -u jsmith -w COMPUTE_WH
```

Alter a user to set the RSA_PUBLIC_KEY property. If your public key looks like this...

$ cat rsa_key.pub
-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAwViztpm3l7IfLN/prlBb
KJi/yTMngmTBKFp5YOyWqswlXwVlH6zehbCgFYxjo36RxxDtwqTSzaEalraalvjE
aRKKb+esi8TYvbd1eiJGJqoq5WjTIHs0lwRMwLkCn6mHX9I4J0Wp6mR2DhjWvey6
IE4aKT0kXrKChs/GCOGFi3lAIRIXy9FQusYW2OyNoG1iogFLoWPH1Od5bmVl6TJv
xXXKeUj0omTq+3CKZxELxgr5nYU44MQgq/NH1X+nEjIiA0W2RiS6heKNgwRUg2EQ
8UXbrHCjhgnONRnnHGukh9CpH4KTTmQc2hifTbH7g39f31hrEWBfVTECESUAY+qz
kwIDAQAB
-----END PUBLIC KEY-----

...then the command to set the RSA_PUBLIC_KEY for user jsmith is the following:

ALTER USER jsmith SET RSA_PUBLIC_KEY='MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAwViztpm3l7IfLN/prlBb
KJi/yTMngmTBKFp5YOyWqswlXwVlH6zehbCgFYxjo36RxxDtwqTSzaEalraalvjE
aRKKb+esi8TYvbd1eiJGJqoq5WjTIHs0lwRMwLkCn6mHX9I4J0Wp6mR2DhjWvey6
IE4aKT0kXrKChs/GCOGFi3lAIRIXy9FQusYW2OyNoG1iogFLoWPH1Od5bmVl6TJv
xXXKeUj0omTq+3CKZxELxgr5nYU44MQgq/NH1X+nEjIiA0W2RiS6heKNgwRUg2EQ
8UXbrHCjhgnONRnnHGukh9CpH4KTTmQc2hifTbH7g39f31hrEWBfVTECESUAY+qz
kwIDAQAB';

Note Only security administrators (i.e. users with the SECURITYADMIN role) or higher can alter a user.

For more information on setting up key pair authentication, see Snowflake documentation.

Adding Snowflake as a DeltaStream Store

Open DeltaStream. In the lefthand navigation, click Resources ( ) and then click Add Store +.
DeltaStream Store List
From the menu that displays, click Snowflake. The Add Store window opens.
Adding a Snowflake Store
Enter the authentication and connection parameters. These include:
- Store Name – A name to identify your Snowflake Store (See Store).
- Store Type – Snowflake.
- Access Region – DeltaStream access region to associate with the store (See Region)
- URL – URL for Snowflake account in the format https://<account_id>.snowflakecomputing.com (see Snowflake Account Identifiers documentation).
- Account ID – Account identifier in the form <orgname>-<account_name> (see Snowflake Account Identifiers documentation).
- Role Name – The name of the access control role to use for the store operations after connecting to Snowflake (for more inforation see Snowflake documentation on Roles). To find roles in Snowflake, click the top user profile icon and select from the dropdown menu.
  Selecting a Snowflake Role
- Warehouse Name – The name for a Snowflake compute warehouse to use for queries and other store operations that require compute resources (see Add Snowflake Warehouse).
- Username – User login name for the Snowflake account.
- Client Key File – Snowflake account's private key in PEM format (see Create Snowflake Key Pair Authentication).
- Key Passphrase – If applicable, passphrase for decrypting the Snowflake account's private key (see Create Snowflake Key Pair Authentication).
Click Add to save and create the store.

For instructions on creating the store using DSQL, see CREATE STORE.

Process Streaming Data and Sink to Snowflake

To follow the next few steps, you must already have a stream defined in DeltaStream labeled pageviews, which is backed by a topic in an Apache Kafka Store. You also must have a Snowflake store labeled snowflake_store (see Adding Snowflake as a DeltaStream Store). When you have those, you can perform a simple filter on the pageviews stream and sink the results into Snowflake.

For details on how to set up a stream or a Kafka store, see Starting with the Web App or Starting with the CLI.

Inspect the Snowflake store

In the lefthand navigation, click Resources ( ). This displays a list of the existing stores.
DeltaStream Store List
Click snowflake_store. The store page opens, displaying a list of the existing databases in your Snowflake account for the role you specified as part of the store's creation:
(Optional) Create a new database. To do this:
- Click + Add Database. When prompted, enter a name for the new database and click Add. The new database displays in the list.
Click on a database name to see the schemas that exist under that database.
(Optional) Create a new schema. To do this:
1. Click + Add Schema.
2. In the window that opens, enter a name for the new schema and click Add. The new schema displays in the schema list.
To see the tables that exist under a particular schema, click the schema name.

Write a CTAS (CREATE TABLE AS SELECT) Query to Sink Data into Snowflake

In the lefthand navigation, click Workspace ( ).
In the SQL pane of your workspace, write the CREATE TABLE AS SELECT (CTAS) query to ingest from pageviews and output to a new table titled pv_table.

CREATE TABLE pv_table WITH (
  'store' = 'snowflake_store', 
  'snowflake.db.name' = 'new_db',
  'snowflake.schema.name' = 'new_schema'
) AS 
SELECT 
  viewtime, 
  pageid, 
  userid 
FROM 
  pageviews 
WHERE 
  pageid != 'Page_3';

Click Run.
In the lefthand navigation click Queries ( ) to view the existing queries, including the query from the step immediately prior. It may take a few moments for the query to transition into the Running state. Keep refreshing your screen until the query transitions.

View the results

In the lefthand navigation, click Resources ( ). This displays a list of the existing stores.
To view the new table created by the above CTAS, navigate to snowflake_store --> Databases --> new_database --> new_schema --> pv_table. Of course, if you wrote your CTAS such that the store/catalog/schema/table names are different, navigate accordingly.
To view a sample of the data in your Snowflake table, click Print.

PreviousPostgreSQL NextWarpStream

Last updated 5 months ago