Azure Synapse Analytics Data Warehouse

Microsoft Azure Synapse Analytics is a limitless analytics service that brings together data integration, enterprise data warehousing and Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) analytics.

Detailed Functionality

Replication to Synapse uses stage and merge data flow.

The change data is staged in a temporary location in micro-batches and eventually merged into the target table.

Azure Data Lake Storage (ADLS) Gen 2 is used as the staging area for change data.

The Synapse Event handler is used as a downstream Event handler connected to the output of the Parquet Event handler.

The Parquet Event handler loads files generated by the File Writer Handler into ADLS Gen2.

The Synapse Event handler executes SQL statements to merge the operation records staged in ADLS Gen2.

The SQL operations are performed in batches providing better throughput.

Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) uses the MERGE SQL statement or a combination of DELETE and INSERT SQL statements to perform the merge operation.

Database User Privileges

Database user used for replication has to be granted the following privileges:

Merge SQL Statement

The merge SQL statement for Azure Synapse Analytics was made generally available during the later part of the year 2022 and therefore Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) uses merge statement by default. To disable merge SQL, ensure that a Java System property is set in the jvm.bootoptions parameter.

For example:

jvm.bootoptions=-Dsynapse.use.merge.sql=false

Prerequisites

The following are the prerequisites:

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.

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

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.

If you want to use DELETE+INSERT SQL statements instead of a MERGE SQL statement, then set gg.eventhandler.synapse.deleteInsert=true.

Configuration

Automatic Configuration

Synapse replication involves configuration of multiple components, such as File Writer handler, Parquet Event handler, and Synapse Event handler.

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

The properties modified by auto configuration will also be logged in the handler log file.

To enable auto-configuration to replicate to Synapse target we need to set the parameter as follows: gg.target=synapse.

When replicating to Synapse target, customization of Parquet Event handler name and Synapse Event handler name is not allowed.

File Writer Handler Configuration

File writer handler name is pre-set to the value synapse. The following is an example to edit a property of File Writer handler:

gg.handler.synapse.pathMappingTemplate=./dirout

Parquet Event Handler Configuration

The Parquet Event Handler name is pre-set to the value parquet. The Parquet Event Handler is auto-configured to write to HDFS. The hadoop configuration file core-site.xml must be configured to write data files to the respective container in the Azure Data Lake Storage(ADLS) Gen2 account. See Azure Data Lake Gen2 using Hadoop Client and ABFS.

The following is an example to edit a property of Parquet Event handler:

gg.eventhandler.parquet.finalizeAction=delete

Azure Blob Storage Event Handler Configuration

The Azure Blob Storage (ABS) Event Handler name is pre-set to the value abs and must be configured to match your ADLS Gen2 configuration. The following is an example of editing a property of the ABS Event Handler:

gg.eventhandler.abs.bucketMappingTemplate=container1

Related Topics

Synapse Event Handler Configuration

Synapse Event Handler name is pre-set to the value synapse.

Table 19 Synapse Event Handler Configuration

Properties Required/Optional Legal Values Default Explanation
gg.eventhandler.synapse.connectionURL Required 
jdbc:sqlserver://.sql.azuresynapse.net:1433;database= ;encrypt=true; trustServerCertificate=false; hostNameInCertificate=*.sql.azuresynapse.net; loginTimeout=300;
 None JDBC URL to connect to Synapse.
