python连接hive

安装

"""
pip install pyhs2

等待这个模块安装完成之后不要关闭命令行，接着在新的一行去执行命令。此时这个命令的作用是开启hive服务，否则python程序无法成功连接，命令如下： 

hive --service hiveserver
这个是启动hive
"""

python3.7 利用pyhive 连接上hive

pip install sasl

pip install thrift

pip install thrift-sasl

pip install PyHive

使用

# 连接hive 注意端口 这里是hiveserver2的端口 默认为10000
from pyhive import hive
conn = hive.Connection(host='10.8.13.120', port=10000, username='hdfs', database='default')
cursor = conn.cursor()
cursor.execute('show tables')

for result in cursor.fetchall():
    print(result)
cursor.close()
conn.close()

示例

# -*- coding: utf-8 -*-


from __future__ import print_function
import thrift
import requests
import sys
import json
import sqlite3
import pymssql
import pandas as pd
#import impala.dbapi as hive
from pyhive import hive
from pyhive import presto


PY3 = 1 if sys.version > '3' else 0


class BaseConn(object):

    def __init__(self, **config):
        self.config = config

    def build_connection(self):
        pass

    def init_from_json(self, json_file):
        with open(json_file) as f:
            self.config = json.load(f)
        self.build_connection()

    def read_df(self, sql):
        return pd.read_sql(sql, self.con)

    def execute(self, sql):
        cursor = self.con.cursor()
        try:
            cursor.execute(sql)
            res = cursor.fetchall()
            return res
        except Exception as e:
            raise e

    def iter_execute(self, sql):
        """将结果做成一个生成器"""
        cursor = self.con.cursor()
        try:
            cursor.execute(sql)
            while True:
                one = cursor.fetchone()
                if one is None:
                    break
                else:
                    yield one
        except Exception as e:
            raise e

    def __del__(self):
        if self.con:
            # print("close the connection")
            self.con.close()


class PrestoConn(BaseConn):

    def __init__(self, **config):
        if PY3:
            super().__init__(**config)
        else:
            super(PrestoConn, self).__init__(**config)

    def build_connection(self):
        self.con = presto.connect(**self.config)


class HiveConn(BaseConn):

    def __init__(self, **config):
        if PY3:
            super().__init__(**config)
        else:
            super(HiveConn, self).__init__(**config)

    def build_connection(self):
        self.con = hive.connect(**self.config)


class SqliteConn(BaseConn):

    def __init__(self, **config):
        if PY3:
            super().__init__(**config)
        else:
            super(SqliteConn, self).__init__(**config)

    def build_connection(self):
        self.con = sqlite3.connect(**self.config)


class MssqlConn(BaseConn):

    def __init__(self, **config):
        if PY3:
            super().__init__(**config)
        else:
            super(MssqlConn, self).__init__(**config)

    def build_connection(self):
        self.con = pymssql.connect(**self.config)