Snowflake

Snowflake is a serverless data warehouse that runs on any of the following cloud providers: Amazon Web Services (AWS), Google Cloud Platform (GCP), or Microsoft Azure. Oracle GoldenGate provides two handlers to load data into Snowflake:

Table 49 Differences between Stage and Merge, and Streaming

Details Stage and Merge Streaming Handler
Workload Any Inserts-only
Latency Micro-batch Real-time
Resources Customer-managed virtual warehouse Snowflake-managed serverless compute
API JDBC+SQL commands (PUT/COPY INTO/MERGE) Snowpipe Streaming
Data load Requires intermediary loading step from Int/Ext Stage. Direct loading into Snowflake

Topics:

Snowflake Stage and Merge Handler

Topics:

Overview

Snowflake is a serverless data warehouse that runs on any of the following cloud providers: Amazon Web Services (AWS), Google Cloud Platform (GCP), or Microsoft Azure.

The Snowflake Event Handler is used to replicate data into Snowflake.

Detailed Functionality

Replication to Snowflake uses the stage and merge data flow.

This topic contains the following:

Staging Location

The change data records from the Oracle GoldenGate trail files are formatted into Avro OCF (Object Container Format) and are then uploaded to the staging location.

Change data can be staged in one of the following object stores:

Database User Privileges

The database user used for replicating into Snowflake has to be granted the following privileges:

Prerequisites

Configuration

The configuration of the Snowflake replication properties is stored in the Replicat properties file.

  <div class="infoboxnote" markdown="1">
  **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:


  <pre class="copy"><code>TARGETDB LIBFILE libggjava.so SET property=&lt;parameter file directory&gt;/&lt;properties file name&gt;</code></pre>
  </div>

Automatic Configuration

Snowflake replication involves configuring multiple components, such as the File Writer Handler, S3 or HDFS or GCS Event Handler, and the target Snowflake Event Handler.

The Automatic Configuration functionality helps you to auto-configure these components so that the manual configuration is minimal.

The properties modified by auto-configuration is also logged in the handler log file.

To enable auto-configuration to replicate to the Snowflake target, set the parameter gg.target=snowflake.

The parameter gg.stage determines the staging location. If gg.stage is unset, then Snowflake internal stage is used.

If gg.stage is set to one of them - s3, abs, or gcs, then AWS S3, ADLS Gen2, or GCS are respectively used as the staging locations.

The JDBC Metadata provider is also automatically enabled to retrieve target table metadata from Snowflake.

File Writer Handler Configuration

The File Writer Handler name is pre-set to the value snowflake and its properties are automatically set to the required values for Snowflake.

You can add or edit a property of the File Writer Handler. For example:

gg.handler.snowflake.pathMappingTemplate=./dirout
S3 Handler Configuration

The S3 Event Handler name is pre-set to the value s3 and must be configured to match your S3 configuration.

The following is an example of editing a property of the S3 Event Handler:

gg.eventhandler.s3.bucketMappingTemplate=bucket1

For more information, see Amazon S3.

HDFS Event Handler Configuration

The Hadoop Distributed File System (HDFS) Event Handler name is pre-set to the value hdfs and it is auto-configured to write to HDFS.

Ensure that the Hadoop configuration file core-site.xml is configured to write data files to the respective container in the Azure Data Lake Storage (ADLS) Gen2 storage account. For more information, see Azure Data Lake Gen2 using Hadoop Client and ABFS.

The following is an example of editing a property of the HDFS Event handler:

gg.eventhandler.hdfs.finalizeAction=delete
Google Cloud Storage Event Handler Configuration

The Google Cloud Storage (GCS) Event Handler name is pre-set to the value gcs and must be configured to match your GCS configuration.

The following is an example of editing a GCS Event Handler property:

gg.eventhandler.gcs.bucketMappingTemplate=bucket1
Snowflake Event Handler Configuration

The Snowflake Event Handler name is pre-set to the value snowflake.

The following are configuration properties available for the Snowflake Event handler, the required ones must be changed to match your Snowflake configuration:

Table 50 Snowflake Event Handler