gg.eventhandler.synapse.UserName Required Database username. None Synapse database user in the Synapse workspace. The username has to be qualified with the Synapse workspace name. Example: sqladminuser@synapseworkspace.
gg.eventhandler.synapse.Password Required Supported database string. None Synapse database password.
gg.eventhandler.synapse.credential Required Credential name. None Synapse database credential name to access Azure Data Lake Gen2 files. See Synapse Database Credentials for steps to create credential.
gg.eventhandler.synapse.maxConnnections Optional Integer value 10 Use this parameter to control the number of concurrent JDBC database connections to the target Synapse database.
gg.eventhandler.synapse.dropStagingTablesOnShutdown Optional true or false false If set to true, the temporary staging tables created by GoldenGate will be dropped on replicat graceful stop.
gg.maxInlineLobSize Optional Integer Value 16000 This parameter can be used to set the maximum inline size of large object (LOB) columns in bytes. For more information, see Large Object (LOB) Performance.
gg.handler.synapse.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 Synapse. 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-17E4796B-9C7D-4907-A58A-08D8C2B2BCDF__GUID-65A8AE4B-4B49-43B7-BA3A-86A12294083E"> <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-17E4796B-9C7D-4907-A58A-08D8C2B2BCDF__GUID-9D6BD20B-2564-4CBB-ABA8-BC5EC68EE929"> <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.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 true, then the source trail files are expected to contain uncompressed update operations.
gg.eventhandler.synapse.connectionRetryIntervalSeconds Optional Integer Value 30 Specifies the delay in seconds between connection retry attempts.
gg.eventhandler.synapse.connectionRetries Optional Integer Value 3 Specifies the number of times connections to the target data warehouse will be retried.
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.synapse.deleteInsert Optional true or false false If set to true, Replicat will merge records using SQL DELETE+INSERT statements instead of SQL MERGE statement. <div class="infoboxnote" id="GUID-17E4796B-9C7D-4907-A58A-08D8C2B2BCDF__GUID-5EA5EFFD-3AA7-474F-9E10-9D165E09A092"> <p class="notep1">Note:</p>Applicable only if gg.compressed.update is set to false. </div>
gg.eventhandler.synapse.createTable Optional true or false true If the value is set to true, then target tables are automatically created if missing.
gg.eventhandler.databricks.nullableKeyComparisonStrategy Optional nvl, or expandedOr nvl Controls how nullable key columns are compared in generated MERGE and DELETE+INSERT predicates. Synapse supports nvl and the portable expanded (= OR both NULL) predicate. nullSafeOperator and nullSafeFunction are not supported and fail configuration initialization.
         

Nullable-key Comparison Strategy

The gg.eventhandler.aidp.nullableKeyComparisonStrategy property specifies how nullable key columns are compared when generating MERGE predicates. By default, Oracle AI Data Platform uses the nvl strategy. To generate the portable expanded predicate, set the property to expandedOr, which produces predicates of the form ((TARGET.ID=STAGE.ID) OR (TARGET.ID IS NULL AND STAGE.ID IS NULL)).

Oracle AI Data Platform does not support target-specific null-safe comparison operators or functions for this framework. Configuring the nullSafeOperator or nullSafeFunction strategy causes handler initialization to fail with a ConfigException.

Synapse Auto Creation Target Table Configuration

The Auto Creation feature, is pre-set to true, this means, that the source tables will be automatically generated on the Synapse target if they do not already exist before replicating the data.

If it is desired for the tables to do not get automatically generated at the same time when the data is being replicated, this configuration can be turned off. The following is an example to edit a property of File Writer handler:

gg.eventhandler.synapse.createTable=false

If the Merge SQL statement is enabled, HASH will be used as the distribution method for the generated tables, and the first primary key column of each table will be chosen as the HASH distribution key. If the Merge SQL statement is disabled, ROUND_ROBIN method will be used as the distribution method, because this is Synapse’s default configuration. For more information, see Merge SQL Statement.

Synapse Database Credentials

