Vertice Logo

Odi 12c And Push to DBaaS

Share Post:

Introduction 

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

USING ‘MACHINE_NAME:PORT/DBAAS_SERVICE_NAME‘;

 

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.

MERGE

  INTO SCHEMA_NAME.DBAAS@CLOUD_DB_LINK DBAAS

  USING

  (

SELECT

  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 ,

  ON_PREMISE.COLUMN_3  COLUMN_3

FROM

  SCHEMA_NAME.ON_PREMISE ON_PREMISE  

)

  ) MERGE_SUBQUERY

  ON

  (

    DBAAS.PK_ID = MERGE_SUBQUERY.PK_ID

  )

  WHEN NOT MATCHED THEN

    INSERT

    (

      PK_ID ,

      FK_1_ID ,

      FK_2_ID ,

      FK_3_ID ,

      COLUMN_1 ,

      COLUMN_2 ,

      COLUMN_3

    )

    VALUES

    (

      MERGE_SUBQUERY.PK_ID ,

      MERGE_SUBQUERY.FK_1_ID ,

      MERGE_SUBQUERY.FK_2_ID ,

      MERGE_SUBQUERY.FK_3_ID ,

      MERGE_SUBQUERY.COLUMN_1 ,

      MERGE_SUBQUERY.COLUMN_2 ,

      MERGE_SUBQUERY.COLUMN_3

    )

  WHEN MATCHED THEN

    UPDATE SET

      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

Get in touch

Contact us today to learn more about what Vertice can do for you.

Cookies & Privacy

Vertice collects and stores your information to better customise your site experience and to optimise our website. However, your consent is required. Privacy Policy