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.
Now the important part is choosing the right set of KM. You can choose any of the combination from below.
|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
2. Using LKM Hive to Oracle OLH-OSCH Direct (IKM is not required)
3. Using LKM Hive to SQL SQOOP, IKM Oracle Insert
4. Using LKM Hive to Oracle (Big Data SQL),IKM Oracle Insert
5. LKM SQL Multi-Connect, IKM File-Hive to Oracle (OLH-OSCH)
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.
|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.