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

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

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Not A Problem
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:

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

      Description

      Sqoop1 (import + --target-dir + --table) using DB2 with case-sensitive-table fails with error (DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704), see 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 Data (baseline) using (--as-textfile, --as-avrodatafile, --as-parquetfile)
      #################
      
      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 import --connect $MYCONN --username $MYUSER --password $MYPSWD --table t1_default --target-dir /user/root/t1_default --delete-target-dir --num-mappers 1 --as-avrodatafile
      avro-tools tojson --pretty 'hdfs://host.domain.com/user/root/t1_default/part-m-00000.avro'
      sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table t1_default --target-dir /user/root/t1_default --delete-target-dir --num-mappers 1 --as-parquetfile
      hdfs dfs -ls /user/root/t1_default/*.parquet
      parquet-tools cat --json 'hdfs://host.domain.com/user/root/t1_default/c2a7687c-b2bd-40d6-8959-d8ce9c240ae6.parquet'
      
      Output:
      17/07/17 14:00:41 INFO mapreduce.ImportJobBase: Transferred 2 bytes in 30.2579 seconds (0.0661 bytes/sec)
      17/07/17 14:00:41 INFO mapreduce.ImportJobBase: Retrieved 1 records.
      ~~~~~
      1
      ~~~~~
      17/07/17 14:02:05 INFO mapreduce.ImportJobBase: Transferred 263 bytes in 22.8116 seconds (11.5292 bytes/sec)
      17/07/17 14:02:05 INFO mapreduce.ImportJobBase: Retrieved 1 records.
      ~~~~~
      {
        "C1_DEFAULT" : {
          "int" : 1
        }
      }
      ~~~~~
      17/07/17 14:06:29 INFO mapreduce.ImportJobBase: Transferred 1.2578 KB in 29.3071 seconds (43.9484 bytes/sec)
      17/07/17 14:06:29 INFO mapreduce.ImportJobBase: Retrieved 1 records.
      ~~~~~
      -rw-r--r--   3 root root        449 2017-07-17 14:06 /user/root/t1_default/c2a7687c-b2bd-40d6-8959-d8ce9c240ae6.parquet
      ~~~~~
      {"C1_DEFAULT":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 Data with case-sensitive DB2 table name using (--as-textfile, --as-avrodatafile, --as-parquetfile)
      #################
      
      sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table \"t1_lower\" --target-dir /user/root/t1_lower --delete-target-dir --num-mappers 1 --as-textfile
      sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table \"t1_lower\" --target-dir /user/root/t1_lower --delete-target-dir --num-mappers 1 --as-avrodatafile
      sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table \"t1_lower\" --target-dir /user/root/t1_lower --delete-target-dir --num-mappers 1 --as-parquetfile
      
      Output:
      17/07/17 14:13:59 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM t1_lower AS t WHERE 1=0
      17/07/17 14:13:59 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 14:14:47 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM t1_lower AS t WHERE 1=0
      17/07/17 14:14:47 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 14:15:26 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM t1_lower AS t WHERE 1=0
      17/07/17 14:15:26 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)
      
      #################
      # STEP 05 - Import Data with case-sensitive DB2 table name using (--query) as workaround
      #################
      
      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 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 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-parquetfile
      hdfs dfs -ls /user/root/t1_lower/*.parquet
      parquet-tools cat --json 'hdfs://host.domain.com/user/root/t1_lower/0a9f9927-1e9e-4f6b-90af-adc68403fea0.parquet'
      
      Output:
      17/07/17 14:17:34 INFO mapreduce.ImportJobBase: Transferred 2 bytes in 23.1601 seconds (0.0864 bytes/sec)
      17/07/17 14:17:34 INFO mapreduce.ImportJobBase: Retrieved 1 records.
      ~~~~~
      1
      ~~~~~
      17/07/17 14:30:30 INFO mapreduce.ImportJobBase: Transferred 270 bytes in 21.6549 seconds (12.4683 bytes/sec)
      17/07/17 14:30:30 INFO mapreduce.ImportJobBase: Retrieved 1 records.
      ~~~~~
      {
        "c1_lower" : {
          "int" : 1
        }
      }
      ~~~~~
      17/07/17 14:32:51 INFO mapreduce.ImportJobBase: Transferred 1.2832 KB in 24.6545 seconds (53.2966 bytes/sec)
      17/07/17 14:32:51 INFO mapreduce.ImportJobBase: Retrieved 1 records.
      ~~~~~
      -rw-r--r--   3 root root        461 2017-07-17 14:32 /user/root/t1_lower/0a9f9927-1e9e-4f6b-90af-adc68403fea0.parquet
      ~~~~~
      {"c1_lower":1}
      

        Activity

        Hide
        Ying Cao Ying Cao added a comment -

        The return DB2 error of SQLCODE=-204 is a typical error for "can not find table".

        From details error:

        _17/07/17 14:14:47 INFO manager.SqlManager: Executing SQL statement: SELECT t. FROM t1_lower AS t WHERE 1=0*
        17/07/17 14:14:47 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)_

        Sqoop invoke sql "SELECT t.* FROM t1_lower AS t WHERE 1=0" to DB2, but DB2 is not case-sensitive which mean this statement works on table "T1_LOWER" instead of "t1_lower", so DB2 returns SQLCODE 204 it works well.

        Let's return to you issue, special table name with \"\" to make table name lower case and mark with "" will help to fix the error.

        For example : import a DB2 table "test"

        sqoop export -connect <<url> --username <user> --password <password> --table \"\"test\"\"

        then it will try to access table "test": _ INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM "test" AS t WHERE 1=0_

        Show
        Ying Cao Ying Cao added a comment - The return DB2 error of SQLCODE=-204 is a typical error for "can not find table". From details error: _17/07/17 14:14:47 INFO manager.SqlManager: Executing SQL statement: SELECT t. FROM t1_lower AS t WHERE 1=0* 17/07/17 14:14:47 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)_ Sqoop invoke sql "SELECT t.* FROM t1_lower AS t WHERE 1=0" to DB2, but DB2 is not case-sensitive which mean this statement works on table "T1_LOWER" instead of "t1_lower", so DB2 returns SQLCODE 204 it works well. Let's return to you issue, special table name with \"\" to make table name lower case and mark with "" will help to fix the error. For example : import a DB2 table "test" sqoop export -connect <<url> --username <user> --password <password> --table \"\"test\"\" then it will try to access table "test": _ INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM "test" AS t WHERE 1=0_
        Hide
        Ying Cao Ying Cao added a comment -

        this is a typical error of DB2 is not case-sensitive database, for the lower-case table name please use \"\" to make table name

        Show
        Ying Cao Ying Cao added a comment - this is a typical error of DB2 is not case-sensitive database, for the lower-case table name please use \"\" to make table name

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development