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.

Not able to connect to hiveserver2 to access hivetables with python

Not able to connect to hiveserver2 to access hivetables with python

Explorer

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

 

13 REPLIES 13

Re: Not able to connect to hiveserver2 to access hivetables with python

Guru
@ranger ,

What is port 10002? I can see you only configured 10001 for HS2.

Cheers
Eric

Re: Not able to connect to hiveserver2 to access hivetables with python

Mentor

@ranger 

Can you try something like this it explains how to connect Hive running on a remote host  (HiveSever2)  using commonly used Python package, Pyhive? There are a lot of other Python packages available to connect to remote Hive, but Pyhive package is one of the easy and well-maintained and supported package

Here I am assuming you installed already the Pyhive package if not please do that first!

 

from phive import hive
import re,os, time


host_name = "localhost"
port = 10001
user = "hive"
password ="hive"
database = "employeeDB"


def hiveconnection(host_name, port, user,password, database):
          conn = hive.Connection(host=host_name, port=port, username=user, password=password,
          database=database, auth='CUSTOM')
          cur = conn.cursor()
          cur.execute('select * from employees returns limit 5')
          result = cur.fetchall()

          return result

# Call above function
output = hiveconnection(host_name, port, user,password, database)
print(output)

 

Before you attempt to connect using Pyhive you should execute the below steps to install the Pyhive package below are the step on an ubuntu machine as Pyhive is dependent on these Modules:

Installing gcc

sudo apt-get install gcc

Install Thrift

pip install thrift+

Install SASL

pip install sasl

Install thrift sasl

pip install thrift_sasl

 

After the above steps have run successfully, you can go ahead and install Pyhive using pip:

pip install pyhive

should you encounter Pyhive sasl fatal error install the below dependencies.

sudo apt-get install libsasl2-dev

 

Now you can re-test your hive database connection

Please let me know

 

 

 

 

Re: Not able to connect to hiveserver2 to access hivetables with python

Explorer

I had all the pyhive related libraries previously also . I describe all the logs and actions here.. Can somebody please help me. I have marked in bold all relevant portions of the log in starting HiveServer2

 

I try to start Hiveserver2 as follows and here goes the log. HiveServer2 by default is getting  started on port 10002

 

sudo ./hive --service metastore . (no problem->success)

 

[shachatt1@sharmi-dgov1 bin]$ sudo ./hiveserver2 --hiveconf hive.root.logger=INFO,console

Logs:

-------

