sqoop-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Eric Lin (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (SQOOP-3077) Add support for (import + --hcatalog + --as-avrodatafile) with RDBMS type TIMESTAMP
Date Thu, 23 Aug 2018 01:28:00 GMT

    [ https://issues.apache.org/jira/browse/SQOOP-3077?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16589549#comment-16589549
] 

Eric Lin commented on SQOOP-3077:
---------------------------------

It looks like Hive itself does not support AVRO Timestamp logical type yet:

https://github.com/cloudera/hive/blob/cdh5-1.1.0_5.15.0/serde/src/java/org/apache/hadoop/hive/serde2/avro/TypeInfoToSchema.java#L102-L166

> Add support for (import + --hcatalog + --as-avrodatafile) with RDBMS type TIMESTAMP
> -----------------------------------------------------------------------------------
>
>                 Key: SQOOP-3077
>                 URL: https://issues.apache.org/jira/browse/SQOOP-3077
>             Project: Sqoop
>          Issue Type: Improvement
>            Reporter: Markus Kemper
>            Priority: Major
>
> Please consider adding support for --hcatalog import and TIMESTAMPS, the Avro Specification
suggest that Logical Types support TIMESTAMPS.
> Avro Doc:
> https://avro.apache.org/docs/1.8.1/spec.html#Logical+Types
> {noformat}
> #################
> # STEP 01 - Setup Table and Data
> #################
> sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "drop table
t1_dates"
> sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "create table
t1_dates (c1_int integer, c2_date date, c3_timestamp timestamp)"
> sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "select dbms_metadata.get_ddl('TABLE',
'T1_DATES', 'SQOOP') from dual"
> sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "insert into
t1_dates values (1, current_date, current_timestamp)"
> sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "select *
from t1_dates"
> Output:
> ------------------------
> | DBMS_METADATA.GET_DDL('TABLE','T1_DATES','SQOOP') | 
> ------------------------
> | 
>   CREATE TABLE "SQOOP"."T1_DATES" 
>    (	"C1_INT" NUMBER(*,0), 
> 	"C2_DATE" DATE, 
> 	"C3_TIMESTAMP" TIMESTAMP (6)
>    ) SEGMENT CREATION DEFERRED 
>   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
>  NOCOMPRESS LOGGING
>   TABLESPACE "SQOOP"  | 
> ------------------------
> ---
> ------------------------------------------------
> | C1_INT               | C2_DATE | C3_TIMESTAMP | 
> ------------------------------------------------
> | 1                    | 2016-12-10 15:48:23.0 | 2016-12-10 15:48:23.707327 | 
> ------------------------------------------------
> #################
> # STEP 02 - Import with Text Format
> #################
> beeline -u jdbc:hive2:// -e "use default; drop table t1_dates_text;"
> sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table T1_DATES
--hcatalog-database default --hcatalog-table t1_dates_text --create-hcatalog-table --hcatalog-storage-stanza
'stored as textfile' --num-mappers 1 --map-column-hive c2_date=date,c3_timestamp=timestamp
> beeline -u jdbc:hive2:// -e "use default; describe t1_dates_text; select * from t1_dates_text;"
> +-----------------------------------------------------------------+--+
> |                         createtab_stmt                          |
> +-----------------------------------------------------------------+--+
> | CREATE TABLE `t1_dates_text`(                                   |
> |   `c1_int` decimal(38,0),                                       |
> |   `c2_date` date,                                               |
> |   `c3_timestamp` timestamp)                                     |
> | ROW FORMAT SERDE                                                |
> |   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'          |
> | STORED AS INPUTFORMAT                                           |
> |   'org.apache.hadoop.mapred.TextInputFormat'                    |
> | OUTPUTFORMAT                                                    |
> |   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'  |
> | LOCATION                                                        |
> |   'hdfs://nameservice1/user/hive/warehouse/t1_dates_text'       |
> | TBLPROPERTIES (                                                 |
> |   'transient_lastDdlTime'='1481386391')                         |
> +-----------------------------------------------------------------+--+
> --
> +-----------------------+------------------------+-----------------------------+--+
> | t1_dates_text.c1_int  | t1_dates_text.c2_date  | t1_dates_text.c3_timestamp  |
> +-----------------------+------------------------+-----------------------------+--+
> | 1                     | 2016-12-10             | 2016-12-10 15:48:23.707327  |
> +-----------------------+------------------------+-----------------------------+--+
> #################
> # STEP 03 - Import with Avro Format (default)
> #################
> beeline -u jdbc:hive2:// -e "use default; drop table t1_dates_text;"
> sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table T1_DATES
--hcatalog-database default --hcatalog-table t1_dates_avro --create-hcatalog-table --hcatalog-storage-stanza
'stored as avro' --num-mappers 1
> beeline -u jdbc:hive2:// -e "use default; show create table t1_dates_avro; select * from
t1_dates_avro;"
> +------------------------------------------------------------------+--+
> |                          createtab_stmt                          |
> +------------------------------------------------------------------+--+
> | CREATE TABLE `t1_dates_avro`(                                    |
> |   `c1_int` decimal(38,0) COMMENT '',                             |
> |   `c2_date` string COMMENT '',                                   |
> |   `c3_timestamp` string COMMENT '')                              |
> | ROW FORMAT SERDE                                                 |
> |   'org.apache.hadoop.hive.serde2.avro.AvroSerDe'                 |
> | STORED AS INPUTFORMAT                                            |
> |   'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'   |
> | OUTPUTFORMAT                                                     |
> |   'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'  |
> | LOCATION                                                         |
> |   'hdfs://nameservice1/user/hive/warehouse/t1_dates_avro'        |
> | TBLPROPERTIES (                                                  |
> |   'transient_lastDdlTime'='1481390657')                          |
> +------------------------------------------------------------------+--+
> --
> +-----------------------+------------------------+-----------------------------+--+
> | t1_dates_avro.c1_int  | t1_dates_avro.c2_date  | t1_dates_avro.c3_timestamp  |
> +-----------------------+------------------------+-----------------------------+--+
> | 1                     | 2016-12-10 15:48:23.0  | 2016-12-10 15:48:23.707327  |
> +-----------------------+------------------------+-----------------------------+--+
> #################
> # STEP 04 - Import with Avro Format (--map-column-hive c2_date=date)
> #################
> beeline -u jdbc:hive2:// -e "use default; drop table t1_dates_avro;"
> sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table T1_DATES
--hcatalog-database default --hcatalog-table t1_dates_avro --create-hcatalog-table --hcatalog-storage-stanza
'stored as avro' --num-mappers 1 --columns c1_int,c2_date --map-column-hive c2_date=date
> beeline -u jdbc:hive2:// -e "use default; show create table t1_dates_avro; select * from
t1_dates_avro;"
> +------------------------------------------------------------------+--+
> |                          createtab_stmt                          |
> +------------------------------------------------------------------+--+
> | CREATE TABLE `t1_dates_avro`(                                    |
> |   `c1_int` decimal(38,0) COMMENT '',                             |
> |   `c2_date` date COMMENT '')                                     |
> | ROW FORMAT SERDE                                                 |
> |   'org.apache.hadoop.hive.serde2.avro.AvroSerDe'                 |
> | STORED AS INPUTFORMAT                                            |
> |   'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'   |
> | OUTPUTFORMAT                                                     |
> |   'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'  |
> | LOCATION                                                         |
> |   'hdfs://nameservice1/user/hive/warehouse/t1_dates_avro'        |
> | TBLPROPERTIES (                                                  |
> |   'transient_lastDdlTime'='1481390814')                          |
> +------------------------------------------------------------------+--+
> ---
> +-----------------------+------------------------+--+
> | t1_dates_avro.c1_int  | t1_dates_avro.c2_date  |
> +-----------------------+------------------------+--+
> | 1                     | 2016-12-10             |
> +-----------------------+------------------------+--+
> #################
> # STEP 05 - Import with Avro Format (--map-column-hive c3_timestamp=timestamp)
> #################
> beeline -u jdbc:hive2:// -e "use default; drop table t1_dates_avro;"
> sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table T1_DATES
--hcatalog-database default --hcatalog-table t1_dates_avro --create-hcatalog-table --hcatalog-storage-stanza
'stored as avro' --num-mappers 1 --columns c1_int,c3_timestamp --map-column-hive c3_timestamp=timestamp
> beeline -u jdbc:hive2:// -e "use default; show create table t1_dates_avro; select * from
t1_dates_avro;"
> Output:
> 16/12/10 09:28:52 INFO hcat.SqoopHCatUtilities: Database column name - info map :
> 	c1_int : [Type : 2,Precision : 38,Scale : 0]
> 	c3_timestamp : [Type : 93,Precision : 0,Scale : 6]
> 16/12/10 09:28:52 INFO hcat.SqoopHCatUtilities: Creating HCatalog table default.t1_dates_avro
for import
> 16/12/10 09:28:52 INFO hcat.SqoopHCatUtilities: HCatalog Create table statement: 
> create table `default`.`t1_dates_avro` (
> 	`c1_int` decimal(38),
> 	`c3_timestamp` timestamp)
> stored as avro
> 16/12/10 09:28:52 INFO hcat.SqoopHCatUtilities: Executing external HCatalog CLI process
with args :-f,/tmp/hcat-script-1481390932995
> 16/12/10 09:28:57 INFO hcat.SqoopHCatUtilities: FAILED: Execution Error, return code
1 from org.apache.hadoop.hive.ql.exec.DDLTask. java.lang.UnsupportedOperationException: timestamp
is not supported.
> 16/12/10 09:28:57 ERROR tool.ImportTool: Encountered IOException running import job:
java.io.IOException: HCat exited with status 1
> 	at org.apache.sqoop.mapreduce.hcat.SqoopHCatUtilities.executeExternalHCatProgram(SqoopHCatUtilities.java:1148)
> 	at org.apache.sqoop.mapreduce.hcat.SqoopHCatUtilities.launchHCatCli(SqoopHCatUtilities.java:1097)
> 	at org.apache.sqoop.mapreduce.hcat.SqoopHCatUtilities.createHCatTable(SqoopHCatUtilities.java:644)
> 	at org.apache.sqoop.mapreduce.hcat.SqoopHCatUtilities.configureHCat(SqoopHCatUtilities.java:340)
> 	at org.apache.sqoop.mapreduce.hcat.SqoopHCatUtilities.configureImportOutputFormat(SqoopHCatUtilities.java:802)
> 	at org.apache.sqoop.mapreduce.ImportJobBase.configureOutputFormat(ImportJobBase.java:98)
> 	at org.apache.sqoop.mapreduce.ImportJobBase.runImport(ImportJobBase.java:259)
> 	at org.apache.sqoop.manager.SqlManager.importTable(SqlManager.java:692)
> 	at org.apache.sqoop.manager.OracleManager.importTable(OracleManager.java:444)
> 	at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:507)
> 	at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:615)
> 	at org.apache.sqoop.Sqoop.run(Sqoop.java:143)
> 	at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
> 	at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179)
> 	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218)
> 	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227)
> 	at org.apache.sqoop.Sqoop.main(Sqoop.java:236)
> {noformat}



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Mime
View raw message