Not able to connect to hiveserver2 to access hivetables with python

[#1]

Can somebody please help me in connecting to hiveserver2 with python 2.7.5?

hive-site.xml contains

-------------------------------

<property>
<name>hive.server2.transport.mode</name>
<value>http</value>
<description>IP address (or fully-qualified domain name) and port of the metastore host</description>
</property>
<property>
<name>hive.server2.thrift.http.port</name>
<value>10001</value>
</property>
<property>
<name>hive.server2.thrift.http.path</name>
<value>cliservice</value>
</property>

<property>
<name>hive.server2.authentication</name>
<value>NOSASL</value>
</property>
<property>
<name>hive.server2.thrift.bind.host</name>
<value>localhost</value>
</property>
<property>

<name>hive.metastore.uris</name>
<value>thrift://localhost:9083</value>
</property>
<property>
<name>hive.metastore.event.db.notification.api.auth</name>
<value>false</value>
</property>

Using beeline

----------------

./beeline
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/hive/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/hadoop-2.7.3/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Beeline version 3.1.2 by Apache Hive
beeline> !connect jdbc:hive2://127.0.0.1:10001/default;auth=noSasl hive hive
Connecting to jdbc:hive2://127.0.0.1:10001/default;auth=noSasl
Java heap space
0: jdbc:hive2://127.0.0.1:10001/default (closed)> Connection is already closed.

With Pyhive

------------

conn = hive.Connection(host='127.0.0.1', port=10002, username='hive', password='hive',
database='default', auth='CUSTOM'). // tried auth ='CUSTOM'/'NOSASL'
cur = conn.cursor()
cur.execute('select * from employee limit 5')
result = cur.fetchall()

File "connect_hive.py", line 47, in <module>
output = hiveconnection(host_name, port, user,password, database)
File "connect_hive.py", line 29, in hiveconnection
database=database, auth='CUSTOM')
File "/usr/lib/python2.7/site-packages/pyhive/hive.py", line 192, in __init__
self._transport.open()
File "/usr/lib/python2.7/site-packages/thrift_sasl/__init__.py", line 80, in open
status, payload = self._recv_sasl_message()
File "/usr/lib/python2.7/site-packages/thrift_sasl/__init__.py", line 101, in _recv_sasl_message
payload = read_all_compat(self._trans, length)
File "/usr/lib/python2.7/site-packages/thrift_sasl/six.py", line 31, in <lambda>
read_all_compat = lambda trans, sz: trans.readAll(sz)
File "/usr/lib64/python2.7/site-packages/thrift/transport/TTransport.py", line 60, in readAll
chunk = self.read(sz - have)
File "/usr/lib64/python2.7/site-packages/thrift/transport/TSocket.py", line 132, in read
message='TSocket read 0 bytes')
thrift.transport.TTransport.TTransportException: TSocket read 0 byte

using pyhs2

-------------

pyhs2.connect(host='localhost',port=10002, authMechanism="NOSASL", user='hive',password='hive',database='default')

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/lib/python2.7/site-packages/pyhs2/__init__.py", line 7, in connect
return Connection(*args, **kwargs)
File "/usr/lib/python2.7/site-packages/pyhs2/connections.py", line 47, in __init__
res = self.client.OpenSession(TOpenSessionReq(username=user, password=password, configuration=configuration))
File "/usr/lib/python2.7/site-packages/pyhs2/TCLIService/TCLIService.py", line 154, in OpenSession
return self.recv_OpenSession()
File "/usr/lib/python2.7/site-packages/pyhs2/TCLIService/TCLIService.py", line 165, in recv_OpenSession
(fname, mtype, rseqid) = self._iprot.readMessageBegin()
File "/usr/lib64/python2.7/site-packages/thrift/protocol/TBinaryProtocol.py", line 148, in readMessageBegin
name = self.trans.readAll(sz)
File "/usr/lib64/python2.7/site-packages/thrift/transport/TTransport.py", line 60, in readAll
chunk = self.read(sz - have)
File "/usr/lib64/python2.7/site-packages/thrift/transport/TTransport.py", line 162, in read
self.__rbuf = BufferIO(self.__trans.read(max(sz, self.__rbuf_size)))
File "/usr/lib64/python2.7/site-packages/thrift/transport/TSocket.py", line 132, in read
message='TSocket read 0 bytes')
thrift.transport.TTransport.TTransportException: TSocket read 0 bytes

