HUE and a JDBC driver

Question

I am struggeling with some problems regarding a JAR file and HUE. The big goal is to use DRILL via HUE.

As outlined here it's possible to integrate a database in HUE. So I try to do this as well. Therefor I downloaded a JAR file and try to integrate this in HUE.

So I added a notebook and my code looks like the following lines

[notebook]  
show_notebooks=true  
[[interpreters]]  
[[[hive]]] name = Hive interface = hiveserver2  
[[[jdbc]]] name=JDBC interface=jdbc options='{"url": "jdbc:drill:zk=r2c00.pmd.local:2181,r2c01.pmd.local:2181,r2c02.pmd.local:2181/drill/pacluster;schema=pacs2","driver": "org.apache.drill.jdbc.Driver","user": "admin","password": "admin"}'

I see my notebook called JDBC in the HUE interface under "notebook"

select * from pacs2.pacs2.campaign limit 10

If I want to execute the following code I get the following error:

An error occurred while calling z:java.sql.DriverManager.getConnection. : java.sql.SQLException: No suitable driver found for jdbc:drill:zk=r2c00.pmd.local:2181,r2c01.pmd.local:2181,r2c02.pmd.local:2181/drill/pacluster;schema=pacs2 at java.sql.DriverManager.getConnection(DriverManager.java:689) at java.sql.DriverManager.getConnection(DriverManager.java:247) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:231) at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:379) at py4j.Gateway.invoke(Gateway.java:259) at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:133) at py4j.commands.CallCommand.execute(CallCommand.java:79) at py4j.GatewayConnection.run(GatewayConnection.java:207) at java.lang.Thread.run(Thread.java:745)

Obviously HUE is not possible to find the driver. Surprisingly if I try to connect to the database via SQuirrel SQL it's no problem.

So my key question is where I have to locate the JDBC jar file? Is there a specific location for this file?

I am using HUE 3.11 and Cloudera CDH 5.9.0.

Thanks for all your help!

Peter

You need to add the right JDBC driver to the classpath of your application. — Mark Rotteveel
Yes, I know... I guess I have the right driver since a connection with Squirrel SQL is possible. The key question is now, how I add this driver to my application - in this case HUE? I found nothing! — Peter
That squirrel has the driver on its class path doesn't mean it is on the class path of other Java applications. — Mark Rotteveel
I know and that's the reason why I ask how I can add a jar file to the classpath used by HUE! — Peter
A quick search leads me to gethue.com/custom-sql-query-editors (although it uses a way to configure the classpath that is awful and not used by most Java applications). I hope it helps. — Mark Rotteveel

Vladyslav Pavlenko Vladyslav Pavlenko · Accepted Answer · 2017-03-21T11:40:33

Hue uses a DBProxy service to connect to Drill(and for all JDBC connection). Sometimes this service can start multiply times and you need to stop they manually.

Check how many DBProxy services is currently running:

$ ps -e -o pid,command | grep DBProxy

1503 java -cp /opt/hue-3.11.0/desktop/libs/librdbms/java-lib/dbproxy-1.0.jar: com.cloudera.hue.dbproxy.DBProxy

1202 java -cp /opt/hue-3.11.0/desktop/libs/librdbms/java-lib/dbproxy-1.0.jar:/opt/drill-1.9.0/jars/jdbc-driver/drill-jdbc-all-1.9.0.jar: com.cloudera.hue.dbproxy.DBProxy

As you can see I have two and one of them is broken (1503).

Kill all DBProxy service.

kill 1503

kill 1202
Restart Hue.
All works fine.

My CLASSPATH in this example: /opt/drill-1.9.0/jars/jdbc-driver/drill-jdbc-all-1.9.0.jar

HUE and a JDBC driver

1 Answers