LogoLogo
Start Trial
  • Overview
    • What is DeltaStream?
    • Core Concepts
      • Access Control
      • Region
      • SQL
      • Store
      • Database
      • Query
      • Visualizing Data Lineage
      • Function
  • Getting Started
    • Free Trial Quick Start
    • Starting with the Web App
    • Starting with the CLI
  • Tutorials
    • Managing Users and User Roles
      • Inviting Users to an Organization
      • Administering Users in your Organization
      • Using the CLI to Manage User Roles
      • Example: Setting Up Custom Roles for Production and Stage
    • Creating Stores for Streaming Data
    • Using Multiple Stores in Queries
    • Creating Relations to Structure Raw Data
    • Namespacing with Database and Schema
    • Creating and Querying Materialized Views
    • Creating a Function
    • Securing Your Connections to Data Stores
      • Introducing DeltaStream Private Links
      • Creating an AWS Private Link from DeltaStream to your Confluent Kafka Dedicated Cluster
      • Enabling Private Link Connectivity to Confluent Enterprise Cluster and Schema Registry
      • Creating a Private Link from DeltaStream to Amazon MSK
      • Creating a Private Link for RDS Databases
      • Deleting a Private Link
    • Integrations
      • Connecting to Confluent Cloud
      • Databricks
      • PostgreSQL
      • Snowflake
      • WarpStream
    • Serialization
      • Working with ProtoBuf Serialized Data and DeltaStream Descriptors
      • Working with Avro Serialized Data and Schema Registries
      • Configuring Deserialization Error Handling
  • Reference
    • Enterprise Security Integrations
      • Okta SAML Integration
      • Okta SCIM Integration
    • Metrics
      • Prometheus Integration
      • Built-In Metrics
      • Custom Metrics in Functions
    • SQL Syntax
      • Data Formats (Serialization)
        • Serializing with JSON
        • Serializing with Primitive Data Types
        • Serializing with Protobuf
      • Data Types
      • Identifiers and Keywords
      • Command
        • ACCEPT INVITATION
        • CAN I
        • COPY DESCRIPTOR_SOURCE
        • COPY FUNCTION_SOURCE
        • DESCRIBE ENTITY
        • DESCRIBE QUERY
        • DESCRIBE QUERY METRICS
        • DESCRIBE QUERY EVENTS
        • DESCRIBE QUERY STATE
        • DESCRIBE RELATION
        • DESCRIBE RELATION COLUMNS
        • DESCRIBE ROLE
        • DESCRIBE SECURITY INTEGRATION
        • DESCRIBE <statement>
        • DESCRIBE STORE
        • DESCRIBE USER
        • GENERATE COLUMNS
        • GENERATE TEMPLATE
        • GRANT OWNERSHIP
        • GRANT PRIVILEGES
        • GRANT ROLE
        • INVITE USER
        • LIST API_TOKENS
        • LIST DATABASES
        • LIST DESCRIPTORS
        • LIST DESCRIPTOR_SOURCES
        • LIST ENTITIES
        • LIST FUNCTIONS
        • LIST FUNCTION_SOURCES
        • LIST INVITATIONS
        • LIST METRICS INTEGRATIONS
        • LIST ORGANIZATIONS
        • LIST QUERIES
        • LIST REGIONS
        • LIST RELATIONS
        • LIST ROLES
        • LIST SCHEMAS
        • LIST SCHEMA_REGISTRIES
        • LIST SECRETS
        • LIST SECURITY INTEGRATIONS
        • LIST STORES
        • LIST USERS
        • PRINT ENTITY
        • REJECT INVITATION
        • REVOKE INVITATION
        • REVOKE PRIVILEGES
        • REVOKE ROLE
        • SET DEFAULT
        • USE
      • DDL
        • ALTER API_TOKEN
        • ALTER SECURITY INTEGRATION
        • CREATE API_TOKEN
        • CREATE CHANGELOG
        • CREATE DATABASE
        • CREATE DESCRIPTOR_SOURCE
        • CREATE ENTITY
        • CREATE FUNCTION_SOURCE
        • CREATE FUNCTION
        • CREATE INDEX
        • CREATE METRICS INTEGRATION
        • CREATE ORGANIZATION
        • CREATE ROLE
        • CREATE SCHEMA_REGISTRY
        • CREATE SCHEMA
        • CREATE SECRET
        • CREATE SECURITY INTEGRATION
        • CREATE STORE
        • CREATE STREAM
        • CREATE TABLE
        • DROP API_TOKEN
        • DROP CHANGELOG
        • DROP DATABASE
        • DROP DESCRIPTOR_SOURCE
        • DROP ENTITY
        • DROP FUNCTION_SOURCE
        • DROP FUNCTION
        • DROP METRICS INTEGRATION
        • DROP RELATION
        • DROP ROLE
        • DROP SCHEMA
        • DROP SCHEMA_REGISTRY
        • DROP SECRET
        • DROP SECURITY INTEGRATION
        • DROP STORE
        • DROP STREAM
        • DROP USER
        • UPDATE ENTITY
        • UPDATE SCHEMA_REGISTRY
        • UPDATE SECRET
        • UPDATE STORE
      • Query
        • APPLICATION
        • Change Data Capture (CDC)
        • CREATE CHANGELOG AS SELECT
        • CREATE STREAM AS SELECT
        • CREATE TABLE AS SELECT
        • Function
          • Built-in Functions
          • Row Metadata Functions
        • INSERT INTO
        • Materialized View
          • CREATE MATERIALIZED VIEW AS
          • SELECT (FROM MATERIALIZED VIEW)
        • Query Name and Version
        • Resume Query
        • RESTART QUERY
        • SELECT
          • FROM
          • JOIN
          • MATCH_RECOGNIZE
          • WITH (Common Table Expression)
        • TERMINATE QUERY
      • Sandbox
        • START SANDBOX
        • DESCRIBE SANDBOX
        • STOP SANDBOX
      • Row Key Definition
    • Rest API
