How to Use mapped object to import ORACLE table to HIVE HPL/SQL

Question

I am using hplsql-0.3.31 to import a table data from ORACLE database using MAP OBJECT feature. I can successfully import the object and print it's values. But if I use this object with any hive table/cursor, the code gives me errors.

Here is a sample code (test_ora_cursor.sql)

CREATE OR REPLACE PROCEDURE TEST_PROC_CURR IS
BEGIN

MAP OBJECT test_hive_import TO <schema_name>.TST_HQL AT oraconn;

DECLARE id INT;
DECLARE cur CURSOR FOR SELECT ordr_id FROM test_hive_import;
OPEN cur;
FETCH cur INTO id;
WHILE SQLCODE=0 THEN
  PRINT id;
  FETCH cur INTO id;
END WHILE;
CLOSE cur;
END;

CALL TEST_PROC_CURR;

hplsql-site.xml

<property> 
    <name>hplsql.conn.oraconn</name> 
    <value>
        oracle.jdbc.driver.OracleDriver;jdbc:oracle:thin:@<se‌rver>:<port>:<SID>;<‌user>;<password>
    </va‌lue> 
   <description>ORA connection</description> 
</property>

I am expecting ORDR_ID to be imported in Cursor so that I can use these values to manipulate hive table for by business logic. But I am getting following exceptions:

./hplsql -f ./sqls/test_ora_cursor.sql
17/12/09 21:46:07 INFO jdbc.Utils: Supplied authorities: <hostname:10000>
17/12/09 21:46:07 INFO jdbc.Utils: Resolved authority: <hostname:10000>
Open connection: jdbc:hive2://<hostname>:10000/<db_name>;principal=hive/<Principal>;auth=kerbros (861 ms)
Starting query
Unhandled exception in HPL/SQL
org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException [Error 10001]: Line 1:20 Table not found 'TST_HQL'
        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.jdbc.HiveStatement.executeQuery(HiveStatement.java:378)
        at org.apache.hive.hplsql.Conn.executeQuery(Conn.java:63)
        at org.apache.hive.hplsql.Exec.executeQuery(Exec.java:560)
        at org.apache.hive.hplsql.Stmt.open(Stmt.java:467)
        at org.apache.hive.hplsql.Exec.visitOpen_stmt(Exec.java:1271)
        at org.apache.hive.hplsql.Exec.visitOpen_stmt(Exec.java:52)
        at org.apache.hive.hplsql.HplsqlParser$Open_stmtContext.accept(HplsqlParser.java:11293)
        at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:70)
        at org.apache.hive.hplsql.Exec.visitStmt(Exec.java:1013)
        at org.apache.hive.hplsql.Exec.visitStmt(Exec.java:52)
        at org.apache.hive.hplsql.HplsqlParser$StmtContext.accept(HplsqlParser.java:1023)
        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:454)
        at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:70)
        at org.apache.hive.hplsql.Exec.visitBegin_end_block(Exec.java:930)
        at org.apache.hive.hplsql.Exec.visitBegin_end_block(Exec.java:52)
        at org.apache.hive.hplsql.HplsqlParser$Begin_end_blockContext.accept(HplsqlParser.java:549)
        at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:70)
        at org.apache.hive.hplsql.HplsqlBaseVisitor.visitProc_block(HplsqlBaseVisitor.java:56)
        at org.apache.hive.hplsql.HplsqlParser$Proc_blockContext.accept(HplsqlParser.java:756)
        at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:42)
        at org.apache.hive.hplsql.functions.Function.visit(Function.java:754)
        at org.apache.hive.hplsql.functions.Function.execProc(Function.java:244)
        at org.apache.hive.hplsql.Exec.visitCall_stmt(Exec.java:1797)
        at org.apache.hive.hplsql.Exec.visitCall_stmt(Exec.java:52)
        at org.apache.hive.hplsql.HplsqlParser$Call_stmtContext.accept(HplsqlParser.java:3191)
        at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:70)
        at org.apache.hive.hplsql.Exec.visitStmt(Exec.java:1013)
        at org.apache.hive.hplsql.Exec.visitStmt(Exec.java:52)
        at org.apache.hive.hplsql.HplsqlParser$StmtContext.accept(HplsqlParser.java:1023)
        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:454)
        at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:70)
        at org.apache.hive.hplsql.Exec.visitProgram(Exec.java:920)
        at org.apache.hive.hplsql.Exec.visitProgram(Exec.java:52)
        at org.apache.hive.hplsql.HplsqlParser$ProgramContext.accept(HplsqlParser.java:395)
        at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:42)
        at org.apache.hive.hplsql.Exec.run(Exec.java:775)
        at org.apache.hive.hplsql.Exec.run(Exec.java:751)
        at org.apache.hive.hplsql.Hplsql.main(Hplsql.java:23)
