安装包版本说明
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
