You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@sqoop.apache.org by anil gupta <an...@gmail.com> on 2012/03/12 21:57:18 UTC
Sqoop modifies the DATE format in the exported data
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
Re: Sqoop modifies the DATE format in the exported data
Posted by anil gupta <an...@buffalo.edu>.
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
On Mon, Mar 12, 2012 at 2:31 PM, Cheolsoo Park <ch...@cloudera.com>wrote:
> 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
>
>
> On Mon, Mar 12, 2012 at 2:26 PM, anil gupta <an...@buffalo.edu> wrote:
>
>> 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
>>
>> On Mon, Mar 12, 2012 at 2:17 PM, anil gupta <an...@buffalo.edu> wrote:
>>
>>> 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
>>>
>>>
>>> On Mon, Mar 12, 2012 at 2:08 PM, Cheolsoo Park <ch...@cloudera.com>wrote:
>>>
>>>> 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
>>>>
>>>>
>>>> On Mon, Mar 12, 2012 at 1:57 PM, anil gupta <an...@gmail.com>wrote:
>>>>
>>>>> 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
>>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> Thanks & Regards,
>>> Anil Gupta
>>>
>>
>>
>>
>> --
>> Thanks & Regards,
>> Anil Gupta
>>
>
>
--
Thanks & Regards,
Anil Gupta
Re: Sqoop modifies the DATE format in the exported data
Posted by Cheolsoo Park <ch...@cloudera.com>.
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
On Mon, Mar 12, 2012 at 2:26 PM, anil gupta <an...@buffalo.edu> wrote:
> 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
>
> On Mon, Mar 12, 2012 at 2:17 PM, anil gupta <an...@buffalo.edu> wrote:
>
>> 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
>>
>>
>> On Mon, Mar 12, 2012 at 2:08 PM, Cheolsoo Park <ch...@cloudera.com>wrote:
>>
>>> 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
>>>
>>>
>>> On Mon, Mar 12, 2012 at 1:57 PM, anil gupta <an...@gmail.com>wrote:
>>>
>>>> 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
>>>>
>>>
>>>
>>
>>
>> --
>> Thanks & Regards,
>> Anil Gupta
>>
>
>
>
> --
> Thanks & Regards,
> Anil Gupta
>
Re: Sqoop modifies the DATE format in the exported data
Posted by anil gupta <an...@buffalo.edu>.
>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
On Mon, Mar 12, 2012 at 2:17 PM, anil gupta <an...@buffalo.edu> wrote:
> 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
>
>
> On Mon, Mar 12, 2012 at 2:08 PM, Cheolsoo Park <ch...@cloudera.com>wrote:
>
>> 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
>>
>>
>> On Mon, Mar 12, 2012 at 1:57 PM, anil gupta <an...@gmail.com>wrote:
>>
>>> 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
>>>
>>
>>
>
>
> --
> Thanks & Regards,
> Anil Gupta
>
--
Thanks & Regards,
Anil Gupta
Re: Sqoop modifies the DATE format in the exported data
Posted by anil gupta <an...@buffalo.edu>.
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
On Mon, Mar 12, 2012 at 2:08 PM, Cheolsoo Park <ch...@cloudera.com>wrote:
> 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
>
>
> On Mon, Mar 12, 2012 at 1:57 PM, anil gupta <an...@gmail.com> wrote:
>
>> 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
>>
>
>
--
Thanks & Regards,
Anil Gupta
Re: Sqoop modifies the DATE format in the exported data
Posted by Cheolsoo Park <ch...@cloudera.com>.
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
On Mon, Mar 12, 2012 at 1:57 PM, anil gupta <an...@gmail.com> wrote:
> 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
>