question Re: Not able to connect to hiveserver2 to access hivetables with python in Support Questions

question Re: Not able to connect to hiveserver2 to access hivetables with python in Support Questions https://community.cloudera.com/t5/Support-Questions/Not-able-to-connect-to-hiveserver2-to-access-hivetables-with/m-p/277350#M207533 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 <A href="http://www.slf4j.org/codes.html#multiple_bindings" target="_blank" rel="noopener">http://www.slf4j.org/codes.html#multiple_bindings</A> 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)  Mon, 16 Sep 2019 13:45:14 GMT ranger 2019-09-16T13:45:14Z