Oracle AI Data Platform

Overview

Oracle AI Data Platform is a new product offering that brings together data lake, analytics, and orchestration services into one unified platform. With centralized governance, built-in collaboration, and seamless integration across internal and external data sources, it helps enterprises to accelerate their data-driven decision-making and enables them to perform data analytics at scale with greater speed and efficiency.

Detailed Functionality

Overview

Replication to Oracle AI Data Platform 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 (external stage) in OCI Object Storage. The staged records are then merged into the Oracle AI Data Platform target tables using a merge SQL statement. By default, staged records are merged in every 3 minutes.

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 on the following object store:

Database User Privileges

For replicating into the Oracle AI Data Platform the database user requires the following privileges:

Prerequisites

Configuration

The Oracle AI Data Platform replication properties configuration is stored in the Replicat properties file.

Automatic Configuration

Oracle AI Data Platform replication involves configuring multiple components, such as the File Writer Handler, the OCI Event Handler, and the target Oracle AI Data Platform Event Handler.

The Automatic Configuration functionality helps you to configure these components automatically to minimize the manual configuration.

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

The parameter gg.stage determines the staging location.

To enable auto-configuration to replicate to the Oracle AI Data Platform target, set the parameter gg.target=aidp.

If gg.stage is not set, the OCI object storage will be used as the staging location.

The JDBC Metadata provider is also automatically enabled to retrieve target table metadata from the Oracle AI Data Platform.

Target tables are automatically created if missing.

File Writer Handler Configuration

The File Writer Handler name is pre-set to the value aidp and its properties are automatically set to the required values for Oracle AI Data Platform.

OCI Event Handler Configuration

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

The following is an example of editing the OCI Event property:

gg.eventhandler.oci.bucketMappingTemplate=container1

Related Topics

Oracle AI Data Platform Event Handler Configuration

The Oracle AI Data Platform Event Handler name is pre-set to the value aidp.

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

Table 44 Oracle AI Data Platform Event Handler Configuration properties

| Properties | Required/Optional | Legal Values | Default | Explanation | | — | — | — | — | — | | gg.eventhandler.aidp.connectionURL | Required | jdbc:spark://<server-hostname>;SparkServerType=AIDP;httpPath=<http-path>[;<setting1>=<value1>;<setting2>=<value2>;<settingN>=<valueN>] | None | Specifies the JDBC URL to connect to Oracle AI Data Platform. | | gg.eventhandler.aidp.maxConnections | Optional | Integer Value | 10 | You can use this parameter to control the number of concurrent JDBC database connections to the target database. | | gg.eventhandler.aidp.connectionRetries | Optional | Integer Value | 3 | Specifies the number of times connections to the target data warehouse will be retried. | | gg.eventhandler.aidp.connectionRetryIntervalSeconds | Optional | Integer Value | 30 | Specifies the delay in seconds between connection retry attempts. | | gg.eventhandler.aidp.createTable | Optional | True or False | True | If the parameter value is set to true. The missing target tables are automatically created. | | gg.eventhandler.aidp.detectMissingBaseRow | Optional | True or False | False | Diagnostic parameter to find the UPDATE operations without base row. If set to true, Replicat will <kbd class="userinput">ABEND</kbd> if there are Confidential - Oracle Internal <kbd class="userinput">UPDATE</kbd> operations without base row. These rows are collected into another table that can be investigated. | | gg.eventhandler.aidp.dropStagingTablesOnShutdown | Optional | True or False | False | If the parameter value is set to true, the temporary staging tables created by Oracle GoldenGate will be dropped on replicat graceful stop. | | gg.handler.aidp.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 lower indicate that file roll-out operation is disabled. | 3m | <p>The parameter determines how often the data will be merged into AIDP. </p>**Caution**: Increasing the default value from 3m to higher value will increase the amount of data stored in the Replicat internal memory. This can cause out of memory errors and stop the Replicat process. | | gg.eventhandler.aidp.deltaUniversalFormat | Optional | iceberg or '' " (empty). | '' " (empty) | When set to Iceberg, Replicat automatically creates target tables in Delta Lake Universal Format configured for Iceberg. This allows you to read Delta tables with Iceberg reader clients. | | gg.eventhandler.aidp.nullableKeyComparisonStrategy ` | Optional | nvl or expandedOr | nvl | Controls how nullable key columns are compared when generating MERGE predicates. Oracle AI Data Platform supports the expandedOr and nvl comparison strategies. The nullSafeOperator and nullSafeFunction strategies are not supported and cause handler initialization to fail with a configuration validation error. |

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.

Authentication to Oracle AI Data Platform

The Oracle AI Data Platform JDBC connection URLs use the following format for authentication:

jdbc:spark://<Host>;SparkServerType=AIDP;httpPath=<http-path>[;<setting1>=<value1>;<setting2>=<value2>;<settingN>=<valueN>]

Oracle AI Data Platform JDBC Driver Compute Settings

The Oracle AI Data Platform JDBC driver requires the following compute resource configuration settings.

Table 45 Oracle AI Data Platform JDBC

