Uploaded image for project: 'Sqoop'
  1. Sqoop
  2. SQOOP-3038

Sqoop export using --hcatalog with RDBMS reserved word column name results in "null" value

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.4.7
    • Component/s: hive-integration
    • Labels:
      None

      Description

      -----------------------

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

      -------------------------------------------------------------------------------------+

        Attachments

        1. SQOOP-3038.patch
          7 kB
          Anna Szonyi

          Issue Links

            Activity

              People

              • Assignee:
                szonyi Anna Szonyi
                Reporter:
                markuskemper@me.com Markus Kemper
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: