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 users all of the functionality of an enterprise analytic database, along with many additional special features and unique capabilities.
This document will walk through setting up Snowflake to be used as a data Store in DeltaStream.
Setting up Snowflake Account
Prerequisites
Unix-like environment (Linux, Mac OS X)
Add Snowflake Warehouse
Under
Admin
in the left side panel, selectWarehouses
. You'll see a list of existing warehouses in your Snowflake account. Snowflake creates a starter warehouse for you calledCOMPUTE_WH
.To create a new Warehouse, select the
+ Warehouse
button in the top right of the screen. To edit an existing warehouse, click the 3 horizontal dots on the right of each listed warehouse and clickEdit
.Configure your warehouse with your preferred specifications. To learn more about configuring your warehouse, take a look at the Snowflake warehouse documentation.
Create Snowflake Key Pair Authentication
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:
Enter a password for this new private key when prompted, remember this password as it is necessary in the following steps.
A new file called
rsa_key.p8
should be generated which contains the encrypted private key in PEM format.
Generate a public key
A new file called
rsa_key.pub
should be generated which 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
Start the snowsql REPL in your terminal. Here's the example of starting snowsql for account
ABCDEF-ABC12345
with the userjsmith
and warehouseCOMPUTE_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 userjsmith
is the following:Note: Only security administrators (i.e. users with the
SECURITYADMIN
role) or higher can alter a user.
See Snowflake documentation for setting up key pair authentication for more infromation.
Adding Snowflake as a DeltaStream Store
Navigate to the DeltaStream web console
Select
Stores
in the left panel then select+ New Store
in the top rightEnter the following information
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 (See Snowflake docs on Roles). Roles can be found by clicking on the dropdown in the top user profile icon.
Username – User login name for the Snowflake account
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)
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
Save
to create the Store
For instructions on creating the Store using DSQL, see CREATE STORE.
Process Streaming Data and Sink to Snowflake
For the steps below, let's assume you already have a Stream defined called pageviews
which is backed by a topic in an Apache Kafka Store. We'll also assume that there is a Snowflake Store called snowflake_store
(See Adding Snowflake as a DeltaStream Store). We are going to perform a simple filter on the pageviews
Stream and sink the results into Snowflake.
If you are curious about 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
Navigate to the
Stores
tab in the left side panel. This will display a list of the existing Stores:Select the Store called
snowflake_store
then selectEntities
. This will bring up a list of the existing databases in your Snowflake account for the role that was specified as part of the Store's creation:(Optional) Create a new database
Click on the 3 vertical dots next to your store name and select
Create Entity
:In the popup, enter a name for the new database and select
Create
. You should now be able to see the new database in the entities list.
Click on a database name to see the schemas that exist under that database
(Optional) Create a new schema
Select
+ New Entity
to create a new schemaIn the popup, enter a name for the new schema and select
Create
. You should now be able to see the new schema in the entities list.
Click on a schema name to see the tables that exist under that schema
Write a CTAS query to sink data into Snowflake
Navigate to the "SQL Editor" tab in the left side panel:
Select
Run
Navigate to the
Queries
tab in the left side panel to see the existing queries, including the query from step 2Refresh until you see the query is in the
Running
state:
Print the results
Navigate to the
Stores
tab in the left side panelNavigate to the new Table created by the above CTAS:
snowflake_store
-->Entities
-->new_database
-->new_schema
-->pv_table
. NOTE: If you wrote your CTAS such that the store/database/schema/table names are different, then navigate accordingly.Select
Print
to see a sample of the data from your Snowflake table,pv_table
:
Last updated