Ok, here's an update on this. I'm going to provide as much intel as possible
but let me know if folks need more context. Appreciate the help in advance...
1. Process I'm testing: Source table in Oracle 11.2.0.3 > Sqoop Import to
HDFS > Sqoop Export from HDFS > Target table in Oracle 11.2.0.3 (source and
target have exact same DDL) (note: Sqoop version is 1.4.3.1.3.2.0-111)
2. Source/target table schema:
column type
COL1 NUMBER
COL2 VARCHAR2(3 BYTE)
COL3 VARCHAR2(2 BYTE)
COL4 VARCHAR2(2 BYTE)
COL5 NUMBER
COL6 VARCHAR2(60 BYTE)
COL7 VARCHAR2(70 BYTE)
COL8 VARCHAR2(70 BYTE)
COL9 VARCHAR2(40 BYTE)
COL10 VARCHAR2(3 BYTE)
COL11 VARCHAR2(12 BYTE)
COL12 VARCHAR2(30 BYTE)
COL13 DATE
COL14 VARCHAR2(1 BYTE)
COL15 VARCHAR2(70 BYTE)
COL16 VARCHAR2(70 BYTE)
COL17 DATE
COL18 VARCHAR2(30 BYTE)
COL19 DATE
COL20 VARCHAR2(30 BYTE)
COL21 VARCHAR2(3 BYTE)
COL22 NUMBER
COL23 VARCHAR2(30 BYTE)
COL24 DATE
COL25 VARCHAR2(1 BYTE)
3. Sqoop import syntax: sqoop import --table schema.table_name -m 8
--target-dir path/to_dir --connect jdbc:oracle:thin:@xxx.xxx.com:0000/schema
--username xxx --password xxx
Import doesn't fail; record count imported into HDFS matches the rowcount
on the source table
4. Sqoop export syntax: sqoop export --connect
jdbc:oracle:thin:@xxx.xxx.xxx.com:0000/schema --table schema.table_name
--export-dir /path/to_dir --username xxx --password xxx
Export fails with:
java.io.IOException: Can't export data, please check task tracker logs
at
org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)
at
org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:145)
at
org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:763)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:363)
at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1232)
at org.apache.hadoop.mapred.Child.main(Child.java:249)
Caused by: java.lang.IllegalArgumentException: Timestamp format must be
yyyy-mm-dd hh:mm:ss[.fffffffff]
at java.sql.Timestamp.valueOf(Timestamp.java:194)
at tableinfo.__loadFromFields(tableinfo.java:939)
at tableinfo.parse(schema_tableinfo.java:776)
at
org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)
... 10 more
Venkat - I tried your suggestion below and the same failure happened (although
I checked in HDFS and it did indeed bring the DATE in without flipping to
TIMESTAMP).
Any ideas? Need any other info?
From: Venkat Ranganathan [mailto:[email protected]]
Sent: Wednesday, October 30, 2013 12:55 AM
To: [email protected]
Subject: Re: Sqoop from/to Oracle
One thing that you may want to do (depending on the version of Oracle you are
using) is to setup a connection parameter file (please see
--connection-param-file option) and have one Oracle JDBC connection parameter
set to not convert Data to timestamps
oracle.jdbc.mapDateToTimestamp=false
On Tue, Oct 29, 2013 at 5:32 PM, Martin, Nick
<[email protected]<mailto:[email protected]>> wrote:
Weird...let me re-test and if it fails again I'll include some sample data and
the error output.
Thanks for the help Abe!
From: Abraham Elmahrek [mailto:[email protected]<mailto:[email protected]>]
Sent: Tuesday, October 29, 2013 7:06 PM
To: [email protected]<mailto:[email protected]>
Subject: Re: Sqoop from/to Oracle
Nick,
I haven't tested this, but Sqoop should accept full timestamps when exporting
into DATE columns. If the data you're exporting has a full timestamp (as it
should after import), then the export job should just work.
-Abe
On Mon, Oct 28, 2013 at 9:55 AM, Martin, Nick
<[email protected]<mailto:[email protected]>> wrote:
Hi Abe,
After doing some digging on this issue I've found I'm facing what seems like a
fairly common issue with importing data from an Oracle DATE column and trying
to export that same data (from HDFS) back into an Oracle DATE column (of
course, it will have been converted to a timestamp on the way into HDFS).
So, what's the current thinking on the best way to get around this issue?
Thanks,
Nick
From: Abraham Elmahrek [mailto:[email protected]<mailto:[email protected]>]
Sent: Tuesday, October 01, 2013 7:31 PM
To: [email protected]<mailto:[email protected]>
Subject: Re: Sqoop from/to Oracle
Nick,
It looks like Sqoop believes the format of the timestamps are incorrect. Could
you please inspect the data you are attempting to import and verify the columns
are aligned and the data is correctly formatted? I believe if you use the
--verbose option, Sqoop will give more details in its own logs and the tasks
logs. Can you post the task logs back here?
Some other information that might be helpful to us is an example of the data
you're trying to export and the schema of the table you're importing to.
-Abe
On Tue, Oct 1, 2013 at 4:11 PM, Martin, Nick
<[email protected]<mailto:[email protected]>> wrote:
Hi Abe,
Just checking in to see if you had any suggestions for me to try?
Thanks again,
Nick
Sent from my iPhone
On Sep 30, 2013, at 6:36 PM, "Abraham Elmahrek"
<[email protected]<mailto:[email protected]>> wrote:
Nick,
What is the exact command you are using and the exact error you are seeing?
Also, what version of sqoop are you using?
-Abe
On Mon, Sep 30, 2013 at 3:32 PM, Martin, Nick
<[email protected]<mailto:[email protected]>> wrote:
Hi all,
I have a table I've imported from Oracle into HDFS and now I want to export it
into an Oracle Db (import and export table schemas are identical).
My initial attempt at exporting from HDFS (using Sqoop) to Oracle failed (data
types failures). So, my question is what's the best way for me to accomplish
this? My source table is a mixture of NUMBER, VARCHAR, DATE fields. Am I better
off importing into a Hive table I define and then exporting into Oracle from
there? Or do I just need to do some export formatting in my Sqoop export
statement?
Any other intel you'd need to make a recommendation?
Thanks in advance,
Nick
Sent from my iPhone
CONFIDENTIALITY NOTICE
NOTICE: This message is intended for the use of the individual or entity to
which it is addressed and may contain information that is confidential,
privileged and exempt from disclosure under applicable law. If the reader of
this message is not the intended recipient, you are hereby notified that any
printing, copying, dissemination, distribution, disclosure or forwarding of
this communication is strictly prohibited. If you have received this
communication in error, please contact the sender immediately and delete it
from your system. Thank You.