Setting Description
Host Specifies the Oracle AI Data Platform compute resource’s Server hostname value.
SparkServerType Specifies the Oracle AI Data Platform compute resource’s spark server type, that is, AIDP.
httpPath Specifies the Oracle AI Data Platform compute resource’s HTTP path value.

Connection Details for Oracle AI Data Platform Compute Cluster

To get the connection details for the Oracle AI Data Platform compute cluster: 1. Log in to your Oracle AI Data Platform instance.

  1. In the sidebar, select the required workspace and then click Compute.

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

  3. Click on the Connection Details tab that mentions the JDBC URL details and the driver details associated with that cluster.

  4. In the JDBC tab, the JDBC URL is available for the use.

Simba JDBC Driver for Apache Spark

Use the following steps to get the Simba JBDC driver for the Oracle AI Data Platform compute cluster: 1. Log in to your Oracle AI Data Platform instance.

  1. Select the required workspace.

  2. Click Compute.

  3. Select the target cluster’s name from the list of available clusters.

  4. Click the Connection Details tab that mentions the JDBC URL details and the driver details associated with that cluster.

  5. From the JDBC tab, select Download JDBC Driver option to obtain the JDBC driver jar.

Oracle AI Data Platform Authentication Methods

OCI API Key

The Oracle AI Data Platform JDBC driver supports the following authentication methods:

Use the following steps to generate the OCI API key and the corresponding profile: 1. In your OCI tenancy where AI Data Platform instance is present, navigate to the User icon available at the top-right corner.

  1. Click User Settings, Tokens & Keys, Add API Key.

  2. Click Generate API key pair.

  3. Download the Public and Private keys.

  4. Click Add to add the Public and Private keys.

  5. From the displayed window, copy the configuration file preview and add the contents in the ~/.oci/config file.

    Sample OCI Configuration File

    [DEFAULT]
    user=ocid1.user.oc1..mockValue
    fingerprint=mockFingerPrintValue
    tenancy=ocid1.compartment.oc1..mockValue
    region=us-phoenix-1
    key_file=<path to your private keyfile>

    Note:

    • The Simba JDBC driver has default configuration of the OCI configurations in the ~/.oci/config file with the profile name as DEFAULT.

    • If you change any of the property, provide the similar details in the JDBC URL using the keys, OCIConfigFile and OCIProfile to indicate the new configuration file path and new name of the OCI profile. For Example:

    ```\

    jdbc:spark://sample.url.com/default;SparkServerType=AIDP;httpPath=cliservice/mockPath;OCIConfigFile=/tmp/config;OCIProfile=MY_PROFILE\

    ```\

Classpath Configuration

Oracle AI Data Platform Event Handler uses the Simba JDBC driver for Apache Spark.

Ensure the following for Simba JDBC driver classpath configuration:

INSERTALLRECORDS Support

To enable bulk insert operations for Replicat, set the INSERTALLRECORDS parameter in the Replicat parameter file (.prm). This optimizes data loading into the target table.

You can adjust the bulk insert batch size and frequency using the following File writer properties:

Operation Aggregation

Operation aggregation combines multiple operations on the same row into a single output operation based on a threshold.

In-Memory Operation Aggregation

By default, operation records are aggregated in-memory. You can adjust the merge interval using the gg.handler.aidp.fileRollInterval property. The default value for gg.handler.aidp.fileRollInterval property is 3 minutes.

Note: The in-memory operation aggregation requires additional JVM memory configuration.

SQL-Based Operation Aggregation

Currently, Oracle AI Data Platform does not support the operation aggregation using SQL.

End-to-End Configuration

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

Oracle AI Data Platform with OCI Object Storage

# Properties file for Replicat
# Configuration to load GoldenGate trail operation records into AI Data Platform using OCI object store staging location.
# Note: Recommended to only edit the configuration marked as TODO
gg.target=aidp

# OCI Event handler.
#TODO: Edit the OCI region
gg.eventhandler.oci.region=<oci-region>
#TODO: Edit the OCI compartment OCID
gg.eventhandler.oci.compartmentID=<oci-compartment-ocid>
#TODO: Edit the OCI bucket name
gg.eventhandler.oci.bucketMappingTemplate=<oci-bucket-name>
#TODO: Edit the OCI Config Profile name
gg.eventhandler.oci.profile=<oci-profile-name>
#TODO: Edit the OCI Config file path
gg.eventhandler.oci.configFilePath=./oci/config

# AI Data Platform Event Handler.
#TODO: Edit JDBC ConnectionUrl
gg.eventhandler.aidp.connectionURL=<connection-url>

#TODO: Edit the classpath to include OCI Event handler dependencies and Simba JDBC driver.
gg.classpath=/path/to/oci-dep/*:/path/to/simba-jdbc-driver/*

Table Mapping

If the MAP statement does not specify a target catalog, then the Oracle AI Data Platform workspace use hive as the default catalog. The event handler will use this as the default catalog during initialization and display the log message as, for example Connection catalog is set to [hive].

Table 46 Mapping Table

MAP statement in the Replicat parameter file AIDP Catalog AIDP Schema AIDP Table
MAP SCHEMA_1.TABLE_1, TARGET "schema_1"."table_1"; Default catalog, that is, hive 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