Cloudera Community

Support Questions

Find answers, ask questions, and share your expertise
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Advanced Search
Solved Go to solution

HPL-SQL : Function creation issues in Hive 2.0

Labels:
avatar
author-rank avinashrpds7
Explorer

Created ‎06-27-2016 03:28 PM 

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
7,134 Views
0 Kudos
1 ACCEPTED SOLUTION

avatar
author-rank mkumar13
Guru

Created ‎06-28-2016 09:47 AM

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

5,340 Views
12 REPLIES 12

avatar
author-rank avinashrpds7
Explorer

Created ‎07-03-2016 08:32 AM

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]

2,152 Views
0 Kudos

avatar
author-rank avinashrpds7
Explorer

Created ‎07-03-2016 01:48 PM

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();
2,152 Views
0 Kudos

avatar
author-rank priyanka1_munja
Explorer

Created ‎05-29-2017 07:16 AM

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

2,152 Views
0 Kudos
Announcements
What's New @ Cloudera
Cloudera Data Engineering 1.23: Access Spark from Your Favor...
What's New @ Cloudera
HBase REST server scaling support is Generally Available
What's New @ Cloudera
New CLI option in the update-database command
What's New @ Cloudera
New Action menu item in the Cloudera Operational Database UI
What's New @ Cloudera
Get the list of supported instance types in the Cloudera Ope...
View More Announcements