Powered by GitBook
On this page
  • Setting up Snowflake Account
  • Prerequisites
  • Add Snowflake Warehouse
  • Create Snowflake Key Pair Authentication
  • Adding Snowflake as a DeltaStream Store
  • Process Streaming Data and Sink to Snowflake
  • Inspect the Snowflake store
  • Write a CTAS (CREATE TABLE AS SELECT) Query to Sink Data into Snowflake
  • View the results
  1. Tutorials
  2. Integrations

Snowflake

PreviousPostgreSQLNextWarpStream

Last updated 5 months ago

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

  1. .

  2. Unix-like environment (Linux, Mac OS X).

Add Snowflake Warehouse

  1. 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.

  2. 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.

  3. Configure your warehouse with your preferred specifications. To learn more about configuring your warehouse, review the .

Create Snowflake Key Pair Authentication

  1. 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.

  2. 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.

  3. 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.

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

    1. $ snowSQL -a ABCDEF-ABC12345 -u jsmith -w COMPUTE_WH
    2. 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.

Adding Snowflake as a DeltaStream Store

  1. From the menu that displays, click Snowflake. The Add Store window opens.

  2. 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)

    • 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).

  3. 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

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

  1. 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:

  2. (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.

  3. Click on a database name to see the schemas that exist under that database.

  4. (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.

  5. 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

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';
  1. Click Run.

View the results

  1. 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.

  2. To view a sample of the data in your Snowflake table, click Print.

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 .

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

Open DeltaStream. In the lefthand navigation, click Resources ( ) and then click Add Store +.

URL – URL for Snowflake account in the format https://<account_id>.snowflakecomputing.com (see ).

Account ID – Account identifier in the form <orgname>-<account_name> (see ).

Role Name – The name of the access control role to use for the store operations after connecting to Snowflake (for more inforation see ). To find roles in Snowflake, click the top user profile icon and select from the dropdown menu.

To follow the next few steps, you must already have a 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.

In the lefthand navigation, click Resources ( ). This displays a list of the existing stores.

In the lefthand navigation, click Workspace ( ).

In the SQL pane of your workspace, write the query to ingest from pageviews and output to a new table titled pv_table.

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.

Snowflake's documentation
Snowflake documentation
Snowflake Account Identifiers documentation
Snowflake Account Identifiers documentation
Snowflake documentation on Roles
CREATE TABLE AS SELECT (CTAS)
Snowflake
Sign up for a Snowflake account using AWS
Download SnowSQL
Snowflake warehouse documentation
stream
Listing Snowflake Warehouses
DeltaStream Store List
Adding a Snowflake Store
Selecting a Snowflake Role
DeltaStream Store List