(/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/local/bin/python:/usr/games:/usr/local/games:/usr/lib/jvm/java-1.8.0-openjdk-1.8.0:/opt/apache-maven-3.6.1/bin:/opt/hadoop-2.7.3/bin:/opt/hive/bin)
2019-09-16 13:11:17: Starting HiveServer2
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]
Hive Session ID = c11defb5-d58a-4124-b1b3-a42c895665fc
2019-09-16T13:11:20,592 INFO [main] SessionState: Hive Session ID = c11defb5-d58a-4124-b1b3-a42c895665fc
2019-09-16T13:11:21,429 INFO [main] session.SessionState: Created HDFS directory: hdfs://localhost:9000/hivetemp/scratchdir/shachatt1/c11defb5-d58a-4124-b1b3-a42c895665fc
2019-09-16T13:11:21,493 INFO [main] session.SessionState: Created local directory: /tmp/root/c11defb5-d58a-4124-b1b3-a42c895665fc
2019-09-16T13:11:21,501 INFO [main] session.SessionState: Created HDFS directory: hdfs://localhost:9000/hivetemp/scratchdir/shachatt1/c11defb5-d58a-4124-b1b3-a42c895665fc/_tmp_space.db
2019-09-16T13:11:21,522 INFO [main] sqlstd.SQLStdHiveAccessController: Created SQLStdHiveAccessController for session context : HiveAuthzSessionContext [sessionString=c11defb5-d58a-4124-b1b3-a42c895665fc, clientType=HIVESERVER2]
2019-09-16T13:11:21,526 WARN [main] session.SessionState: METASTORE_FILTER_HOOK will be ignored, since hive.security.authorization.manager is set to instance of HiveAuthorizerFactory.
2019-09-16T13:11:21,890 INFO [main] metastore.HiveMetaStoreClient: Trying to connect to metastore with URI thrift://localhost:9083
2019-09-16T13:11:21,910 INFO [main] metastore.HiveMetaStoreClient: Opened a connection to metastore, current connections: 1
2019-09-16T13:11:21,918 INFO [main] metastore.HiveMetaStoreClient: Connected to metastore.
2019-09-16T13:11:21,918 INFO [main] metastore.RetryingMetaStoreClient: RetryingMetaStoreClient proxy=class org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient ugi=shachatt1 (auth:SIMPLE) retries=1 delay=1 lifetime=0
2019-09-16T13:11:22,238 INFO [main] service.CompositeService: Operation log root directory is created: /tmp/root/operation_logs
2019-09-16T13:11:22,240 INFO [main] service.CompositeService: HiveServer2: Background operation thread pool size: 100
2019-09-16T13:11:22,240 INFO [main] service.CompositeService: HiveServer2: Background operation thread wait queue size: 100
2019-09-16T13:11:22,240 INFO [main] service.CompositeService: HiveServer2: Background operation thread keepalive time: 10 seconds
2019-09-16T13:11:22,241 INFO [main] service.CompositeService: Connections limit are user: 0 ipaddress: 0 user-ipaddress: 0
2019-09-16T13:11:22,338 INFO [main] metastore.HiveMetaStoreClient: Mestastore configuration metastore.filter.hook changed from org.apache.hadoop.hive.ql.security.authorization.plugin.AuthorizationMetaStoreFilterHook to org.apache.hadoop.hive.metastore.DefaultMetaStoreFilterHookImpl
2019-09-16T13:11:22,343 INFO [main] metastore.HiveMetaStoreClient: Closed a connection to metastore, current connections: 0
2019-09-16T13:11:22,350 INFO [main] results.QueryResultsCache: Initializing query results cache at /tmp/hive/_resultscache_
Hive Session ID = 8355ee16-bb99-439b-9351-581f999fc7be
2019-09-16T13:11:22,363 INFO [pool-6-thread-1] SessionState: Hive Session ID = 8355ee16-bb99-439b-9351-581f999fc7be
2019-09-16T13:11:22,378 INFO [main] results.QueryResultsCache: Query results cache: cacheDirectory /tmp/hive/_resultscache_/results-b1249629-b368-488f-b0fe-5473085778cd, maxCacheSize 2147483648, maxEntrySize 10485760, maxEntryLifetime 3600000
2019-09-16T13:11:22,382 INFO [pool-6-thread-1] session.SessionState: Created HDFS directory: hdfs://localhost:9000/hivetemp/scratchdir/shachatt1/8355ee16-bb99-439b-9351-581f999fc7be
2019-09-16T13:11:22,384 INFO [main] metastore.HiveMetaStoreClient: Trying to connect to metastore with URI thrift://localhost:9083
2019-09-16T13:11:22,384 INFO [main] metastore.HiveMetaStoreClient: Opened a connection to metastore, current connections: 1
2019-09-16T13:11:22,387 INFO [pool-6-thread-1] session.SessionState: Created local directory: /tmp/root/8355ee16-bb99-439b-9351-581f999fc7be
2019-09-16T13:11:22,395 INFO [pool-6-thread-1] session.SessionState: Created HDFS directory: hdfs://localhost:9000/hivetemp/scratchdir/shachatt1/8355ee16-bb99-439b-9351-581f999fc7be/_tmp_space.db
2019-09-16T13:11:22,405 INFO [main] metastore.HiveMetaStoreClient: Connected to metastore.
2019-09-16T13:11:22,405 INFO [main] metastore.RetryingMetaStoreClient: RetryingMetaStoreClient proxy=class org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient ugi=shachatt1 (auth:SIMPLE) retries=1 delay=1 lifetime=0
2019-09-16T13:11:22,479 INFO [main] events.NotificationEventPoll: Initializing lastCheckedEventId to 0
2019-09-16T13:11:22,600 INFO [pool-6-thread-1] metadata.HiveMaterializedViewsRegistry: Materialized views registry has been initialized
2019-09-16T13:11:22,606 INFO [main] util.log: Logging initialized @5482ms
2019-09-16T13:11:23,041 WARN [main] thrift.ThriftCLIService: XSRF filter disabled
2019-09-16T13:11:23,071 INFO [main] server.Server: jetty-9.3.20.v20170531
2019-09-16T13:11:23,099 INFO [main] handler.ContextHandler: Started o.e.j.s.ServletContextHandler@37854b34{/,null,AVAILABLE}
2019-09-16T13:11:23,111 INFO [main] server.AbstractConnector: Started ServerConnector@329548d0{HTTP/1.1,[http/1.1]}{0.0.0.0:10001}
2019-09-16T13:11:23,111 INFO [main] server.Server: Started @5988ms
2019-09-16T13:11:23,111 INFO [main] thrift.ThriftCLIService: Started ThriftHttpCLIService in http mode on port 10001 path=/cliservice/* with 5...500 worker threads
2019-09-16T13:11:23,113 INFO [main] server.Server: jetty-9.3.20.v20170531
2019-09-16T13:11:23,242 INFO [main] handler.ContextHandler: Started o.e.j.w.WebAppContext@5634a861{/,file:///tmp/jetty-0.0.0.0-10002-hiveserver2-_-any-1620856912156958300.dir/webapp/,AVAILABLE}{jar:file:/opt/hive/lib/hive-service-3.1.2.jar!/hive-webapps/hiveserver2}
2019-09-16T13:11:23,244 INFO [main] handler.ContextHandler: Started o.e.j.s.ServletContextHandler@2b0dc227{/static,jar:file:/opt/hive/lib/hive-service-3.1.2.jar!/hive-webapps/static,AVAILABLE}
2019-09-16T13:11:23,245 INFO [main] server.AbstractConnector: Started ServerConnector@221a2068{HTTP/1.1,[http/1.1]}{0.0.0.0:10002}
2019-09-16T13:11:23,245 INFO [main] server.Server: Started @6122ms
2019-09-16T13:11:23,245 INFO [main] http.HttpServer: Started HttpServer[hiveserver2] on port 10002
2019-09-16T13:11:23,260 INFO [Thrift Server] thrift.ThriftCLIService: Caught InterruptedException. Shutting down thrift server.
2019-09-16T13:11:23,267 INFO [main] server.AbstractConnector: Stopped ServerConnector@329548d0{HTTP/1.1,[http/1.1]}{0.0.0.0:10001}
2019-09-16T13:11:23,268 INFO [main] handler.ContextHandler: Stopped o.e.j.s.ServletContextHandler@37854b34{/,null,UNAVAILABLE}
2019-09-16T13:11:23,268 INFO [main] thrift.ThriftCLIService: Thrift HTTP server has been stopped
2019-09-16T13:11:23,270 INFO [main] metastore.HiveMetaStoreClient: Closed a connection to metastore, current connections: 0
2019-09-16T13:11:23,273 INFO [main] server.AbstractConnector: Stopped ServerConnector@221a2068{HTTP/1.1,[http/1.1]}{0.0.0.0:10002}
2019-09-16T13:11:23,273 INFO [main] handler.ContextHandler: Stopped o.e.j.s.ServletContextHandler@2b0dc227{/static,jar:file:/opt/hive/lib/hive-service-3.1.2.jar!/hive-webapps/static,UNAVAILABLE}
2019-09-16T13:11:23,297 INFO [main] handler.ContextHandler: Stopped o.e.j.w.WebAppContext@5634a861{/,null,UNAVAILABLE}{jar:file:/opt/hive/lib/hive-service-3.1.2.jar!/hive-webapps/hiveserver2}
2019-09-16T13:12:22,650 INFO [NotificationEventPoll 0] metastore.HiveMetaStoreClient: Trying to connect to metastore with URI thrift://localhost:9083
2019-09-16T13:12:22,651 INFO [NotificationEventPoll 0] metastore.HiveMetaStoreClient: Opened a connection to metastore, current connections: 1
2019-09-16T13:12:22,653 INFO [NotificationEventPoll 0] metastore.HiveMetaStoreClient: Connected to metastore.
2019-09-16T13:12:22,654 INFO [NotificationEventPoll 0] metastore.RetryingMetaStoreClient: RetryingMetaStoreClient proxy=class org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient ugi=shachatt1 (auth:SIMPLE) retries=1 delay=1 lifetime=0
Hive Session ID = d9572012-0dda-4f90-87e3-8918b7edc9c3
2019-09-16T13:12:23,409 INFO [main] SessionState: Hive Session ID = d9572012-0dda-4f90-87e3-8918b7edc9c3
2019-09-16T13:12:23,424 INFO [main] session.SessionState: Created HDFS directory: hdfs://localhost:9000/hivetemp/scratchdir/shachatt1/d9572012-0dda-4f90-87e3-8918b7edc9c3
2019-09-16T13:12:23,426 INFO [main] session.SessionState: Created local directory: /tmp/root/d9572012-0dda-4f90-87e3-8918b7edc9c3
2019-09-16T13:12:23,431 INFO [main] session.SessionState: Created HDFS directory: hdfs://localhost:9000/hivetemp/scratchdir/shachatt1/d9572012-0dda-4f90-87e3-8918b7edc9c3/_tmp_space.db
2019-09-16T13:12:23,431 INFO [main] sqlstd.SQLStdHiveAccessController: Created SQLStdHiveAccessController for session context : HiveAuthzSessionContext [sessionString=d9572012-0dda-4f90-87e3-8918b7edc9c3, clientType=HIVESERVER2]
2019-09-16T13:12:23,432 WARN [main] session.SessionState: METASTORE_FILTER_HOOK will be ignored, since hive.security.authorization.manager is set to instance of HiveAuthorizerFactory.
2019-09-16T13:12:23,433 INFO [main] metastore.HiveMetaStoreClient: Trying to connect to metastore with URI thrift://localhost:9083
2019-09-16T13:12:23,433 INFO [main] metastore.HiveMetaStoreClient: Opened a connection to metastore, current connections: 2
2019-09-16T13:12:23,438 INFO [main] metastore.HiveMetaStoreClient: Connected to metastore.
2019-09-16T13:12:23,438 INFO [main] metastore.RetryingMetaStoreClient: RetryingMetaStoreClient proxy=class org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient ugi=shachatt1 (auth:SIMPLE) retries=1 delay=1 lifetime=0
2019-09-16T13:12:23,438 INFO [main] service.CompositeService: Operation log root directory is created: /tmp/root/operation_logs
2019-09-16T13:12:23,438 INFO [main] service.CompositeService: HiveServer2: Background operation thread pool size: 100
2019-09-16T13:12:23,438 INFO [main] service.CompositeService: HiveServer2: Background operation thread wait queue size: 100
2019-09-16T13:12:23,438 INFO [main] service.CompositeService: HiveServer2: Background operation thread keepalive time: 10 seconds
2019-09-16T13:12:23,438 INFO [main] service.CompositeService: Connections limit are user: 0 ipaddress: 0 user-ipaddress: 0
2019-09-16T13:12:23,552 INFO [main] metastore.HiveMetaStoreClient: Mestastore configuration metastore.filter.hook changed from org.apache.hadoop.hive.ql.security.authorization.plugin.AuthorizationMetaStoreFilterHook to org.apache.hadoop.hive.metastore.DefaultMetaStoreFilterHookImpl
2019-09-16T13:12:23,553 INFO [main] metastore.HiveMetaStoreClient: Closed a connection to metastore, current connections: 1
Hive Session ID = 9d75feed-93f6-4fd5-8bbd-da8808a21d5c
2019-09-16T13:12:23,573 INFO [pool-10-thread-1] SessionState: Hive Session ID = 9d75feed-93f6-4fd5-8bbd-da8808a21d5c
2019-09-16T13:12:23,651 INFO [pool-10-thread-1] session.SessionState: Created HDFS directory: hdfs://localhost:9000/hivetemp/scratchdir/shachatt1/9d75feed-93f6-4fd5-8bbd-da8808a21d5c
2019-09-16T13:12:23,662 INFO [pool-10-thread-1] session.SessionState: Created local directory: /tmp/root/9d75feed-93f6-4fd5-8bbd-da8808a21d5c
2019-09-16T13:12:23,698 INFO [pool-10-thread-1] session.SessionState: Created HDFS directory: hdfs://localhost:9000/hivetemp/scratchdir/shachatt1/9d75feed-93f6-4fd5-8bbd-da8808a21d5c/_tmp_space.db
2019-09-16T13:12:23,702 INFO [pool-10-thread-1] metastore.HiveMetaStoreClient: Trying to connect to metastore with URI thrift://localhost:9083
2019-09-16T13:12:23,702 INFO [pool-10-thread-1] metastore.HiveMetaStoreClient: Opened a connection to metastore, current connections: 2
2019-09-16T13:12:23,717 WARN [main] thrift.ThriftCLIService: XSRF filter disabled
2019-09-16T13:12:23,717 INFO [main] server.Server: jetty-9.3.20.v20170531
2019-09-16T13:12:23,721 INFO [main] handler.ContextHandler: Started o.e.j.s.ServletContextHandler@67d32a54{/,null,AVAILABLE}
2019-09-16T13:12:23,723 INFO [main] server.AbstractConnector: Started ServerConnector@59edb4f5{HTTP/1.1,[http/1.1]}{0.0.0.0:10001}
2019-09-16T13:12:23,723 INFO [main] server.Server: Started @66600ms
2019-09-16T13:12:23,723 INFO [main] thrift.ThriftCLIService: Started ThriftHttpCLIService in http mode on port 10001 path=/cliservice/* with 5...500 worker threads
2019-09-16T13:12:23,724 INFO [pool-10-thread-1] metastore.HiveMetaStoreClient: Connected to metastore.
2019-09-16T13:12:23,724 INFO [pool-10-thread-1] metastore.RetryingMetaStoreClient: RetryingMetaStoreClient proxy=class org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient ugi=shachatt1 (auth:SIMPLE) retries=1 delay=1 lifetime=0
2019-09-16T13:12:23,732 INFO [main] server.Server: jetty-9.3.20.v20170531
2019-09-16T13:12:23,774 INFO [pool-10-thread-1] metadata.HiveMaterializedViewsRegistry: Materialized views registry has been initialized
2019-09-16T13:12:23,822 INFO [main] handler.ContextHandler: Started o.e.j.w.WebAppContext@3a401749{/,file:///tmp/jetty-0.0.0.0-10002-hiveserver2-_-any-8065703031579820150.dir/webapp/,AVAILABLE}{jar:file:/opt/hive/lib/hive-service-3.1.2.jar!/hive-webapps/hiveserver2}
2019-09-16T13:12:23,822 INFO [main] handler.ContextHandler: Started o.e.j.s.ServletContextHandler@7793ad58{/static,jar:file:/opt/hive/lib/hive-service-3.1.2.jar!/hive-webapps/static,AVAILABLE}
2019-09-16T13:12:23,823 INFO [main] server.AbstractConnector: Started ServerConnector@4f169009{HTTP/1.1,[http/1.1]}{0.0.0.0:10002}
2019-09-16T13:12:23,823 INFO [main] server.Server: Started @66700ms
2019-09-16T13:12:23,823 INFO [main] http.HttpServer: Started HttpServer[hiveserver2] on port

10002

 

Then when I run the program you provided, I get 

 

python2 cloudera.py . (sample code provided by you:)


[shachatt1@sharmi-dgov1 atlas_samples]$ python2 cloudera.py
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 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 bytes

 

and Corressponding HiveServer2 logs:

 

2019-09-16T13:17:42,539 WARN [HiveServer2-HttpHandler-Pool: Thread-56] http.HttpParser: Illegal character 0x1 in state=START for buffer HeapByteBuffer@7c6ba135[p=1,l=29,c=8192,r=28]={\x01<<<\x00\x00\x00\x05PLAIN\x02\x00\x00\x00\x0ehive\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-16T13:17:42,539 WARN [HiveServer2-HttpHandler-Pool: Thread-56] http.HttpParser: bad HTTP parsed: 400 Illegal character 0x1 for HttpChannelOverHttp@3d0722e6{r=0,c=false,a=IDLE,uri=null}

 

cloudera.py

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

 

from pyhive import hive
import re,os, time


host_name = "localhost"
port = 10002
user = "hive"
password ="hive"
database = "default"


def hiveconnection(host_name, port, user,password, database):
conn = hive.Connection(host=host_name, port=port, username=user, password=password,
database=database, auth='CUSTOM')
cur = conn.cursor()
cur.execute('select * from employees returns limit 5')
result = cur.fetchall()

return result

# Call above function
output = hiveconnection(host_name, port, user,password, database)
print(output)

 

Re: Not able to connect to hiveserver2 to access hivetables with python

Explorer

@EricL @Shelton 

Based on your inputs, I have tried and narrated the issues below. Can you please guide for the next steps.

Re: Not able to connect to hiveserver2 to access hivetables with python

Explorer

HiveServer2 is starting up with port 10002, so used this port to connect from Hive cklient

Re: Not able to connect to hiveserver2 to access hivetables with python

Guru
@ranger ,

By default, HS2 uses 10000 port number, can you please confirm the value for "hive.server2.thrift.port"? Do you have LB setup?

Re: Not able to connect to hiveserver2 to access hivetables with python

Explorer

No LB setup. 

 

Thrift is configured as 

 

<property>
<name>hive.metastore.uris</name>
<value>thrift://localhost:9083</value>
<description>Thrift uri for the remote metastore. Used by metastore client to connect to remote metastore.</description>
</property>
<property>

 

Please see my log above, HiveServer2 is starting by default on port 10002

 

 

Highlighted

Re: Not able to connect to hiveserver2 to access hivetables with python

Guru
@ranger

Did you change HS2's default ports? 10002 by default is HS2's web UI port, the thrift port should be 10000. Can you please check your HS2's hive-site.xml file and confirm the value for hive.server2.thrift.port and hive.server2.webui.port?

Thanks
Eric

Re: Not able to connect to hiveserver2 to access hivetables with python

Explorer

@EricL 

 

I have these settings in hive-site.xml as you asked:

 

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

 

 

Same issue , when I try to connect to HiveSERVER2 I get, at server side 

 

2019-09-18T07:42:44,436 WARN [HiveServer2-HttpHandler-Pool: Thread-56] http.HttpParser: Illegal character 0x1 in state=START for buffer HeapByteBuffer@4d5dd3fb[p=1,l=29,c=8192,r=28]={\x01<<<\x00\x00\x00\x05PLAIN\x02\x00\x00\x00\x0ehive\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-18T07:42:44,437 WARN [HiveServer2-HttpHandler-Pool: Thread-56] http.HttpParser: bad HTTP parsed: 400 Illegal character 0x1 for HttpChannelOverHttp@e93e496{r=0,c=false,a=IDLE,uri=null}

 

 

and on clinet :

 

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