ODI 12C - Importing flat file to ODI studio

Usecase: Here, we will see how to import a flat .csv file in ODI studio.

High level steps:

• Under Topology, choose physical architecture >> File Technology and create new data server and new physical schema with the file location.
• Under Topology, choose logical architecture >> File Technology and create new logical schema.
• Under Design >> model >> create new model or use existing model >>select Technology and logical schema 
• Under model, create new datastore >> provide resource file name >> files format 
• Over data store >> view data

If you want to have variable file name, then create a variable in a project and use it as refresh variable and use it in the model data store resource name as #procjectcode.variablename

Detailed Steps with screenshots:

ODI 12C - Write file names from OS location to database

Usecase: Here we will use a procedure and jython script to take file names from OS location or mount location and write them to a database table. 

Step1: Create a database table to store the file names and other params

Create table poc_param_tbl (

SNO NUMBER,

FILE_NAME VARCHAR2(500),

PROCESSED_FLAG VARCHAR2(1),

INSERT_DT TIMESTAMP(6)

UPDATE_DT TIMESTAMP(6)

);

Step2: create database physical and logical architecture connection under Topology:

Step3: create a project, a variable and create a new procedure with 2 tasks - to trucate the table and to run the jython code.

Jython script:

import os

import datetime

sc=odiRef.getJDBCConnection("SRC")

cnt=0

filepath="#POC.LV_EPM_OBJ_STORAGE_SRC_FOL_PATH"

ct=datetime.datetime.now()

setFileNameQuery="insert into POC_param_tbl(sno, file_name,insert_dt,update_dt,processed_flag) values(?,?,?,?,?)"

for files in os.listdir(filepath):

   if files.endswith(".csv"):

      cnt =cnt+1

      sqls=sc.prepareStatement(setFileNameQuery)

sqls.setInt(1,cnt)

sqls.setString(2,files)

sqls.setTimestamp(3,ct)

sqls.setTimestamp(4,ct)

sqls.setString(5,"N")

executeStatement=sqls.executeUpdate()

sqls.close()

Notes:

1. Suppose any patching is done to the server where ODI studio installed, then we need to clear the cache and restart odi studio otherwise it will fail with "Script Engine not present error"

Path:

Cd /u01/oracle/mwh/odi/studio

./odi.sh -clean -initialize

2. Here, we have hardcoded the sourcr path in the project variable, that we can use as refresh variable and fetch from the database table.

ODI - Why ODI has two J$ views with CDC and Journalizing

The J$D view is called the data view. It stores the details of changed data. Other view stores status of data consumption by subscribers.

OIC - Create a Retry logic | To overcome putting file to AWS S3 bucket timeout issue

Usecase: We are trying to put file in S3 bucket using rest connection but unlucky that its getting failed and giving a timeout issue if its taking more than 2 minutes. So what we did, we have put a retry logic to try 3 times to put the files in S3.

Retry logic implemented:

1. Take a assign variable and assign 2 variables: v_S3FileStatus ="error" and v_S3Counter = 0.0
2. Assign file reference and PathAndFilename to move file to S3.
3. Take a while loop with condition: $v_S3FileStatus = "error" and $v_S3Counter < 3.0
1. Take a scope within the while loop
1. Drag and drop S3 Rest connection and configure to put files in s3.
2. Take a assign activity and assign v_S3FileStatus = "success"
4. Open the scope default fault handler and assign as => v_S3Counter = $v_S3Counter + 1.0 and v_S3FileStatus = "error"

Steps with screenshots:

OCI - How to get Tenancy OCID, User OCID, Private Key and Fingerprint

Tenancy OCID: get the Tenancy OCID from the OCI console on the Tenancy Details page.

Search Tenancy in the search box

Or from profile >> Tennacy

User OCID : get the user's OCID in the console on the User details page.

Profile >> User Settings

Private Key & FingerPrint: 

Profile >> User settings >> API Keys >> Add API Key >> download private key >> add >> note the fingerprint.

Note: Private key downloaded from the Oracle Cloud Infrastructure Console are in PKCS8 format. The OCI Signature version 1 security policy available with the Rest adapter only supports reading of the private key in RSA format(PKCS1) format.

If you receive the following error, you must convert the private key from PKCS8 to RSA(PKC1) format:

oracle.cloud.connector.impl.rest.security.signature.signatureException: java.lang.ClassCastException: org.bouncycastle.asn1.pkcs.PrivateKeyInfo can not be cast to org.bouncycastle.openssl.PEMKeyPair.

Convert the private key with the following command:

openssl rsa -in private_key_in_pkcs8_format.pem -out new_converted_file.pem

We can also convert it using online available site like below:

https://8gwifi.org/pemconvert.jsp