Properties Required/Optional Legal Values Default Explanation
gg.eventhandler.snowflake.connectionURL Required Supported connection URL. For example, jdbc:snowflake://<account_name>.snowflakecomputing.com/?warehouse=<warehouse-name>&db=<database-name> None JDBC URL to connect to Snowflake. Snowflake account name, warehouse and database must be set in the JDBC URL. The warehouse can be set using warehouse=<warehouse name>, database can set using db=<db name>. In some cases for authorization, a role should be set using role=<rolename>
gg.eventhandler.snowflake.storageIntegration Optional Storage integration name. None This parameter is required when using an external stage such as ADLS Gen2 or GCS or S
3. This is the credential for Snowflake data warehouse to access the respective Object store files. For more information, see Snowflake Storage Integration.        
gg.eventhandler.snowflake.maxConnections Optional Integer value 10 Use this parameter to control the number of concurrent JDBC database connections to the target Snowflake database.
gg.eventhandler.snowflake.dropStagingTablesOnShutdown Optional true | false false If set to true, the temporary staging tables created by Oracle GoldenGate are dropped on replicat graceful stop.
gg.handler.snowflake.fileRollInterval Optional The default unit of measure is milliseconds. You can stipulate ms, s, m, h to signify milliseconds, seconds, minutes, or hours respectively. Examples of legal values include 10000, 10000ms, 10s, 10m, or 1.5h. Values of 0 or less indicate that file rolling on time is turned off. 3m (three minutes) The parameter determines how often the data will be merged into Snowflake. Use with caution, the higher this value is the more data will need to be stored in the memory of the Replicat process. <div class="infoboxnote" id="GUID-E36A3BA9-D68F-4F7D-9F93-392147511DC7__GUID-4F23F554-2A80-4D8A-86DC-8844C1786D66"> <p class="notep1">Note:</p>Use the parameter with caution. Increasing its default value (3m) will increase the amount of data stored in the internal memory of the Replicat. This can cause out of memory errors and stop the Replicat if it runs out of memory. </div> <div class="infoboxnote" id="GUID-E36A3BA9-D68F-4F7D-9F93-392147511DC7__GUID-5A0D5A0C-FBBC-4763-AF7E-7BD607DEA565"> <p class="notep1">Note:</p>Starting with the 23ai release, the gg.aggregate.operations.flush.interval property is deprecated and no longer supported. For more information, see In-Memory Operation Aggregation</div>
gg.eventhandler.snowflake.putSQLThreads Optional Integer Value 4 Specifies the number of threads (PARALLEL clause) to use for uploading files using PUT SQL. This is only relevant when Snowflake internal stage (named stage) is used.
gg.eventhandler.snowflake.putSQLAutoCompress Optional true | false false Specifies whether Snowflake uses gzip to compress files (AUTO_COMPRESS clause) during upload using PUT SQL.<p></p>true: Files are compressed (if they are not already compressed). <p></p>false: Files are not compressed (which means, the files are uploaded as is). This is only relevant when Snowflake internal stage (named stage) is used.
gg.validate.keyupdate Optional true or false false If set to true, Replicat will validate key update operations (optype 115) and correct to normal update if no key values have changed. Compressed key update operations do not qualify for merge.
gg.eventhandler.snowflake.useCopyForInitialLoad Optional true or false true If set to true, then COPY SQL statement will be used during initial load. If set to false, then INSERT SQL statement will be used during initial load.
gg.compressed.update Optional true or false true If set the true, then this indicates that the source trail files contain compressed update operations. If set to false, then the source trail files are expected to contain uncompressed update operations.
gg.eventhandler.snowflake.connectionRetries Optional Integer Value 3 Specifies the number of times connections to the target data warehouse will be retried.
gg.eventhandler.snowflake.connectionRetryIntervalSeconds Optional Integer Value 30 Specifies the delay in seconds between connection retry attempts.
gg.eventhandler.snowflake.deleteInsert Optional true or false false If set to true, Replicat will merge records using SQL DELETE+INSERT statements instead of SQL MERGEstatement. <div class="infoboxnote" id="GUID-E36A3BA9-D68F-4F7D-9F93-392147511DC7__GUID-9D0D0855-3466-40AD-807F-920F53EF8643"> <p class="notep1">Note:</p>Applicable only if gg.compressed.update is set to false. </div>
gg.eventhandler.snowflake.detectMissingBaseRow Optional true or false false Diagnostic parameter to find UPDATE operations without base row. If set to true, Replicat will ABEND if there are UPDATE operations without base row. These rows will be collected into another table that can be investigated.
gg.eventhandler.snowflake.createTable Optional true or false true If the value is set to true, then target tables are automatically created if missing.
gg.eventhandler.snowflake.tableType Optional native or iceberg. native Indicates Snowflake table format for automatic table creation. Options are native (default): automatically create native Snowflake tables, and iceberg: automatically create Iceberg tables.
gg.eventhandler.snowflake.icebergExternalVolume Optional External volume name None Required when the property tableType is set to iceberg. An external volume name to connect Snowflake to your external cloud storage for Iceberg tables.
gg.eventhandler.snowflake.icebergBaseLocation Optional Base location path Empty ("") Applicable when the property tableType is set to iceberg. Base location path specifies a relative path from the Iceberg table’s external volume location, can be empty. Oracle GoldenGate will append the qualified table name to the base location path to create the Iceberg table.
gg.eventhandler.snowflake.driverVersion Optional v3 or v4 None Snowflake JDBC driver major version. If unset, the driver version is auto-detected from the classpath. If both v3 and v4 are present, you must set this explicitly. For v4, the handler appends disablePlatformDetection=true and DATE_OUTPUT_FORMAT=YYYY-MM-DD to the JDBC URL if they are missing.
gg.eventhandler.snowflake.driverVersion Optional 3 or 4 None Snowflake JDBC driver major version. If unset, the driver version is auto-detected from the classpath. If both v3 and v4 are present, you must set this explicitly. For v4, the handler appends disablePlatformDetection=true and DATE_OUTPUT_FORMAT=YYYY-MM-DD to the JDBC URL if they are missing.

