Databricks

Overview

Databricks is a unified, open analytics platform for building, deploying, sharing, and maintaining enterprise-grade data,analytics, and AI solutions at scale.

Detailed Functionality

Replication to Databricks uses stage and merge data flow.

The change data from the Oracle GoldenGate trails is staged in micro-batches at a temporary staging location, typically a cloud object store.

The staged records are then merged into the Databricks target tables using a merge SQL statement.

Staging location

The change data records from the GoldenGate trail files are formatted into Avro OCF (Object Container Format) and uploaded to the staging location. Change data can be staged in one of the following object stores based on the Databricks configuration.

Database User Privileges

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

Prerequisites

Configuration

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

Automatic Configuration

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

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

The properties modified by autoconfiguration is also logged in the handler log file.

To enable autoconfiguration to replicate to the Databricks target, set the parameter gg.target=databricks.

The parameter gg.stage determines the staging location.

If gg.stage is unset, ADLS Gen2 will be used as the staging location.

If gg.state is set to either abs, s3, or gcs, then ADLS Gen2, AWS S3, or GCS are respectively used as the staging locations.

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

Target tables are automatically created if missing.

File Writer Handler Configuration

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

ABS Event Handler Configuration

The 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 S3 Event Handler: gg.eventhandler.abs.bucketMappingTemplate=container1

For more information about integrating with ADLS Gen2, see Azure Blob Storage Configuration.

s3 Event 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.s 3.bucketMappingTemplate=bucket1.

For more information about integrating with s3, see S3 Event handler configuration.

GCS Event Handler Configuration

The 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

The following is an example of editing a property of the S3 Event Handler: gg.eventhandler.abs.bucketMappingTemplate=container1

For more information about integrating with GCS, see GCS Event Handler Configuration.

Databricks Event Handler Configuration

The Databricks Event Handler name is pre-set to the value databricks.

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

Table 11-20

Properties Required/Optional Legal Values Default Explanation
gg.eventhandler.databricks.connectionURL Required jdbc:databricks://<server-hostname>:443;httpPath=<http-path>[;<setting1>=<value1>;<setting2>=<value2>;<settingN>=<valueN>] None JDBC URL to connect to Databricks. See Databricks Authentication Methods.
gg.eventhandler.databricks.UserName Optional Supported database user name string. None Databricks database user or token.
gg.eventhandler.databricks.Password Optional Supported database password string. None Databricks database password or token value.
gg.eventhandler.databricks.credential Optional Storage Credential name. None External Storage credential name to access files on object storage such as ADLS Gen2, S3 or GCS. For more information, see Create a Storage Credential.
gg.eventhandler.databricks.createTable Optional true or false true If the value is set to true, then target tables are automatically created if missing.
gg.eventhandler.databricks.maxConnections Optional Integer value 10 Use this parameter to control the number of concurrent JDBC database connections to the target database.
gg.eventhandler.databricks.connectionRetries Optional Integer value 3 Specifies the number of times connections to the target data warehouse will be retried.
gg.eventhandler.databricks.connectionRetryIntervalSeconds Optional Integer value 30 Specifies the delay in seconds between connection retry attempts.
gg.eventhandler.databricks.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-FD32FD1B-494A-4E09-9DCE-DB3159ED61D6__GUID-9D0D0855-3466-40AD-807F-920F53EF8643"> <p class="notep1">Note:</p>Applicable only if gg.compressed.update is set to false. </div>
gg.eventhandler.databricks.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.databricks.dropStagingTablesOnShutdown Optional true or false false If set to true, the temporary staging tables created by Oracle GoldenGate will be dropped on replicat graceful stop.
gg.handler.databricks.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 Databricks. 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-FD32FD1B-494A-4E09-9DCE-DB3159ED61D6__GUID-7B0350FB-7A53-4A60-86A1-1168B8A09ABC"> <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-FD32FD1B-494A-4E09-9DCE-DB3159ED61D6__GUID-4F23F554-2A80-4D8A-86DC-8844C1786D66"> <p class="notep1">Note:</p></div>
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.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.databricks.deltaUniversalFormat Optional iceberg or '' (empty). '' (empty) If set to iceberg, Replicat will automatically create target tables with Delta Lake Universal Format set to iceberg. This allows you to read Delta tables with Iceberg reader clients. Universal Format can be enabled only on Databricks tables managed by Unity Catalog. This feature requires Databricks Runtime 14.3 LTS or above. See: https://docs.databricks.com/en/delta/uniform.html
gg.eventhandler.databricks.externalTableRefreshMethod Optional REFRESH or ALTER or RECREATE ALTER Determines how the external tables used for staging data files are handled.

  • REFRESH will refresh the external table on each staging event with the new contents of the staging directory.
  • ALTER will alter the external table to point to the new data file specifically.
  • RECREATE will drop and create the external table on each staging event.
gg.eventhandler.databricks.useExternalTable | Optional | true or false | false

  • If set to true, use external staging tables.
  • If set to false (default), staging data in the data files are accessed without creating an external table using direct path queries.
This approach can simplify setup and potentially avoid external table refresh issues by directly querying AVRO files from the storage location , for example, ADLS Gen2, S3, or GCS, within a view. The underlying query might look like: 
SELECT `col1`::INT `col1`, `col2`::STRING `col2`, ... FROM AVRO. `abfss://@.dfs.core.windows.net//ogg///_.dat`
(the exact columns and casts depend on the table metadata).

      
gg.eventhandler.databricks.nullableKeyComparisonStrategy Optional nvl, nullSafeOperator, nullSafeFunction, or expandedOr nullSafeOperator Controls how nullable key columns are compared when generating MERGE and DELETE+INSERT predicates. Azure Synapse Analytics supports the nvl and expandedOr comparison strategies. The nullSafeOperator and nullSafeFunction strategies are not supported and result in a configuration validation error during handler initialization.

Nullable-key Comparison Strategy

The gg.eventhandler.databricks.nullableKeyComparisonStrategy property controls how nullable key columns are compared in generated MERGE and DELETE+INSERT predicates. By default, Databricks uses the nullSafeOperator strategy, which generates the Databricks null-safe equality operator (<=>) for nullable-key comparisons. Alternatively, you can configure the nullSafeFunction, expandedOr, or nvl strategies.

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

Example configuration to use the Databricks null-safe operator:

gg.eventhandler.databricks.nullableKeyComparisonStrategy=nullSafeOperator

Example configuration to use the Databricks null-safe function:

gg.eventhandler.databricks.nullableKeyComparisonStrategy=nullSafeFunction

Authentication to Databricks

Databricks JDBC connection URLs use the following format: jdbc:databricks://<Host>:<Port>;httpPath=<http-path>[;<setting1>=<value1>;<setting2>=<value2>;<settingN>=<valueN>]

Compute Settings for the Databricks JDBC Driver

The driver requires the following compute resource configuration settings:

Table 21 Compute Settings for the Databricks JDBC Driver

Setting Description
Host The Databricks compute resource’s Server Hostname value.
Port 443
httpPath The Databricks compute resource’s HTTP Path value.
ssl 1
Connection Details for Compute Cluster

To get the connection details for the Databricks compute cluster:

Connection Details for Databricks SQL Warehouse

To get the connection details for the Databricks SQL warehouse:

Databricks Authentication Methods

The Databricks JDBC Driver supports the following authentication methods:

Note: Oracle GoldenGate does not support authentication using OAuth user-to-machine (U2M).

Databricks Personal Access Token

To create a Databricks personal access token:

Databricks Username and Password

Databricks username and password authentication is also known as Databricks basic authentication. Username and password authentication is possible only if single sign-on is disabled.

To use Databricks username and password for authentication, set the gg.eventhandler.databricks.UserName and gg.eventhandler.databricks.Password properties to the respective values.

Note: In the 23ai release, Oracle has not yet certified authentication using OAuth 2.0 tokens, OAuth user-to-machine (U2M), and OAuth machine-to-machine (M2M)

.

