Hadoop Hive Data Base Fetching records in Oracle SOA

Hi Everyone,

Today we are going to see how we can execute a Select statement in Hadoop Hive data base using oracle soa DB adapter.

Before going into configuration & deployment steps, One thing oracle SOA/Weblogic does not have default built in support to connect over HIVE DB, to full fill same we have to use external JAR's of hadoop hive which will be configured in oracle weblogic.

Following is list of JAR's which will be used inside configuration.

• hive-jdbc-1.2.1-standalone.jar
• hadoop-common.jar

JDev configuration for Data base connection:

Click on Driver Class "Add button".

• Driver class:org.apache.hive.jdbc.HiveDriver
• Libaray: Add Jar "Hive-jdbc-1.2.1-standalone"
• Add class path for both jar.

Check Connection Status, If you have configured JAR correctly with exact version number of DB. It will work.

SOA Project configuration:

• Create one SOA application.
• Drag & Drop DB adapter
• You can't use standard implementation like select/merge/insert of DB adapter due to non-support of HIVE into DB adapter so you have to go with "Pure SQL"

• Before deployment you need to create Data source and connection factory in Weblogic console with name "eis/DB/Hive" and jdbc/Hive.

Weblogic Console configuration:

• Copy both JAR's in SOA server and some accessiable location and add CLASSPATH entry in setDomainEnv.sh file and do a full bounce.
• Unix classpath sample below.
• POST_CLASSPATH="/home/Hive_Jar_For_Server/*****.jar${CLASSPATHSEP}/home/Hive_Jar_For_Server/*****.jar${CLASSPATHSEP}${POST_CLASSPATH}"

• export POST_CLASSPATH
• 
  
• Full bounce Domain.
• 
  

When you will start servers, You will notice Weblogic/Em console are not working, It's due to mutliple version of "commons-logging".

• Oracle weblogic console app already have a different version of same.
• Same "hive-jdbc-1.2.1-standalone" have a different version of same.
• Two different version will generate issue.
• To over come same issue we need find alternate JAR's for "hive-jdbc-1.2.1-standalone"(As this JAR is a combination of multiple JAR).

After spending 1 complete day, I got to know exact list of JAR's which will help us to connect with HIVE.

• commons-logging-1.1.3
• hadoop-common
• hive-common-1.2.1
• hive-jdbc-1.2.1
• hive-metastore-1.2.1
• hive-service-1.2.1
• httpclient-4.5.2
• httpcore-4.4.4
• libthrift-0.9.2

I am suing 1.2.1 version for HIVE as it matches with my HIVE DB, you have to use same version with your HIVE DB.

Weblogic Console re-configuration:

• POST_CLASSPATH="/Hive_Jar_For_Server/hadoop-common.jar${CLASSPATHSEP}/Hive_Jar_For_Server/hive-common-1.2.1.jar${CLASSPATHSEP}
• /Hive_Jar_For_Server/hive-jdbc-1.2.1.jar${CLASSPATHSEP}/Hive_Jar_For_Server/hive-metastore-1.2.1.jar${CLASSPATHSEP}
• /Hive_Jar_For_Server/hive-service-1.2.1.jar${CLASSPATHSEP}/Hive_Jar_For_Server/httpclient-4.5.2.jar${CLASSPATHSEP}
• /Hive_Jar_For_Server/httpcore-4.4.4.jar${CLASSPATHSEP}/Hive_Jar_For_Server/libthrift-0.9.2.jar${CLASSPATHSEP}${POST_CLASSPATH}"
• export POST_CLASSPATH
• Full bounce Domain.

Data source configuration and DB adapter Connection factory:

• Click on data source
• Click new
• Enter name and JNDI name

• Click next
• Click next
• uncheck global transaction check box, As this driver does not support XA transaction.
• Enter DB user name & password

• Click on test configuration
• if your class path is set correct and your machine have DB access it will be success message.
• Finish the changes
• Activate changes
• Click one DB adapter connection factory by using newly created data source.
• Deploy SOA POC application & test it.
• Here is your output

Congrats!!! You did..

Post me if you face any issue