Snowflake Streaming

Jump to Section

The Snowflake Streaming connection connects to Snowflake using the Snowpipe Streaming API. The connection also uses the Snowflake JDBC driver to search for and create tables.

The Intelligence Hub connects outbound on port 443 to Snowflake (example: https://accountid.snowflakecomputing.com).

Connection Settings

Account Identifier

The identifier for the Snowflake account, which can be found when logging into the Snowflake portal. See the Snowflake documentation for more details. An example format is ZHX29753.us-east-1.

https://docs.snowflake.com/en/user-guide/admin-account-identifier

User

The user account in Snowflake to authenticate with.

Role

The role of this user account. The role must have the ability to create tables if create is used in the Output settings.

Private Key

The private key used to authenticate the user. See the Snowflake documentation for generating private key for an account. The private key must be imported into the Intelligence Hub certificate store (See Certificates) and referenced here.

https://docs.snowflake.com/en/user-guide/key-pair-auth

Database

The Snowflake database to write to.

Schema

The default schema to use. Defaults to Public.

Warehouse

The warehouse to use for any processing of data.

Output Settings

Outputs can create and stream data to tables in the Snowflake database.

Table

The name of the table.

Create

Enable this to create the table if is doesn’t exist. The table is created using the schema of the data being sent.

Case Sensitive

By default, Snowflake is case insensitive, and all tables and columns are upper case. This is the recommended approach. When writing to a table that is case sensitive, the table name and columns must be wrapped in quotes. Enabling this option wraps all names in quotes.

Output Example

Below is an example of how an output payload maps to a Snowflake table. If the table is already created, payload attributes are mapped to existing column names. If the columns don’t exist the data is omitted.

Example payload:

json

{
  "col1": 123,
  "col2": 1.23,
  "col3": "123",
  "col4": true,
  "col5": "2023-11-02T14:31:55Z",   // shown as string, but assume this is a DateTime type
  "col6": {
    "col6_attr1": 123,
    "col6_attr2": [1,2,3]
  }
}

Snowflake table:

Column Name	Data Type	Notes
COL1	NUMBER
COL2	FLOAT
COL3	NVARCHAR(1024)
COL4	BOOLEAN
COL5	DATETIME
COL6	NVARCHAR(16777216)	Stored as JSON {“col6_attr1”: 123, “col6_attr2”: [1,2,3]}

Sparkplug