Python 远程连接 hive

来自：http://www.zhangdongshengtech.com/article-detials/267

1. pyhive

首先是pyhive的安装：pyhive这个包依 赖于sasl，thrift,thrift-sasl这三个包，因此请参照下面的顺序安装

pip install sasl
pip install thrift
pip install thrift-sasl
pip install PyHive

pip安装sasl报错

Debian/Ubuntu:

sudo apt-get install python-dev libsasl2-dev gcc
pip install pyhs2  #代替上边4个包安装

CentOS/RHEL:

sudo yum install gcc-c++ python-devel.x86_64 cyrus-sasl-devel.x86_64
pip install pyhs2

2. 连接数据库

连接数据库，如果需要设置密码，那么必须制定auth参数

from pyhive import hive
conn = hive.connect(host="server_ip",port=10000, auth="CUSTOM", database="...",username="...",password="...")

3. 查询数据

query_sql = "select * from users"
curosr = conn.cursor()
curosr.execute(query_sql)

# 获得列的信息
clumns = curosr.description
# 获取全部数据，result是tuple
for result in curosr.fetchall():
    print(result)

curosr.close()

查询结果以tuple的形式返回，与之对应的列信息存放在curosr.description 中，如果想最终以字典的形式获得数据，那么需要根据description 和result进行组装。

4. HiveClient

编写一个HiveClient 类，只实现query功能，但是支持返回字典格式的数据，同时如果连接断开，可以进行重连

from itertools import zip_longest
from pyhive import hive
from functools import wraps

class Retry(object):
    def __init__(self, retry=3):
        self.retry = retry

    def __call__(self, func):
        @wraps(func)
        def wrapped_func(conn, query_sql):
            retry_count = 0
            while retry_count < self.retry:
                try:
                    return func(conn, query_sql)
                except Exception as e:
                    print(str(e))
                    conn.init_connection()
                    retry_count += 1
                    continue
            raise Exception("多次重试仍然失败，sql语句为: " + query_sql)
        return wrapped_func

class HiveClient(object):
    def __init__(self, host, port, username, password, auth='CUSTOM'):
        self.host = host
        self.port = port
        self.username = username
        self.password = password
        self.auth = auth
        self.init_connection()

    def init_connection(self):
        self.conn = hive.Connection(host=self.host, port=self.port, username=self.username,password=self.password, auth=self.auth)

    @Retry()
    def query(self, query_sql):
        datas = []
        curosr = self.conn.cursor()
        curosr.execute(query_sql)
        clumns = curosr.description
        for result in curosr.fetchall():
            item = {}
            for key, value in zip_longest(clumns, result):
                item[key[0]] = value
                datas.append(item)

        curosr.close()
        return datas

hc = HiveClient('ip', 15000, 'username', 'password')
sql = "select * from user"
data = hc.query(sql)
print(data)