使用 python 访问 HDInsight Hive

我们有一个 HDInsight 集群，其中包含 HIVE 中的一些表。我想从客户端计算机（Azure 外部）的 Python 3.6 查询这些表。

我尝试过使用PyHive, pyhs2并且impyla但我遇到了所有这些问题。

有没有人有一个访问的工作示例HDInsight HIVE来自Python？

我对此经验很少，不知道如何配置PyHive（这似乎是最有希望的），特别是在授权方面。

With impyla:

from impala.dbapi import connect
conn = connect(host='redacted.azurehdinsight.net',port=443)
cursor = conn.cursor()
cursor.execute('SELECT * FROM cs_test LIMIT 100')
print(cursor.description)  # prints the result set's schema
results = cursor.fetchall()

这给出：

Traceback (most recent call last):
  File "C:/git/ml-notebooks/impyla.py", line 3, in <module>
    cursor = conn.cursor()
  File "C:\Users\chris\Anaconda3\lib\site-packages\impala\hiveserver2.py", line 125, in cursor
    session = self.service.open_session(user, configuration)
  File "C:\Users\chris\Anaconda3\lib\site-packages\impala\hiveserver2.py", line 995, in open_session
    resp = self._rpc('OpenSession', req)
  File "C:\Users\chris\Anaconda3\lib\site-packages\impala\hiveserver2.py", line 923, in _rpc
    response = self._execute(func_name, request)
  File "C:\Users\chris\Anaconda3\lib\site-packages\impala\hiveserver2.py", line 954, in _execute
    .format(self.retries))
impala.error.HiveServer2Error: Failed after retrying 3 times

With Pyhive:

from pyhive import hive

conn = hive.connect(host="redacted.azurehdinsight.net",port=443,auth="NOSASL")
#also tried other auth-types, but as i said, i have no clue here

这给出：

Traceback (most recent call last):
  File "C:/git/ml-notebooks/PythonToHive.py", line 3, in <module>
    conn = hive.connect(host="redacted.azurehdinsight.net",port=443,auth="NOSASL")
  File "C:\Users\chris\Anaconda3\lib\site-packages\pyhive\hive.py", line 64, in connect
    return Connection(*args, **kwargs)
  File "C:\Users\chris\Anaconda3\lib\site-packages\pyhive\hive.py", line 164, in __init__
    response = self._client.OpenSession(open_session_req)
  File "C:\Users\chris\Anaconda3\lib\site-packages\TCLIService\TCLIService.py", line 187, in OpenSession
    return self.recv_OpenSession()
  File "C:\Users\chris\Anaconda3\lib\site-packages\TCLIService\TCLIService.py", line 199, in recv_OpenSession
    (fname, mtype, rseqid) = iprot.readMessageBegin()
  File "C:\Users\chris\Anaconda3\lib\site-packages\thrift\protocol\TBinaryProtocol.py", line 134, in readMessageBegin
    sz = self.readI32()
  File "C:\Users\chris\Anaconda3\lib\site-packages\thrift\protocol\TBinaryProtocol.py", line 217, in readI32
    buff = self.trans.readAll(4)
  File "C:\Users\chris\Anaconda3\lib\site-packages\thrift\transport\TTransport.py", line 60, in readAll
    chunk = self.read(sz - have)
  File "C:\Users\chris\Anaconda3\lib\site-packages\thrift\transport\TTransport.py", line 161, in read
    self.__rbuf = BufferIO(self.__trans.read(max(sz, self.__rbuf_size)))
  File "C:\Users\chris\Anaconda3\lib\site-packages\thrift\transport\TSocket.py", line 117, in read
    buff = self.handle.recv(sz)
ConnectionResetError: [WinError 10054] An existing connection was forcibly closed by the remote host

根据官方文件了解并解决从 HDInsight 上的 WebHCat 收到的错误 https://learn.microsoft.com/en-us/azure/hdinsight/hdinsight-hadoop-templeton-webhcat-debug-errors，如下所述。

什么是WebHCat

WebHCat 是 HCatalog 的 REST API、Hadoop 的表和存储管理层。 WebHCat 是在 HDInsight 群集上默认启用, and 被各种工具用来提交作业、获取作业状态等。无需登录集群。

因此，解决方法是使用 WebHCat 在 Python 中运行 Hive QL，请参阅 Hivedocument https://cwiki.apache.org/confluence/display/Hive/WebHCat学习和使用它。作为参考，MSDN上有一个类似的thread https://social.msdn.microsoft.com/Forums/en-US/b8026143-975f-46c9-b937-99f604e3b757/connect-to-hive-in-hdinsight-using-python-app-on-remote-linuxbased-server?forum=hdinsight对此进行了讨论。

希望能帮助到你。