Snowflake Storage Integration

When you use an external staging location, ensure to setup Snowflake storage integration to grant Snowflake database read permission to the files located in the cloud object store.

If the configuration property gg.stage is not set, then the storage integration is not required, and Oracle GoldenGate will default to internal stage.

Classpath Configuration

Snowflake Event Handler uses the Snowflake JDBC driver. Ensure that the classpath includes the path to the JDBC driver. If an external stage is used, then you need to also include the respective object store Event Handler’s dependencies in the classpath.

Dependencies

Snowflake JDBC driver: You can use the Dependency Downloader tool to download the JDBC driver by running the following script: <OGGDIR>/DependencyDownloader/snowflake.sh.

See Dependency Downloader for more information.

Alternatively, you can also download the JDBC driver from Maven central using the following Dependency Downloader co-ordinates:

<dependency>
   <groupId>net.snowflake</groupId>
   <artifactId>snowflake-jdbc</artifactId>
   <version>3.13.19</version>
</dependency>

Note: For v4, use the same coordinates with a v4 driver version.

Edit the gg.classpath configuration parameter to include the path to the object store Event Handler dependencies (if external stage is in use) and the Snowflake JDBC driver.

Proxy Configuration

When the Replicat process runs behind a proxy server, you can use the jvm.bootoptions property for proxy server configuration.

Example:

jvm.bootoptions=-Dhttp.useProxy=true -Dhttps.proxyHost=<some-proxy-address.com>
-Dhttps.proxyPort=80 -Dhttp.proxyHost=<some-proxy-address.com> -Dhttp.proxyPort=80

INSERTALLRECORDS Support

Stage and merge targets supports INSERTALLRECORDS parameter.

See INSERTALLRECORDS in Reference for Oracle GoldenGate. Set the INSERTALLRECORDS parameter in the Replicat parameter file (.prm). Set the INSERTALLRECORDS parameter in the Replicat parameter file (.prm)

