Guest Authors: Alex Kong & Weida Wang - Oracle Solution Engineers
This article presents an overview on how to use Oracle Data Integrator in order to manipulate data from Oracle Cloud Infrastructure Object Storage. The scenarios here present loading the data from an object storage in Oracle Cloud Infrastructure and then move the data to Oracle Autonomous Data Warehouse (ADW).
This document could be a reference for customer have data storage in different regions and want to do the data integration and feed into a data warehouse.
Main steps are listed here:
1. Install ODI 18.104.22.168.0.
2. Patch p26669648_122130_Generic to upgrade ODI to version 22.214.171.124.1.
3. Set up Source Data Server/Physical Schema/Model in Object Storage.
4. Set up Target Data Server/Physical Schema/Model in ADW.
5. Creating a Mapping and test it.
You should have Object storage and ADW instance provisioned.
Not included in this document. You can refer below link for reference.
You need to patch ODI to version 126.96.36.199.1 firstly.
Let’s setup the topology. Right click Oracle Object Storage
Let me explain the items above.
Oracle Object Storage region. A region is a localized geographic area, and an availability domain is one or more data centers located within a region. A region is composed of several availability domains. Most Oracle Cloud Infrastructure resources are either region-specific, such as a virtual cloud network, or availability domain-specific, such as a compute instance.
b. Tenant OCID:
Tenant’s Oracle Cloud ID. Every Oracle Cloud Infrastructure resource has an Oracle-assigned unique ID called an Oracle Cloud Identifier (OCID). It's included as part of the resource's information in both the Console and API. To find your tenancy's OCID. Go to Administration-> Tenancy Details.
c. User OCID:
Oracle Cloud ID of the user logging into Oracle Object Storage.
In the Console on the page showing the user's details. To get to that page:
User OCID: api.user
d. Private Key File – Click the browse button to choose the location of the private key file (in PEM format)
Follow the steps to generate the private key and fingerprint
Specify the user api.user, need to be same with item c. User OCID
Caution: Upload the public key to Object Storage.
You can upload the PEM public key in the Console, located at https://console.us-ashburn-1.oraclecloud.com. If you don't have a login and password for the Console, contact an administrator.
Create an Oracle Object Storage physical schema using the standard procedure, in Administering Oracle Data Integrator.
Oracle Object Storage specific parameters are:
And the logical schema:
Creating an Oracle Object Storage Model
An Oracle Object Storage model is a set of data stores, corresponding to files stored in an Oracle Object Storage bucket. In a given context, the logical schema corresponds to one physical schema. You can create a model from the logical schema for the Oracle Object Storage technology. The bucket schema of this physical schema is the Oracle Object Storage bucket containing all the files. You can create new ODI Data store that will represent a file in Oracle Object Storage so that it can be used in mappings.
Input the information required and Save.
Reverse-Engineering Delimited Files from Oracle Object Storage
To perform a delimited file reverse engineering:
Click Reverse Engineer, ODI will generate the Metadata based on the header of the file.
Create a Data Server for ADW. Specify the Credential file and choose the connection details from dropdown list.
JDBC information will be there, no need to update.
And Test the connection
And then new a Physical Schema.
New the Model and Reverse Engineer.
Set the AP (Access Point) as below:
Caution: You need to run the store procedure to create credential on ADW before running the Mapping.
set define off
credential_name => 'ODI',
username => 'api.user',
password => '.};rKwO6t8***'
set define on
Mapping run finished.
And Review the data loaded in ADW.
Comparing with the source csv file in Oracle Object Storage:
With the Oracle Data Integrator 12c releases Oracle introduced several new enhancements, more source and target are supported (Oracle Object Storage, Oracle Autonomous Data Warehouse Cloud (ADW), Oracle Autonomous Transaction Processing (ATP), Oracle Enterprise Resource Planning (ERP) Cloud etc.). This document could help customer to achieve their data integration over different regions or oversea.
The ODI 12c releases continue to improve Oracle’s strategic Data Integration platform while preserving the key product differentiators: Declarative Design, Knowledge Modules, Hot-Plug-ability, and E-LT architecture.