Hi All,
I am exporting a table from Oracle using Sqoop. I have a date column in Oracle table with format as DD-MON-YY. I get the same format when i dump the data from Oracle SQL Developer. But, when i dump the data using Sqoop i get the following format YYYY-MM-DD HH:MM:SS.x. For the value "24-JAN-08" in DB, Sqoop will dump it as "2008-01-24 00:00:00.0". Is this an expected behavior? If yes, please let me know why does sqoop adds the unnecessary timestamp at the end and also modifies the original date format?
-- Thanks & Regards, Anil Gupta
anil gupta's gravatar image asked Mar 12 2012 at 20:57 in Sqoop-User by anil gupta

5 Answers

Hi Anil,
Some of the Oracle JDBC drivers (version < 9.2 && > 11.1) auto-converts date to timestamp: http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#08_01
Since Sqoop uses the JDBC driver to import data from the Oracle db, dates in output files are in the form of timestamp.
Thanks, Cheolsoo
Cheolsoo Park's gravatar image answered Mar 12 2012 at 21:08 by Cheolsoo Park
Hi Cheolsoo,
Thanks for the inputs. AFAIK, SQL Developer also uses JDBC but its dumping the data in the same format as its in DB. So, i am wondering why Sqoop is unable to dump the data similar to SQL Developer? I am using SQL Developer 3.1.07.
Thanks, Anil Gupta
anil gupta's gravatar image answered Mar 12 2012 at 21:17 by anil gupta
>From the link you had sent me in previous mail. Here is something which might stop the date being converted into Timestamp: "If for some reason your app is very sensitive to this change and you simply must have the 9i-10g behavior, there is a connection property you can set. Set mapDateToTimestamp to false and the driver will revert to the default 9i-10g behavior and map DATE to Date. " Will this help in resolving this problem?
Thanks, Anil
anil gupta's gravatar image answered Mar 12 2012 at 21:26 by anil gupta
Hi Anil,
This issue is already being tracked by the following Apache JIRAs:
https://issues.apache.org/jira/browse/SQOOP-451 https://issues.apache.org/jira/browse/SQOOP-456
In fact, I am currently working on adding new options via which the user can specify format masks for date, time, and timestamp.
Thanks, Cheolsoo
Cheolsoo Park's gravatar image answered Mar 12 2012 at 21:31 by Cheolsoo Park
Thanks Cheolsoo, SQOOP-456 will address my problem. In the meantime, i will be moving ahead by truncating the time part from the export and then processing the data because in my use-case the time is stored in another column.
Thanks, Anil
anil gupta's gravatar image answered Mar 12 2012 at 21:54 by anil gupta