Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Hive jdbc connection string

Solved Go to solution
Highlighted

Hive jdbc connection string

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

Accepted Solutions
Highlighted

Re: Hive jdbc connection string

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
Highlighted

Re: Hive jdbc connection string

@pooja khandelwal What is the error are you getting?

Highlighted

Re: Hive jdbc connection string

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

Highlighted

Re: Hive jdbc connection string

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?

Highlighted

Re: Hive jdbc connection string

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)

Highlighted

Re: Hive jdbc connection string

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

Highlighted

Re: Hive jdbc connection string

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

Highlighted

Re: Hive jdbc connection string

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.

Highlighted

Re: Hive jdbc connection string

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.. :)

Don't have an account?
Coming from Hortonworks? Activate your account here