You can choose to configure the JDBC URL as per: https://docs.databricks.com/en/integrations/jdbc/authentication.html#oauth-20-tokens . For more information about all the authentication mechanisms supported by Databricks, see https://docs.databricks.com/en/integrations/jdbc/authentication.html

Use a Service Principal to Authenticate with Databricks (OAuth M2M)

To create a service principal at the workspace level, follow these steps:

  1. Login to the Databricks workspace as a workspace admin.

  2. Click your username in the top bar of the Databricks workspace and select Settings.

  3. Click Identity and Access.

  4. Next to Service Principals, click Manage.

  5. Click Add service principal.

  6. Click the drop-down arrow in the search box and then click Add New.

  7. Enter a name for the service principal.

  8. Click Add.

Unity Catalog

Unity Catalog provides centralized access control, auditing, lineage, and data discovery capabilities across Databricks workspaces.

In Unity Catalog, the hierarchy of primary data objects flows from metastore to table or volume:

Managed Tables

Oracle GoldenGate replicates data to Databricks managed tables. Managed tables use the DELTA table format.

Tables Inside hive_metastore Catalog

If Unity Catalog was enabled on an existing Databricks workspace, then the existing tables are available in the hive_metastore catalog.

The tables under the hive_metastore do not support primary key.

Oracle GoldenGate Replicat MAP statement should use KEYCOLS to define the key columns required for stage/merge replication.

External Tables

External tables are file-backed tables that reference data stored in an external location.

External location is an object storage such as ADLS Gen2, AWS S3, or GCS.

To manage cloud storage for external tables, unity catalog uses the following:

Create a Storage Credential

A storage credential represents an authentication and authorization mechanism for accessing data stored on your cloud tenant.

Storage Credential to Access Azure Storage Account

To create a storage credential to access an Azure storage account:

Storage Credential to Access Google Storage Account

To create a storage credential to access an Google storage account:

Create an External Location

External locations associate Unity Catalog storage credentials with cloud object storage containers.

External locations are used to define managed storage locations for catalogs and schemas, and to define locations for external tables and external volumes.

Oracle GoldenGate recommends creating an external location to simplify configuration, but you may decide to skip this step.

External Location to Access Azure Storage Account

To create an external location:

External Location to Access Google Storage Account

To create an external location:

Compute Clusters Without Unity Catalog

Legacy Databricks compute clusters may not have Unity Catalog support, or some compute clusters do not have Unity Catalog enabled.

In these clusters, access to external object storage should be configured by creating Databricks secrets and by setting spark configuration in Databricks compute. Follows one of the configuration steps below depending on your storage service.

Databricks replication without unity catalog also requires modifications in replicat properties:

Spark Configuration to Access Azure Storage Account

To create a Databricks secret, perform the following steps:

  1. Log in to your Databricks workspace.

  2. From the sidebar, select Compute.

  3. In the list of available clusters, click the target cluster’s name.

  4. Select Apps and then click Web Terminal.

    A new terminal session opens in a new tab in the web browser

  5. Create a Databricks secrets scope using databricks secrets create-scope <scope-name>. For example:

    databricks secrets create-scope gg

  6. Create a secret using the scope.

    databricks secrets put-secret --json '{  "scope":
      "\<scope-name>",  "key": "\<key-name>",  "string_value":
        "\<storage_account_access_key>"
      }'

    For exmaple:

    databricks secrets put-secret --json '{  "scope": "gg",  "key": "mykey",  "string_value":
        "---------storage-account-access-key-----------------"
      }'

In Databricks compute, set spark configuration as follows: 1. Log in to the Databricks workspace

  1. From the sidebar, select Compute.

  2. In the list of available clusters, click the target cluster’s name.

  3. Select the Configuration tab and then expand the Advanced options.

  4. In the Spark tab, specify the spark configuration for the cluster.

To configure spark configuration for Databricks on Azure:

fs.azure.account.key.<your_storage_account_name>.dfs.core.windows.net

For example:

fs.azure.account.key.<your_storage_account_name>.dfs.core.windows.net

Spark Configuration to Access Google Cloud Storage

