Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

HPL-SQL : Function creation issues in Hive 2.0

avatar
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
1 ACCEPTED SOLUTION

avatar

Below is the code and output from function:-

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; -- Invoke the function PRINT 'world, come with me now'; PRINT my_function();

[root@sandbox hplsql-0.3.17]# ./hplsql -f my_function.txt world, come with me now 16/06/28 09:34:15 INFO jdbc.Utils: Supplied authorities: localhost:10000 16/06/28 09:34:15 INFO jdbc.Utils: Resolved authority: localhost:10000 Open connection: jdbc:hive2://localhost:10000 (414 ms) Starting SQL statement SQL statement executed successfully (387 ms) Starting SQL statement SQL statement executed successfully (1.29 sec) Starting SQL statement SQL statement executed successfully (9.85 sec) Starting query Query executed successfully (880 ms) null

View solution in original post

12 REPLIES 12

avatar

Thank you Chuck.It worked for me.Would also share a sample function which joins two tables and returns a value ?

[We are able to return a value when using single table in the function .When doing a join between two tables and returning a value it is failing like the above syntax]

avatar

Hi @bpreachuk /All , We were able to find the issue.Changing the return type to record will work.Special thanks to Chuk.

CREATE FUNCTION my_functionn() RETURNS RECORD 
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;
PRINT my_functionn();

avatar

I am facing some issues in HPLSQL.I am using Hive 1.2 version Can you please help me out.

1. TRIM in plsql supports trimming of a pattern from a string.. but TRIM in hplsql removes only spaces but doesnt support removing a pattern.

2. UDFs created on hive on cannot be used in a hplsql script as its antl4 parser doesnt have it in its lexicon