8.2.30 Snowflake

Topics:

• Overview
  
• Detailed Functionality
  
• Configuration
  
• Troubleshooting and Diagnostics
  

8.2.30.1 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.

8.2.30.2 Detailed Functionality

Replication to Snowflake uses the stage and merge data flow.

• The change data from the Oracle GoldenGate trails is staged in micro-batches at a temporary staging location (internal or external stage).
• The staged records are then merged into the Snowflake target tables using a merge SQL statement.

This topic contains the following:

• Staging Location
  
• Database User Privileges
  
• Prerequisites
  

8.2.30.2.1 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:

• Snowflake internal stage
• Snowflake external stage
  • AWS Simple Storage Service (S3)
  • Azure Data Lake Storage (ADLS) Gen2
  • Google Cloud Storage (GCS)

8.2.30.2.2 Database User Privileges

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

• INSERT, UPDATE, DELETE, and TRUNCATE on the target tables.
• CREATE and DROP on Snowflake named stage and external stage.
• If using external stage (S3, ADLS, GCS), CREATE, ALTER, and DROP external table.

8.2.30.2.3 Prerequisites

• Verify that the target tables exist on the Snowflake database.
• You must have Amazon Web Services, Google Cloud Platform, or Azure cloud accounts set up if you intend to use any of the external stage locations such as, S3, ADLS Gen2, or GCS.
• Snowflake JDBC driver

8.2.30.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>

• Automatic Configuration
  
• Snowflake Storage Integration
  
• Classpath Configuration
  
• Proxy Configuration
  
• INSERTALLRECORDS Support
  
• Snowflake Key Pair Authentication
  
• Mapping Source JSON/XML to Snowflake VARIANT
  
• Operation Aggregation
  
• Compressed Update Handling
  
• End-to-End Configuration
  
• Compressed Update Handling
  

8.2.30.3.1 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 Java system property SF_STAGE determines the staging location. If SF_STAGE is not set, then Snowflake internal stage is used.

If SF_STAGE is set to either s3, hdfs, 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
  
• S3 Handler Configuration
  
• HDFS Event Handler Configuration
  
• Google Cloud Storage Event Handler Configuration
  
• Snowflake Event Handler Configuration
  

8.2.30.3.1.1 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

8.2.30.3.1.2 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.

8.2.30.3.1.3 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

8.2.30.3.1.4 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

8.2.30.3.1.5 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 8-36 Snowflake Event Handler Configuration

Properties	Required/Optional	Legal Values	Default	Explanation
gg.eventhandler.snowflake.connectionURL	Required	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.
gg.eventhandler.snowflake.connectionURL	Required	Supported connection URL.	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.UserName	Required	Supported database user name string.	None	Snowflake database user.
gg.eventhandler.snowflake.Password	Required	Supported database password string.	None	Snowflake database password.
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 S3. 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.aggregate.operations.flush.interval	Optional	Integer	30000	The flush interval parameter determines how often the data will be merged into Snowflake. The value is set in milliseconds. Use with caution, the higher this value is the more data will need to be stored in the memory of the Replicat process. Note: Use the flush interval parameter with caution. Increasing its default value 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.
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. true: Files are compressed (if they are not already compressed). 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.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 minutes between connection retry attempts.

8.2.30.3.2 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 Java system property SF_STAGE is not set, then the storage integration is not required, and Oracle GoldenGate defaults to internal stage.

• Azure Data Lake Storage (ADLS) Gen2 Storage Integration: For more information about creating the storage integration for Azure, see Snowflake documentation to create the storage integration for Azure.
  Example:

  -- AS ACCOUNTADMIN
  create storage integration azure_int
  type = external_stage
  storage_provider = azure
  enabled = true
  azure_tenant_id = '<azure tenant id>'
  storage_allowed_locations = ('azure://<azure-account-name>.blob.core.windows.net/<azure-container>/');
  
  desc storage integration azure_int;
  -- Read AZURE_CONSENT_URL and accept the terms and conditions specified in the link.
  -- Read AZURE_MULTI_TENANT_APP_NAME to get the Snowflake app name to be granted Blob Read permission.
  
  grant create stage on schema <schema name> to role <role name>;
  grant usage on integration azure_int to role <role name>;

