DBVisualizer and HIVE

Question

I am using DBVisualizer 9.2 and Cloudera 5.4.1

I want to setup my db visualizer such that I can query hive database from the dbvisualizer tool.

I downloaded the jdbc driver for HIVE from here

http://www.cloudera.com/downloads/connectors/hive/jdbc/2-5-16.html

I extracted all the jar files in /Users/User1/.dbvis/jdbc

But now, when I start dbvisualizer, I get an error

 Ignored as there is no matching Default Driver for "com.cloudera.hive.jdbc41.HS1Driver", "com.cloudera.hive.jdbc41.HS2Driver"
   /Users/User1/.dbvis/jdbc
    HiveJDBC41.jar
    TCLIServiceClient.jar
    hive_metastore.jar
    hive_service.jar
    libfb303-0.9.0.jar
    libthrift-0.9.0.jar
    log4j-1.2.14.jar
    ql.jar
    slf4j-api-1.5.11.jar
    slf4j-log4j12-1.5.11.jar
    zookeeper-3.4.6.jar

So my question is, has anyone successfully configured the DBVisualizer tool to connect to cloudera hive server?

I did it with the Cloudera *Impala* driver, that sports the same exact JAR dependencies, so it should work exactly the same way. Should. The trick is, DBVis probably expects the **Hive** driver to be the **Apache Hive** driver, with a different class name and different JAR dependencies. I advise you to *(a)* move the Cloudera JARs in another directory, out of range the auto-load-drivers-for-jerks feature that-takes-ages-on-every-startup; *(b)* setup **manually** the Hive driver in [Tools][Driver Manager] — Samson Scharfrichter, Mar 21 '16 at 22:54
I tried setting up the driver manually via "tools -> driver manager". After this I choose Database URL based connection and chose my new driver and URL `jdbc:hive2://foo:10000/bar`. I chose admin / admin as username password. ping was successful. but when I try to connect it says `An error occurred while establishing the connection: The selected Driver cannot handle the specified Database URL. The most common reason for this error is that the database URL contains a syntax error preventing the driver from accepting it. The error also occurs when trying to connect to a database` — Knows Not Much, Mar 22 '16 at 00:19
Tried with apache driver and got error `Type: java.lang.reflect.UndeclaredThrowableException` — Knows Not Much, Mar 22 '16 at 00:43
Your solution is actually just like this guidance(https://github.com/cyanfr/dbvis_to_hortonworks_hiveserver2/wiki/How-I-Connected-DBVisualizer-9.2.2-on-Windows-to-Hortonwork-HiveServer2), and it is recommended by the official site. Anyway it works as "It's All About getting the right jar Files". — Won Jack, Jul 25 '17 at 02:41

score 4 · Accepted Answer · answered Mar 22 '16 at 02:00

After several hours of troubleshooting. I was able to resolve the error and successfully connect to HIVE from DB Visualizer using the HIVE JDBC Driver from cloudera.

These are the steps I took

First go to Tools -> Tool Properties -> Driver finder paths.

Here register a new empty directory. this will be the place where you will download all your jars.

First in this directory extract all the JAR files which come along with the cloudera JDBC Hive Driver.

http://www.cloudera.com/downloads/connectors/hive/jdbc/2-5-4.html

Now go to Tools -> driver manager and select Hive. In the "user specified" tab. click on the "folder icon" on the right hand side and select all the jar files which you just unzipped. (not just the folder... select all jars).
Make sure you select com.cloudera.hive.jdbc41.HS2Driver
Now define connection to Hive using these parameters

url: jdbc:hive2://foo:10000/default user: admin password: admin

Now when I tried to connect, I still got errors.

"Type: java.lang.reflect.UndeclaredThrowableException"
In order to resolve the above, I you need to see the error log. (this was the most important step).

Tools -> Debug Window -> Error log

Here I saw that the mysterious "UndeclaredThrowableException" is occuring because a bunch of class files like http utils, http core, hadoop core, hive core and hive cli jar files were missing. I downloaded these jars from maven central

hadoop-core-0.20.2.jar
hive-exec-2.0.0.jar
hive-service-1.1.1.jar
httpclient-4.5.2.jar
httpcore-4.4.4.jar

and again I went inside Tools->DriverManager -> Hive -> user defined and clicked on folder on right hand side and selected each of these jars as well.

Now when I restarted DBVisualizer, I connected to hive just fine and I can query it using DBVisualizer.

Duh. That combination of "hadoop-core" and "hive-blahblah" deps is typically required to access the Metastore API. Should have been covered by `hive_metastore.jar`. And why on hell the "http" stuff - unless your HS2 service is using HTTP protocol instead of the default (binary Thrift)?? — Samson Scharfrichter, Mar 22 '16 at 09:15
BTW it's a bit awkward to mix components of Hadoop 0.20, Hive 1.1 and Hive 2.0 -- would be safer to get the JARs from your actual Hadoop cluster (with e.g. Hadoop 2.7 and Hive 1.2) — Samson Scharfrichter, Mar 22 '16 at 09:18

DBVisualizer and HIVE

1 Answers1