You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@sqoop.apache.org by "wu (JIRA)" <ji...@apache.org> on 2014/11/18 09:06:35 UTC
[jira] [Comment Edited] (SQOOP-1600) Exception when import data
using Data Connector for Oracle with TIMESTAMP column type to Parquet files
[ https://issues.apache.org/jira/browse/SQOOP-1600?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14215866#comment-14215866 ]
wu edited comment on SQOOP-1600 at 11/18/14 8:06 AM:
-----------------------------------------------------
it seems off, here is what i done,
I). sqoop scott.emp into hdfs
sqoop import -Doraoop.timestamp.string=false --connect jdbc:oracle:thin:@10.1.100.99:1521:ORCL --username YY_YY --password 12345678 --table SCOTT.EMP --target-dir /user/hive/tmpta --as-parquetfile --fields-terminated-by '$' -m 1
II). crate external table for impala, and HIREDATE column is mapped into bigint.
[node01.hd.com:21000] > CREATE EXTERNAL TABLE aaa (EMPNO varchar(200) ,ENAME varchar(20) ,JOB varchar(20) ,MGR varchar(200) ,HIREDATE bigint ,SAL varchar(200),COMM varchar(200),DEPTNO varchar(200))ROW FORMAT DELIMITED FIELDS TERMINATED BY '$' STORED AS PARQUET LOCATION '/user/hive/tmpta';
Query: create EXTERNAL TABLE aaa (EMPNO varchar(200) ,ENAME varchar(20) ,JOB varchar(20) ,MGR varchar(200) ,HIREDATE bigint ,SAL varchar(200),COMM varchar(200),DEPTNO varchar(200))ROW FORMAT DELIMITED FIELDS TERMINATED BY '$' STORED AS PARQUET LOCATION '/user/hive/tmpta'
Fetched 0 row(s) in 0.09s
[node01.hd.com:21000] > select * from aaa;
Query: select * from aaa
+-------+--------+-----------+------+--------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+--------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 345830400000 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 351446400000 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 351619200000 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 354988800000 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 370454400000 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 357494400000 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 360864000000 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 545756400000 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 374774400000 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 368726400000 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 548694000000 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 376156800000 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 376156800000 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 380563200000 | 1300 | NULL | 10 |
+-------+--------+-----------+------+--------------+------+------+--------+
Fetched 14 row(s) in 1.21s
[node01.hd.com:21000] > select from_unixtime(hiredate,'yyyy-Mm-dd') from aaa;
Query: select from_unixtime(hiredate,'yyyy-Mm-dd') from aaa
+---------------------------------------+
| from_unixtime(hiredate, 'yyyy-mm-dd') |
+---------------------------------------+
| 1904-850-29 |
| 1946-722-10 |
| 1951-1222-31 |
| 1922-953-04 |
| 2004-629-14 |
| 2002-153-27 |
| 1972-1025-01 |
| 1979-510-14 |
| 2005-30-31 |
| 1949-929-11 |
| 1936-541-09 |
| 1912-1232-13 |
| 1912-1232-13 |
| 1916-64-25 |
+---------------------------------------+
Fetched 14 row(s) in 0.12s
how can I map timestamp or date type into parquet files?
was (Author: coobery):
it seems off, here is what i done,
I). sqoop scott.emp into hdfs
sqoop import -Doraoop.timestamp.string=false --connect jdbc:oracle:thin:@10.1.10.61:1521:ORCL --username YY_YY --password 12345678 --table SCOTT.EMP --target-dir /user/hive/tmpta --as-parquetfile --fields-terminated-by '$' -m 1
II). crate external table for impala, and HIREDATE column is mapped into bigint.
[node01.hd.com:21000] > CREATE EXTERNAL TABLE aaa (EMPNO varchar(200) ,ENAME varchar(20) ,JOB varchar(20) ,MGR varchar(200) ,HIREDATE bigint ,SAL varchar(200),COMM varchar(200),DEPTNO varchar(200))ROW FORMAT DELIMITED FIELDS TERMINATED BY '$' STORED AS PARQUET LOCATION '/user/hive/tmpta';
Query: create EXTERNAL TABLE aaa (EMPNO varchar(200) ,ENAME varchar(20) ,JOB varchar(20) ,MGR varchar(200) ,HIREDATE bigint ,SAL varchar(200),COMM varchar(200),DEPTNO varchar(200))ROW FORMAT DELIMITED FIELDS TERMINATED BY '$' STORED AS PARQUET LOCATION '/user/hive/tmpta'
Fetched 0 row(s) in 0.09s
[node01.hd.com:21000] > select * from aaa;
Query: select * from aaa
+-------+--------+-----------+------+--------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+--------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 345830400000 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 351446400000 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 351619200000 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 354988800000 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 370454400000 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 357494400000 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 360864000000 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 545756400000 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 374774400000 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 368726400000 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 548694000000 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 376156800000 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 376156800000 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 380563200000 | 1300 | NULL | 10 |
+-------+--------+-----------+------+--------------+------+------+--------+
Fetched 14 row(s) in 1.21s
[node01.hd.com:21000] > select from_unixtime(hiredate,'yyyy-Mm-dd') from aaa;
Query: select from_unixtime(hiredate,'yyyy-Mm-dd') from aaa
+---------------------------------------+
| from_unixtime(hiredate, 'yyyy-mm-dd') |
+---------------------------------------+
| 1904-850-29 |
| 1946-722-10 |
| 1951-1222-31 |
| 1922-953-04 |
| 2004-629-14 |
| 2002-153-27 |
| 1972-1025-01 |
| 1979-510-14 |
| 2005-30-31 |
| 1949-929-11 |
| 1936-541-09 |
| 1912-1232-13 |
| 1912-1232-13 |
| 1916-64-25 |
+---------------------------------------+
Fetched 14 row(s) in 0.12s
how can I map timestamp or date type into parquet files?
> Exception when import data using Data Connector for Oracle with TIMESTAMP column type to Parquet files
> ------------------------------------------------------------------------------------------------------
>
> Key: SQOOP-1600
> URL: https://issues.apache.org/jira/browse/SQOOP-1600
> Project: Sqoop
> Issue Type: Bug
> Affects Versions: 1.4.6
> Environment: Hadoop version: 2.5.0-cdh5.2.0
> Sqoop: 1.4.5
> Reporter: Daniel Lanza García
> Labels: Connector, Oracle, Parquet, Timestamp
> Fix For: 1.4.6
>
> Original Estimate: 24h
> Remaining Estimate: 24h
>
> A error is thrown in each mapper when a import job is run using Quest data connector for Oracle (-direct argument), the source table has a column of the type timestamp and the destination files are of Parquet format.
> The mapper's log show that the error is the following:
> WARN [main] org.apache.hadoop.mapred.YarnChild: Exception running child : org.apache.avro.UnresolvedUnionException: Not in union ["long","null"]: 2012-7-1 0:4:44. 403000000
> Which means the data obtained by the mapper (by the connector) is not of the same type that the schema describe in this field. As we can read in the error, the problem is related with the column UTC_STAMP (the unique column in the source table that store a time stamp).
> If we check the generated schema for this column, we can observe that the column is of the type long and SQL data type TIMESTAMP (93), which is correct.
> Schema: {"name" : "UTC_STAMP","type" : [ "long", "null" ],"columnName" : "UTC_STAMP","sqlType" : "93"}
> If we debug the method where the exception is thrown (org.apache.avro.generic.GenericData.resolveUnion(GenericData.java:605)), we can see that the problem comes when the type of the data obtained by the mapper is of the type String which doesn't correspond with the type described by the schema (long).
> The exception is not thrown when the destination files are text files. The reason is that when you import to text files, a schema is not generated.
> Solution
> In the documentation, there is a section which describe how manage data and timestamps when you use the Data Connector for Oracle and Hadoop. As we can read in this section, this connector has a different way to manage this type of data. However, this behavior can be disabled as describe this section with the below parameter.
> -Doraoop.timestamp.string=false
> Although the problem is solved with this parameter (mandatory if you are in this conditions), the software should deal with this types of column and doesn't throw an exception.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)