Hive jdbc connection string

[#1]

I am trying to connect to hive through a java program using the below connection string. It was working fine until the hdp upgrade to 2.3.

jdbc:hive2://knoxserver.net:8443/default;ssl=false;transportMode=http;httpPath=knox/sandbox/hive", "knoxusername", "knoxuserpwd"

I am able to execute the connection string through beeline as below.

"jdbc:hive2://m1.hdp.local:10010/<db>;principal=hive/_HOST@HDP.LOCAL;transportMode=http;httpPath=cliservice"

but that works only when I do a kinit from the edge node. But I want the coneection string to work in the java program without doing kinit. Also the connection string I mentioned in my question is giving a invalid connection string error in beeline. In java program I am getting the below error.

Exception in thread "main" java.sql.SQLException: Could not open connection to jdbc:hive2://hive2://knoxserver.net:8443/default;ssl=false;transportMode=http;httpPath=knox/sandbox/hive: null at org.apache.hive.jdbc.HiveConnection.openTransport(HiveConnection.java:206) at org.apache.hive.jdbc.HiveConnection.<init>(HiveConnection.java:178) at org.apache.hive.jdbc.HiveDriver.connect(HiveDriver.java:105) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at find_file_by_filename.main(find_file_by_filename.java:51) Caused by: org.apache.thrift.transport.TTransportException at org.apache.thrift.transport.TIOStreamTransport.read(TIOStreamTransport.java:132) at org.apache.thrift.transport.TTransport.readAll(TTransport.java:84) at org.apache.thrift.transport.TSaslTransport.receiveSaslMessage(TSaslTransport.java:178) at org.apache.thrift.transport.TSaslTransport.open(TSaslTransport.java:288) at org.apache.thrift.transport.TSaslClientTransport.open(TSaslClientTransport.java:37) at org.apache.hive.jdbc.HiveConnection.openTransport(HiveConnection.java:203) ... 5 more

[#2]

@pooja khandelwal What is the error are you getting?

[#3]

I am able to execute the connection string thirugh beeline as below.

"jdbc:hive2://m1.hdp.local:10010/<db>;principal=hive/_HOST@HDP.LOCAL;transportMode=http;httpPath=cliservice"

but that works only when I do a kinit from the edge node. But I want the coneection string to work in the java program without doing kinit. Also the connection string I mentioned in my question is giving a invalid connection string error in beeline. In java program I am getting the below error.

Exception in thread "main" java.sql.SQLException: Could not open connection to jdbc:hive2://hive2://knoxserver.net:8443/default;ssl=false;transportMode=http;httpPath=knox/sandbox/hive: null at org.apache.hive.jdbc.HiveConnection.openTransport(HiveConnection.java:206) at org.apache.hive.jdbc.HiveConnection.<init>(HiveConnection.java:178) at org.apache.hive.jdbc.HiveDriver.connect(HiveDriver.java:105) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at find_file_by_filename.main(find_file_by_filename.java:51) Caused by: org.apache.thrift.transport.TTransportException at org.apache.thrift.transport.TIOStreamTransport.read(TIOStreamTransport.java:132) at org.apache.thrift.transport.TTransport.readAll(TTransport.java:84) at org.apache.thrift.transport.TSaslTransport.receiveSaslMessage(TSaslTransport.java:178) at org.apache.thrift.transport.TSaslTransport.open(TSaslTransport.java:288) at org.apache.thrift.transport.TSaslClientTransport.open(TSaslClientTransport.java:37) at org.apache.hive.jdbc.HiveConnection.openTransport(HiveConnection.java:203) ... 5 more

[#4]

@pooja khandelwal If you look closely, the connection string is having two "hive2://". So are you sure that you are building correct connection string in your java program? Would you mind posting the connection code snippet of you program?

[#5]

I changed the connection string to below.

"jdbc:hive2://knoxserver.net:443/default;ssl=false;hive.server2.transport.mode=http;hive.server2.thrift.http.path=knox/sandbox/hive", "username", "pwd"

And getting the below error.

INFO: Transport Used for JDBC connection: null Exception in thread "main" java.sql.SQLException: Could not open client transport with JDBC Uri: jdbc:hive2://knoxserver.net:443/default;ssl=false;hive.server2.transport.mode=http;hive.server2.thrift.http.path=knox/sandbox/hive: null at org.apache.hive.jdbc.HiveConnection.openTransport(HiveConnection.java:237)

[#6]

Ok. So the transport mode is not being conveyed while opening a hive connection. Reason: "hive.server2.transport.mode=http". If you'd use "transportMode=http" (like @Kevin Minder has suggested below), it might just work. You should also use "httpPath=..." instead of "hive.server2.thrift.http.path=...".

Hope this helps.

Source: https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients#HiveServer2Clients-JDBC

[#7 · Accepted solution]

Try:

"jdbc:hive2://knoxserver.net:443/;ssl=false;transportMode=http;httpPath=knox/sandbox/hive", "username", "pwd"

This assumes a few things that you should double check:

1. You actually have SSL disabled in knox. Check ssl.enabled in gateway-site.xml. Otherwise use transportMode=https;sslTrustStore=<trustStoreFileName>;trustStorePassword=<trustStorePassword>. The <trustStoreFileName> and <trustStorePassword> need to be replaced with the real values from your system. This trust store needs to contain the public certificate used by the Knox server.
2. You have gatway.path in gateway.site.xml set to knox. The default is gateway. Otherwise use httpPath=gateway/sandbox/hive
3. Your topology file is named sandbox.xml. If for example you are using Ambari your topology file is probably named default.xml so you would use httpPath=knox/default/hive or gateway/default/hive as per #2.

[#8]

To remove the need for sslTrustStore & trustStorePassword you could also import the Knox SSL certificate as trusted into the cacerts truststore used by your java application, or use a CA-signed cert.

[#9]

Thank you @Alex Miller I imported the Knox SSl certificate into cacerts and used the below connection string .

( "jdbc:hive2://knoxserver.net:443/;ssl=true;transportMode=http;httpPath=knox/nn01/hive", "username", "pwd");

It finally worked.. 🙂