创建带有pyhive和sqlalchemy的表

hof1towb  于 2021-06-27  发布在  Hive
关注(0)|答案(1)|浏览(759)

我正在尝试使用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示例假设表已经存在,但是如果我需要创建它呢?

lmyy7pcs

lmyy7pcs1#

我最近也遇到了同样的问题,出现了类似的错误。奇怪的是,我发现如果我在jupyter中重新运行单元格,表是在hive中创建的。
为了消除错误并使代码平稳运行,我添加了CREATETABLE语句。下面的最终代码运行正常。如果需要添加更多数据,只需注解掉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')

相关问题