Loading Hive to Oracle DB in ODI

Loading Hive to Oracle DB in ODI:

In the previous article we tried to load  a local file to Hive table with combination of KMs. Also we saw that Data Direct KM performed extremely well as compared to others. Today we will see how we can load data from Hive table to Oracle DB using multiple Knowledge Modules. We will also do the same comparison with respect to total time taken by each set of KM to see which one performs better over the other. Well this time I found some interesting results 🙂 . Lets move ahead.

System Details: Oracle Big Data Lite VM 4.1
ODI 12.1.3 ( Applied new patch for  Bigdata Advance Option)
Database: Oracle 12C

As we have already the hive table with us from previous article, we just need to create an Oracle table and reverse it in one of the Oracle Model.

CREATE TABLE empinfo(
id number,
name varchar2(30),
salary varchar2(30),
location varchar2(30))

Once both the datastores are available, create a simple one to one mapping. In this case Hive will be our source and Oracle will be our target.

ho2

Now the important part is choosing the right set of KM. You can choose any of the combination from below.

SLNO KM Combination
1 LKM File to Oracle OLH-OSCH, IKM Oracle Insert
2 LKM Hive to Oracle OLH-OSCH Direct (IKM is not required)
3 LKM Hive to SQL SQOOP, IKM Oracle Insert
4 LKM Hive to Oracle (Big Data SQL),IKM Oracle Insert
5 LKM SQL Multi-Connect, IKM File-Hive to Oracle (OLH-OSCH)

 

1. Using LKM File to Oracle OLH-OSCH, IKM Oracle Inser

ho3ho4

 

2. Using LKM Hive to Oracle OLH-OSCH Direct  (IKM is not required)

ho5

 

3. Using LKM Hive to SQL SQOOP, IKM Oracle Insert

ho6ho7

 

4. Using LKM Hive to Oracle (Big Data SQL),IKM Oracle Insert

ho8ho9

 

5. LKM SQL Multi-Connect, IKM File-Hive to Oracle (OLH-OSCH)

ho10ho11

 

After execution you can see all the records should have been loaded to the target table.

Now its time to see the winner from these set of KMs. This test was performed in standalone mode with 6120000 records. Though we cant (we should not too ) set a benchmark on this but yes we can use the sample to see how they are going to behave in distributed mode. The result will definitely vary in your case but let me know your findings. I will update it here.

ho1

 

Results:

KM Combination Start Time Stop Time Total Time in Sec
LKM SQL Multi-Connect, IKM File-Hive to Oracle (OLH-OSCH) 6/21/2015 15:12 6/21/2015 15:15 167
LKM Hive to Oracle (Big Data SQL),IKM Oracle Insert 6/21/2015 15:20 6/21/2015 15:20 21
LKM Hive to SQL SQOOP, IKM Oracle Insert 6/21/2015 15:24 6/21/2015 15:28 226
LKM Hive to Oracle OLH-OSCH Direct 6/21/2015 15:31 6/21/2015 15:33 116
LKM File to Oracle OLH-OSCH, IKM Oracle Insert 6/21/2015 15:35 6/21/2015 15:38 170

 

As you can see clearly the winner is LKM Hive to Oracle (Big Data SQL),IKM Oracle Insert which took just 21 seconds and it is 6 to 8 times faster than others. Very interesting right? I am sure initially you must have thought it is going to be Data Direct. But thats not the case 🙂

 

Thats it for today. Keep watching for more updates on ODI Bigdata Integration.

Thanks for reading.

0

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

1 comments

Interesting result Bhabani, any insight on why the data direct was not the fastest?

Leave a reply

required*

Are you a human? *