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)

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.

• 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

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.

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.

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

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.

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

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

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.

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>

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

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 is the process of aggregating (merging/compressing) multiple operations on the same row into a single output operation based on a threshold.

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.

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

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

# 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     

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.

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

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

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.

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