How to access hive from python

[#1]

I want to access the hive through python. What process should i follow. How can i install pyhive in HDP2.5

[#2]

@Jay SenSharma Can you please help me in connecting hive through python.

conda --version 4.3.25

python-error.png

Below is my pyhive-test.py file

from pyhive import hive

conn = hive.Connection(host="192.168.1.100", port=10000, auth="KERBEROS", database="bmdatalake", kerberos_service_name= "hive")

cursor = conn.cursor() cursor.execute("select * from dl_his_kotesh limit 10")

for result in cursor.fetchall():

use_result(result)

[#3]

@kotesh banoth

Do you have the library ?

# ls -l  /usr/lib64 | grep libsasl
lrwxrwxrwx.  1 root root       17 Dec  1  2014 libsasl2.so.3 -> libsasl2.so.3.0.0
-rwxr-xr-x.  1 root root   121296 Jun 10  2014 libsasl2.so.3.0.0

.

[#4]

@kotesh banoth

In your case the version it is expecting as "libsasl.2.so.2"

so please check if you can reinstall that package:

Reference: https://github.com/cloudera/python-sasl/issues/9

Not sure if the following will work at your end or not but worth trying:

sudo yum install cyrus-sasl-devel
OR
sudo pip install sasl

.

[#5]

Dear @Jay SenSharma , i have the mentioned libraries

[medwadmin@chtcuxhd05 pyhive]$ ls -lh /usr/lib64/libsasl*

lrwxrwxrwx 1 root root 17 Jul 17 19:21 /usr/lib64/libsasl2.so -> libsasl2.so.3.0.0

lrwxrwxrwx. 1 root root 17 Sep 21 2016 /usr/lib64/libsasl2.so.3 -> libsasl2.so.3.0.0

-rwxr-xr-x. 1 root root 119K Feb 16 2016 /usr/lib64/libsasl2.so.3.0.0

Below is my sample python code(file name: pyhive-test.py) to access hive:

Error while executing: python pyhive-test.py:

==================

[medwadmin@chtcuxhd05 pyhive]$ python pyhive-test.py

Could not connect to any of [('172.16.0.125', 10000)] Traceback (most recent call last): File "pyhive-test.py", line 15, in <module> conn = hive.connect(host='172.16.0.125', port=10000, auth='KERBEROS',kerberos_service_name='hive') File "/home/medwadmin/anaconda3/lib/python3.5/site-packages/pyhive/hive.py", line 65, in connect return Connection(*args, **kwargs) File "/home/medwadmin/anaconda3/lib/python3.5/site-packages/pyhive/hive.py", line 156, in __init__ self._transport.open() File "/home/medwadmin/anaconda3/lib/python3.5/site-packages/thrift_sasl/__init__.py", line 61, in open self._trans.open() File "/home/medwadmin/anaconda3/lib/python3.5/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 [('172.16.0.125', 10000)]

[#6]

@kotesh banoth

Good to see that this time you are not getting the "Import Error: libsasl.2.so.2"

The current error seems to be related to connectivity:

thrift.transport.TTransport.TTransportException: Could not connect to any of [('172.16.0.125', 10000)] 

.

So there can be two reasons , Either you do not have a valid Kerberos Ticket Or you do not have the connectivity from your host to 172.16.0.125 on port 10000

So please check the following:

1. Check if you can access the 172.16.0.125 on port 10000, From the host where you are running the python code.

# nc -v  172.16.0.125  10000
OR
# telnet 172.16.0.125  10000

2. Please check that before running the python code , you have a valid kerberos ticket, Because in your code we see that you are using kerberos. So using the following command please check if you have a valid ticket or not?

# klist

.

3. Also please check your code to find out if you are using correct IP Address or not? Because in your original post we see a different Address was used instead of 172.16.0.125

conn = hive.Connection(host="192.168.1.100", port=10000, auth="KERBEROS", database="bmdatalake", kerberos_service_name= "hive") 

.

[#7]

Thanks so much @Jay SenSharma. Still getting the error

I have hive installed in server(172.16.0.XXX)

Is the below one is the right way to connect the hive from python ?

1. conn = hive.connect(host='172.16.0.XXX', port=10000, username='kotesh')

Port 10000 is listening:

1. telnet 172.16.0.XXX 10000
   1. Trying 172.16.0.XXX... Connected to 172.16.0.XXX. Escape character is '^]'.

============================================================

I had installed python in different server(172.16.0.YYY)

python code:

from pyhive import hive

conn = hive.connect(host='172.16.0.XXX', port=10000, username='kotesh')

cursor = conn.cursor()

cursor.execute("select * from bmdatalake.dl_his_viewhistory limit 10")

for result in cursor.fetchall():

use_result(result)

While executing getting the error as below:

[#8]

@kotesh banoth

Can you try without SASL, or somehow it need to be imported in your python.

Pleas try this with NOSASL to see what you get next: (or verify if the "cyrus-sasl-devel" is installed properly)

conn = hive.connect(host='172.16.0.XXX', port=10000, username='kotesh', auth='NOSASL')

.

It will need to following config on your hive side.

<property>
   <name>hive.server2.authentication</name>
   <value>NOSASL</value>
</property>

.