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
  

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.

8.2.32.2.2 Detailed Functionality

The change data from the Oracle GoldenGate trails is appended/streamed to a Snowflake target table using the Snowpipe Streaming API. The Streaming API provides low-latency loading of rows directly into the target table and also eliminates the need for a staging area.

Note:

The Snowflake Streaming Handler supports INSERT-only workloads.

• Database User Privileges
  
• Prerequisites
  
• Staging using Snowpipe Streaming API
  

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 setting gg.handler.snow.createTable to true.

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 like INSERTUPDATE, or INSERTDELETE, then those inserts are also supported by handler.

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.

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.

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.

• Dependencies
  
• Maven Co-ordinates
  

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.

8.2.32.2.4.2 Maven Co-ordinates

Snowflake Ingest SDK:

<dependency>
      <groupId>net.snowflake</groupId>
      <artifactId>snowflake-ingest-sdk</artifactId>
      <version>2.1.0</version>
      <scope>provided</scope>
</dependency>

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>

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.

8.2.32.2.7 Sample Configuration

The sample properties file can also be found in the directory <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

8.2.32.2.8 Troubleshooting and Diagnostics

• Connectivity issues to Snowflake:
  • Validate configuration parameters: account, user, role, warehouse, privateKeyFile, privateKeyFilePassword, and database.
  • Check HTTP(S) proxy configuration if running Replicat process behind a proxy.
• 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.