sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Venkat Ranganathan <vranganat...@hortonworks.com>
Subject Re: Sqoop from/to Oracle
Date Wed, 30 Oct 2013 20:44:32 GMT
Do the data in any of the varchar columns have the delimiter character in
them? That could be tripping the parser.   You can replace or drop the
delimiter using --hive-delims-replacement or --hive-drop-import-delims
options (Yes they can be used independent of Hive import)

Or you can try using the imports to a hive table that does not use text
format and you don't have to modify the data

Venkat


On Wed, Oct 30, 2013 at 12:38 PM, Martin, Nick <NiMartin@pssd.com> wrote:

>  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:vranganathan@hortonworks.com]
> *Sent:* Wednesday, October 30, 2013 12:55 AM
>
> *To:* user@sqoop.apache.org
> *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 <NiMartin@pssd.com> 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:abe@cloudera.com]
> *Sent:* Tuesday, October 29, 2013 7:06 PM****
>
>
> *To:* user@sqoop.apache.org
> *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 <NiMartin@pssd.com> 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:abe@cloudera.com]
> *Sent:* Tuesday, October 01, 2013 7:31 PM****
>
>
> *To:* user@sqoop.apache.org
> *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 <NiMartin@pssd.com> 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" <abe@cloudera.com> 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 <NiMartin@pssd.com> 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.****
>
>

-- 
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.

Mime
View raw message