删除表语句中的Hive ParseException [英] Hive ParseException in Drop Table Statement

问题描述

我使用python和pyodbc模块特别是在Hadoop上执行Hive查询。代码触发问题的部分是这样的：

  import pyodbc 
 import pandas 
 
 oConnexionString ='Driver = {ClouderaHive}; [...]'
 oConnexion = pyodbc.connect（oConnexionString，autocommit = True）
 oConnexion.setencoding（encoding ='utf-8'）
 oQueryParameter =select * from my_db.my_table; 
 oParameterData = pandas.read_sql（oQueryParameter，oConnexion）
 oCursor = oConnexion.cursor（）
 
 for oParameterData.index中的oRow：
 sTableName = oParameterData.loc [ oRow，'TableName'] 
 oQueryDeleteTable ='drop table if exists my_db。'+ sTableName +';'
 print（oQueryDeleteTable）
 oCursor.execute（oQueryDeleteTable）
  

打印给出： drop table if exists dl_audit_data_quality.hero_context_start_gamemode; / p>



但是， cursor.execute 触发以下错误消息

< blockquote>

pyodbc.Error：（'HY000'，[HY000] [Cloudera] [HiveODBC]（80）语法或
服务器中抛出的语义分析错误，而execurint查询错误
消息从服务器：编译语句时出错：FAILED：
ParseException行1:44字符'（80）（SQLExecDirectW））

请注意，当我复制原件t在Hue手动执行它，效果很好。我猜想它与变量 sTableName 的编码有关，但我无法弄清楚如何解决它。

谢谢

解决方案

查询失败，因为变量 sTableName 。
单独打印变量将正确显示文本。上面列印的例子：

 >>> print（oQueryDeleteTable）
>>> 'drop table if exists dl_audit_data_quality.hero_context_start_gamemode;'
  

但打印原始数据框显示包含字符这个：

 >>> print（oParameterData.loc [oRow，'TableName'] 
>>>'h\x00e\x00r\x00o\x00_c\x00o\x00n\x00t\x00e\ x00x\x00t\x00'
  

问题是通过对如下所述的编码进行重编来解决的：一个href =https://stackoverflow.com/questions/43827811/python-dictionary-contains-encoded-values> Python字典包含编码值

  import pyodbc 
 import pandas 
 
 oConnexionString ='Driver = {ClouderaHive}; [...]'
 oConnexion = pyodbc.connect （oConnexionString，autocommit = True）
 oConnexion.setdecoding（pyodbc.SQL_CHAR，encoding ='utf-8'）
 oConnexion.setdecoding（pyodbc.SQL_WCHAR，encoding ='utf-8'）
 oConnexion.setencoding（encoding ='utf-8'）
 oQueryParameter =select * from my_db.my_table;
 oParameterData = pandas.read_sql（oQueryParameter，oConnexion）
 oCursor = oConnexion。对于oParameterData.ind中的oRow，cursor（）
 
例如：
 sTableName = oParameterData.loc [oRow，'TableName'] 
 oQueryDeleteTable ='drop table if exists my_db。'+ sTableName +';'
 print（oQueryDeleteTable）
 oCursor.execute（oQueryDeleteTable）
  

I'm using python and pyodbc module in particular to execute Hive queries on Hadoop. The portion of code triggering issue is like this:

import pyodbc
import pandas

oConnexionString = 'Driver={ClouderaHive};[...]'
oConnexion = pyodbc.connect(oConnexionString, autocommit=True)
oConnexion.setencoding(encoding='utf-8')
oQueryParameter = "select * from my_db.my_table;"
oParameterData = pandas.read_sql(oQueryParameter, oConnexion)
oCursor = oConnexion.cursor()

for oRow in oParameterData.index:
    sTableName = oParameterData.loc[oRow,'TableName']
    oQueryDeleteTable = 'drop table if exists my_db.' + sTableName + ';'
    print(oQueryDeleteTable)
    oCursor.execute(oQueryDeleteTable)

The print gives this: drop table if exists dl_audit_data_quality.hero_context_start_gamemode;

But the cursor.execute triggers the following error message

pyodbc.Error: ('HY000', "[HY000] [Cloudera][HiveODBC] (80) Syntax or semantic analysis error thrown in server while execurint query. Error message from server: Error while compiling statement: FAILED: ParseException line 1:44 character ' (80) (SQLExecDirectW)")

Note that when I copy the print and execute it manually in Hue, it works well. I am guessing it has something to do with the encoding of the variable sTableName but I can't figure out how to fix it.

Thanks

解决方案

The query was failing due to incorrect encoding of the variable sTableName. Printing the variable alone would display the text properly. Example with the print above:

>>> print(oQueryDeleteTable)
>>> 'drop table if exists dl_audit_data_quality.hero_context_start_gamemode;'

But printing the original data frame showed it contained characters like this:

>>> print(oParameterData.loc[oRow,'TableName']
>>> 'h\x00e\x00r\x00o\x00_c\x00o\x00n\x00t\x00e\x00x\x00t\x00'

Issue was solved by reworking on the encoding as described here: Python Dictionary Contains Encoded Values

import pyodbc
import pandas

oConnexionString = 'Driver={ClouderaHive};[...]'
oConnexion = pyodbc.connect(oConnexionString, autocommit=True)
oConnexion.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
oConnexion.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
oConnexion.setencoding(encoding='utf-8')
oQueryParameter = "select * from my_db.my_table;"
oParameterData = pandas.read_sql(oQueryParameter, oConnexion)
oCursor = oConnexion.cursor()

for oRow in oParameterData.index:
    sTableName = oParameterData.loc[oRow,'TableName']
    oQueryDeleteTable = 'drop table if exists my_db.' + sTableName + ';'
    print(oQueryDeleteTable)
    oCursor.execute(oQueryDeleteTable)

这篇关于删除表语句中的Hive ParseException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！