Help on JDBC connection with HDP 2.6,Help with JDBC connection HDP 2.6

[#1]

I am trying to connect to HDP sandbox through JDBC using SquirrelSQL and not having any luck. Below are the set up from HDP:

I am using these jars:

Getting these errors:

java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.lang.NoClassDefFoundError: org/apache/hive/service/rpc/thrift/TCLIService$Iface at java.util.concurrent.FutureTask.report(FutureTask.java:122) at java.util.concurrent.FutureTask.get(FutureTask.java:206) at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.awaitConnection(OpenConnectionCommand.java:132) at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$100(OpenConnectionCommand.java:45) at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$2.run(OpenConnectionCommand.java:115) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: java.lang.RuntimeException: java.lang.NoClassDefFoundError: org/apache/hive/service/rpc/thrift/TCLIService$Iface at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:175) at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$000(OpenConnectionCommand.java:45) at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$1.run(OpenConnectionCommand.java:104) ... 5 more Caused by: java.lang.NoClassDefFoundError: org/apache/hive/service/rpc/thrift/TCLIService$Iface at org.apache.hive.jdbc.HiveDriver.connect(HiveDriver.java:107) at net.sourceforge.squirrel_sql.fw.sql.SQLDriverManager.getConnection(SQLDriverManager.java:133) at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:167) ... 7 more Caused by: java.lang.ClassNotFoundException: org.apache.hive.service.rpc.thrift.TCLIService$Iface at java.net.URLClassLoader.findClass(URLClassLoader.java:381) at java.lang.ClassLoader.loadClass(ClassLoader.java:424) at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331) at java.lang.ClassLoader.loadClass(ClassLoader.java:357) ... 10 more

Please help.

Thanks,

Rick

[#2 · Accepted solution]

Hi @Rick Liao

I think the issue is coming from the jars in the hive driver config within SquirrelSQL (most noticeably, you are missing the required hive-metastore jar). I just installed SquirrelSQL on my laptop, and successfully connected it to the HDP 2.6 Sandbox (same version as you 2.6.0.3) using the config in my screenshot attached here. Try matching my jar config and see if that gets you one step further. As always, if you find this post useful, please "accept" the answer.

screen-shot-2017-06-22-at-100451-am.png

[#3]

Hi @Sonu Sahi

Thanks for the reply. I tried to match your jars, but still got the same error.

Is there a username and password I have to put in?

[#4]

Hi @Rick Liao Attached here is my Alias screenshot showing its configuration. I just used hive/hive for the test credentials, which worked as I can see the pre-loaded databases in the Sandbox.

One thing I am noticing is that you are using HDP 2.4.x jars but from the screenshots above trying to connect to to an HDP 2.6.x cluster. I would strongly recommend you download fresh jars from the 2.6 cluster and only add the ones required (see my attachment).

[#5]

Hi @Sonu Sahi

Do you know where can I find those jars from the cluster? I tried following this: https://community.hortonworks.com/questions/830/how-to-do-i-get-the-hive-jdbc-driver-for-my-clinet.h... but there are no hdp or hive under /usr. I used the 2.4.x because they were the latest ones on maven.

[#6]

Hi @Rick Liao you can SCP them directly from the Sandbox, which is the most reliable way to ensure compatibility. The following examples download more jars from the Sandbox to your local machine, then, in the SquirrelSQL Drivers config, just pick only the ones you need.

scp -P 2222 root@sandbox.hortonworks.com:/usr/hdp/2.6.0.3-8/hadoop/lib/slf* /your/local/dir/

scp -P 2222 root@sandbox.hortonworks.com:/usr/hdp/2.6.0.3-8/hadoop/hadoop-common-2.7.3.2.6.0.3-8.jar /your/local/dir/

scp -P 2222 root@sandbox.hortonworks.com:/usr/hdp/2.6.0.3-8/hive/lib/* /your/local/dir/

[#7]

@Sonu Sahi Finally got it. Thanks!

[#8]

Check the all the jar versions required also match. I noticed libthrift jar version does not match the one I have (see answer below) from the HDP 2.6 sandbox /usr/hdp/2.6.0.3-8/hive/lib/