• Google Cloud Storage (GCS) Storage Integration: For more information about creating the storage integration for GCS, see Snowflake Documentation.
  Example:

  create storage integration gcs_int
  type = external_stage
  storage_provider = gcs
  enabled = true
  storage_allowed_locations = ('gcs://<gcs-bucket-name>/');
  
  desc storage integration gcs_int;
  -- Read the column STORAGE_GCP_SERVICE_ACCOUNT to get the GCP Service Account email for Snowflake.
  -- Create a GCP role with storage read permission and assign the role to the Snowflake Service account.
  
  grant create stage on schema <schema name> to role <role name>;
  grant usage on integration gcs_int to role <role name>;
  

• AWS S3 Storage Integration: For more information about creating the storage integration for S3, see Snowflake Documentation.

  Note:

  When you use S3 as the external stage, you don't need to create storage integration if you already have access to the following AWS credentials: AWS Access Key Id and Secret key. You can set AWS credentials in the jvm.bootoptions property.
• The storage integration name must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes for example, My object. Identifiers enclosed in double quotes are also case-sensitive.

8.2.30.3.3 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
  

8.2.30.3.3.1 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.

For more information about Dependency Downloader, see Dependency Downloader in the Installing and Upgrading Oracle GoldenGate for Big Data guide.

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

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

• If staging location is set to S3, then the classpath should include the S3 Event handler dependencies. See S3 Handler Configuration.
• If staging location is set to HDFS, then the classpath should include the HDFS Event handler dependencies. See HDFS Event Handler Configuration.
• If staging location is set to Google Cloud Storage (GCS), then the classpath should include the GCS Event handler dependencies. See Google Cloud Storage Event Handler Configuration.

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.

8.2.30.3.4 Proxy Configuration

When the Replicat process runs behind a proxy server, you can use the jvm.bootoptions property 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

8.2.30.3.5 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.

8.2.30.3.6 Snowflake Key Pair Authentication

Snowflake supports key pair authentication as an alternative to basic authentication using username and password.

The path to the private key file must be set in the JDBC connection URL using the property: private_key_file.

If the private key file is encrypted, then the connection URL should also include the property: private_key_file_pwd.

Additionally, the connection URL should also include the Snowflake user that is assigned the respective public key by setting the property user.

Example JDBC connection 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 using key pair authentication, ensure that the Snowflake event handler parameters Username and Password are not set.

Note:

Oracle recommends you to upgrade Oracle GoldenGate for Big Data to version 21.10.0.0.0. In case you cannot upgrade to 21.10.0.0.0, then modify the JDBC URL to replace '\' characters with '/'.

8.2.30.3.7 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.

8.2.30.3.8 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
  

8.2.30.3.8.1 In-Memory Operation Aggregation

• Operation records can be aggregated in-memory by setting gg.aggregate.operations=true.

  This is the default configuration.

• You can tune the frequency of merge interval using gg.aggregate.operations.flush.interval property, the default value is set to 30000 milliseconds (thirty seconds).
• Operation aggregation in-memory requires additional JVM memory configuration.

8.2.30.3.8.2 Operation Aggregation Using SQL

• To use SQL aggregation, it is mandatory that the trail files contain uncompressed UPDATE operation records, which means that the UPDATE operations contain full image of the row being updated.
• Operation aggregation using SQL can provide better throughput if the trails files contains uncompressed update records.
• Replicat can aggregate operations using SQL statements by setting the gg.aggregate.operations.using.sql=true.
• You can tune the frequency of merge interval using the File writer gg.handler.snowflake.fileRollInterval property, the default value is set to 3m (three minutes).
• Operation aggregation using SQL does not require additional JVM memory configuration.

