[
https://issues.apache.org/jira/browse/SQOOP-3038?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Anna Szonyi reassigned SQOOP-3038:
----------------------------------
Assignee: Anna Szonyi
> 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
> Assignee: Anna Szonyi
> Attachments: SQOOP-3038.patch
>
>
> -----------------------
> | 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)
