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-3038) Sqoop export using --hcatalog with RDBMS reserved word column name results in "null" value
Date Wed, 26 Oct 2016 04:39:58 GMT
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)

Mime
View raw message