question Re: Hive jdbc connection string in Archives of Support Questions (Read Only)

Hive jdbc connection string

pooja_khandelwa — Wed, 16 Dec 2015 21:06:35 GMT

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

Re: Hive jdbc connection string

pardeep_kumar — Wed, 16 Dec 2015 21:11:24 GMT

@pooja khandelwal What is the error are you getting?

Re: Hive jdbc connection string

pooja_khandelwa — Wed, 16 Dec 2015 21:17:33 GMT

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"

Re: Hive jdbc connection string

VR46 — Wed, 16 Dec 2015 22:27:59 GMT

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

Re: Hive jdbc connection string

pooja_khandelwa — Thu, 17 Dec 2015 00:25:13 GMT

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)

Re: Hive jdbc connection string

kevin_minder — Thu, 17 Dec 2015 01:55:12 GMT

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:

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.
You have gatway.path in gateway.site.xml set to knox. The default is gateway. Otherwise use httpPath=gateway/sandbox/hive
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.

Re: Hive jdbc connection string

amiller — Thu, 17 Dec 2015 02:13:41 GMT

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.

Re: Hive jdbc connection string

VR46 — Thu, 17 Dec 2015 04:13:55 GMT

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

Re: Hive jdbc connection string

pooja_khandelwa — Thu, 17 Dec 2015 13:27:41 GMT

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