Dmitry Kozlov created HIVE-14804: ------------------------------------ Summary: HPLSQL multiple db connection does not switch back to Hive Key: HIVE-14804 URL: https://issues.apache.org/jira/browse/HIVE-14804 Project: Hive Issue Type: Bug Components: hpl/sql Reporter: Dmitry Kozlov Assignee: Dmitry Tolpeko Priority: Blocker
I have a problem with a multi database connection. I have 3 environments that I would like to connect in my HPLSQL code Hive, DB2 and MySql. As soon as I map any table either from DB2 or MySQL my code stops to recognize Hive tables. Actually it starts to think that it is a table from the same database (DB2 or MySql) that was mapped the last. It means your example http://www.hplsql.org/map-object works only one way from Hive to MySQL and it is not possible to go back to Hive. Here is a simple piece of code. declare cnt int; begin /* PRINT 'Start MySQL'; MAP OBJECT tbls TO hive.TBLS AT mysqlconn; select count(*) into cnt from tbls; PRINT cnt; PRINT 'Start Db2'; MAP OBJECT exch TO DBDEV2.TEST_EXCHANGE AT db2conn; select count(1) into cnt from exch; PRINT cnt;*/ PRINT 'Check Hive'; SELECT count(1) into cnt FROM dev.test_sqoop; PRINT cnt; end; It has three blocks. One select from MySQL, second from DB2 and third from Hive ORC table. When first two blocks are commented then block 3 works. See below Check Hive 16/09/20 18:08:08 INFO jdbc.Utils: Supplied authorities: localhost:10000 16/09/20 18:08:08 INFO jdbc.Utils: Resolved authority: localhost:10000 16/09/20 18:08:08 INFO jdbc.HiveConnection: Will try to open client transport with JDBC Uri: jdbc:hive2://localhost:10000 Open connection: jdbc:hive2://localhost:10000 (497 ms) Starting query Query executed successfully (177 ms) 82 When I try to uncomment any of those blocks then block 3 stops working. For example, if I uncomment block 1 I get this output. It is now assumes that dev.test_sqoop is a MySQL table. Contrarily to your example Start MySQL Open connection: jdbc:mysql://10.11.12.144:3306/hive (489 ms) Starting query Query executed successfully (4 ms) 539 Check Hive Starting query Unhandled exception in HPL/SQL com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'dev.test_sqoop' doesn't exist If I comment the second block then it starts to assume that dev.test_sqoop is a DB2 table. See below. So switch between DB2 and MySQL is working, however, the hive table is still not working Start MySQL Open connection: jdbc:mysql://10.11.12.144:3306/hive (485 ms) Starting query Query executed successfully (5 ms) 539 Start Db2 Open connection: jdbc:db2://10.11.12.141:50000/WM (227 ms) Starting query Query executed successfully (48 ms) 0 Check Hive Starting query Unhandled exception in HPL/SQL com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DEV.TEST_SQOOP, DRIVER=4.16.53 Could you, please, provide your feedback on this finding. In addition, I would like to check if it would be possible to insert into a DB2 table records that were selected from a Hive with one statement as soon as DB2 table is properly mapped. Please, explain. Looking forward to hearing from you soon. Regards, Dmitry Kozlov Daisy Intelligence -- This message was sent by Atlassian JIRA (v6.3.4#6332)