Setting this property directs the Replicat process to use bulk insert operations to load operation data into the target table. You can tune the batch size of bulk inserts using the File Writer property gg.handler.snowflake.maxFileSize. The default value is set to 1GB. The frequency of bulk inserts can be tuned using the File writer property gg.handler.snowflake.fileRollInterval, the default value is set to 3m (three minutes).

  **Note:**

  - When using the Snowflake internal stage, the staging files can be compressed by setting ` gg.eventhandler.snowflake.putSQLAutoCompress` to `true`.

  - Consider using the Snowflake Streaming Handler for INSERT-only workloads. See [Snowflake Streaming Handler](snowflake1.html#GUID-2C2607F9-B9C6-47BE-A724-F44B7F29D6F4).

  </div>

Snowflake Key Pair Authentication

Snowflake is deprecating single-factor password authentication. Oracle recommends using Key Pair Authentication for Snowflake connections. For more information about the Snowflake authentication changes, see the Snowflake documentation https://docs.snowflake.com/en/user-guide/security-mfa-rollout.

Configure the private key path using the private_key_file property in the JDBC URL.

If the private key is encrypted, specify the password using the private_key_file_pwd property in the JDBC URL.

Specify the Snowflake user associated with the configured public key using the user property in the JDBC URL.

Add the following example JDBC URL:

jdbc:snowflake://<account_name>.snowflakecomputing.com/?warehouse=<warehouse-name>&db=<database-name>&private_key_file=/path/to/private/key/rsa_key.p8&private_key_file_pwd=<private-key-password>&user=<db-user>

When Key Pair Authentication is used, do not configure gg.eventhandler.snowflake.UserNameor gg.eventhandler.snowflake.Password.

Note: Oracle recommends upgrading Oracle GoldenGate for Distributed Applications and Analytics to version 21.10.0.0.0. If upgrading to version 21.10.0.0.0 is not possible, modify the JDBC URL so that Windows-style path separators (\) are replaced with forward slashes (/) in the private_key_file path.

Note: Oracle recommends that you upgrade Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) to version 21.10.0.0.0 or later. If you cannot upgrade to version 21.10.0.0.0 or later, update the JDBC URL by replacing \ characters with /.

Mapping Source JSON/XML to Snowflake VARIANT

The JSON and XML source column types in the Oracle GoldenGate trail gets automatically detected and mapped into Snowflake VARIANT.

You can inspect the metadata in the Oracle GoldenGate trail file for JSON and XML types using logdump.

Example: logdump output showing JSON and XML types:

022/01/06 01:38:54.717.464 Metadata             Len 679 RBA 6032
Table Name: CDB1_PDB1.TKGGU1.JSON_TAB1
*
 1)Name          2)Data Type        3)External Length  4)Fetch Offset      5)Scale         6)Level
 7)Null          8)Bump if Odd      9)Internal Length 10)Binary Length    11)Table Length 12)Most Sig DT
13)Least Sig DT 14)High Precision  15)Low Precision   16)Elementary Item  17)Occurs       18)Key Column
19)Sub DataType 20)Native DataType 21)Character Set   22)Character Length 23)LOB Type     24)Partial Type
25)Remarks
*
TDR version: 11
Definition for table CDB1_PDB1.TKGGU1.JSON_TAB1
Record Length: 81624
Columns: 7
ID                                              64     50        0  0  0 0 0     50     50     50 0 0 0 0 1    0 1   2    2       -1      0 0 0
COL                                             64   4000       56  0  0 1 0   4000   8200      0 0 0 0 0 1    0 0   0  119        0      0 1 1  JSON
COL2                                            64   4000     4062  0  0 1 0   4000   8200      0 0 0 0 0 1    0 0   0  119        0      0 1 1  JSON
COL3                                            64   4000     8068  0  0 1 0   4000   4000      0 0 0 0 0 1    0 0  10  112       -1      0 1 1  XML
SYS_NC00005$                                    64   8000    12074  0  0 1 0   4000   4000      0 0 0 0 0 1    0 0   4  113       -1      0 1 1  Hidden
SYS_IME_OSON_CF27CFDF1CEB4FA2BF85A3D6239A433C   64  65534    16080  0  0 1 0  32767  32767      0 0 0 0 0 1    0 0   4   23       -1      0 0 0  Hidden
SYS_IME_OSON_CEE1B31BB4494F6ABF31AC002BEBE941   64  65534    48852  0  0 1 0  32767  32767      0 0 0 0 0 1    0 0   4   23       -1      0 0 0  Hidden
End of definition

In this example, COL and COL2 are JSON columns and COL3 is an XML column.

