Connecting to Knox through Squirrel using Hive JDBC driver

Question

I created a VMWare machine on my computer running Ubuntu. I set up Apache Knox on there using the demo LDAP and I'm currently trying to set up a connection string to Knox through SQuirreL. I can't use the Hortonworks Sandboxes because I need to make this compatible with Hive under Cloudera. Before I start configuring Knox, I want to be able to connect to it first using the Hive JDBC driver. Here is the string that I have so far:

jdbc:hive2://<host>:8443/;ssl=1;sslTrustStore=/gateway.jks;trustStorePassword=<master secret>?hive.server2.transport.mode=http;httpPath=gateway/default/hive

My specific questions are:

1. What path should I be using for my sslTrustStore? It's currently located in /home/<user>/Downloads/knox-1.0.0/data/security/keystores/gateway.jks. I tried the same string with the full path but still no luck.

2. What should I be using for httpPath? My VM doesn't specifically have Hive on it since Knox will be connecting to a Hadoop Node with Hive.

3. Is there anything else I'm missing in the connection string?

In SQuirreL, after I get the error message and click "stack trace", this is the general gist of what I get:

java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.sql.SQLException: [Cloudera][HiveJDBCDriver](500164) Error initialized or created transport for authentication: \home\anudeep\Downloads\knox-1.0.0\data\security\keystores\gateway.jks (The system cannot find the path specified).
    at java.util.concurrent.FutureTask.report(Unknown Source)
    at java.util.concurrent.FutureTask.get(Unknown Source)
    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(Unknown Source)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)

Answer 1

Can you connect to Hive directly without Knox ? Looking at the stack trace it appears that the keystore (gateway.jks) is not found, this could be permissions issue. Try installing Knox on the host machine. I had a lot of issues connecting to outside services (running on Host OS) from VM, but this could just be me. There are few ways to debug this, before that let me answer your questions:

1. You are right, you need to use the security/keystores/gateway.jks path so that Beeline (or any JDBC client) can trust the certificates presented by Knox.
2. Looks like you are using Apache Knox so your path would look something like gateway/sandbox/hive (you need to update the HIVE service url under sandbox.xml topology). gateway/default/hive is mostly used by Knox instances configured by Ambari, which I don't think is true in your case.
3. Try making few changes such as ssl=true, and instead of query string (?) use a colon (:) for transport.mode i.e. ;transportMode=http

This is the connection sting that works for me with Beeline

beeline -u "jdbc:hive2://<knox-host>:8443/;ssl=true;sslTrustStore=/var/lib/knox/security/keystores/gateway.jks;trustStorePassword=<trustPassword>;transportMode=http;httpPath=gateway/sandbox/hive" -n admin -p admin-password

Now onto some debugging.

1. I think it will be easier if you simply download Knox on your Host OS (instead of VM) and talk to Hive, Knox needs 'line of sight' to services it proxies, with VMs it can be tricky. Also, I find it convenient to troubleshoot and check logs. You do not need Hive running on the same machine, just a line of sight to Knox is enough.
2. Make sure hive-server.xml has the property hive.server2.servermode=http, this gets me all the time :)
3. This tutorial/example explains how to connect to Hive2 using Knox using JDBC, it uses groovy scripting but you can just look at the setup and connection strings.
4. This is another example using KnoxShell to connect to Hive2.

Hope this helps.