使用PyHive和SqlAlchemy创建表

Question

我正在尝试使用SqlAlchemy对象关系映射在配置单元数据库中创建一个表。我的设置是带有PyHive==0.6.1和SQLAlchemy==1.2.11 (以及它们的相对依赖项)和Hive 1.1.0-cdh5.15.1的Python3.6。

我的方法如下：

from sqlalchemy import create_engine

host = 'localhost'
port = 10000
database = 'foo'

engine = create_engine(f'hive://{host}:{port}')
engine.execute(f'CREATE DATABASE {database}')
engine.execute(f'USE {database}')

这可以很好地连接到配置单元并创建一个新的数据库。此时，我创建了数据模型：

from sqlalchemy import Column
from sqlalchemy import String
from sqlalchemy import Integer
from sqlalchemy.ext.declarative import declarative_base

ModelBase = declarative_base()


class TestTable(ModelBase):
    __tablename__ = 'test_table'

    id = Column(Integer, primary_key=True)
    text = Column(String(32), index=True)

我试着：

ModelBase.metadata.create_all(engine)

没有成功:(因为引发了以下异常：

OperationalError: (pyhive.exc.OperationalError) TExecuteStatementResp(status=TStatus(statusCode=3, infoMessages=["*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: ParseException line 3:10 mismatched input 'NOT' expecting ) near 'INT' in create table statement:28:27", 'org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:400', 'org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:187', 'org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:271', 'org.apache.hive.service.cli.operation.Operation:run:Operation.java:337', 'org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementInternal:HiveSessionImpl.java:439', 'org.apache.hive.service.cli.session.HiveSessionImpl:executeStatement:HiveSessionImpl.java:405', 'sun.reflect.GeneratedMethodAccessor21:invoke::-1', 'sun.reflect.DelegatingMethodAccessorImpl:invoke:DelegatingMethodAccessorImpl.java:43', 'java.lang.reflect.Method:invoke:Method.java:606', 'org.apache.hive.service.cli.session.HiveSessionProxy:invoke:HiveSessionProxy.java:78', 'org.apache.hive.service.cli.session.HiveSessionProxy:access$000:HiveSessionProxy.java:36', 'org.apache.hive.service.cli.session.HiveSessionProxy$1:run:HiveSessionProxy.java:63', 'java.security.AccessController:doPrivileged:AccessController.java:-2', 'javax.security.auth.Subject:doAs:Subject.java:415', 'org.apache.hadoop.security.UserGroupInformation:doAs:UserGroupInformation.java:1924', 'org.apache.hive.service.cli.session.HiveSessionProxy:invoke:HiveSessionProxy.java:59', 'com.sun.proxy.$Proxy28:executeStatement::-1', 'org.apache.hive.service.cli.CLIService:executeStatement:CLIService.java:257', 'org.apache.hive.service.cli.thrift.ThriftCLIService:ExecuteStatement:ThriftCLIService.java:501', 'org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1313', 'org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1298', 'org.apache.thrift.ProcessFunction:process:ProcessFunction.java:39', 'org.apache.thrift.TBaseProcessor:process:TBaseProcessor.java:39', 'org.apache.hive.service.auth.TSetIpAddressProcessor:process:TSetIpAddressProcessor.java:56', 'org.apache.thrift.server.TThreadPoolServer$WorkerProcess:run:TThreadPoolServer.java:286', 'java.util.concurrent.ThreadPoolExecutor:runWorker:ThreadPoolExecutor.java:1145', 'java.util.concurrent.ThreadPoolExecutor$Worker:run:ThreadPoolExecutor.java:615', 'java.lang.Thread:run:Thread.java:745', "*org.apache.hadoop.hive.ql.parse.ParseException:line 3:10 mismatched input 'NOT' expecting ) near 'INT' in create table statement:32:5", 'org.apache.hadoop.hive.ql.parse.ParseDriver:parse:ParseDriver.java:208', 'org.apache.hadoop.hive.ql.parse.ParseDriver:parse:ParseDriver.java:170', 'org.apache.hadoop.hive.ql.Driver:compile:Driver.java:524', 'org.apache.hadoop.hive.ql.Driver:compileInternal:Driver.java:1358', 'org.apache.hadoop.hive.ql.Driver:compileAndRespond:Driver.java:1345', 'org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:185'], sqlState='42000', errorCode=40000, errorMessage="Error while compiling statement: FAILED: ParseException line 3:10 mismatched input 'NOT' expecting ) near 'INT' in create table statement"), operationHandle=None) [SQL: '\nCREATE TABLE `test_table` (\n\t`id` INT NOT NULL, \n\t`text` STRING, \n\tPRIMARY KEY (`id`)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/e3q8)

我认为这是相关的部分：

Error while compiling statement: FAILED: ParseException line 3:10 mismatched input 'NOT' expecting ) near 'INT' in create table statement"), operationHandle=None) [SQL: '\nCREATE TABLE `test_table` (\n\t`id` INT NOT NULL, \n\t`text` STRING, \n\tPRIMARY KEY (`id`)\n)\n\n']

这里PyHive#sqlalchemy示例假设表已经存在，但是如果我需要创建它怎么办？

Answer 1

我最近遇到了同样的问题，出现了类似的错误。奇怪的是，我发现如果我在Jupyter中重新运行单元，那么表就是在Hive中创建的。

为了消除错误并使代码顺利运行，我添加了一个create table语句。下面的最终代码运行时没有错误。如果需要添加更多数据，只需注释掉engine.execute行即可。祝好运!

from sqlalchemy import create_engine

#Input Information
host = 'username@local-host'
port = 10000
schema = 'hive_schema'
table = 'new_table'

#Execution
engine = create_engine(f'hive://{host}:{port}/{schema}')
engine.execute('CREATE TABLE ' + table + ' (col1 col1-type, col2 col2-type)')
Data.to_sql(name=table, con=engine, if_exists='append')