When running with hplsql ,below two scenarios are failing.My goal is to create a function and test it.
We are using Hive 2.0 version.
************************************************************************
Scenario 1 :
************************************************************************
Here is the function which I'm trying to create and is failing.
dg_1 and dg_2 tables are already created in hive, by using the above function we are trying to create dg_join3 table and populate some data.
If you run these queries one by one using hplsql then they are created in Hive and are visible.
CREATE FUNCTION my_function()
RETURNS STRING
BEGIN
DROP TABLE default.dg_join3 ;
CREATE TABLE default.dg_join3 (name string, phone string) ;
INSERT INTO TABLE default.dg_join3 SELECT a.name, b.phone from default.dg_1 a,default.dg_2 b where a.id = b.id ;
SELECT * FROM default.dg_join3 ;
END;
************************************************************************
Scenario 2 :
************************************************************************
hplsql -e "INSERT INTO TABLE default.dg_join3 SELECT a.name, b.phone from default.dg_1 a join default.dg_2 b on a.id = b.id ;"
************************************************************************
Observations :
If you run these queries one by one using hplsql then tables are created in Hive and are visible.
hplsql -e "DROP TABLE default.dg_join3 ;"
hplsql -e "CREATE TABLE default.dg_join3 (name string, phone string) ;"
hplsql -e "SELECT * FROM default.dg_join3 ;"
************************************************************************
Log of scneario 2 :
************************************************************************
hplsql -e "INSERT INTO TABLE default.dg_join3 SELECT a.name, b.phone from default.dg_1 a join default.dg_2 b on a.id = b.id ;"
WARNING: Use "yarn jar" to launch YARN applications.
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/appdata/hwx/apache-hive-2.0.0-bin/lib/hive-jdbc-2.0.0-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/appdata/hwx/apache-hive-2.0.0-bin/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/appdata/hwx/hdp/2.4.2.0-258/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Open connection: jdbc:hive2://I-P:10000 (189 ms)
Starting SQL statement
Unhandled exception in HPL/SQL
org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException [Error 10004]: Line 1:50 Invalid table alias or column reference 'b': (possible column names are: id, name)
at org.apache.hive.jdbc.Utils.verifySuccess(Utils.java:258)
at org.apache.hive.jdbc.Utils.verifySuccessWithInfo(Utils.java:244)
at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:247)
at org.apache.hive.hplsql.Conn.executeSql(Conn.java:110)
at org.apache.hive.hplsql.Exec.executeSql(Exec.java:590)
at org.apache.hive.hplsql.Stmt.insertSelect(Stmt.java:570)
at org.apache.hive.hplsql.Stmt.insert(Stmt.java:556)
at org.apache.hive.hplsql.Exec.visitInsert_stmt(Exec.java:1094)
at org.apache.hive.hplsql.Exec.visitInsert_stmt(Exec.java:51)
at org.apache.hive.hplsql.HplsqlParser$Insert_stmtContext.accept(HplsqlParser.java:8154)
at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:70)
at org.apache.hive.hplsql.Exec.visitStmt(Exec.java:987)
at org.apache.hive.hplsql.Exec.visitStmt(Exec.java:51)
at org.apache.hive.hplsql.HplsqlParser$StmtContext.accept(HplsqlParser.java:891)
at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:70)
at org.apache.hive.hplsql.HplsqlBaseVisitor.visitBlock(HplsqlBaseVisitor.java:28)
at org.apache.hive.hplsql.HplsqlParser$BlockContext.accept(HplsqlParser.java:412)
at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:70)
at org.apache.hive.hplsql.Exec.visitProgram(Exec.java:895)
at org.apache.hive.hplsql.Exec.visitProgram(Exec.java:51)
at org.apache.hive.hplsql.HplsqlParser$ProgramContext.accept(HplsqlParser.java:355)
at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:42)
at org.apache.hive.hplsql.Exec.run(Exec.java:753)
at org.apache.hive.hplsql.Exec.run(Exec.java:729)
at org.apache.hive.hplsql.Hplsql.main(Hplsql.java:23)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
Caused by: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException [Error 10004]: Line 1:50 Invalid table alias or column reference 'b': (possible column names are: id, name)
at org.apache.hive.service.cli.operation.Operation.toSQLException(Operation.java:315)
at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:112)
at org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:181)
at org.apache.hive.service.cli.operation.Operation.run(Operation.java:257)
at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:419)
at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:406)
at sun.reflect.GeneratedMethodAccessor110.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:78)
at org.apache.hive.service.cli.session.HiveSessionProxy.access$000(HiveSessionProxy.java:36)
at org.apache.hive.service.cli.session.HiveSessionProxy$1.run(HiveSessionProxy.java:63)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:422)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1709)
at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:59)
at com.sun.proxy.$Proxy20.executeStatementAsync(Unknown Source)
at org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:276)
at org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:486)
at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1317)
at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1302)
at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
at org.apache.hive.service.auth.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:56)
at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.hadoop.hive.ql.parse.SemanticException: Line 1:50 Invalid table alias or column reference 'b': (possible column names are: id, name)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genAllExprNodeDesc(SemanticAnalyzer.java:10537)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:10485)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genSelectPlan(SemanticAnalyzer.java:3822)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genSelectPlan(SemanticAnalyzer.java:3601)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPostGroupByBodyPlan(SemanticAnalyzer.java:8965)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genBodyPlan(SemanticAnalyzer.java:8920)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9765)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9658)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genOPTree(SemanticAnalyzer.java:10131)
at org.apache.hadoop.hive.ql.parse.CalcitePlanner.genOPTree(CalcitePlanner.java:329)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:10142)
at org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:211)
at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:227)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:459)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:316)
at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1189)
at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1183)
at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:110)
... 26 more
... View more
