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: