sqoop-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Anna Szonyi (JIRA)" <j...@apache.org>
Subject [jira] [Assigned] (SQOOP-3038) Sqoop export using --hcatalog with RDBMS reserved word column name results in "null" value
Date Fri, 04 Nov 2016 14:55:01 GMT

     [ 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)

Mime
View raw message