8.2.32.2 Snowflake Streaming Handler
- Overview
- Detailed Functionality
- Configuration
- Classpath Configuration
- Proxy Configuration
- Snowflake Streaming Handler Key Pair Authentication
- Sample Configuration
- Troubleshooting and Diagnostics
Parent topic: Snowflake
8.2.32.2.1 Overview
The Snowflake Streaming Handler replicates data into Snowflake using the Snowpipe Streaming API. This handler supports INSERT-only workloads using the Snowpipe Streaming API, which can result in lower load latencies at a lower cost for loading data into Snowflake.
Note:
If your workload includes updates and deletes, consider using the Snowflake Stage and Merge Handler.For more information, see Snowpipe Streaming documentation.
Parent topic: Snowflake Streaming Handler
8.2.32.2.2 Detailed Functionality
Note:
The Snowflake Streaming Handler supportsINSERT
-only workloads.
Parent topic: Snowflake Streaming Handler
8.2.32.2.2.1 Database User Privileges
The database user used for replicating into Snowflake has to be granted the following privileges:
INSERT
on the target tables.- Optionally,
CREATE TABLE
if settinggg.handler.snow.createTable
totrue
.
Parent topic: Detailed Functionality
8.2.32.2.2.2 Prerequisites
- Oracle GoldenGate trails must be configured to generate
INSERT
operations only. If update and delete operations are converted to inserts by using parameters likeINSERTUPDATE
, orINSERTDELETE
, then those inserts are also supported by handler.
Parent topic: Detailed Functionality
8.2.32.2.2.3 Staging using Snowpipe Streaming API
The Snowpipe Streaming API allows for a low-latency ingest into the target table.
Parent topic: Detailed Functionality
8.2.32.2.3 Configuration
The configuration of the Snowflake replication properties is stored in the Replicat properties file.
Note:
Ensure to specify the path to the properties file in the parameter file only when using Coordinated Replicat. Add the following line to the parameter file:TARGETDB LIBFILE libggjava.so SET property=<parameter file directory>/<properties file name>
The following are configuration properties available for the Snowflake Streaming handler, the required ones must be changed to match your Snowflake configuration.
Table 8-41 Snowflake Streaming Handler Configuration
Properties | Required/Optional | Legal Values | Default | Explanation |
---|---|---|---|---|
gg.handlerlist |
Required | String value. For example,
snow .
|
snow |
Choose the name snow for the
handler.
|
gg.handler.snow.type |
Required | snowflakestreaming |
None | Type of handler to use. |
gg.handler.snow.account |
Required | String value. | None | Snowflake account name. |
gg.handler.snow.user |
Required | String value. | None | Snowflake data warehouse user. |
gg.handler.snow.role |
Optional | String value. | ACCOUNTADMIN |
Snowflake data warehouse role. |
gg.handler.snow.warehouse |
Required | String value. | None | Snowflake data warehouse name. |
gg.handler.snow.database |
Required | String value. | None | Snowflake default database name used during connection. |
gg.handler.snow.privateKeyFile |
Required | String value. | None | Specifies the fully qualified path to the private key file for the user. This is used for key-pair authentication. |
gg.handler.snow.privateKeyFilePassword |
Optional | String value. | None | Specifies the password for the private key file in case the private key file is encrypted. |
gg.handler.snow.createTable |
Optional | true or false |
true | Set to true to automatically create the target
table if it does not exist.
|
gg.handler.name.flushTimeout |
Optional | Numeric value | 30 seconds | Set the flush timeout for streaming operations to commit to target. The value should be in seconds. |
Parent topic: Snowflake Streaming Handler
8.2.32.2.4 Classpath Configuration
Snowflake Streaming Handler uses the Snowflake Ingest Java SDK. Ensure
that the gg.classpath configuration
parameter includes the path to
the Ingest SDK.
Parent topic: Snowflake Streaming Handler
8.2.32.2.4.1 Dependencies
You can download the Dependency Downloader tool to download the dependencies by running the following script:
<OGGDIR>/DependencyDownloader/snowflake_streaming.sh.
For more information about Dependency Downloader, see Dependency Downloader.
Parent topic: Classpath Configuration
8.2.32.2.4.2 Maven Co-ordinates
<dependency> <groupId>net.snowflake</groupId> <artifactId>snowflake-ingest-sdk</artifactId> <version>2.1.0</version> <scope>provided</scope> </dependency>
Parent topic: Classpath Configuration
8.2.32.2.5 Proxy Configuration
When the Replicat process runs behind a proxy server, you can use the
jvm.bootoptions
property to set the proxy
server configuration.
Example:
jvm.bootoptions= -Dhttp.useProxy=true -Dhttp.proxyHost=<some-proxy-address.com> -Dhttp.proxy.port=<some-port-number>
Parent topic: Snowflake Streaming Handler
8.2.32.2.6 Snowflake Streaming Handler Key Pair Authentication
Snowflake Streaming API requires using key pair authentication. The path
to the private key file must be set using the property:
gg.handler.snow.privateKeyFile
.
If the private key file is encrypted, specify its password using the
property: gg.handler.snow.privateKeyFilePassword
Additionally, include the Snowflake user that is assigned to the
respective public key by setting the property gg.handler.snow.user
.
Parent topic: Snowflake Streaming Handler
8.2.32.2.7 Sample Configuration
<OGGDIR>/AdapterExamples/big-data/snowflake_streaming/
.# Note: Recommended to only edit the configuration marked as TODO gg.handlerlist=snow gg.handler.snow.type=snowflakestreaming #TODO: Edit database user. gg.handler.snow.user=<db-user> #TODO: Edit account name. gg.handler.snow.account=<account-name> #TODO: Edit role name. gg.handler.snow.role=<role-name> #TODO: Edit warehouse name. gg.handler.snow.warehouse=<warehouse-name> #TODO: Edit default database name. gg.handler.snow.database=<default-db-name> #TODO: Edit path to the private key file. gg.handler.snow.privateKeyFile=/path/to/private/key/file/rsa_key.p8 #TODO: Edit password for the private key file. gg.handler.snow.privateKeyFilePassword=<some-password> #TODO:Set the classpath to include Snowflake ingest SDK and the Snowflake JDBC driver. gg.classpath=.snowflake-ingest-sdk-2.1.1.jar
Parent topic: Snowflake Streaming Handler
8.2.32.2.8 Troubleshooting and Diagnostics
- Connectivity issues to Snowflake:
- Validate configuration parameters:
account
,user
,role
,warehouse
,privateKeyFile
,privateKeyFilePassword
, anddatabase
. - Check HTTP(S) proxy configuration if running Replicat process behind a proxy.
- Validate configuration parameters:
- DDL not applied on the target table: GG for DAA does not support DDL replication.
- SQL Errors: In case there are any errors while executing any SQL, the SQL statements along with the bind parameter values are logged into the GG for DAA handler log file.
Parent topic: Snowflake Streaming Handler