How to connect JDBC Hive with IBM InfoSphere Information Server

How to connect JDBC Hive with IBM InfoSphere Information Server

In this article we'll discuss how to Hive with IBM IIS.  Apache Hive is a data warehouse software project built on top of Apache Hadoop for providing data query and analysis. Hive gives an SQL-like interface to query data stored in various databases and file systems that integrate with Hadoop.

That's much of the Intro. Let's start the process.

1. Get Hive JDBC Driver

It is important to obtain the latest DataDirect Hive JDBC driver to benefit from known fixes and performance improvements.

To get the current version of your Hive driver:

java -cp <location_of_IShive.jar> com.ibm.isf.jdbc.hive.HiveDriver

IShive.jar is typically located in: /opt/IBM/InformationServer/ASBNode/lib/java so the full command would be:

java -cp /opt/IBM/InformationServer/ASBNode/lib/java/IShive.jar com.ibm.isf.jdbc.hive.HiveDriver

Tips: You can copy "hive-jdbc.jar" from the Hadoop Master server as-well and place it in the Current directory.

2. Make Sure you have IBM JDK installed. If Not install it.

All stages: JDBC Connector, Hive Connector, and File Connector (Hive Table create option) rely on the IBM JDK for the JVM that is used to establish the JDBC connection.

3. Create "isjdbc.config" file in your IBM InfoSphere Information Server.
Generally the Location would be "/opt/IBM/InformationServer/Server/DSEngine/isjdbc.config"

Enter your "hive-jdbc.jar" location and CLASS_NAMES.

CLASSPATH=/opt/IBM/InformationServer/ASBNode/lib/java/IShive.jar;/opt/IBM/InformationServer/Server/DSEngine/jdbc_jar/hive-jdbc.jar
CLASS_NAMES=com.ibm.isf.jdbc.hive.HiveDriver

4. Now you can Configure IBM InfoSphere Information Server to connect to Hive.

The most important aspect to remember is that because the DataDirect JDBC drivers are embedded/provided by IBM InfoSphere Information Server, they are branded.  This means the beginning of the JDBC URL will be:

jdbc:ibm:hive://

Some basic JDBC URL examples are included below:

Sample URL (Non-Kerberos)

jdbc:ibm:hive://hiveserver.ibm.com:10000

Sample URL (Kerberos)

jdbc:ibm:hive://hiveserver.ibm.com:10000;AuthenticationMethod=kerberos;ServicePrincipalName=hive/hiverserver.ibm.com@IBM.COM