功能需求是cursor.execute(HQL)后获取Hive执行结果影响的数据行。
HQL语句类型主要为:1、INSERT INTO TABLE AS SELECT...FROM..;2、CREATE TABLE AS SELECT... FROM...
已知在Pyhon3.6+MySQL环境下,由cursor.execute(HQL)、cursor.rownumber、cursor.rowcount函数可以正确获取数据库语句的执行结果影响的行数,但在Hive上测试失败。寻求解决方案。使用过的测试伪码如下:
def exec_sql(cursor,SQL):
try:
print('cursor.execute(SQL) ==',cursor.execute(SQL))
for row in cursor: # == cursor.fetchall()
print(row)
print('cursor.rownumber ==',cursor.rownumber)
print('cursor.rowcount ==',cursor.rowcount)
except:
cursor.close
conn.close
# 程序主体
SQL_select = 'select col,col2 from tb2_name limit 100'
SQL_create = 'create table tb_name(column,colunm2) as '+ HQL_select
SQL_insert = 'insert into table tb_name(colunm1,colunm2)'+ HQL_select
import pymysql.cursors
cursor = pymysql.Connect(host = '..',port=10010,user='..',password='..',database='..').cursor()
exec_sql(cursor,SQL_select)
# cursor.execute(SQL) == 100
# cursor.rownumber == 100
# cursor.rowcount == 100
exec_sql(cursor,SQL_insert)
# cursor.execute(SQL) == 100
# cursor.rownumber == 100
# cursor.rowcount == 100
exec_sql(cursor,SQL_create)
# cursor.execute(SQL) == 100
# cursor.rownumber == 100
# cursor.rowcount == 100
from pyhive import Hive
cursor = hive.Connect(host='..', port=3306, user='..', password='..', database='..').cursor()
exec_sql(cursor,SQL_select)
# cursor.execute(SQL) == None
# cursor.rownumber == 100
# cursor.rowcount == -1
exec_sql(cursor,SQL_insert)
# cursor.execute(SQL) == None
# cursor.rownumber == except
# cursor.rowcount == -1
exec_sql(cursor,SQL_create)
# cursor.execute(SQL) == None
# cursor.rownumber == except
# cursor.rowcount == -1