Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Sqoop, mail # user - Sqoop from/to Oracle


Copy link to this message
-
RE: Sqoop from/to Oracle
Martin, Nick 2013-10-30, 19:38
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).