To allow Synapse to access the data files in Azure Data Lake Gen2 storage account, follow the steps to create a database credential: 1. Connect to the respective Synapse SQL dedicated pool using the Azure Web SQL console (https://web.azuresynapse.net/en-us/).

  1. Create a DB master key if one does not already exist, using your own password.

  2. Create a database scoped credential. This credential allows Oracle GoldenGate replicat process to access Azure Storage Account.

    Provide the Azure Storage Account name and Access key when creating this credential.

    Storage Account Access keys can be retrieved from the Azure cloud console.

For example:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Your own password' ;
CREATE DATABASE SCOPED CREDENTIAL OGGBD_ADLS_credential
WITH
-- IDENTITY = '<storage_account_name>' ,
  IDENTITY = 'sanavaccountuseast' ,
-- SECRET = '<storage_account_key>'
  SECRET = 'c8C0yR-this-is-a-fake-access-key-Gc9c5mENOJ1mLyxlO1vSRDlRG0/Ke+tbAvi6xe73HAAhLtdMFZRA=='
;

Classpath Configuration

Synapse Event handler relies on the upstream File Writer handler and the Parquet Event handler.

Dependencies

<dependencies>
    <dependency>
      <groupId>com.azure</groupId>
      <artifactId>azure-storage-blob</artifactId>
      <version>12.13.0</version>
    </dependency>
    <dependency>
      <groupId>com.azure</groupId>
      <artifactId>azure-identity</artifactId>
      <version>1.3.3</version>
    </dependency>
</dependencies>

Classpath

Edit the gg.classpath configuration parameter to include the path to the Parquet Event Handler, Synapse JDBC, Azure Storage and hadoop dependencies.

For example:

gg.classpath=/path/to/parquet-deps/*:/path/to/abs-deps/*:path/to/synapse-deps/mssql-jdbc-8.4.1.jre8.jar:/path/to/hadoop-deps/*

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.synapse.maxFileSize. The default value is set to 1GB. The frequency of bulk inserts can be tuned using the File Writer property gg.handler.synapse.fileRollInterval, the default value is set to 3m (three minutes).

  <div class="infoboxnote" markdown="1">

  **Note:**

  - When using the Synapse internal stage, the staging files can be compressed by setting ` gg.handler.synapse.putSQLAutoCompress` to `true`.

  </div>

Large Object (LOB) Performance

The presence of large object (LOB) columns can impact Replicat’s apply performance. Any LOB column changes that exceed the inline threshold gg.maxInlineLobSize does not qualify for batch processing and such operations gets slower.

If the compute machine has sufficient RAM, you can increase this parameter to speed up processing.

End-to-End Configuration

The following is an end-end configuration example which uses auto-configuration for FW handler, Parquet and Synapse Event handlers.

This sample properties file can also be found in the directory AdapterExamples/big-data/synapse/synapse.props:

# Azure Synapse Analytics Data Warehouse Template
# Configuration to load GoldenGate trail operation records into Azure Synapse Analytics by chaining
# File writer handler -> Parquet Event handler -> Synapse Event handler.
# Note: Recommended to only edit the configuration marked as  TODO

gg.target=synapse

#The Parquet Event Handler
No properties are required for the Parquet Event handler.
#gg.eventhandler.parquet.finalizeAction=delete

ADLS Gen 2 stage (Using Azure Blob SDK).
#Azure Blob Event handler
#TODO: Edit the Azure Blob Storage container name
gg.eventhandler.abs.bucketMappingTemplate=<abs-container-name>
#TODO: Edit the Azure storage.account name. gg.eventhandler.abs.accountName=<storage-account-name>
#TODO: Edit the Azure storage account key.
gg.eventhandler.abs.accountKey=<storage-account-key>

#The Synapse Event Handler
#TODO: Edit JDBC ConnectionUrl
gg.eventhandler.synapse.connectionURL=jdbc:sqlserver://<synapse-workspace>.sql.azuresynapse.net:1433;database=<db-name>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=300;
#TODO: Edit JDBC user name
gg.eventhandler.synapse.UserName=<db user name>@<synapse-workspace>
#TODO: Edit JDBC password
gg.eventhandler.synapse.Password=<db password>
#TODO: Edit Credential to access Azure storage.
gg.eventhandler.synapse.credential=OGGBD_ADLS_credential
#TODO: Edit the classpath to include dependencies for Parquet Event Handler, ABS Event handler and the Synapse JDBC driver.
gg.classpath=/path/to/parquet-deps/:/path/to/abs-deps/:path/to/synapse-deps/mssql-jdbc-8.4.1.jre8.jar:/path/to/hadoop-deps/*
#TODO: Provide sufficient memory (at least 8GB).
jvm.bootoptions=-Xmx8g -Xms8g -DSYNAPSE_STAGE=parquet,abs

Authenticate using EntraID

Azure Synapse Analytics supports authentication using Microsoft Entra ID by allowing users to sign-in using their Entra ID credentials.

This can be achieved using the following Oracle GoldenGate Synapse Event Handler properties:

The output properties should look like the following:

gg.eventhandler.synapse.UserName=00000000-0000-0000-0000-000000000000
gg.eventhandler.synapse.Password=...
gg.eventhandler.synapse.connectionURL=jdbc:sqlserver://
workspace.sql.azuresynapse.net:1433;database=db;
encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=300;authentication=ActiveDirectoryServicePrincipal;

Restrictions on Database Object Names

Troubleshooting and Diagnostics