ODI 12C - Read multiple files and store them in database table

Usecase: Here, we have multiple csv files from Source team. This usecase is to process multiple files from file location and store into database table.

Lets see how we can achieve this:

Highlevel steps

1. First write all file names from the source file location to database table using Jython script procedure. Follow my below blog. https://soalicious.blogspot.com/2022/10/odi-12c-write-file-names-from-location.html
2. Import flat file to odi studio model using a variable for file name. Follow my below blog: https://soalicious.blogspot.com/2022/10/odi-12c-importing-flat-file-to-odi.html
3. Creat a package to read the file contents one by one and write to dadabase table. Which has been described in the details steps below.

Detailed steps:

Package flow: File Load to Emp Db Table

Step1: first create a parameter table to fetch all the parametes needed to poll the files like file location, mail from, mail to , archive location etc.

Create table poc_parameter_tbl(

Par_Name varchar2(200),

Par_val varchar2(500),

Insert_dt timestamp(6),

update_dt timestamp(6)

)

Step2: Create a table to store all the file contents:

Create table poc_emp(

EmpId number,

Name varchar2(200),

Dept varchar2(200),

Salary Number

)

Step3: create model

Step4: create refersh variables

Step5: create procedures

Raise error

Truncate table

Update processed flag

Step6: create mapping

Step6: create package with following sequence to process multiple files one by one:

• Drag and drop LV_EPM_OBJ_STORAGE_SRC_FOL_PATH
• Drag and drop LV_FILE_COUNT
• Drag and Drop LV_FILE_ITERATOR as set variable and assign to zero
• Drag and Drop LV_FILE_COUNT as evaluate variable and  >= 0
• If True, 
• Drag and drop PROC_Truncate_TargetTable
• Drag and drop LV_FILE_ITERATOR as set variable and increment with 1
• Drag and drop LV_FILE_ITERATOR as  evaluate variable and <= #POC.LV_FILE_COUNT
• If True,
• Drag and drop POC_FILE_NAME
• Take mapping FileToDBLoad
• Proc_Update_Processed_Flag
• Loop to LV_FILE_ITERATOR where it is crementaing with 1.
• If False,
• Take LV_ERROR_MSG as set variable and assign with "No File Found"
• Call Proc_Raise_Error

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.