Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-14804

HPLSQL multiple db connection does not switch back to Hive

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.0.2, 2.1.2
    • Component/s: hpl/sql
    • Labels:
      None

      Description

      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

        Attachments

        1. HIVE-14804.1-branch-2.0.patch
          1 kB
          Fei Hui
        2. HIVE-14804.1-branch-2.1.patch
          1 kB
          Fei Hui
        3. HIVE-14804.2-branch-2.0.patch
          1 kB
          Fei Hui
        4. HIVE-14804.2-branch-2.1.patch
          1 kB
          Fei Hui

          Issue Links

            Activity

              People

              • Assignee:
                ferhui Fei Hui
                Reporter:
                dkozlovdaisy Dmitry Kozlov
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: