sqoop-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Markus Kemper (JIRA)" <j...@apache.org>
Subject [jira] [Created] (SQOOP-3212) Sqoop1 (export + --export-dir + --table) using DB2 with case-sensitive-table fails with error (DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704)
Date Mon, 17 Jul 2017 22:26:00 GMT
Markus Kemper created SQOOP-3212:
------------------------------------

             Summary: Sqoop1 (export + --export-dir + --table) using DB2 with case-sensitive-table
fails with error (DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704)
                 Key: SQOOP-3212
                 URL: https://issues.apache.org/jira/browse/SQOOP-3212
             Project: Sqoop
          Issue Type: Bug
         Environment: $ sqoop version
17/07/17 15:22:37 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.10.1
            Reporter: Markus Kemper


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*
{noformat}
#################
# 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)
{noformat}





--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message