CODEWITHSUNDEEP
oracle-databasehdfssqoop
vinayak_narune
vinayak_narune

Reputation: 737

sqoop export from hdfs to oracle Error

Command used:

sqoop export --connect jdbc:oracle:thin:@//xxx:1521/BDWDEV4 --username xxx --password xxx --table TW5T0 --export-dir '/data/raw/oltp/cogen/oraclexport/TW5T0/2015-08-18' -m 8 --input-fields-terminated-by '\001' --lines-terminated-by '\n' --input-escaped-by '\"' --input-optionally-enclosed-by '\"'

The destination table has columns with datatype date in oracle but as show in error it is parsing simple date as timestamp

Error:

15/09/11 06:07:12 INFO mapreduce.Job:  map 0% reduce 0% 15/09/11 06:07:17 INFO mapreduce.Job: Task Id : attempt_1438142065989_99811_m_000000_0, Status : FAILED Error: java.io.IOException: Can't export data, please check failed map task 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:764)
        at org.apache.hadoop.mapred.MapTask.run(MapTask.java:340)
        at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:168)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.security.auth.Subject.doAs(Subject.java:415)
        at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1594)
        at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:163) Caused by: java.lang.RuntimeException: Can't parse input data: '2015-08-15'
        at TZ401.__loadFromFields(TZ401.java:792)
        at TZ401.parse(TZ401.java:645)
        at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)
        ... 10 more Caused by: java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
       at java.sql.Timestamp.valueOf(Timestamp.java:202)
        at TZ401.__loadFromFields(TZ401.java:709)
        ... 12 more

Upvotes: 3

Views: 6849

Answers (6)

Ramesh Muthavarapu
Ramesh Muthavarapu

Reputation: 67

If Hive table columns sequence doesn't match with RDBMS table columns sequence order then there is a chance of same error.
I have resolve my issue after rearrange the columns in RDBMS by creating the table again.

Upvotes: 0

Amey Shirke
Amey Shirke

Reputation: 714

For using with sqoop you need to add option:

--connection-param-file conn-param-file.txt

conn-param-file.txt:

oracle.jdbc.mapDateToTimestamp=false

Upvotes: 0

Amey Shirke
Amey Shirke

Reputation: 714

Oracle drivers map oracle.sql.DATE to java.sql.Timestamp, retaining the time information. If you still want the incorrect but 10g compatible oracle.sql.DATE to java.sql.Date mapping, then you can get it by setting the value of mapDateToTimestamp flag to false (default is true).

https://docs.oracle.com/cd/E11882_01/java.112/e16548/apxref.htm#JJDBC28920

For using with sqoop you need to add option:

--connection-param-file conn-param-file.txt

conn-param-file.txt:

oracle.jdbc.mapDateToTimestamp=false

Upvotes: 0

Olaf H
Olaf H

Reputation: 496

Instead of changing your data files in Hadoop, you should use the --map-column-java argument in your sqoop export.

If you have for example two DATE columns named DATE_COLUMN_1 and DATE_COLUMN_2 in your Oracle table, then you can add the following argument to your sqoop command:

--map-column-java DATE_COLUMN_1=java.sql.Date,DATE_COLUMN_2=java.sql.Date

As mentioned before, the JDBC format has to be used in your Hadoop text file. But in this case yyyy-mm-dd will work.

Upvotes: 4

vinayak_narune
vinayak_narune

Reputation: 737

used --connection-param-file ora.porperties in export sqoop

ora.properties contains oracle.jdbc.mapDateToTimestamp=false

Upvotes: 1

toddlermenot
toddlermenot

Reputation: 1618

From http://archive.cloudera.com/cdh/3/sqoop/SqoopUserGuide.html#_dates_and_times,

Oracle JDBC represents DATE and TIME SQL types as TIMESTAMP values. Any DATE columns in an Oracle database will be imported as a TIMESTAMP in Sqoop, and Sqoop-generated code will store these values in java.sql.Timestamp fields.

When exporting data back to a database, Sqoop parses text fields as TIMESTAMP types (with the form yyyy-mm-dd HH:MM:SS.ffffffff) even if you expect these fields to be formatted with the JDBC date escape format of yyyy-mm-dd. Dates exported to Oracle should be formatted as full timestamps.

So you would need to format the dates in your files to conform to the format yyyy-mm-dd HH:MM:SS.ffffffff before exporting to Oracle.

EDIT:

Answering the comment,

There around 70 files(tables) in hdfs I need to export..So,in all files I need to change the date from yyyy-mm-dd to yyyy-mm-dd HH:MM:SS.ffffffff, any simple way to format it.

Well you could write an awk script to do that for you. Or else you can check if the below idea works:

  1. Create a new temporary table TEMPIMPORT with the same structure as table TW5T0 except changing the column which has the DATE datatype to VARCHAR2
  2. Load using Sqoop into the new temporary table TEMPIMPORT.

  3. Run the DML below to export the data back int TW5T0 (and commit of course):

    insert into tw5t0 (select [[all_your_columns_here_except_date_column]],to_date(date_column,'yyyy-mm-dd') from tempimport);

Upvotes: 1

Related Questions