Sandbox : cannot connect to Hive / Zookeeper with JDBC

[#1]

Hello

I downloaded HDP-Sandbox (in an Oracle VirtualBox VM) a while ago, never used it much, and I’m now trying to access data from the outside world using Hive HDBC.

I use hive-jdbc 2.3.2 from apache, which I got from mvnrepository, with all the dependencies in the classpath

http://mvnrepository.com/artifact/org.apache.hive/hive-jdbc/2.3.2

Connection fails with a timeout, I guess it’s caused by a configuration error – even though I use my sandbox out of the… box.

My sandbox version

Sandbox information:
Created on: 25_10_2016_08_11_26 for
Hadoop stack version:  Hadoop 2.7.3.2.5.0.0-1245
Ambari Version: 2.4.0.0-1225
Ambari Hash: 59175b7aa1ddb74b85551c632e3ce42fed8f0c85
Ambari build:  Release : 1225
Java version:  1.8.0_111
OS Version:  CentOS release 6.8 (Final)

My VM screen

HDP 2.5
http://hortonworks.com
To initiate your Hortonworks Sandbox session,
please open a browser and enter this address
in the browser's address field:
http://127.0.0.1:8888/

My hosts file :

.../...
127.0.0.1 sandbox.hortonworks.com
.../...

Hive Summary from the Ambari screen

Hive Metastore        Started No alerts
HiveServer2           Started No alerts
WebHCat Server        Started No alerts
Hive Client           1 Hive Client Installed
HiveServer2 JDBC URL  jdbc:hive2://sandbox.hortonworks.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2

Zookeeper Summary

ZooKeeper Server 	Started No alerts
ZooKeeper Client 	1 ZooKeeper Client Installed

The jdbc URL I use

jdbc:hive2://sandbox.hortonworks.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2

The message I get from my java Driver#connect(String url, Properties info)

Could not open client transport for any of the Server URI's in ZooKeeper: java.net.SocketTimeoutException: Read timed out

The message I get from Squirrel with the same URL

Unexpected Error occurred attempting to open an SQL connection.
class java.util.concurrent.TimeoutException: null

I’m stuck, any help would be appreciated very much!

Thanks !

[#2]

@Luc Chapon

Heya, check out the "Hortonworks Data Platform Add-Ons" section on Hortonworks' download page for JDBC drivers and some docs. https://hortonworks.com/downloads/

Regarding your JDBC url: I would refer to the documentation for the best answer, but if you want to connect to Hive by querying Zookeeper, here's an excerpt from the docs:

jdbc:hive2://zk=192.168.0.1:2181/hiveserver2

Use this option to enable the Dynamic Service Discovery feature, which allows you to connect to Hive servers that are registered against a ZooKeeper service by connecting to the ZooKeeper service.

[#3]

Thank you

I tried

jdbc:hive2://zk=myip:2181/hiveserver2

This gave me, as I feared, an “unknown host” error.

I use the Apache JDBC driver 2.3.2.

I also tried

jdbc:hive2://myip:2181/hiveserver2

That gave me (using Squirrel)

Unexpected Error occurred attempting to open an SQL connection.
class org.apache.thrift.transport.TTransportException: null

The url I use is the one given by Ambari in the Hive summary for my config. Why couldn’t I use it directly ?

jdbc:hive2://sandbox.hortonworks.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2

[#4]

More info:

On the console, I get the following error while trying to connect:

ERROR Utils Unable to read HiveServer2 configs from ZooKeeper

[#5]

I cant connect, I cant connect!

Two days spent googling and searching, and I cant connect to Hive using JDBC. I’m pretty sure it’s a configuration error, but I cant find where and what.

I putty’ed into the docker HDP Sandbox and tried to connect from there, but it failed too!

[root@sandbox /]# cat /etc/hosts
127.0.0.1       localhost
::1     localhost ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
172.17.0.2      sandbox.hortonworks.com

[root@sandbox /]# netstat -an | grep 2181
tcp        0      0 172.17.0.2:51006            172.17.0.2:2181             ESTABLISHED
tcp        0      0 172.17.0.2:41062            172.17.0.2:2181             ESTABLISHED
tcp        0      0 172.17.0.2:44137            172.17.0.2:2181             ESTABLISHED
tcp        0      0 :::2181                     :::*                        LISTEN
tcp        0      0 ::ffff:172.17.0.2:2181      ::ffff:172.17.0.2:44137     ESTABLISHED
tcp        0      0 ::ffff:172.17.0.2:45391     ::ffff:172.17.0.2:2181      ESTABLISHED
tcp        0      0 ::ffff:172.17.0.2:2181      ::ffff:172.17.0.2:41062     ESTABLISHED
tcp        0      0 ::ffff:172.17.0.2:2181      ::ffff:172.17.0.2:45391     ESTABLISHED
tcp        0      0 ::ffff:172.17.0.2:2181      ::ffff:172.17.0.2:51006     ESTABLISHED

Port 2181 seems to be listening on ipv6 only, is that right?

Beelining, using the very jdbc URL Ambari grives me, fails…

[root@sandbox /]# beeline --verbose=true -u jdbc:hive2://sandbox.hortonworks.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2
issuing: !connect jdbc:hive2://sandbox.hortonworks.com:2181/ '' [passwd stripped]
Connecting to jdbc:hive2://sandbox.hortonworks.com:2181/
18/01/15 17:26:44 [main]: WARN jdbc.HiveConnection: Failed to connect to sandbox.hortonworks.com:2181
Error: Could not open client transport with JDBC Uri: jdbc:hive2://sandbox.hortonworks.com:2181/: null (state=08S01,code=0)
java.sql.SQLException: Could not open client transport with JDBC Uri: jdbc:hive2://sandbox.hortonworks.com:2181/: null
        at org.apache.hive.jdbc.HiveConnection.openTransport(HiveConnection.java:217)
        at org.apache.hive.jdbc.HiveConnection.<init>(HiveConnection.java:155)
        at org.apache.hive.jdbc.HiveDriver.connect(HiveDriver.java:105)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:208)
        at org.apache.hive.beeline.DatabaseConnection.connect(DatabaseConnection.java:146)
        at org.apache.hive.beeline.DatabaseConnection.getConnection(DatabaseConnection.java:211)
        at org.apache.hive.beeline.Commands.connect(Commands.java:1190)
        at org.apache.hive.beeline.Commands.connect(Commands.java:1086)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.hive.beeline.ReflectiveCommandHandler.execute(ReflectiveCommandHandler.java:52)
        at org.apache.hive.beeline.BeeLine.dispatch(BeeLine.java:989)
        at org.apache.hive.beeline.BeeLine.initArgs(BeeLine.java:714)
        at org.apache.hive.beeline.BeeLine.begin(BeeLine.java:776)
        at org.apache.hive.beeline.BeeLine.mainWithInputRedirection(BeeLine.java:490)
        at org.apache.hive.beeline.BeeLine.main(BeeLine.java:473)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.hadoop.util.RunJar.run(RunJar.java:233)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:148)
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:86)
        at org.apache.thrift.transport.TSaslTransport.receiveSaslMessage(TSaslTransport.java:178)
        at org.apache.thrift.transport.TSaslTransport.open(TSaslTransport.java:307)
        at org.apache.thrift.transport.TSaslClientTransport.open(TSaslClientTransport.java:37)
        at org.apache.hive.jdbc.HiveConnection.openTransport(HiveConnection.java:193)
        ... 24 more
Beeline version 1.2.1000.2.5.0.0-1245 by Apache Hive

Can anyone help me please?

Thanks

[#6]

@Luc Chapon

Try using quotes around JDBC URL:

beeline -u 'jdbc:hive2://sandbox.hortonworks.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2'

Tutorial References:
HADOOP TUTORIAL – GETTING STARTED WITH HDP

USING HIVE ACID TRANSACTIONS TO INSERT, UPDATE AND DELETE DATA

[#7]

Hi @gdelon, that worked, thanks !

It’s weird though, CentOS shell seems to do something with the unquoted string, I can’t see what.

It also works if you quote only the semi-colon part of the url. Semi-colon is the culprit. Ok 🙂

beeline -u jdbc:hive2://sandbox.hortonworks.com:2181/';serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2'

So it works from within the docker inside the VM. So at least the configuration is correct for this case.

Now how could the same jdbc url work from outside that box ?

Any clue anyone ?

Thanks