[#2]

hive.server2.thrift.port is the one you should connect to, which is 10000, NOT 10002, please try with 10000 port and see how you go.

Cheers
Eric

[#3]

@EricL 

netstat -tlnp . (hive is running on 10001 ND 10002)  eventhough hive-site.xml conatains:

<property>
<name>hive.server2.thrift.port</name>
<value>10000</value>
</property>
<property>
<name>hive.server2.webui.port</name>
<value>10002</value>
</property>

proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp 0 0 0.0.0.0:9083 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:8670 0.0.0.0:* LISTEN -
tcp 0 0 127.0.0.1:9026 0.0.0.0:* LISTEN -
tcp 0 0 127.0.0.1:9027 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:21000 0.0.0.0:* LISTEN -
tcp 0 0 127.0.0.1:9000 0.0.0.0:* LISTEN 2412/java-
tcp 0 0 0.0.0.0:9083 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:8670 0.0.0.0:* LISTEN -
tcp 0 0 127.0.0.1:9026 0.0.0.0:* LISTEN -
tcp 0 0 127.0.0.1:9027 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:21000 0.0.0.0:* LISTEN -
tcp 0 0 127.0.0.1:9000 0.0.0.0:* LISTEN 2412/java

tcp 0 0 0.0.0.0:10001 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:10002 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:50070 0.0.0.0:* LISTEN 2412/java
tcp 0 0 0.0.0.0:50070 0.0.0.0:* LISTEN 2412/java

With 10000 , I receive this error:

No handlers could be found for logger "thrift.transport.TSocket"
Traceback (most recent call last):
File "cloudera.py", line 22, in <module>
output = hiveconnection(host_name, port, user,password, database)
File "cloudera.py", line 14, in hiveconnection
database=database, auth='CUSTOM')
File "/usr/lib/python2.7/site-packages/pyhive/hive.py", line 192, in __init__
self._transport.open()
File "/usr/lib/python2.7/site-packages/thrift_sasl/__init__.py", line 61, in open
self._trans.open()
File "/usr/lib64/python2.7/site-packages/thrift/transport/TSocket.py", line 113, in open
raise TTransportException(TTransportException.NOT_OPEN, msg)
thrift.transport.TTransport.TTransportException: Could not connect to any of [('::1', 10000, 0, 0), ('127.0.0.1', 10000)]

and with 10001, error is:

  File "cloudera.py", line 22, in <module>
output = hiveconnection(host_name, port, user,password, database)
File "cloudera.py", line 14, in hiveconnection
database=database, auth='CUSTOM')
File "/usr/lib/python2.7/site-packages/pyhive/hive.py", line 192, in __init__
self._transport.open()
File "/usr/lib/python2.7/site-packages/thrift_sasl/__init__.py", line 80, in open
status, payload = self._recv_sasl_message()
File "/usr/lib/python2.7/site-packages/thrift_sasl/__init__.py", line 101, in _recv_sasl_message
payload = read_all_compat(self._trans, length)
File "/usr/lib/python2.7/site-packages/thrift_sasl/six.py", line 31, in <lambda>
read_all_compat = lambda trans, sz: trans.readAll(sz)
File "/usr/lib64/python2.7/site-packages/thrift/transport/TTransport.py", line 60, in readAll
chunk = self.read(sz - have)
File "/usr/lib64/python2.7/site-packages/thrift/transport/TSocket.py", line 132, in read
message='TSocket read 0 bytes')

[#4]

Before you try to connect from PyHive, please confirm if you can do so through beeline and make sure HS2 is functioning OK. Again, HS2 port is 10000 based on your config, so you should connect to 10000 from client side.

Do you see any errors from HS2 log? Try beeline and what does it say?

Cheers

[#5]

@EricL 

I am facing the following problems when connecting to port 10000 and 10001 and Hive server2 logs goes as follows

!connect jdbc:hive2://localhost:10000 hive hive
Connecting to jdbc:hive2://localhost:10000
19/09/23 10:22:05 [main]: WARN jdbc.HiveConnection: Failed to connect to localhost:10000
Could not open connection to the HS2 server. Please check the server URI and if the URI is correct, then ask the administrator to check the server status.
Error: Could not open client transport with JDBC Uri: jdbc:hive2://localhost:10000: java.net.ConnectException: Connection refused (Connection refused) (state=08S01,code=0)

HiveServer2 log

It does not even hit Hiveserver2 , hence there are no logs

beeline> !connect jdbc:hive2://localhost:10001 hive hive
Connecting to jdbc:hive2://localhost:10001
19/09/23 10:22:21 [main]: WARN jdbc.HiveConnection: Failed to connect to localhost:10001
Unknown HS2 problem when communicating with Thrift server.
Error: Could not open client transport with JDBC Uri: jdbc:hive2://localhost:10001: Invalid status 72 (state=08S01,code=0)

HiveServer2 log:

2019-09-23T10:24:08,767 WARN [HiveServer2-HttpHandler-Pool: Thread-59] http.HttpParser: Illegal character 0x1 in state=START for buffer HeapByteBuffer@63bc8470[p=1,l=25,c=8192,r=24]={\x01<<<\x00\x00\x00\x05PLAIN\x05\x00\x00\x00\n\x00hive\x00hive>>>\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00...\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00}
2019-09-23T10:24:08,767 WARN [HiveServer2-HttpHandler-Pool: Thread-59] http.HttpParser: bad HTTP parsed: 400 Illegal character 0x1 for HttpChannelOverHttp@751d099b{r=0,c=false,a=IDLE,uri=null}