The following information is required to configure access to Google Cloud Storage:

To create a Databricks secret: 1. Log in to your Databricks workspace.

  1. From the sidebar, select Compute.

  2. In the list of available clusters, click the target cluster’s name.

  3. Select Apps and then click Web Terminal.

    A new terminal session opens in a new tab in the web browser

  4. Create a Databricks secrets scope using databricks secrets create-scope <scope-name>.

    For example:

    databricks secrets create-scope gg

  5. Store service account private key id using Databricks.

    secrets put-secret \<scope_name>  private_key_id --string-value \<service_account_private_key_id>

  6. Store service account private key using Databricks.

    secrets put-secret \<scope_name> private_key --string-value "------BEGIN RSA PRIVATE KEY-----\n..."

In Databricks compute, set spark configuration as follows: 1. Log in to your Databricks workspace.

  1. From the sidebar, select Compute.

  2. In the list of available clusters, click the target cluster’s name.

  3. On the Configuration tab, expand Advanced options.

  4. In the Spark tab, you can specify the spark configuration for the cluster.

To configure spark configuration for Databricks on Google Cloud:

google.cloud.auth.service.account.enable true
    fs.gs.auth.service.account.email <client-email>
    fs.gs.project.id <project-id>
    fs.gs.auth.service.account.private.key 
    fs.gs.auth.service.account.private.key.id

Spark Configuration to Access AWS S3

To create a Databricks secret: 1. Log in to your Databricks workspace.

  1. From the sidebar, select Compute.

  2. In the list of available clusters, click the target cluster’s name.

  3. Select Apps and then click Web Terminal.

    A new terminal session opens in a new tab in the web browser

  4. Create a Databricks secrets scope using databricks secrets create-scope <scope-name>. For example:

    databricks secrets create-scope gg

  5. Store AWS access key id using:

    databricks secrets put-secret \<scope_name> access_key_id ---string_value\
      "\<your_aws_access_key_id>"

  6. Store AWS secret access key using:

    databricks secrets put-secret \<scope_name> secret_access_key ---string_value\
      "\<your_aws_secret_access_key>"

In Databricks compute, set spark configuration as follows: 1. Log in to your Databricks workspace.

  1. From the sidebar, select Compute.

  2. In the list of available clusters, click the target cluster’s name.

  3. On the Configuration tab, expand Advanced options.

  4. In the Spark tab, you can specify the spark configuration for the cluster.

To configure spark configuration for Databricks on Amazon S3:

fs.s3a.access.key 
fs.s3a.secret.key 
fs.s3a.endpoint s3.amazonaws.com
fs.s3a.aws.credentials.provider org.apache.hadoop.fs.s3a.SimpleAWSCredentialsProvider

Creating Databricks Secrets

To create a Databricks secret:

Note: On Unity Catalog enabled workspaces, the tables inside the hive_metastore catalog cannot use external location or external storage credentials. The tables inside the hive_metastore catalog also require the spark cluster configuration listed in this section.

Classpath Configuration

Databricks Event Handler uses the Databricks JDBC driver. Ensure that the classpath includes the path to the JDBC driver. You need to also include the respective object store Event Handler’s dependencies in the classpath.

Dependencies

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

Running this script without any input parameters will download the JDBC driver version 2.6.36. The script also can be run with a single argument to download a specific version of the JDBC driver.

For more information about Dependency Downloader, see Dependency Downloader.

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

<dependency>
          <groupId>com.databricks</groupId>
          <artifactId>databricks-jdbc</artifactId>
          <version>2.6.36</version>
      </dependency>

Proxy Configuration

When the Replicat process runs behind a proxy server, the JDBC connection URL must be appended with the following property values:

jdbc:databricks://<server-hostname>:443;httpPath=<http-path>[;<setting1>=<value1>;<setting2>=<value2>;<settingN>=<valueN>]
      ;EnableArrow=0;UseProxy=1;ProxyHost=<proxy_host>;ProxyPort=<proxy_port>

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

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.

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

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.databricks.deleteInsert=true.

End-to-End Configuration

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

