Comment on page
Setting up and Integrating Snowflake with your Organization
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.
Adminin the left side panel, select
Warehouses. You'll see a list of existing warehouses in your Snowflake account. Snowflake creates a starter warehouse for you called
- 2.To create a new Warehouse, select the
+ Warehousebutton 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 click
- 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 -nocryptA new file called
rsa_key.p8should 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.p8Enter 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.p8should be generated which contains the encrypted private key in PEM format.
- 2.Generate a public key$ openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pubA new file called
rsa_key.pubshould be generated which 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
- 1.Start the snowsql REPL in your terminal. Here's the example of starting snowsql for account
ABCDEF-ABC12345with the user
COMPUTE_WH. For more information about using snowsql, see Snowflake's documentation.$ snowSQL -a ABCDEF-ABC12345 -u jsmith -w COMPUTE_WH
- 2.Alter a user to set the
RSA_PUBLIC_KEYproperty If your public key looks like this:$ cat rsa_key.pub-----BEGIN PUBLIC KEY-----MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAwViztpm3l7IfLN/prlBbKJi/yTMngmTBKFp5YOyWqswlXwVlH6zehbCgFYxjo36RxxDtwqTSzaEalraalvjEaRKKb+esi8TYvbd1eiJGJqoq5WjTIHs0lwRMwLkCn6mHX9I4J0Wp6mR2DhjWvey6IE4aKT0kXrKChs/GCOGFi3lAIRIXy9FQusYW2OyNoG1iogFLoWPH1Od5bmVl6TJvxXXKeUj0omTq+3CKZxELxgr5nYU44MQgq/NH1X+nEjIiA0W2RiS6heKNgwRUg2EQ8UXbrHCjhgnONRnnHGukh9CpH4KTTmQc2hifTbH7g39f31hrEWBfVTECESUAY+qzkwIDAQAB-----END PUBLIC KEY-----Then the command to set the
jsmithis the following:ALTER USER jsmith SET RSA_PUBLIC_KEY='MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAwViztpm3l7IfLN/prlBbKJi/yTMngmTBKFp5YOyWqswlXwVlH6zehbCgFYxjo36RxxDtwqTSzaEalraalvjEaRKKb+esi8TYvbd1eiJGJqoq5WjTIHs0lwRMwLkCn6mHX9I4J0Wp6mR2DhjWvey6IE4aKT0kXrKChs/GCOGFi3lAIRIXy9FQusYW2OyNoG1iogFLoWPH1Od5bmVl6TJvxXXKeUj0omTq+3CKZxELxgr5nYU44MQgq/NH1X+nEjIiA0W2RiS6heKNgwRUg2EQ8UXbrHCjhgnONRnnHGukh9CpH4KTTmQc2hifTbH7g39f31hrEWBfVTECESUAY+qzkwIDAQAB';
- 3.Note: Only security administrators (i.e. users with the
SECURITYADMINrole) or higher can alter a user.
Storesin the left panel then select
+ New Storein the top right
- 3.Enter the following information
- Store Type –
- 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
Saveto create the Store
For the steps below, let's assume you already have a Stream defined called
pageviewswhich 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
pageviewsStream and sink the results into Snowflake.
- 1.Navigate to the
Storestab in the left side panel. This will display a list of the existing Stores:
- 2.Select the Store called
Entities. 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:
- 3.(Optional) Create a new database
- 1.Click on the 3 vertical dots next to your store name and select
- 2.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.
- 4.Click on a database name to see the schemas that exist under that database
- 5.(Optional) Create a new schema
+ New Entityto create a new schema
- 2.In 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.
- 6.Click on a schema name to see the tables that exist under that schema
CREATE TABLE pv_table WITH (
'store' = 'snowflake_store',
'snowflake.db.name' = 'new_db',
'snowflake.schema.name' = 'new_schema'
pageid != 'Page_3';
- 4.Navigate to the
Queriestab in the left side panel to see the existing queries, including the query from step 2
- 5.Refresh until you see the query is in the
- 1.Navigate to the
Storestab in the left side panel
- 2.Navigate to the new Table created by the above CTAS:
pv_table. NOTE: If you wrote your CTAS such that the store/database/schema/table names are different, then navigate accordingly.