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 都替换自己的登录的用户名

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<!-- Put site-specific property overrides in this file. -->

<configuration>
  <!-- 指定HDFS中NameNode的地址 -->
<property>
    <name>fs.defaultFS</name>
      <value>hdfs://lylg102:9000</value>
</property>

<!-- 指定Hadoop运行时产生文件的存储目录 -->
<property>
    <name>hadoop.tmp.dir</name>
    <value>/opt/module/hadoop-2.7.2/data/tmp</value>
</property>
 
<property>
    <name>hadoop.proxyuser.lylg.hosts</name>
    <value>*</value>
</property>
<property>
    <name>hadoop.proxyuser.lylg.groups</name>
    <value>*</value>
</property>
</configuration>

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

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
        <property>
            <name>javax.jdo.option.ConnectionURL</name>
            <value>jdbc:mysql://lylg102:3306/metastore?createDatabaseIfNotExist=true</value>
            <description>JDBC connect string for a JDBC metastore</description>
        </property>

        <property>
            <name>javax.jdo.option.ConnectionDriverName</name>
            <value>com.mysql.jdbc.Driver</value>
            <description>Driver class name for a JDBC metastore</description>
        </property>

        <property>
            <name>javax.jdo.option.ConnectionUserName</name>
            <value>root</value>
            <description>username to use against metastore database</description>
        </property>

        <property>
            <name>javax.jdo.option.ConnectionPassword</name>
            <value>000000</value>
            <description>password to use against metastore database</description>
        </property>

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

    <property>
        <name>hive.cli.print.header</name>
        <value>true</value>
    </property>

    <property>
        <name>hive.cli.print.current.db</name>
        <value>true</value>
    </property>

    <property>
        <name>hive.metastore.schema.verification</name>
        <value>false</value>
    </property>

    <property>
        <name>datanucleus.schema.autoCreateAll</name>
        <value>true</value>
    </property>
<property>
    <name>hive.execution.engine</name>
    <value>tez</value>
</property>
<!-- 指定 hiveserver2 连接的 host -->
<property>
        <name>hive.server2.thrift.bind.host</name>
        <value>lylg102</value>
</property>
<!-- 指定 hiveserver2 连接的端口号 -->
<property>
        <name>hive.server2.thrift.port</name>
        <value>10000</value>
</property>
<property>
  <name>hive.metastore.sasl.enabled</name>
  <value>false</value>
  <description>If true, the metastore Thrift interface will be secured with SASL. Clients must authenticate with Kerberos.</description>
</property>

<property>
  <name>hive.server2.enable.doAs</name>
  <value>false</value>
</property>

<property>
  <name>hive.server2.authentication</name>
  <value>NONE</value>
</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