Markus Kemper created SQOOP-3038:
------------------------------------
Summary: Sqoop export using --hcatalog with RDBMS reserved word
column name results in "null" value
Key: SQOOP-3038
URL: https://issues.apache.org/jira/browse/SQOOP-3038
Project: Sqoop
Issue Type: Bug
Components: hive-integration
Reporter: Markus Kemper
-----------------------
| c1 | value | <=== RDBMS reserved word "value"
-----------------------
| 1 | (null) | <=== null?
-----------------------
TEST CASE:
STEP 01 - Create MySQL Tables
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"drop table t1"
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"create table t1 (c1 int, c2 varchar(5))"
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"insert into t1 values (1, 'one')"
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"show create table t1"
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"select * from t1"
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"drop table t1_value"
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"create table t1_value (c1 int, \`value\` varchar(5))"
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"insert into t1_value select * from t1"
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"show create table t1_value"
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"select * from t1_value"
Output:
-----------------------------------------------
| Table | Create Table |
-----------------------------------------------
| t1 | CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL,
`c2` varchar(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
-----------------------------------------------
-----------------------
| c1 | c2 |
-----------------------
| 1 | one |
-----------------------
-----------------------------------------------
| Table | Create Table |
-----------------------------------------------
| t1_value | CREATE TABLE `t1_value` (
`c1` int(11) DEFAULT NULL,
`value` varchar(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
-----------------------------------------------
-----------------------
| c1 | value |
-----------------------
| 1 | one |
-----------------------
STEP 02 - Verify Import/Export Using --target-dir and --export-dir
sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table t1
--target-dir /user/root/t1 --delete-target-dir --num-mappers 1
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"delete from t1"
sqoop export --connect $MYCONN --username $MYUSER --password $MYPSWD --table t1
--export-dir /user/root/t1 --num-mappers 1
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"select * from t1"
sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table
t1_value --target-dir /user/root/t1 --delete-target-dir --num-mappers 1
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"delete from t1_value"
sqoop export --connect $MYCONN --username $MYUSER --password $MYPSWD --table
t1_value --export-dir /user/root/t1 --num-mappers 1
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"select * from t1_value"
Output:
-----------------------
| c1 | c2 |
-----------------------
| 1 | one |
-----------------------
-----------------------
| c1 | value |
-----------------------
| 1 | one |
-----------------------
STEP 03 - Verify import with --hive-import and export with --hcatalog
beeline -u jdbc:hive2:// -e "use default; drop table t1;"
sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table t1
--target-dir /user/root/t1 --delete-target-dir --hive-import --hive-database
default --hive-table t1 --num-mappers 1
beeline -u jdbc:hive2:// -e "use default; select * from t1;"
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"delete from t1"
sqoop export --connect $MYCONN --username $MYUSER --password $MYPSWD --table t1
--hcatalog-database default --hcatalog-table t1 --num-mappers 1
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"select * from t1"
Output:
+--------+--------+--+
| t1.c1 | t1.c2 |
+--------+--------+--+
| 1 | one |
+--------+--------+--+
-----------------------
| c1 | c2 |
-----------------------
| 1 | one |
-----------------------
beeline -u jdbc:hive2:// -e "use default; drop table t1_value;"
sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table
t1_value --target-dir /user/root/t1 --delete-target-dir --hive-import
--hive-database default --hive-table t1_value --num-mappers 1
beeline -u jdbc:hive2:// -e "use default; select * from t1_value;"
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"delete from t1_value"
sqoop export --connect $MYCONN --username $MYUSER --password $MYPSWD --table
t1_value --hcatalog-database default --hcatalog-table t1_value --num-mappers 1
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"select * from t1_value"
Output:
+--------------+-----------------+--+
| t1_value.c1 | t1_value.value |
+--------------+-----------------+--+
| 1 | one |
+--------------+-----------------+--+
-----------------------
| c1 | value |
-----------------------
| 1 | (null) | <========== null?
-----------------------
beeline -u jdbc:hive2:// -e "use default; show create table t1;"
beeline -u jdbc:hive2:// -e "use default; show create table t1_value;"
Output:
+-------------------------------------------------------------------------------+--+
| createtab_stmt
|
+-------------------------------------------------------------------------------+--+
| CREATE TABLE `t1`(
|
| `c1` int,
|
| `c2` string)
|
| COMMENT 'Imported by sqoop on 2016/10/25 17:16:20'
|
| ROW FORMAT SERDE
|
| 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
|
| WITH SERDEPROPERTIES (
|
| 'field.delim'='\u0001',
|
| 'line.delim'='\n',
|
| 'serialization.format'='\u0001')
|
| STORED AS INPUTFORMAT
|
| 'org.apache.hadoop.mapred.TextInputFormat'
|
| OUTPUTFORMAT
|
| 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
|
| LOCATION
|
| 'hdfs://<hostname>:8020/user/hive/warehouse/t1' |
| TBLPROPERTIES (
|
| 'COLUMN_STATS_ACCURATE'='true',
|
| 'numFiles'='1',
|
| 'totalSize'='6',
|
| 'transient_lastDdlTime'='1477440983')
|
+-------------------------------------------------------------------------------+--+
+-------------------------------------------------------------------------------------+--+
| createtab_stmt
|
+-------------------------------------------------------------------------------------+--+
| CREATE TABLE `t1_value`(
|
| `c1` int,
|
| `value` string)
|
| COMMENT 'Imported by sqoop on 2016/10/25 17:23:02'
|
| ROW FORMAT SERDE
|
| 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
|
| WITH SERDEPROPERTIES (
|
| 'field.delim'='\u0001',
|
| 'line.delim'='\n',
|
| 'serialization.format'='\u0001')
|
| STORED AS INPUTFORMAT
|
| 'org.apache.hadoop.mapred.TextInputFormat'
|
| OUTPUTFORMAT
|
| 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
|
| LOCATION
|
| 'hdfs://<hostname>:8020/user/hive/warehouse/t1_value' |
| TBLPROPERTIES (
|
| 'COLUMN_STATS_ACCURATE'='true',
|
| 'numFiles'='1',
|
| 'totalSize'='6',
|
| 'transient_lastDdlTime'='1477441386')
|
+-------------------------------------------------------------------------------------+--+
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
