JDBC Hive Connection fails : Unable to read HiveServer2 uri from ZooKeeper

I have been struggling from couple of days to get my jdbc hive connection working using Zookeeper URI and after putting lot of effort finally I realised problem.

In this post I will sharing my experience  and steps I took to rectify problem.

Notes : I am having a HDP 2.6 sandbox along with my JDBC program .

Use case : Many companies does not provide HiveServer2 url for configuring your JDBC string and its important because if your hiveserver goes down  , your whole job will abort (You will be missing Hive HA capabilities). So How do I dynamically determine second Hive Server URI ?

Solution is to use Zookeeper Service Discovery feature. Zookeepers keeps all HiveServer 2 URI into its namespace. When client tries to make a connection to Hive , Zookeeper will look for available hive server and return URL to client program.

You can quickly verify HiveServer namespace in zk Path with below command on HDP.

 /usr/hdp/current/zookeeper-client/bin/zookeeper-client 

Once you do ls /hiveserver2 , you can see all hiveserver urls available in zookeeper. At this moment I have only one URL available as I am currently working with sandbox.

Great ..  !! Everything looks fine so far. Zookeeper has properly listed all your hiveserver information.

Once I had all HDP configuration in place , I started testing my code by making jdbc connection with Hive using Zookeeper . Here is sample zookeeper string.


jdbc:hive2://127.0.0.1:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2

My code was failing measurably. I was continuously getting below exception whenever i tried to execute my code.

 Caused by: java.sql.SQLException: Could not open client transport for any of the Server URI's in ZooKeeper: Unable to read HiveServer2 uri from ZooKeeper  
      at org.apache.hive.jdbc.HiveConnection.openTransport(HiveConnection.java:219)  
      at org.apache.hive.jdbc.HiveConnection.<init>(HiveConnection.java:176)  
      at org.apache.hive.jdbc.HiveDriver.connect(HiveDriver.java:105)  
      at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:307)  
      at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:200)  
      at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:710)  
      at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:644)  
      at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:466)  
      at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:143)  
      at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:115)  
      at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:102)  
      at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:126)  
      at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:85)  
      ... 118 more  
 Caused by: org.apache.hive.jdbc.ZooKeeperHiveClientException: Unable to read HiveServer2 uri from ZooKeeper  
      at org.apache.hive.jdbc.ZooKeeperHiveClientHelper.getNextServerUriFromZooKeeper(ZooKeeperHiveClientHelper.java:86)  
      at org.apache.hive.jdbc.Utils.updateConnParamsFromZooKeeper(Utils.java:532)  
      at org.apache.hive.jdbc.HiveConnection.openTransport(HiveConnection.java:217)  
      ... 136 more  
 Caused by: org.apache.hive.jdbc.ZooKeeperHiveClientException: Tried all existing HiveServer2 uris from ZooKeeper.  
      at org.apache.hive.jdbc.ZooKeeperHiveClientHelper.getNextServerUriFromZooKeeper(ZooKeeperHiveClientHelper.java:73)  
      ... 138 more  

After spending lot of time and googling about problem , I came to know that this feature was developed in Hive 0.14 and there were still some bugs in this feature.

Finally I discovered that problem is around hive version as my pom.xml was pointing all the time to 0.14 jars.

I updated pom.xml to point patch released from hortonrowks and which gave me great relief and my code started giving results..

Below are two dependencies I updated.

 <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-service -->  
 <dependency>  
   <groupId>org.apache.hive</groupId>  
   <artifactId>hive-service</artifactId>  
   <version>1.2.1000.2.4.2.10-1</version>  
 </dependency>  

 <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->  
 <dependency>  
   <groupId>org.apache.hive</groupId>  
   <artifactId>hive-jdbc</artifactId>  
   <version>1.2.1000.2.4.2.10-1</version>  
 </dependency>  

Hope this helps ... !!!

Keep sharing...!!