INSERT INTO <table_name> AS SELECT doesn't work
shawnzhu opened this issue · 1 comments
shawnzhu commented
Given SQL:
INSERT INTO db2.<schema_name>.<table_name>
SELECT
EMAIL,
GITHUB_LOGIN
FROM hive.default.<table_name>
WHERE dt = '2020-03-01'
;I got the following error message when running via presto:
Query 20200327_155309_00048_dirc7 failed: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=END-OF-STATEMENT;AS SELECT "EMAIL", "GITHUB_LO;WITH DATA, WITH NO DATA, DRIVER=4.25.13
It doesn't expect such error and data should be inserted into the target table
shawnzhu commented
stack trace info from prestosql web UI:
io.prestosql.spi.PrestoException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=END-OF-STATEMENT;AS SELECT "EMAIL", "GITHUB_LO;WITH DATA, WITH NO DATA, DRIVER=4.25.13
at io.prestosql.plugin.jdbc.BaseJdbcClient.beginInsertTable(BaseJdbcClient.java:499)
at io.prestosql.plugin.jdbc.jmx.StatisticsAwareJdbcClient.lambda$beginInsertTable$18(StatisticsAwareJdbcClient.java:186)
at io.prestosql.plugin.jdbc.jmx.JdbcApiStats.wrap(JdbcApiStats.java:35)
at io.prestosql.plugin.jdbc.jmx.StatisticsAwareJdbcClient.beginInsertTable(StatisticsAwareJdbcClient.java:186)
at io.prestosql.plugin.jdbc.ForwardingJdbcClient.beginInsertTable(ForwardingJdbcClient.java:138)
at io.prestosql.plugin.jdbc.CachingJdbcClient.beginInsertTable(CachingJdbcClient.java:182)
at io.prestosql.plugin.jdbc.JdbcMetadata.beginInsert(JdbcMetadata.java:250)
at io.prestosql.metadata.MetadataManager.beginInsert(MetadataManager.java:781)
at io.prestosql.sql.planner.optimizations.BeginTableWrite$Rewriter.createWriterTarget(BeginTableWrite.java:183)
at io.prestosql.sql.planner.optimizations.BeginTableWrite$Rewriter.visitTableFinish(BeginTableWrite.java:142)
at io.prestosql.sql.planner.optimizations.BeginTableWrite$Rewriter.visitTableFinish(BeginTableWrite.java:76)
at io.prestosql.sql.planner.plan.TableFinishNode.accept(TableFinishNode.java:106)
at io.prestosql.sql.planner.plan.SimplePlanRewriter$RewriteContext.rewrite(SimplePlanRewriter.java:84)
at io.prestosql.sql.planner.plan.SimplePlanRewriter$RewriteContext.lambda$defaultRewrite$0(SimplePlanRewriter.java:73)
at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
at java.base/java.util.Collections$2.tryAdvance(Collections.java:4745)
at java.base/java.util.Collections$2.forEachRemaining(Collections.java:4753)
at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
at io.prestosql.sql.planner.plan.SimplePlanRewriter$RewriteContext.defaultRewrite(SimplePlanRewriter.java:74)
at io.prestosql.sql.planner.plan.SimplePlanRewriter.visitPlan(SimplePlanRewriter.java:38)
at io.prestosql.sql.planner.plan.SimplePlanRewriter.visitPlan(SimplePlanRewriter.java:22)
at io.prestosql.sql.planner.plan.PlanVisitor.visitOutput(PlanVisitor.java:49)
at io.prestosql.sql.planner.plan.OutputNode.accept(OutputNode.java:82)
at io.prestosql.sql.planner.plan.SimplePlanRewriter.rewriteWith(SimplePlanRewriter.java:32)
at io.prestosql.sql.planner.optimizations.BeginTableWrite.optimize(BeginTableWrite.java:73)
at io.prestosql.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:196)
at io.prestosql.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:185)
at io.prestosql.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:180)
at io.prestosql.execution.SqlQueryExecution.doPlanQuery(SqlQueryExecution.java:395)
at io.prestosql.execution.SqlQueryExecution.planQuery(SqlQueryExecution.java:383)
at io.prestosql.execution.SqlQueryExecution.start(SqlQueryExecution.java:338)
at io.prestosql.$gen.Presto_330____20200317_214530_2.run(Unknown Source)
at io.prestosql.execution.SqlQueryManager.createQuery(SqlQueryManager.java:240)
at io.prestosql.dispatcher.LocalDispatchQuery.lambda$startExecution$7(LocalDispatchQuery.java:132)
at io.prestosql.$gen.Presto_330____20200317_214530_2.run(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=END-OF-STATEMENT;AS SELECT "EMAIL", "GITHUB_LO;WITH DATA, WITH NO DATA, DRIVER=4.25.13
at com.ibm.db2.jcc.am.b6.a(b6.java:810)
at com.ibm.db2.jcc.am.b6.a(b6.java:66)
at com.ibm.db2.jcc.am.b6.a(b6.java:140)
at com.ibm.db2.jcc.am.k3.c(k3.java:2824)
at com.ibm.db2.jcc.am.k3.d(k3.java:2808)
at com.ibm.db2.jcc.am.k3.b(k3.java:2168)
at com.ibm.db2.jcc.t4.ab.k(ab.java:446)
at com.ibm.db2.jcc.t4.ab.c(ab.java:104)
at com.ibm.db2.jcc.t4.p.b(p.java:38)
at com.ibm.db2.jcc.t4.av.h(av.java:124)
at com.ibm.db2.jcc.am.k3.ak(k3.java:2163)
at com.ibm.db2.jcc.am.k3.a(k3.java:3366)
at com.ibm.db2.jcc.am.k3.e(k3.java:1131)
at com.ibm.db2.jcc.am.k3.execute(k3.java:1110)
at io.prestosql.plugin.jdbc.BaseJdbcClient.execute(BaseJdbcClient.java:824)
at io.prestosql.plugin.jdbc.BaseJdbcClient.copyTableSchema(BaseJdbcClient.java:513)
at io.prestosql.plugin.jdbc.BaseJdbcClient.beginInsertTable(BaseJdbcClient.java:487)
... 41 more