The sample properties file can also be found in the directory <OGGDIR>/AdapterExamples/big-data/databricks/.

Databricks on Azure

# Configuration to load GoldenGate trail operation records into Databricks using ADLS Gen2 stage.
# Note: Recommended to only edit the configuration marked as  TODO
gg.target=databricks
# 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>

# Databricks Event Handler.
#TODO: Edit JDBC ConnectionUrl
gg.eventhandler.databricks.connectionURL=jdbc:databricks://<server-hostname>:443;httpPath=<http-path>[;<setting1>=<value1>;<setting2>=<value2>;<settingN>=<valueN>];EnableArrow=0
#TODO: Edit JDBC username or 'token'
gg.eventhandler.databricks.UserName=token
#TODO: Edit JDBC password
gg.eventhandler.databricks.Password=<password>

#TODO: Edit the classpath to include Azure Blob Event Handler dependencies and Databricks JDBC driver.
gg.classpath=$THIRD_PARTY_DIR/abs/*:$THIRD_PARTY_DIR/databricks/*
#TODO: Provide sufficient memory (at least 8GB).
jvm.bootoptions=-Xmx8g -Xms1g

Databricks on AWS

# Configuration to load GoldenGate trail operation records into Databricks using S3 stage.
# Note: Recommended to only edit the configuration marked as  TODO
gg.target=databricks
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>

# Databricks Event Handler.
#TODO: Edit JDBC ConnectionUrl
gg.eventhandler.databricks.connectionURL=jdbc:databricks://<server-hostname>:443;httpPath=<http-path>[;<setting1>=<value1>;<setting2>=<value2>;<settingN>=<valueN>];EnableArrow=0
#TODO: Edit JDBC username or 'token'
gg.eventhandler.databricks.UserName=token
#TODO: Edit JDBC password
gg.eventhandler.databricks.Password=<password>

#TODO: Edit the classpath to include GCS Event Handler dependencies and Databricks JDBC driver.
gg.classpath=$THIRD_PARTY_DIR/s3/*:$THIRD_PARTY_DIR/databricks/*
#TODO: Provide sufficient memory (at least 8GB).
jvm.bootoptions=-Xmx8g -Xms1g

Databricks on GCP

# Configuration to load GoldenGate trail operation records into Databricks using GCS stage.
# Note: Recommended to only edit the configuration marked as  TODO
gg.target=databricks
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>

# Databricks Event Handler.
#TODO: Edit JDBC ConnectionUrl
gg.eventhandler.databricks.connectionURL=jdbc:databricks://<server-hostname>:443;httpPath=<http-path>[;<setting1>=<value1>;<setting2>=<value2>;<settingN>=<valueN>];EnableArrow=0
#TODO: Edit JDBC username or 'token'
gg.eventhandler.databricks.UserName=token
#TODO: Edit JDBC password
gg.eventhandler.databricks.Password=<password>

#TODO: Edit the classpath to include GCS Event Handler dependencies and Databricks JDBC driver.
gg.classpath=$THIRD_PARTY_DIR/gcs/*:$THIRD_PARTY_DIR/databricks/*
#TODO: Provide sufficient memory (at least 8GB).
jvm.bootoptions=-Xmx8g -Xms1g

Table Mapping

If the MAP statement does not specify a target catalog, then the default catalog for the Databricks workspace will be used. The handler will log the default catalog during initialization. Example log message: Connection catalog is set to [dbx-catalog].

Mapping Table

Table 11-22

MAP statement in the Replicat parameter file Databricks Catalog Databricks Schema Databricks Table
MAP SCHEMA_1.TABLE_1, TARGET "schema_1"."table_1"; Default catalog schema_1 table_1
MAP DB_1.SCHEMA_1.TABLE_1, TARGET "db_1"."schema_1"."table_1" db_1 schema_1 table_1

Replicat mode

For Databricks Stage and Merge replication, Classic Replicat is recommended as Coordinated Replicat may result in conflicts during merge operation which fails the replicat.

Troubleshooting and Diagnostics

Restrictions on Database Object Names