Caused by: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException [Error 10001]: Line 1:20 Table not found 'TST_HQL'
        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.GeneratedMethodAccessor70.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        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.$Proxy38.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.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor.process(HadoopThriftAuthBridge.java:562)
        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:20 Table not found 'TST_HQL'
        at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.getMetaData(SemanticAnalyzer.java:1850)
        at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.getMetaData(SemanticAnalyzer.java:1531)
        at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genResolvedParseTree(SemanticAnalyzer.java:10086)
        at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:10137)
        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
Caused by: org.apache.hadoop.hive.ql.parse.SemanticException: Line 1:20 Table not found 'TST_HQL'
        at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.getMetaData(SemanticAnalyzer.java:1581)
        ... 36 more

Table not found seems obvious. What database did you connect to? — OneCricketeer, Dec 10 '17 at 03:22
The table(TST_HQL) exists in ORACLE database. I have mapped my local object test_hive_import to this table. I am connecting through JDBC. The thing is HIVE should be using test_hive_import for further operations. But HIVE is using TST_HQL which it obviously would not find since this is an ORACLE table. There is only one example for MAP OBJECT hence I am not sure if I am using it correctly. So I was wondering of someone has used this feature to import ORACLE data in hive and then have used it for HIVE processing. — Raje, Dec 10 '17 at 16:26
`ORACLE` is the name of the database? Or you're connecting to an Oracle product? Did you run a `SHOW TABLES` command on this `ORACLE` database? Maybe you're confusing what I asked... What is ``? The error says `.TST_HQL` does not exist **in the Hive Metastore**. Anyways, sure, I've used Sqoop to extract Oracle data into Hive, and it worked fine. However, you still need to know which table and database to connect to. — OneCricketeer, Dec 10 '17 at 18:06
All the identifiers used within <> can be substituted by your local settings. I can not use the actual names for security reasons. Yes, I am connecting to an ORACLE product. This TST_HQL should not exist in METASTORE since it is not a HIVE table. I agree you can use SQOOP,NIFI,Kafka etc to import ORACLE data but I am trying to avoid that. I am using HPL/SQL utility which states that there is a feature which can help you to connect to multiple databases. Please have a look at it. [link](http://www.hplsql.org/doc) [link](http://www.hplsql.org/multiple-databases) — Raje, Dec 10 '17 at 18:28
It should be in the metastore for Hive to know about the table. The class at the bottom `SemanticAnalyzer.getMetaData` is throwing the exception — OneCricketeer, Dec 10 '17 at 18:37
Sure... I am sorry I am a bit new to HIVE configurations. I added TST_HQL table in HIVE as well.. show tables; +-------------------------------------------------+--+ | tab_name | | st016_full | | tst_hql | Here is the connection Property : hplsql-site.xml hplsql.conn.oraconn oracle.jdbc.driver.OracleDriver;jdbc:oracle:thin:@::;; ORA connection — Raje, Dec 10 '17 at 22:14
Thanks @cricket_007 for editing and making the problem statement clear :) . — Raje, Dec 11 '17 at 02:08

How to Use mapped object to import ORACLE table to HIVE HPL/SQL

0 Answers0