Additionally, mapping to Snowflake VARIANT is supported only if the source columns are stored as text.

Operation Aggregation

Operation aggregation is the process of aggregating (merging/compressing) multiple operations on the same row into a single output operation based on a threshold.

In-Memory Operation Aggregation
Operation Aggregation Using SQL

Compressed Update Handling

A compressed update record contains values for the key columns and the modified columns.

An uncompressed update record contains values for all the columns.

Oracle GoldenGate trails may contain compressed or uncompressed update records. The default extract configuration writes compressed updates to the trails. The parameter gg.compressed.update can be set to true or false to indicate compressed or uncompressed update records.

MERGE Statement with Uncompressed Updates

In some use cases, if the trail contains uncompressed update records, then the MERGE SQL statement can be optimized for better performance by setting gg.compressed.update=false.

End-to-End Configuration

The following is an end-end configuration example which uses auto-configuration.

Location of the sample properties file: <OGGDIR>/AdapterExamples/big-data/snowflake/

# Note: Recommended to only edit the configuration marked as  TODO

gg.target=snowflake

#The Snowflake Event Handler
#TODO: Edit JDBC ConnectionUrl
gg.eventhandler.snowflake.connectionURL=jdbc:snowflake://<account_name>.snowflakecomputing.com/?warehouse=<warehouse-name>&db=<database-name>
#TODO: Edit JDBC user name
gg.eventhandler.snowflake.UserName=<db user name>
#TODO: Edit JDBC password
gg.eventhandler.snowflake.Password=<db password>

