我正在尝试使用sqlalchemy orm在配置单元数据库中创建一个表。我的设置是python3.6 PyHive==0.6.1 以及 SQLAlchemy==1.2.11 （及其相对依赖性）和 Hive 1.1.0-cdh5.15.1 .
我的方法如下：
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示例假设表已经存在，但是如果我需要创建它呢？
1条答案
按热度按时间lmyy7pcs1#
我最近也遇到了同样的问题，出现了类似的错误。奇怪的是，我发现如果我在jupyter中重新运行单元格，表是在hive中创建的。
为了消除错误并使代码平稳运行，我添加了CREATETABLE语句。下面的最终代码运行正常。如果需要添加更多数据，只需注解掉engine.execute行即可。祝你好运！