Markus Kemper created SQOOP-3047:
------------------------------------
Summary: Add support for (import + --hive-import + --as-parquet)
when Parquet table already exists
Key: SQOOP-3047
URL: https://issues.apache.org/jira/browse/SQOOP-3047
Project: Sqoop
Issue Type: Improvement
Components: hive-integration
Reporter: Markus Kemper
When a parquet table already exists (import + --hive-import + --as-parquetfile)
will fail due to a mismatch with the json schema that Hive produces vs. the
json schema that Sqoop generates. The test case below demonstrates how to
reproduce the issue as well as workaround it.
SETUP (create parquet table with Sqoop import and Beeline CTAS)
{noformat}
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 (c_int int, c_date date, c_timestamp timestamp)"
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"describe t1"
---------------------------------------------------------------------------------------------------------
| Field | Type | Null | Key | Default
| Extra |
---------------------------------------------------------------------------------------------------------
| c_int | int(11) | YES | | (null)
| |
| c_date | date | YES | | (null)
| |
| c_timestamp | timestamp | NO | | CURRENT_TIMESTAMP
| on update CURRENT_TIMESTAMP |
---------------------------------------------------------------------------------------------------------
STEP 02 : Insert and Select Row
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"insert into t1 values (1, current_date(), current_timestamp())"
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"select * from t1"
--------------------------------------------------
| c_int | c_date | c_timestamp |
--------------------------------------------------
| 1 | 2016-10-26 | 2016-10-26 14:30:33.0 |
--------------------------------------------------
STEP 03 : Create Hive Tables
beeline -u jdbc:hive2:// -e "use default; drop table t1"
sqoop import -Dmapreduce.map.log.level=DEBUG --connect $MYCONN --username
$MYUSER --password $MYPSWD --table t1 --hcatalog-database default
--hcatalog-table t1_text --create-hcatalog-table --hcatalog-storage-stanza
'stored as parquet' --num-mappers 1
beeline -u jdbc:hive2:// -e "use default; create table t1 stored as parquet as
select * from t1_text;show create table t1;"
+----------------------------------------------------+--+
| createtab_stmt |
+----------------------------------------------------+--+
| CREATE TABLE `t1`( |
| `c_int` int, |
| `c_date` string, |
| `c_timestamp` string) |
| ROW FORMAT SERDE |
| 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' |
| STORED AS INPUTFORMAT |
| 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' |
| OUTPUTFORMAT |
| 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' |
| LOCATION |
| 'hdfs://<namenode>:8020/user/hive/warehouse/t1' |
| TBLPROPERTIES ( |
| 'COLUMN_STATS_ACCURATE'='true', |
| 'numFiles'='1', |
| 'numRows'='2', |
| 'rawDataSize'='6', |
| 'totalSize'='605', |
| 'transient_lastDdlTime'='1478298298') |
+----------------------------------------------------+--+
{noformat}
REPRODUCE ISSUE (import --hive-import append and overwrite)
{noformat}
STEP 01: Attempt --hive-import --append
sqoop import -Dmapreduce.map.log.level=DEBUG --connect $MYCONN --username
$MYUSER --password $MYPSWD --table t1 --hive-import --hive-database default
--hive-table t1 --as-parquetfile --num-mappers 1 --append
16/11/02 20:54:23 WARN mapreduce.DataDrivenImportJob: Target Hive table 't1'
exists! Sqoop will append data into the existing Hive table. Consider using
--hive-overwrite, if you do NOT intend to do appending.
16/11/02 20:54:24 ERROR tool.ImportTool: Encountered IOException running import
job: java.io.IOException: Expected schema:
{"type":"record","name":"t1","fields":[{"name":"c_int","type":["null","int"],"doc":"Converted
from
'int'","default":null},{"name":"c_date","type":["null","string"],"doc":"Converted
from
'string'","default":null},{"name":"c_timestamp","type":["null","string"],"doc":"Converted
from 'string'","default":null}]}
Actual schema: {"type":"record","name":"t1","doc":"Sqoop import of
t1","fields":[{"name":"c_int","type":["null","int"],"default":null,"columnName":"c_int","sqlType":"4"},{"name":"c_date","type":["null","long"],"default":null,"columnName":"c_date","sqlType":"91"},{"name":"c_timestamp","type":["null","long"],"default":null,"columnName":"c_timestamp","sqlType":"93"}],"tableName":"t1"}
STEP 02: Attempt --hive-import --hive-overwrite
sqoop import -Dmapreduce.map.log.level=DEBUG --connect $MYCONN --username
$MYUSER --password $MYPSWD --table t1 --hive-import --hive-database default
--hive-table t1 --as-parquetfile --num-mappers 1 --hive-overwrite
16/11/02 20:56:55 INFO hive.metastore: Connected to metastore.
16/11/02 20:56:56 ERROR tool.ImportTool: Encountered IOException running import
job: java.io.IOException: Expected schema:
{"type":"record","name":"t1","fields":[{"name":"c_int","type":["null","int"],"doc":"Converted
from
'int'","default":null},{"name":"c_date","type":["null","string"],"doc":"Converted
from
'string'","default":null},{"name":"c_timestamp","type":["null","string"],"doc":"Converted
from 'string'","default":null}]}
Actual schema: {"type":"record","name":"t1","doc":"Sqoop import of
t1","fields":[{"name":"c_int","type":["null","int"],"default":null,"columnName":"c_int","sqlType":"4"},{"name":"c_date","type":["null","long"],"default":null,"columnName":"c_date","sqlType":"91"},{"name":"c_timestamp","type":["null","long"],"default":null,"columnName":"c_timestamp","sqlType":"93"}],"tableName":"t1"}
{noformat}
WORKAROUND (create parquet table with Sqoop import --hive-import)
{noformat}
STEP 01: Create Parquet Table with Sqoop
beeline -u jdbc:hive2:// -e "use default; drop table t1"
sqoop import -Dmapreduce.map.log.level=DEBUG --connect $MYCONN --username
$MYUSER --password $MYPSWD --table t1 --hive-import --hive-database default
--hive-table t1 --as-parquetfile --num-mappers 1
hdfs dfs -ls -R /user/hive/warehouse/t1
hdfs dfs -cat /user/hive/warehouse/t1/.metadata/schemas/1.avsc
Output:
drwxr-xr-x - root hive 0 2016-11-02 21:12
/user/hive/warehouse/t1/.metadata
drwxr-xr-x - root hive 0 2016-11-02 21:12
/user/hive/warehouse/t1/.metadata/schemas
-rw-r--r-- 3 root hive 522 2016-11-02 21:12
/user/hive/warehouse/t1/.metadata/schemas/1.avsc
drwxr-xr-x - root hive 0 2016-11-02 21:12
/user/hive/warehouse/t1/.signals
-rw-r--r-- 3 root hive 0 2016-11-02 21:12
/user/hive/warehouse/t1/.signals/unbounded
-rw-r--r-- 3 root supergroup 864 2016-11-02 21:12
/user/hive/warehouse/t1/69f39f03-7b60-472c-a436-0764061fd7e5.parquet
---
{
"type" : "record",
"name" : "t1",
"doc" : "Sqoop import of t1",
"fields" : [ {
"name" : "c_int",
"type" : [ "null", "int" ],
"default" : null,
"columnName" : "c_int",
"sqlType" : "4"
}, {
"name" : "c_date",
"type" : [ "null", "long" ],
"default" : null,
"columnName" : "c_date",
"sqlType" : "91"
}, {
"name" : "c_timestamp",
"type" : [ "null", "long" ],
"default" : null,
"columnName" : "c_timestamp",
"sqlType" : "93"
} ],
"tableName" : "t1"
}
STEP 02: Demonstrate (import overwrite)
sqoop import -Dmapreduce.map.log.level=DEBUG --connect $MYCONN --username
$MYUSER --password $MYPSWD --table t1 --hive-import --hive-database default
--hive-table t1 --as-parquetfile --num-mappers 1 --hive-overwrite
beeline -u jdbc:hive2:// -e "use default; select * from t1;"
+-----------+----------------+-----------------+--+
| t1.c_int | t1.c_date | t1.c_timestamp |
+-----------+----------------+-----------------+--+
| 1 | 1478070000000 | 1478142514000 |
+-----------+----------------+-----------------+--+
STEP 03: Demonstrate (import append)
sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
"insert into t1 values (2, current_date(), current_timestamp())"
sqoop import -Dmapreduce.map.log.level=DEBUG --connect $MYCONN --username
$MYUSER --password $MYPSWD --table t1 --hive-import --hive-database default
--hive-table t1 --as-parquetfile --num-mappers 1 --where "c_int = 2" --append
beeline -u jdbc:hive2:// -e "use default; select * from t1;"
+-----------+----------------+-----------------+--+
| t1.c_int | t1.c_date | t1.c_timestamp |
+-----------+----------------+-----------------+--+
| 1 | 1478070000000 | 1478142514000 |
| 2 | 1478242800000 | 1478297764000 |
+-----------+----------------+-----------------+--+
{noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
