This article describes how to connect from your on premise ODI 12c installation to Database as a Service (DBaaS) then push data from your on premise database to the cloud. We have achieved this via the native JDBC driver and the LKM Oracle to Oracle Push (DB Link).GLOBAL knowledge module. When loading data to the cloud, security and performance can be of concern however, we have not identified any security issues or decrease in performance when compared against on premise data loads.
Define a SSH tunnel
To connect ODI 12c to DBaaS, a SSH tunnel is required to create a secure path between the location on which your ODI Agent is running and DBaaS. During the configuration of DBaaS, SSH-2_RSA public and private keys are generated which are then used during the configuration of the SSH tunnel.
Create new connection in ODI Studio
Once the tunnel has been defined, a connection in ODI can be created using the native JDBC driver. The JDBC URL will include the port defined when configuring the SSH tunnel and the service name created when configuring DBaaS. A sample connection is shown below;
Once a successful connection is established, tables from DBaaS can be reverse engineered into ODI models which can then be used in mappings.
Oracle Platinum partner and recipients of the Oracle Ireland Partner of the year and Oracle Excellence award for the last two years, Vertice Cloud, has been an integral part of this successful transition of iCabbi’s vast amount of data to the Oracle Analytics Cloud. In the Video below CEO of Vertice, Tony Cassidy describes why Oracle Analytics iCabbi’s preferred BI Solution.
Create DB Link in Database
Before data can be pushed from the on premise database to the cloud, a DB Link between the database and DBaaS must be defined which will be used in the ODI mapping LKM. A sample DB Link definition is shown below, text highlighted in red italics is install specific.
CREATE DATABASE LINK “DBLINK_NAME”
connect to “SCHEMA_NAME” identified by “PASSWORD”
Create new mapping
A new mapping can now be created which we will use to load data from the on premise database to DBaaS using a DB Link over the SSH tunnel.
Above you can see the logical view of a simple mapping where the source is the on premise database and the target is DBaaS. The physical view of the same mapping is shown below;
We will be using the LKM Oracle to Oracle Push (DB Link).GLOBAL knowledge module where you define your DB Link.
We have used IKM Oracle Merge.GLOBAL with the following options for integration;
Once the mapping is complete, it can be run the same way as any other mapping. In the SQL generated by our mapping, you can see that the DB Link is being used to load data from the on premise database to DBaaS.
INTO SCHEMA_NAME.DBAAS@CLOUD_DB_LINK DBAAS
ON_PREMISE.PK_ID PK_ID ,
ON_PREMISE.FK_1_ID FK_1_ID ,
ON_PREMISE.FK_2_ID FK_2_ID ,
ON_PREMISE.FK_3_ID FK_3_ID ,
ON_PREMISE.COLUMN_1 COLUMN_1 ,
ON_PREMISE.COLUMN_2 COLUMN_2 ,
DBAAS.PK_ID = MERGE_SUBQUERY.PK_ID
WHEN NOT MATCHED THEN
WHEN MATCHED THEN
FK_1_ID = MERGE_SUBQUERY.FK_1_ID ,
FK_2_ID = MERGE_SUBQUERY.FK_2_ID ,
FK_3_ID = MERGE_SUBQUERY.FK_3_ID ,
COLUMN_1 = MERGE_SUBQUERY.COLUMN_1 ,
COLUMN_2 = MERGE_SUBQUERY.COLUMN_2 ,
COLUMN_3 = MERGE_SUBQUERY.COLUMN_3