Snowflake
Last updated
Last updated
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.
Unix-like environment (Linux, Mac OS X).
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.
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.
In a terminal window, generate a new private key:
Without encryption:
A new file called rsa_key.p8 should be generated which contains the private key in PEM format.
With encryption:
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
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.
Alter a user to set the RSA_PUBLIC_KEY property.
If your public key looks like this...
...then the command to set the RSA_PUBLIC_KEY for user jsmith is the following:
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.
From the menu that displays, click Snowflake. The Add Store window opens.
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.
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.
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.
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:
Click + Add Schema.
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.
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.
Click Run.
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.
Open DeltaStream. In the lefthand navigation, click Resources ( ) and then click Add Store +.
In the lefthand navigation, click Resources ( ). This displays a list of the existing stores.
In the lefthand navigation, click Workspace ( ).
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.
In the lefthand navigation, click Resources ( ). This displays a list of the existing stores.
