Snowflake Streaming
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. Snowflake offers two versions of the Snowpipe Streaming API, and the Streaming Version setting controls which one the connection uses.
The Intelligence Hub connects outbound to Snowflake, in the format: https://<account-identifier>.snowflakecomputing.com. Firewall rules must allow both of the following ports:
- Port 443 is used for all communication with Snowflake.
- Port 80 is used for the OCSP certificate revocation checks the Snowflake JDBC driver performs while establishing the connection.
Connection Settings
Streaming Version
The version of the Snowpipe Streaming API the connection streams data with. Defaults to Classic.
- When Classic is used, the connection streams with Snowflake’s original Snowpipe Streaming API (V1). Data is streamed over a channel per table, and the connection can route traffic through a proxy server.
- When High Performance is used, the connection streams with Snowflake’s high performance Snowpipe Streaming API (V2). Data is streamed through a Snowflake pipe, which provides higher throughput. Proxy servers aren’t supported.
To route either version through a proxy server, see Proxy Configuration.
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.
Keep Alive
See JDBC Driver Settings for keep alive settings.
Use Proxy
Enables routing Snowflake connections through a proxy server. Only available when Streaming Version is set to Classic.
Proxy URL
The URL of the proxy server to use. Only available when Use Proxy is enabled. For proxying with TLS, reference the Snowflake docs.
Proxy Configuration
To route Snowflake Streaming traffic through a proxy server, set JVM system properties and environment variables on the Intelligence Hub runtime. These settings apply to the whole runtime, so every Snowflake Streaming connection uses the same proxy.
The settings needed depend on the connection’s Streaming Version, because each part of the connection reads its proxy settings from a different place:
| Traffic | Streaming Version | Proxy Settings |
|---|---|---|
| Table search and creation (Snowflake JDBC driver) | Classic and High Performance | JVM system properties |
| Streaming data (Snowflake Ingest SDK) | Classic | JVM system properties |
| Streaming data (Snowpipe Streaming SDK) | High Performance | Environment variables |
- Classic connections only need the JVM system properties.
- High Performance connections need both: the JVM system properties for table search and creation, and the environment variables for streaming data.
https://<account-identifier>.snowflakecomputing.com.JVM System Properties (Classic and High Performance)
The Snowflake JDBC driver and the Classic Snowflake Ingest SDK read their proxy settings from the http.* JVM system properties. Some additional Snowflake clients used by Classic connection may also need to the https.* properties too. If writes are failing, both sets of properties should be configured.
| Property | Description |
|---|---|
http.useProxy | Set to true to enable the proxy. |
http.proxyHost | Host name or IP address of the proxy server. Used by the Snowflake JDBC driver and the Classic Snowflake Ingest SDK. |
http.proxyPort | Port of the proxy server. Used by the Snowflake JDBC driver and the Classic Snowflake Ingest SDK. |
https.proxyHost | Host name or IP address of the proxy server. Used by additional Classic clients. Set to the same value as http.proxyHost. |
https.proxyPort | Port of the proxy server. Used by additional Classic clients. Set to the same value as http.proxyPort. |
http.nonProxyHosts | Hosts that bypass the proxy, separated by |. Wildcards are supported (for example, localhost|*.internal.example.com). |
http.proxyUser | User name for proxy authentication, used by the Snowflake JDBC driver and the Classic Snowflake Ingest SDK. Only used when http.proxyPassword is also set. |
http.proxyPassword | Password for proxy authentication, used by the Snowflake JDBC driver and the Classic Snowflake Ingest SDK. Only used when http.proxyUser is also set. |
https.proxyUser | User name for proxy authentication, used by additional Classic clients. |
https.proxyPassword | Password for proxy authentication, used by additional Classic clients. |
To set the JVM system properties:
Stop the Intelligence Hub runtime.
Add the properties to the
JAVA_OPTSline in the start script for the operating system (start-windows.bat,start-linux.sh, orstart-macos.sh). For example, instart-linux.sh:JAVA_OPTS="--enable-native-access=ALL-UNNAMED --sun-misc-unsafe-memory-access=allow -XX:+HeapDumpOnOutOfMemoryError -XX:+CrashOnOutOfMemoryError -Dhttp.useProxy=true -Dhttp.proxyHost=proxy.example.com -Dhttp.proxyPort=8080 -Dhttps.proxyHost=proxy.example.com -Dhttps.proxyPort=8080"When the runtime isn’t started from a start script (for example, when it runs as a service or in a container), set the properties in the
JAVA_TOOL_OPTIONSenvironment variable instead. The JVM reads this variable on startup.JAVA_TOOL_OPTIONS=-Dhttp.useProxy=true -Dhttp.proxyHost=proxy.example.com -Dhttp.proxyPort=8080 -Dhttps.proxyHost=proxy.example.com -Dhttps.proxyPort=8080Start the runtime.
http.proxyHost, http.proxyPort, https.proxyHost, and https.proxyPort are standard Java properties and are used by many different connection types, including REST and AWS connectors. As a result, any connection that supports these system properties, will send their traffic through the configured proxy. http.nonProxyHosts can be used to exclude hosts that must not go through the proxy.Environment Variables (High Performance)
The High Performance Snowpipe Streaming SDK sends streaming data from a native library that doesn’t read JVM system properties. Instead, it reads the standard proxy environment variables. Lowercase names (for example, https_proxy) are also accepted.
The environment variables only cover streaming data. High Performance connections still use the Snowflake JDBC driver for table search and creation, and the driver reads the http.* JVM system properties. Both environment and system properties will have to be configured to use the connection successfully.
| Environment Variable | Description |
|---|---|
HTTPS_PROXY | URL of the proxy server for HTTPS traffic, which includes all traffic to Snowflake. For example, http://proxy.example.com:8080. |
HTTP_PROXY | URL of the proxy server for plain HTTP traffic. |
ALL_PROXY | URL of the proxy server to use when HTTPS_PROXY or HTTP_PROXY isn’t set. |
The proxy URL can use http:// even though traffic to Snowflake is HTTPS. An HTTP proxy tunnels the HTTPS traffic without terminating TLS.
To set the environment variables:
- Stop the Intelligence Hub runtime.
- Set the environment variables in the environment that starts the runtime. Restarting the connection isn’t enough, because the variables are only read when the runtime process starts.
- Start the runtime.
SSL_CERT_FILE or SSL_CERT_DIR environment variable. The Snowflake JDBC driver doesn’t support proxies that inspect TLS traffic; see the Snowflake docs for options.Output Settings
Outputs can create and stream data to tables in the Snowflake database.
Table
The name of the table.
Type
Defines what type of table is
- When Snowflake is used, a standard Snowflake table is assumed.
- When Iceberg is used, an Iceberg configured table is assumed.
NOTE: When using Iceberg tables, writes can take up to 30 seconds to complete.
Create Table
Controls how the table definition is managed. Options are Off, Create, and Create & Update.
- When Off is used, it’s assumed that the table schema is defined and managed externally. The table already must exist.
- When Create is used, it’s assumed the connection manages the table, which will be created once. If the table doesn’t exist it’s created using the schema of the payload.
- When Create & Update is used, it’s assumed the connection manages the table and will update the table on schema change. For example, if a new attribute is added to the data, the column is added to the table.
Pipe
The Snowflake pipe to stream data into. Only available when Create Table is set to Off, and only used when the connection’s Streaming Version is set to High Performance. Leave it empty to stream into the table’s default pipe.
The pipe must already exist and must copy into the table named in Table. The role used by the connection must have the privileges to use it.
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.
When the connection’s Streaming Version is set to High Performance and Case Sensitive is enabled, the Intelligence Hub creates and streams through a pipe named <table>-HIGHBYTE, because a table’s default pipe doesn’t match column names case sensitively. The role used by the connection must have the privilege to create pipes in the schema. Setting a Pipe on the output replaces this behavior.
Catalog
The metadata store that tracks the structure, schema, and location of Iceberg table data files. The catalog maintains the current state of the table and enables features like time travel and schema evolution.
External Volume
The named Snowflake object that references cloud storage where Iceberg table data and metadata files are stored. It provides the connection between Snowflake and the external storage location.
Base Location
The root directory path within an external volume where a specific Iceberg table’s data and metadata files are stored.
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:
{
"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]} |
Note
When Case Sensitive is disabled, columns that differ only by case cannot be added to existing tables. For example, if Create Table is set to Create & Update and a table exists with the following schema:
| Column Name | Data Type |
|---|---|
| STATE | NUMBER |
Attempting a write that includes ‘state’ as a column name will not lead to a table update with both ‘STATE’ and ‘state’ as columns. When Case Sensitive is enabled, ‘STATE’ and ‘state’ are treated as different columns, and the write adds ‘state’ to the table.