Python Anaconda安装sasl,thrift,thrift-sasl 并连接PyHive

安装包版本说明

python: 3.6 / 3.7 / 3.8
pyhive: 0.6.2
thrift: 0.13.0
thrift_sasl: 0.4.2
sasl: 根据python version决定

一，到https://www.lfd.uci.edu/~gohlke/pythonlibs/#sasl下载对应自己python版本的sasl （cp37 代表python version 为3.7）

本地安装：

二，安装pyhive: 0.6.2 ，thrift: 0.13.0 ，thrift_sasl: 0.4.2

pip install thrift==0.13.0
pip install thrift_sasl==0.4.2
pip install pyhive==0.6.2

三，hadoop、hive配置
hadoop 配置目录下的core-site.xml
hadoop.proxyuser.lylg.hosts 和hadoop.proxyuser.lylg.groups 中的lylg 都替换自己的登录的用户名

<configuration>
  
<property>
    <name>fs.defaultFSname>
      <value>hdfs://lylg102:9000value>
property>


<property>
    <name>hadoop.tmp.dirname>
    <value>/opt/module/hadoop-2.7.2/data/tmpvalue>
property>
 
<property>
    <name>hadoop.proxyuser.lylg.hostsname>
    <value>*value>
property>
<property>
    <name>hadoop.proxyuser.lylg.groupsname>
    <value>*value>
property>
configuration>

hive 配置目录下的hive-site.xml

<configuration>
        <property>
            <name>javax.jdo.option.ConnectionURLname>
            <value>jdbc:mysql://lylg102:3306/metastore?createDatabaseIfNotExist=truevalue>
            <description>JDBC connect string for a JDBC metastoredescription>
        property>

        <property>
            <name>javax.jdo.option.ConnectionDriverNamename>
            <value>com.mysql.jdbc.Drivervalue>
            <description>Driver class name for a JDBC metastoredescription>
        property>

        <property>
            <name>javax.jdo.option.ConnectionUserNamename>
            <value>rootvalue>
            <description>username to use against metastore databasedescription>
        property>

        <property>
            <name>javax.jdo.option.ConnectionPasswordname>
            <value>000000value>
            <description>password to use against metastore databasedescription>
        property>

    <property>
         <name>hive.metastore.warehouse.dirname>
         <value>/user/hive/warehousevalue>
         <description>location of default database for the warehousedescription>
    property>

    <property>
        <name>hive.cli.print.headername>
        <value>truevalue>
    property>

    <property>
        <name>hive.cli.print.current.dbname>
        <value>truevalue>
    property>

    <property>
        <name>hive.metastore.schema.verificationname>
        <value>falsevalue>
    property>

    <property>
        <name>datanucleus.schema.autoCreateAllname>
        <value>truevalue>
    property>
<property>
    <name>hive.execution.enginename>
    <value>tezvalue>
property>

<property>
        <name>hive.server2.thrift.bind.hostname>
        <value>lylg102value>
property>

<property>
        <name>hive.server2.thrift.portname>
        <value>10000value>
property>
<property>
  <name>hive.metastore.sasl.enabledname>
  <value>falsevalue>
  <description>If true, the metastore Thrift interface will be secured with SASL. Clients must authenticate with Kerberos.description>
property>

<property>
  <name>hive.server2.enable.doAsname>
  <value>falsevalue>
property>

<property>
  <name>hive.server2.authenticationname>
  <value>NONEvalue>
property>

configuration>

四，测试连接Hive
请看详细官方Github:https://github.com/dropbox/PyHive/tree/v0.6.2
第一种测试：（可能会报错，请使用第二种，详情请看github Pyhive）

from pyhive import hive

conn = hive.Connection(host='lylg102',
                       port=10000,
                       auth="NONE",
                       database='default',
                       username='lylg')
cursor = conn.cursor()
cursor.execute('SELECT * from students')
for result in cursor.fetchall():
    print(result)
cursor.close()
conn.close()

第二种测试：

from pyhive import hive
from TCLIService.ttypes import TOperationState

def execute_sql(query):
    hive.connect('lylg102', configuration={'hive.exec.reducers.max': '123'})
    cursor = hive.Connection(host='lylg102',
                           port=10000,
                           auth='NONE',
                           database='default',
                           username='lylg').cursor()
    cursor.execute(query,  async_=True)
    status = cursor.poll().operationState
    while status in (TOperationState.INITIALIZED_STATE, TOperationState.RUNNING_STATE):
        status = cursor.poll().operationState
    res = cursor.fetchall()
    cursor.close()
    return res
    
if __name__ == '__main__':
    sql = '\
select avg(views), \
avg(score), \
sum(views) from cartoon_info'
    res = execute_sql(sql)
    print(res)

参考：https://its401.com/article/weixin_34232617/93727029
https://github.com/dropbox/PyHive/issues/240