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.

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:

TrafficStreaming VersionProxy Settings
Table search and creation (Snowflake JDBC driver)Classic and High PerformanceJVM system properties
Streaming data (Snowflake Ingest SDK)ClassicJVM system properties
Streaming data (Snowpipe Streaming SDK)High PerformanceEnvironment 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.

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.

PropertyDescription
http.useProxySet to true to enable the proxy.
http.proxyHostHost name or IP address of the proxy server. Used by the Snowflake JDBC driver and the Classic Snowflake Ingest SDK.
http.proxyPortPort of the proxy server. Used by the Snowflake JDBC driver and the Classic Snowflake Ingest SDK.
https.proxyHostHost name or IP address of the proxy server. Used by additional Classic clients. Set to the same value as http.proxyHost.
https.proxyPortPort of the proxy server. Used by additional Classic clients. Set to the same value as http.proxyPort.
http.nonProxyHostsHosts that bypass the proxy, separated by |. Wildcards are supported (for example, localhost|*.internal.example.com).
http.proxyUserUser 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.proxyPasswordPassword for proxy authentication, used by the Snowflake JDBC driver and the Classic Snowflake Ingest SDK. Only used when http.proxyUser is also set.
https.proxyUserUser name for proxy authentication, used by additional Classic clients.
https.proxyPasswordPassword for proxy authentication, used by additional Classic clients.

To set the JVM system properties:

  1. Stop the Intelligence Hub runtime.

  2. Add the properties to the JAVA_OPTS line in the start script for the operating system (start-windows.bat, start-linux.sh, or start-macos.sh). For example, in start-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_OPTIONS environment 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=8080
    
  3. Start the runtime.

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 VariableDescription
HTTPS_PROXYURL of the proxy server for HTTPS traffic, which includes all traffic to Snowflake. For example, http://proxy.example.com:8080.
HTTP_PROXYURL of the proxy server for plain HTTP traffic.
ALL_PROXYURL 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:

  1. Stop the Intelligence Hub runtime.
  2. 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.
  3. Start the runtime.

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 NameData TypeNotes
COL1NUMBER
COL2FLOAT
COL3NVARCHAR(1024)
COL4BOOLEAN
COL5DATETIME
COL6NVARCHAR(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 NameData Type
STATENUMBER

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.