Export Avro data with "null" value in Oracle DATE column

[Brian Henriksen <Br...@humedica.com>]

Hello,

Using Hadoop 1 and Sqoop 1.4.6

I have data in Avro format that I wish to export to Oracle using Sqoop.  Several of the tables columns are “DATE” types.  In one of these columns, the value of the data is often the word “null”.  Sqoop is able to successfully export the data in TEXT format, with the value in Oracle being (null).  When I try to export the Avro data, the Sqoop job fails with this error:


java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

at java.sql.Timestamp.valueOf(Timestamp.java:202)

at org.apache.sqoop.avro.AvroUtil.fromAvro(AvroUtil.java:157)

at org.apache.sqoop.avro.AvroUtil.fromAvro(AvroUtil.java:170)

at org.apache.sqoop.mapreduce.GenericRecordExportMapper.toSqoopRecord(GenericRecordExportMapper.java:94)

at org.apache.sqoop.mapreduce.AvroExportMapper.map(AvroExportMapper.java:36)

at org.apache.sqoop.mapreduce.AvroExportMapper.map(AvroExportMapper.java:30)

at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:140)

at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)

at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:672)

at org.apache.hadoop.mapred.MapTask.run(MapTask.java:330)

at org.apache.hadoop.mapred.Child$4.run(Child.java:268)

at java.security.AccessController.doPrivileged(Native Method)

at javax.security.auth.Subject.doAs(Subject.java:415)

at org.apache.hadoop.security.UserGroupInformat



So it looks like that because a column is designated as DATE type in Oracle, Sqoop tries to construct a java.sql.Timestamp object ONLY WHEN WORKING WITH AVRO?  When Sqoop tries to call java.sql.Timestamp.valueOf(“null”) it throws the exception because “null” does not match the expected format.  This error does NOT happen when I export text data; the data ends up in Oracle successfully.  So the code used when working with TEXT data is able to parse “null” into a DATE field, but the code used when working with AVRO is not.  I was able to complete the export by changing “null” values to “0001-01-01 00:00:00”, but this is not a good solution, I really just need to have nulls in Oracle.  Anyone encountered this issue? Why does Avro need to create a Timestamp object?

Thanks,
Brian