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-3077) Add support for (import + --hcatalog + --as-avrodatafile) with RDBMS type TIMESTAMP
Date Sat, 10 Dec 2016 17:41:59 GMT
Markus Kemper created SQOOP-3077:
------------------------------------

             Summary: 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


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
(v6.3.4#6332)

Mime
View raw message