Uploaded image for project: 'Sqoop'
  1. Sqoop
  2. SQOOP-3212

Sqoop1 (export + --export-dir + --table) using DB2 with case-sensitive-table fails with error (DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704)

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:

      $ sqoop version
      17/07/17 15:22:37 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.10.1

      Description

      Sqoop1 (export + --export-dir + --table) using DB2 with case-sensitive-table fails with error (DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704), test case below.

      Test Case

      #################
      # STEP 01 - Create Table and Data
      #################
      
      export MYCONN=jdbc:db2://host.domain.com:50000/SQOOP
      export MYUSER=sqoop;
      export MYPSWD=sqoop;
      
      sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "drop table t1_default"
      sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "create table t1_default (c1_default int)"
      sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "insert into t1_default values (1)"
      sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "select c1_default from t1_default"
      
      ---------------
      | C1_DEFAULT  | 
      ---------------
      | 1           | 
      ---------------
      
      #################
      # STEP 02 - Import and Export Data (baseline) using (--as-textfile, --as-avrodatafile)
      #################
      
      sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table t1_default --target-dir /user/root/t1_default --delete-target-dir --num-mappers 1 --as-textfile
      hdfs dfs -cat /user/root/t1_default/part*
      sqoop export --connect $MYCONN --username $MYUSER --password $MYPSWD --table t1_default --export-dir /user/root/t1_default --num-mappers 1 
      sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "select c1_default from t1_default"
      sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "delete from t1_default"
      sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "insert into t1_default values (1)"
      sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table t1_default --target-dir /user/root/t1_default --delete-target-dir --num-mappers 1 --as-avrodatafile
      sqoop export --connect $MYCONN --username $MYUSER --password $MYPSWD --table t1_default --export-dir /user/root/t1_default --num-mappers 1 
      sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "select c1_default from t1_default"
      
      Output
      17/07/17 14:57:22 INFO mapreduce.ImportJobBase: Transferred 2 bytes in 22.3116 seconds (0.0896 bytes/sec)
      17/07/17 14:57:22 INFO mapreduce.ImportJobBase: Retrieved 1 records.
      ~~~~~
      1
      ~~~~~
      17/07/17 14:58:37 INFO mapreduce.ExportJobBase: Transferred 138 bytes in 21.4796 seconds (6.4247 bytes/sec)
      17/07/17 14:58:37 INFO mapreduce.ExportJobBase: Exported 1 records.
      ~~~~~
      ---------------
      | C1_DEFAULT  | 
      ---------------
      | 1           | 
      | 1           | 
      ---------------
      ~~~~~
      17/07/17 15:04:18 INFO mapreduce.ImportJobBase: Transferred 263 bytes in 24.5847 seconds (10.6977 bytes/sec)
      17/07/17 15:04:18 INFO mapreduce.ImportJobBase: Retrieved 1 records.
      ~~~~~
      17/07/17 15:06:18 INFO mapreduce.ExportJobBase: Transferred 653 bytes in 22.1808 seconds (29.4398 bytes/sec)
      17/07/17 15:06:18 INFO mapreduce.ExportJobBase: Exported 1 records.
      ~~~~~
      ---------------
      | C1_DEFAULT  | 
      ---------------
      | 1           | 
      | 1           | 
      ---------------
      
      #################
      # STEP 03 - Create Table and Data with case-sensitive DB2 table name
      #################
      
      sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "drop table \"t1_lower\""
      sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "create table \"t1_lower\" (\"c1_lower\" int)"
      sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "insert into \"t1_lower\" values (1)"
      sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "select \"c1_lower\" from \"t1_lower\""
      sqoop list-tables --connect $MYCONN --username $MYUSER --password $MYPSWD | egrep -i "t1"
      
      Output:
      ---------------
      | c1_lower    | 
      ---------------
      | 1           | 
      ---------------
      ~~~~~
      T1_DEFAULT
      t1_lower
      
      #################
      # STEP 04 - Import and Export Data with case-sensitive DB2 table name using (--as-textfile, --as-avrodatafile
      #################
      
      sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --query "select * from \"t1_lower\" where \$CONDITIONS" --target-dir /user/root/t1_lower --delete-target-dir --num-mappers 1 --as-textfile
      hdfs dfs -cat /user/root/t1_lower/part*
      sqoop export --connect $MYCONN --username $MYUSER --password $MYPSWD --table \"t1_lower\" --export-dir /user/root/t1_lower --num-mappers 1
      sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --query "select * from \"t1_lower\" where \$CONDITIONS" --target-dir /user/root/t1_lower --delete-target-dir --num-mappers 1 --as-avrodatafile
      avro-tools tojson --pretty 'hdfs://host.domain.com/user/root/t1_lower/part-m-00000.avro'
      sqoop export --connect $MYCONN --username $MYUSER --password $MYPSWD --table \"t1_lower\" --export-dir /user/root/t1_lower --num-mappers 1
      
      Output:
      17/07/17 15:12:00 INFO mapreduce.ImportJobBase: Transferred 2 bytes in 21.9075 seconds (0.0913 bytes/sec)
      17/07/17 15:12:00 INFO mapreduce.ImportJobBase: Retrieved 1 records.
      ~~~~~
      1
      ~~~~~
      17/07/17 15:13:22 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM t1_lower AS t WHERE 1=0
      17/07/17 15:13:23 ERROR manager.SqlManager: Error executing statement: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2INST1.T1_LOWER, DRIVER=4.16.53
      com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2INST1.T1_LOWER, DRIVER=4.16.53
      	at com.ibm.db2.jcc.am.fd.a(fd.java:739)
      ~~~~
      17/07/17 15:15:17 INFO mapreduce.ImportJobBase: Transferred 270 bytes in 21.3889 seconds (12.6234 bytes/sec)
      17/07/17 15:15:17 INFO mapreduce.ImportJobBase: Retrieved 1 records.
      ~~~~~
      {
        "c1_lower" : {
          "int" : 1
        }
      }
      ~~~~~
      17/07/17 15:17:15 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM t1_lower AS t WHERE 1=0
      17/07/17 15:17:15 ERROR manager.SqlManager: Error executing statement: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2INST1.T1_LOWER, DRIVER=4.16.53
      com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2INST1.T1_LOWER, DRIVER=4.16.53
      	at com.ibm.db2.jcc.am.fd.a(fd.java:739)
      

        Activity

        There are no comments yet on this issue.

          People

          • Assignee:
            Unassigned
            Reporter:
            markuskemper@me.com Markus Kemper
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:

              Development