Oracle ADW Bulk Load with OIC to Improve Data Load Performance

The Oracle ADW Bulk Load feature typically refers to the ability to efficiently load large volumes of data into an Oracle ADW instance using specialized techniques to improve performance.

My Test Case - 

Load 3 files , Each file has 15 Columns  and 300K records. Total Time taken is 1 minutes 30 sec to load 900K records

In this blog I will go over steps , how we setup an OIC orchestration that will invoke ADW Adapter with operation as Bulk Load.

First let's talk about some important features about  ADW Bulk Load

High-Volume Data Loading: Oracle ADW Bulk Load is designed for scenarios where you need to load a large volume of data into your Autonomous Data Warehouse instance efficiently.

Performance Optimization: Bulk loading techniques are employed to optimize the data loading process. This includes methods such as direct path loading, parallel processing, and minimizing logging for faster inserts.

Minimized Resource Utilization: The bulk load feature aims to minimize resource utilization and maximize throughput during the data loading process.

Parallel Loading: Bulk loading often involves parallel loading, where multiple threads or processes are used to load data concurrently, speeding up the overall loading time.

Integration with OIC  & ADW Adapter:

In the context of Oracle Integration Cloud, OIC ADW Adapter provides a set of pre-built, reusable connectors and components for interacting with Oracle ADW. The adapter is used within OIC integrations to facilitate seamless communication between different systems.

Feel free to reach us at inquiry@bizinsightinc.com for any additional information on this Integration

Prerequisites for creating in OIC flow with ADW Adapter for Bulk Load:

1. An Oracle Integration Cloud instance
2. An Oracle Autonomous Transaction Processing (ADW) database
3. A configured Oracle ATP Adapter connection within Oracle Integration Cloud
4. A file containing the data to be loaded, in a supported format (CSV, XML, or JSON)
5. An Oracle Object Storage bucket to temporarily store the file

Steps:

Create an Integration:

1. Access the Oracle Integration Cloud console.
2. Create a new integration.
3. Choose a trigger based on your needs (e.g., scheduled trigger for regular imports, or an HTTP trigger for on-demand imports).

Add Oracle ATP Adapter:

1. Drag and drop the Oracle ADW Adapter onto the integration canvas.
2. Select the "Perform Bulk Data Import Operations" operation.

Configure Source File:

1. Specify the Object Storage bucket and file path where the data is located.
2. If needed, use a mapper to transform the data format.

Map Fields:

1. Map the fields in the source file to the corresponding columns in the ADW database table.
2. Ensure data types align correctly.

Specify Target Table:

1. Provide the name of the ADW database table where you want to load the data.

Configure Security (if needed):

1. While Configuring ADW Adapter connection in OIC , use JDBC With OCI Signature security policy, provide required details like compartment OCID and wallet password.

Deploy and Test:

1. Save and deploy the integration.
2. Test it to ensure successful data loading into the ATP database.

Setup ADW Connection 

For Additional Details on how to setup Private keys  refer https://blogs.oracle.com/integration/post/oci-signature-in-rest-adapter-for-oci-rest-apis

Once Connection is setup , login to OCI and execute below steps

 

1. Setup Compartment and Bucket in OIC

Note - for my testing I have created my Bucket in root compartment

Setup OCI Compartment Policy

Note - I have created bucket Under Root compartment /Tenancy , that is why you don't see any Compartment name in below Screen shot ( Rather it says "in Tenancy") , but if you create bucket in a Compartment , then please define same policy for your Compartment

OIC Orchestration - 

 

1.Create Scheduled based Orchestration

 

2.Add FTP connection to download files from SFTP server - This is the server where you will place the file

 

3.Add ADW DB adapter and configure that for Bulk Load - Here you will setup OICI Bucket /Table Name in ADW as well as table structure

One execution , OIC will move the File to OIC Bucket and then insert data into ADW Folder . While configuring BULK Load for ADW adapter you have option to delete rthe file from OCI bucket once data from file is loaded into ADW table ( for my testing I have Unckeck that and that is why you see the files remain in OCI bucket once OIC job completed)

Configure ADW Adapter

Setup OIC Bucket  ( I have uncheck the check box "Delete file")

 

Setup the columns that you would like to load from File in OCI bucket , I have selected all the Columns

 

 

OCI Flow