# Configuration to load GoldenGate trail operation records into Snowflake using Snowflake internal stage.
#TODO:Set the classpath to include Snowflake JDBC driver.
gg.classpath=$THIRD_PARTY_DIR/snowflake/*
#TODO:Provide sufficient memory.
jvm.bootoptions=-Xmx8g -Xms8g


# Configuration to load GoldenGate trail operation records into Snowflake using S3 stage.
#gg.stage=s3
#The S3 Event Handler
#TODO: Edit the AWS region
#gg.eventhandler.s3.region=<aws region>
#TODO: Edit the AWS S3 bucket
#gg.eventhandler.s3.bucketMappingTemplate=<s3 bucket>
#TODO:Set the classpath to include AWS Java SDK and Snowflake JDBC driver.
#gg.classpath=$THIRD_PARTY_DIR/s3/*:$THIRD_PARTY_DIR/snowflake/*
#TODO:Set the AWS access key and secret key. Provide sufficient memory.
#jvm.bootoptions=-Daws.accessKeyId=<AWS access key> -Daws.secretKey=<AWS secret key> -Xmx8g -Xms8g


# Configuration to load GoldenGate trail operation records into Snowflake using ADLS Gen2 stage.
#gg.stage=abs
# Azure Blob Event handler.
#gg.eventhandler.abs.bucketMappingTemplate=<azure_adls_gen2_container_name>
#gg.eventhandler.abs.accountName=<azure_storage_account_name>
#gg.eventhandler.abs.accountKey=<azure_storage_account_key>
#TODO: Edit snowflake storage integration to access Azure Blob Storage.
#gg.eventhandler.snowflake.storageIntegration=<azure_int>
#TODO: Edit the classpath to include HDFS Event Handler dependencies and Snowflake JDBC driver.
#gg.classpath=$THIRD_PARTY_DIR/abs/*:$THIRD_PARTY_DIR/snowflake/*
#TODO: Provide sufficient memory.
#jvm.bootoptions=-Xmx8g -Xms8g

# Configuration to load GoldenGate trail operation records into Snowflake using GCS stage.
#gg.stage=gcs
## The GCS Event handler
#TODO: Edit the GCS bucket name
#gg.eventhandler.gcs.bucketMappingTemplate=<gcs bucket>
#TODO: Edit the GCS credentialsFile
#gg.eventhandler.gcs.credentialsFile=<oggbd-project-credentials.json>
#TODO: Edit snowflake storage integration to access GCS.
#gg.eventhandler.snowflake.storageIntegration=<gcs_int>
#TODO: Edit the classpath to include GCS Java SDK and Snowflake JDBC driver.
#gg.classpath=$THIRD_PARTY_DIR/gcs/*:$THIRD_PARTY_DIR/snowflake/*
#TODO: Provide sufficient memory.
#jvm.bootoptions=-Xmx8g -Xms8g

Table Mapping

If the MAP statement does not specify a target database, then the database set in the JDBC connection URL will be used. The handler will log the default database during initialization.

Example log message:

Connection catalog is set to [DB_1].
Mapping Table

Table 51 Snowflake Mapping Table

MAP statement in the Replicat parameter file Snowflake Database Snowflake Schema Snowflake Table
MAP SCHEMA_1.TABLE_1, TARGET "schema_1"."table_1"; Default database schema_1 table_1
MAP DB_1.SCHEMA_1.TABLE_1, TARGET "db_1"."schema_1"."table_1" db_1 schema_1 table_1

Troubleshooting and Diagnostics

Restrictions on Database Object Names

Snowflake Streaming Handler

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.

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. You can leverage INSERTALLRECORDS to convert the update and delete statements if needed.

Database User Privileges

The database user used for replicating into Snowflake has to be granted the following privileges:

Prerequisites

Staging using Snowpipe Streaming API

The Snowpipe Streaming API allows for a low-latency ingest into the target table.

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:


  <pre class="copy"><code>TARGETDB LIBFILE libggjava.so SET property=&lt;parameter file directory&gt;/&lt;properties file name&gt;</code></pre>

The following are configuration properties available for the Snowflake Streaming handler, the required ones must be changed to match your Snowflake configuration.

Table 52 Snowflake Streaming Handler Configuration

Properties Required/Optional Legal Values Default Explanation
gg.handlerlist Required String value. For example, snow. None Choose the name snow for the handler.
gg.handler.<name>.type Required snowflakestreaming None Type of handler to use.
gg.handler.<name>.account Required String value. None Snowflake account name.
gg.handler.<name>.user Required String value. None Snowflake data warehouse user.
gg.handler.<name>.role Optional String value. ACCOUNTADMIN Snowflake data warehouse role.
gg.handler.<name>.warehouse Required String value. None Snowflake data warehouse name.
gg.handler.<name>.database Required String value. None Snowflake default database name used during connection.
gg.handler.<name>.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.<name>.privateKeyFilePassword Optional String value. None Specifies the password for the private key file in case the private key file is encrypted.
gg.handler.<name>.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.
gg.handler.<name>.tableType Optional native or iceberg. native Indicates Snowflake table format for automatic table creation. Options are native (default): automatically create native Snowflake tables, and iceberg: automatically create Iceberg tables.
gg.handler.<name>.icebergExternalVolume | Optional | External volume name | None | Required when the property tableType is set to iceberg. An external volume name to connect Snowflake to your external cloud storage for Iceberg tables.        
gg.handler.<name>.icebergBaseLocation Optional Base location path Empty ("") Applicable when the property tableType is set to iceberg. Base location path specifies a relative path from the Iceberg table’s external volume location, can be empty. Oracle GoldenGate appends the qualified table name to the base location path to create the Iceberg table.
gg.handler.<name>.catalogIntegrationName Optional Catalog integration name Empty ("") Applicable when the property tableType is set to iceberg. Catalog integration name of the external catalog. If this is set, then the Snowflake internal Iceberg catalog gets synchronized with the external catalog. Any other SQL engine could use the external catalog to access the Iceberg tables.
gg.eventhandler.databricks.nullableKeyComparisonStrategy Optional nvl, nullSafeOperator, nullSafeFunction, or expandedOr nullSafeOperator Controls how nullable key columns are compared in generated MERGE and DELETE+INSERT predicates. Snowflake defaults to nullSafeOperator, which uses IS NOT DISTINCT FROM; nvl, nullSafeFunction, and expandedOr remain available.

Nullable-key Comparison Strategy

The gg.eventhandler.snowflake.nullableKeyComparisonStrategy property controls how nullable key columns are compared in generated MERGE and DELETE+INSERT predicates.

By default, Snowflake uses the nullSafeOperator strategy, which generates the IS NOT DISTINCT FROM operator for nullable-key comparisons. Alternatively, you can configure the nvl, nullSafeFunction, or expandedOr strategies.

For a nullable key column ID, Snowflake generates the following predicates:

Example configuration to use the Snowflake null-safe operator:

gg.eventhandler.snowflake.nullableKeyComparisonStrategy=nullSafeOperator

Example configuration to use the Snowflake null-safe function:

gg.eventhandler.snowflake.nullableKeyComparisonStrategy=nullSafeFunction

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

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.

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>

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>

Sample Configuration

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

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.

You can perform the following steps for creating keys for Snowflake Key Pair Authentication: 1. Generate the private key using 3DES:

<pre class="copy"><code>openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8</code></pre> 2. Generate the public key by referencing the private key:


<pre class="copy"><code>openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub</code></pre> 3. Associate the public key with the Snowflake user:


<pre class="copy"><code>ALTER USER &lt;user&gt; SET RSA_PUBLIC_KEY=&#39;key_value&#39;</code></pre> 4. Configure the Snowflake JDBC URL:


<pre class="copy"><code>jdbc:snowflake://&lt;account_name&gt;.snowflakecomputing.com /?warehouse=&lt;warehouse-name&gt;&amp;db=&lt;database-name&gt;&amp;role=&lt;role&gt;&amp;user=&lt;user&gt; &amp;private_key_file=/path/to/key/file/rsa_key.p8&amp;private_key_file_pwd=&lt;password&gt;</code></pre>

Oracle recommends to use the latest Snowflake JDBC driver version for key pair authentication. For more information, see https://docs.snowflake.com/en/user-guide/key-pair-auth

Troubleshooting and Diagnostics

Supported Snowflake Date Datatypes

Oracle GoldenGate supports the replication of following Snowflake Date Datatypes:

Snowflake Iceberg Tables

Apache Iceberg tables for Snowflake combine the performance and query semantics of typical Snowflake tables with external cloud storage that you manage.

They are ideal for existing data lakes that you cannot, or choose not to, store in Snowflake. For more information, see https://docs.snowflake.com/en/user-guide/tables-iceberg.

Oracle GoldenGate can automatically create Snowflake Iceberg tables by setting the property gg.eventhandler.snowflake.tableType to iceberg.

The property gg.eventhandler.snowflake.icebergExternalVolume must be set to the external volume name to connect Snowflake to your external cloud storage for Iceberg tables. The property gg.eventhandler.snowflake.icebergBaseLocation must be set to the base location path for the Snowflake Iceberg table.

Oracle GoldenGate will append the qualified table name to the base location path to create the Iceberg table.

For example:

CREATE ICEBERG TABLE "PUBLIC"."ICEBERG_TCUSTMER" (
    "CUST_CODE" VARCHAR,
    "NAME" VARCHAR,
    "CITY" VARCHAR,
    "STATE" VARCHAR,
    PRIMARY KEY ("CUST_CODE")
) CATALOG = 'SNOWFLAKE'
EXTERNAL_VOLUME = 'iceberg_external_volume'
BASE_LOCATION = 'SCHEMA_1/ICEBERG_TCUSTMER'
CATALOG_SYNC = 'polaris_catalog_integration';

External Volume

An external volume is a named, account-level Snowflake object that you use to connect Snowflake to your external cloud storage for Iceberg tables.

You can create an external volume in Snowflake by following the steps here: https://docs.snowflake.com/en/user-guide/tables-iceberg-configure-external-volume.

External Iceberg Catalog Synchronization

To query a Snowflake-managed Apache Iceberg table using a third-party engine such as Apache Spark, you can sync the table with an external Iceberg catalog. Currently, Polaris is only external Iceberg catalog that is supported by Snowflake for synchronization.

This can be enabled by setting the property gg.eventhandler.snowflake.catalogIntegrationName to the catalog integration name pointing to the external catalog.

You can create a catalog integration for an external catalog by using the CREATE CATALOG INTEGRATION SQL statement.

See https://docs.snowflake.com/en/user-guide/tables-iceberg-polaris-sync for steps to setup catalog synchronization.

Co-existence of Snowflake Native and Iceberg Tables

The co-existence of Snowflake native and Iceberg tables can occur provided the target tables exist in the same Snowflake warehouse, a single replicat process can replicate to both Snowflake native and Iceberg tables.

Limitations