Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Hive jdbc connection string

avatar
Contributor

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

1 ACCEPTED SOLUTION

avatar

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.

View solution in original post

8 REPLIES 8

avatar

@pooja khandelwal What is the error are you getting?

avatar
Contributor

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

avatar
Guru

@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?

avatar
Contributor

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)

avatar
Guru

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

avatar

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.

avatar

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.

avatar
Contributor

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.. 🙂