Json File Processing in ODI 12C

Json file processing in ODI 12C:

Environment:
Oracle Data Integrator Version 12.2.1 (Build ODI_12.2.1.2.6_GENERIC_161202.0826)
Oracle XE 11g
OS: Windows 8

This article will walk through the following steps to load data from Json file to Oracle table :

1. Json file walk through
2. Setup Data Server for JSON
3. Wizard to generate XSD file using Native format builder
4. Create mapping and execute the job

This article will not cover the installation and setup of Oracle Data Integrator. The assumption is ODI 12c already installed and is working correctly. For details on how to install and configure ODI Studio see this post.

Before creating the map, lets take a look at the Json file. I have created this file using facebook graph API. It will have the list of public figures and their post information. Each post will have like and comment counts. The URL I am using for this josn file is

Json file from facebook graph API

me?fields=likes.limit(2){name,fan_count,category,posts.limit(1){name,likes.limit(0).summary(true),comments.limit(0).summary(true)}}

And the first element gets repeated for different public figures. So in total I have 4 public figures in my json file. There are some additional fields as well but we will not use them at the moment. If you want more data, change the limit parameter in the Graph API request. .

Json file from facebook graph API

Target table structure for our mapping:

CREATE TABLE FB_PUBLIC_FIGURE(
PFIGURE_NAME VARCHAR2(1000),
PCATEGORY VARCHAR2(1000),
POST_NAME VARCHAR2(1000),
POST_LIKE_COUNT VARCHAR2(1000),
POST_COMMENT_COUNT VARCHAR2(1000)
);

Now connect to ODI studio and create a Data Server under Complex File technology. Note that the complex file driver works in a series of steps.

  1. The complex file is translated to an intermediate XML file using the Native Schema (nXSD) file. Note that no physical file is created for the intermediate XML file but a streaming XML structure.
  2. The driver loads the XML structure and data into a relational schema, using a XML to SQL Mapping.
  3. The user works on the relational schema, manipulating data through regular SQL statements or specific driver commands for driver operations.
  4. Upon disconnection or user request, the Complex Files driver synchronizes the data and structure stored in the schema back to the complex file.

To know more on this refer oracle documentation.

Here is the driver details that I have configured.

Since all the complex file needs to have one XSD , lets generate it using the in-build wizard. Click on Edit nXSD button and follow the steps below.

Native format builder in ODI 12c

Select the location where xsd file will be created.

Native format builder in ODI 12c

Select Json Interchange Format

Native format builder in ODI 12c

Select the json file.

Native format builder in ODI 12c

This is the preview of XSD file. Now close the wizard.

Native format builder in ODI 12c

Next we have to create a model. Follow the standard process for Complex File technology and do the selective reverse engineering. Choose the required tables for our mapping.  In Similar fashion you can create the model for oracle table.

Now create a mapping and drag all the tables as shown above. The joins will be automatically mapped based on primary and foreign key relationship.

Logical diagram of json data stores

We will now do the mapping with target table. I will go ahead with the mapping for name, fan_count, category, likes total_count, comments total_count. Lets take a look at the diagram generated in physical tab.

Physcial diagram of json data stores

We are now done with the development. Save the mapping and execute it.

As you can see I am getting following error in operator.

ODI-1227: Task Insert new rows-LKM SQL to SQL (Built-In)-Load DEFAULT_AP fails on the source connection ds_json.
Caused By: java.sql.SQLSyntaxErrorException: unexpected token: INNER : line: 8
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)

I did some quick research and didn’t find any satisfactory information from Google. Then I looked at the properties of Complex File Technology and thought to give a try with the high lighted check box below which was unticked earlier. Save everything and execute the mapping again.

This time the execution is successful. Now ODI generates parenthesis for all the tables that are joined with each other.

This is how the data look in the target table.

Thats it for today. Let me know if you need any help in setting up the mapping. In the coming post I will do the same in Talend Open Studio and try to compare the development effort required in both tools. Also one thing I noticed here is that if there are two complex type having same name then ODI is not drilling down in the hierarchy. Thats why I had to rename the likes element in my json file to some unique name. I was assuming ODI will give some auto naming convention to avoid multiple data stores having same name but thats not the case.

Thank you and stay tuned!!!

2

About the author

Bhabani(http://dwteam.in) - Currently Bhabani is working as Sr Development Engineer at Harman International. He has good expertise on Oracle, Oracle Data Integrator, Pervasive Data Integrator, MSBI, Talend and Java. He is also contributing in ODI-OTN forum for last 5 years. He is from India. If you want to reach him then please visit contact us page. If you have any doubts or concerns on the above article, please put your question here. Dw Team will try to respond it as soon as possible. Also dont forget to provide your comments / suggestions / feedback for further improvement. Thanks for your time.

Similar Posts

Leave a reply

required*

Are you a human? *