8.2.30.3.9 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 configurationwrites compressed updates to the trails. The parameter gg.compressed.update can be set to true/false to indicate compressed/uncompressed update records.

• MERGE Statement with Uncompressed Updates
  

8.2.30.3.9.1 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.

8.2.30.3.10 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/

• sf.props: Configuration using internal stage
• sf-s3.props: Configuration using S3 stage.
• sf-az.props: Configuration using ADLS Gen2 stage.
• sf-gcs.props: Configuration using GCS stage.

# 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>

# Using Snowflake internal stage.
# Configuration to load GoldenGate trail operation records 
# into Snowflake Data warehouse by chaining
# File writer handler -> Snowflake Event handler.
#TODO:Set the classpath to include Snowflake JDBC driver.
gg.classpath=./snowflake-jdbc-3.13.7.jar
#TODO:Provide sufficient memory (at least 8GB).
jvm.bootoptions=-Xmx8g -Xms8g

# Using Snowflake S3 External Stage. 
# Configuration to load GoldenGate trail operation records 
# into Snowflake Data warehouse by chaining
# File writer handler -> S3 Event handler -> Snowflake Event handler.

#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=aws-java-sdk-1.11.356/lib/*:aws-java-sdk-1.11.356/third-party/lib/*:./snowflake-jdbc-3.13.7.jar
#TODO:Set the AWS access key and secret key. Provide sufficient memory (at least 8GB).
#jvm.bootoptions=-Daws.accessKeyId=<AWS access key> -Daws.secretKey=<AWS secret key> -DSF_STAGE=s3 -Xmx8g -Xms8g

# Using Snowflake ADLS Gen2 External Stage.
# Configuration to load GoldenGate trail operation records 
# into Snowflake Data warehouse by chaining
# File writer handler -> HDFS Event handler -> Snowflake Event handler.

#The HDFS Event Handler
# No properties are required for the HDFS Event handler.
# If there is a need to edit properties, check example in the following line.
#gg.eventhandler.hdfs.finalizeAction=delete
#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=./snowflake-jdbc-3.13.7.jar:hadoop-3.2.1/share/hadoop/common/*:hadoop-3.2.1/share/hadoop/common/lib/*:hadoop-3.2.1/share/hadoop/hdfs/*:hadoop-3.2.1/share/hadoop/hdfs/lib/*:hadoop-3.2.1/etc/hadoop/:hadoop-3.2.1/share/hadoop/tools/lib/* 
#TODO: Set property SF_STAGE=hdfs.  Provide sufficient memory (at least 8GB).
#jvm.bootoptions=-DSF_STAGE=hdfs -Xmx8g -Xms8g

# Using Snowflake GCS External Stage.
# Configuration to load GoldenGate trail operation records 
# into Snowflake Data warehouse by chaining
# File writer handler -> GCS Event handler -> Snowflake Event handler.

## 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=gcs-deps/*:./snowflake-jdbc-3.13.7.jar
#TODO: Set property SF_STAGE=gcs.  Provide sufficient memory (at least 8GB).
#jvm.bootoptions=-DSF_STAGE=gcs -Xmx8g -Xms8g     

8.2.30.3.11 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/uncompressed update records.

• MERGE Statement with Uncompressed Updates
  

8.2.30.3.11.1 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.

8.2.30.4 Troubleshooting and Diagnostics

• Connectivity issues to Snowflake:
  • Validate JDBC connection URL, username, and password.
  • Check HTTP(S) proxy configuration if running Replicat process behind a proxy.
• DDL not applied on the target table: Oracle GoldenGate for Big Data does not support DDL replication.
• Target table existence: It is expected that the target table exists before starting the Replicat process.

  Replicat process will ABEND if the target table is missing.

• 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 Oracle GoldenGate for Big Data handler log file.
• Co-existence of the components: When using an external stage location (S3, ADLS Gen 2 or GCS), the location/region of the machine where the Replicat process is running and the object store’s region have an impact on the overall throughput of the apply process.

  For the best possible throughput, the components need to be located ideally in the same region or as close as possible.

• Replicat ABEND due to partial LOB records in the trail file: Oracle GoldenGate for Big Data does not support replication of partial LOB data. The trail file needs to be regenerated by Oracle Integrated capture using TRANLOGOPTIONS FETCHPARTIALLOB option in the Extract parameter file.
• When replicating to more than ten target tables, the parameter maxConnnections can be increased to a higher value which can improve throughput.

  Note:

  When tuning this, increasing the parameter value would create more JDBC connections on the Snowflake data warehouse.You can consult your Snowflake Database administrators so that the data warehouse health is not compromised.
• The Snowflake JDBC driver uses the standard Java log utility. The log levels of the JDBC driver can be set using the JDBC connection parameter tracing. The tracing level can be set in the Snowflake Event handler property gg.eventhandler.snowflake.connectionURL.
  The following is an example of editing this property:

  jdbc:snowflake://<account_name>.snowflakecomputing.com/?warehouse=<warehouse-name>&db=<database-name>&tracing=SEVERE

  For more information, see https://docs.snowflake.com/en/user-guide/jdbc-parameters.html#tracing.
• Exception: net.snowflake.client.jdbc.SnowflakeReauthenticationRequest: Authentication token has expired. The user must authenticate again.

  This error occurs when are extended periods of inactivity. To resolve this, you can set the JDBC parameter CLIENT_SESSION_KEEP_ALIVE to force the database user to login after a period of inactivity in the session. For example, jdbc:snowflake://<account_name>.snowflakecomputing.com/?warehouse=<warehouse-name>&db=<database-name>&CLIENT_SESSION_KEEP_ALIVE=true

• Replicat stops with an out of memory error: Decrease the gg.aggregate.operations.flush.interval value if you are not using its default value (30000).
• Performance issue while replicating Large Object (LOB) column values: LOB processing can lead to slowness. For every LOB column that exceeds the inline LOB threshold, an UPDATE SQL is executed. Look for the following message to tune throughput during LOB processing: The current operation at position [<seqno>/<rba>] for table [<tablename>] contains a LOB column [<column name>] of length [<N>] bytes that exceeds the threshold of maximum inline LOB size [<N>]. Operation Aggregator will flush merged operations, which can degrade performance. The maximum inline LOB size in bytes can be tuned using the configuration gg.maxInlineLobSize.Check the trail files that contain LOB data and get a maximum size of BLOB/CLOB columns. Alternatively, check the source table definitions to determine the maximum size of LOB data. The default inline LOB size is set to 16000 bytes, which can be increased to a higher value so that all LOB column updates are processed in batches. The configuration property is gg.maxInlineLobSize`. For example: In gg.maxInlineLobSize=24000000 --> , all LOBs up to 24 MB are processed inline. You need to reposition the Replicat, purge the state files, data directory, and start over, so that bigger staging files are generated.
• Error message: No database is set in the current session. Please set a database in the JDBC connection url [gg.eventhandler.snowflake.connectionURL] using the option 'db=<database name>'.`

  Resolution: Set the database name in the configuration property gg.eventhandler.snowflake.connectionURL.

• Warning message: No role is set in the current session. Please set a custom role name in the JDBC connection url [gg.eventhandler.snowflake.connectionURL] using the option 'role=<role name>' if the warehouse [{}] requires a custom role to access it.

  Resolution: In some cases a custom role is required to access the Snowflake warehouse, set the role in the configuration property gg.eventhandler.snowflake.connectionURL.

• Error message: No active warehouse selected in the current session. Please set the warehouse name (and custom role name if required to access the respective warehouse) in the JDBC connection url [gg.eventhandler.snowflake.connectionURL] using the options 'warehouse=<warehouse name>' and 'role=<role name>'.

  Resolution: Set the warehouse and role in the configuration property gg.eventhandler.snowflake.connectionURL.