hive - 特定表上的 SQL 在 Hive 中总是失败，在 Impala 中有效

Question

• 对于包括 table_1 在内的所有表，所有查询在 HUE > Impala 中都可以正常工作
• 但是在 HUE 中使用 Hive 时，所有查询总是只针对表 table_1 失败，并出现以下错误
• 即使是引用没有空格或被反引号包围的列的 SQL
• 唯一明显的区别是 table_1 的列名带有空格，而一列的&.

以下 SQL 在 Hive 中失败，而在 Impala 中成功：

select id from db_name.table_1
select `id` from db_name.table_1
select count(*) from db_name.table_1
select 1 from db_name.table_1

示例代码：

# Test connection
from pyhive import hive
connection = hive.connect(host='hive_server',
                          port=10000,
                          database='db_name',
                          username='hive_username',
                          password='hive_password',
                          auth='CUSTOM')


def run(query):
  cur = connection.cursor()

      cur.execute(query)
      return cur.fetchall()

run("select id from db_name.table_1 limit 1")

失败并出现错误：

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "<stdin>", line 4, in run
  File "/test_venv/lib/python3.6/site-packages/pyhive/common.py", line 136, in fetchall
    return list(iter(self.fetchone, None))
  File "/test_venv/lib/python3.6/site-packages/pyhive/common.py", line 105, in fetchone
    self._fetch_while(lambda: not self._data and self._state != self._STATE_FINISHED)
  File "/test_venv/lib/python3.6/site-packages/pyhive/common.py", line 45, in _fetch_while
    self._fetch_more()
  File "/test_venv/lib/python3.6/site-packages/pyhive/hive.py", line 387, in _fetch_more
    _check_status(response)
  File "/test_venv/lib/python3.6/site-packages/pyhive/hive.py", line 495, in _check_status
    raise OperationalError(response)
pyhive.exc.OperationalError: TFetchResultsResp(status=TStatus(statusCode=3,
 infoMessages=[
 "*org.apache.hive.service.cli.HiveSQLException:java.io.IOException: java.lang.IllegalArgumentException: field ended by ';': expected ';' but got 'benefit' at line 6:   optional binary primary benefit:14:13",
 'org.apache.hive.service.cli.operation.SQLOperation:getNextRowSet:SQLOperation.java:496',
 'org.apache.hive.service.cli.operation.OperationManager:getOperationNextRowSet:OperationManager.java:297',
 'org.apache.hive.service.cli.session.HiveSessionImpl:fetchResults:HiveSessionImpl.java:868',
 'org.apache.hive.service.cli.CLIService:fetchResults:CLIService.java:507',
 'org.apache.hive.service.cli.thrift.ThriftCLIService:FetchResults:ThriftCLIService.java:708',
 'org.apache.hive.service.rpc.thrift.TCLIService$Processor$FetchResults:getResult:TCLIService.java:1717',
 'org.apache.hive.service.rpc.thrift.TCLIService$Processor$FetchResults:getResult:TCLIService.java:1702',
 'org.apache.thrift.ProcessFunction:process:ProcessFunction.java:39',
 'org.apache.thrift.TBaseProcessor:process:TBaseProcessor.java:39',
 'org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor:process:HadoopThriftAuthBridge.java:567',
 'org.apache.thrift.server.TThreadPoolServer$WorkerProcess:run:TThreadPoolServer.java:286',
 'java.util.concurrent.ThreadPoolExecutor:runWorker:ThreadPoolExecutor.java:1142',
 'java.util.concurrent.ThreadPoolExecutor$Worker:run:ThreadPoolExecutor.java:617',
 'java.lang.Thread:run:Thread.java:745',
 "*java.io.IOException:java.lang.IllegalArgumentException: field ended by ';': expected ';' but got 'benefit' at line 6:   optional binary primary benefit:18:4",
 'org.apache.hadoop.hive.ql.exec.FetchOperator:getNextRow:FetchOperator.java:521',
 'org.apache.hadoop.hive.ql.exec.FetchOperator:pushRow:FetchOperator.java:428',
 'org.apache.hadoop.hive.ql.exec.FetchTask:fetch:FetchTask.java:146',
 'org.apache.hadoop.hive.ql.Driver:getResults:Driver.java:2227',
 'org.apache.hive.service.cli.operation.SQLOperation:getNextRowSet:SQLOperation.java:491',
 "*java.lang.IllegalArgumentException:field ended by ';': expected ';' but got 'benefit' at line 6:   optional binary primary benefit:30:12",
 'org.apache.parquet.schema.MessageTypeParser:check:MessageTypeParser.java:241',
 'org.apache.parquet.schema.MessageTypeParser:addPrimitiveType:MessageTypeParser.java:210',
 'org.apache.parquet.schema.MessageTypeParser:addType:MessageTypeParser.java:115',
 'org.apache.parquet.schema.MessageTypeParser:addGroupTypeFields:MessageTypeParser.java:103',
 'org.apache.parquet.schema.MessageTypeParser:parse:MessageTypeParser.java:96',
 'org.apache.parquet.schema.MessageTypeParser:parseMessageType:MessageTypeParser.java:86',
 'org.apache.hadoop.hive.ql.io.parquet.ParquetRecordReaderBase:getSplit:ParquetRecordReaderBase.java:93',
 'org.apache.hadoop.hive.ql.io.parquet.read.ParquetRecordReaderWrapper:<init>:ParquetRecordReaderWrapper.java:78',
 'org.apache.hadoop.hive.ql.io.parquet.read.ParquetRecordReaderWrapper:<init>:ParquetRecordReaderWrapper.java:63',
 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat:getRecordReader:MapredParquetInputFormat.java:75',
 'org.apache.hadoop.hive.ql.exec.FetchOperator$FetchInputFormatSplit:getRecordReader:FetchOperator.java:695',
 'org.apache.hadoop.hive.ql.exec.FetchOperator:getRecordReader:FetchOperator.java:333',
 'org.apache.hadoop.hive.ql.exec.FetchOperator:getNextRow:FetchOperator.java:459'],
 sqlState=None,
 errorCode=0,
 errorMessage="java.io.IOException: java.lang.IllegalArgumentException: field ended by ';': expected ';' but got 'benefit' at line 6:   optional binary primary benefit"),
 hasMoreRows=None,
 results=None)

• 我没有查看 Hive *.java 文件，因为我无权访问 Hive 服务器（由另一个团队管理）。但是从错误看来，Hive 内部出现了问题（可能是由于空格？）
• 你们都面临着类似的事情，或者有什么指示接下来要研究什么？

谢谢

---

更新：

• 经过进一步调查，发现问题不在于 PyHive，而在于 Hive 本身

标签： hiveimpala