hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Lefty Leverenz <leftylever...@gmail.com>
Subject Re: Converting datime formats in Hive - please help
Date Sun, 26 Oct 2014 23:47:32 GMT
The link in the wiki
<https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions>
to Java documentation for SimpleDateFormat was broken; the new link is:
http://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html.

-- Lefty

On Sun, Oct 26, 2014 at 7:30 PM, Harpreet Bedi <harpreet.bedi9@gmail.com>
wrote:

> Hi Vidya,
>
> I tried recreating a similar hive table and it worked fine. Here is a
> snippet from my terminal. I hope it helps.
>
> I created a table name “testone” with three columns “name” “todaydate” and
> “lastname”
> with the row data as:  “Metallica” “-5-FEB-01”  “Today”.
>
> TERMINAL SNIPPET
>
> hive> *select * from testone;*
> OK
> *Metallica 05-FEB-01 Today*
> Time taken: 1.345 seconds, Fetched: 1 row(s)
> hive> *select from_unixtime(unix_timestamp(todaydate,'dd-MMM-yy')) from
> testone;*
> Query ID = root_20141026181818_b37aef53-4c0f-4b24-a54a-f25491a1807f
> Total jobs = 1
> Launching Job 1 out of 1
> Number of reduce tasks is set to 0 since there's no reduce operator
> Starting Job = job_1414362909167_0001, Tracking URL =
> http://sandbox.hortonworks.com:8088/proxy/application_1414362909167_0001/
> Kill Command = /usr/lib/hadoop/bin/hadoop job  -kill job_1414362909167_0001
> Hadoop job information for Stage-1: number of mappers: 1; number of
> reducers: 0
> 2014-10-26 18:19:57,297 Stage-1 map = 0%,  reduce = 0%
> 2014-10-26 18:20:57,752 Stage-1 map = 0%,  reduce = 0%
> 2014-10-26 18:21:19,374 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU
> 5.98 sec
> MapReduce Total cumulative CPU time: 5 seconds 980 msec
> Ended Job = job_1414362909167_0001
> MapReduce Jobs Launched:
> Job 0: Map: 1   Cumulative CPU: 5.98 sec   HDFS Read: 247 HDFS Write: 20
> SUCCESS
> Total MapReduce CPU Time Spent: 5 seconds 980 msec
> OK
> *2001-02-05 00:00:00*
> Time taken: 182.391 seconds, Fetched: 1 row(s)
>
>
> Harpreet Bedi
> On Oct 26, 2014, at 5:11 PM, Vidya Sujeet <sjayatheertha@gmail.com> wrote:
>
> yes, this retunrs NULLs.. from_unixtime(unix_timestamp('05-FEB-01',
> 'dd-MMM-yy '))
>
>
> On Sun, Oct 26, 2014 at 2:09 PM, Harpreet Bedi <harpreet.bedi9@gmail.com>
> wrote:
>
>> Hi Vidya,
>>
>> Have you tried using this instead?
>>
>> select from_unixtime(unix_timestamp('05-FEB-01', 'dd-MMM-yy '))
>>
>>
>>
>> On Sunday, October 26, 2014, Vidya Sujeet <sjayatheertha@gmail.com>
>> wrote:
>>
>>> Hi Harpreet,
>>>
>>> I used the below statement but it doesn't give me the correct time. It
>>> gives me 2000-12-31 instead of 2001/02/05..what am I doing wrong?
>>>
>>>  select from_unixtime(unix_timestamp('05-FEB-01', 'DD-MMM-YY'))
>>> thanks,
>>> Vidya
>>>
>>>
>>> On Sun, Oct 26, 2014 at 1:06 PM, Harpreet Singh Bedi <
>>> harpreet.bedi9@gmail.com> wrote:
>>>
>>>> You’ll have to use lowercase “y” and lowercase “d” for year and
date of
>>>> the month.
>>>>
>>>> On Oct 26, 2014, at 15:03, Harpreet Singh Bedi <
>>>> Harpreet.Bedi9@gmail.com> wrote:
>>>>
>>>> Yup, that should work!
>>>>
>>>> On Oct 26, 2014, at 15:00, Vidya Sujeet <sjayatheertha@gmail.com>
>>>> wrote:
>>>>
>>>> Ok, the pattern should be (DD-MMM-YY) all in upper case! thanks
>>>>
>>>> On Sun, Oct 26, 2014 at 12:47 PM, Vidya Sujeet <sjayatheertha@gmail.com
>>>> > wrote:
>>>>
>>>>> (dd-mmm-yy) returns NULL
>>>>>
>>>>> On Sun, Oct 26, 2014 at 12:11 PM, Vidya Sujeet <
>>>>> sjayatheertha@gmail.com> wrote:
>>>>>
>>>>>> But what should the format be if the source has data coming *"25-FEB-01'
?
>>>>>> appreciate your help.*
>>>>>>
>>>>>> On Sun, Oct 26, 2014 at 12:01 PM, Nagarjuna Vissarapu <
>>>>>> nagarjuna.viss@gmail.com> wrote:
>>>>>>
>>>>>>> Timestamp class is there to do this.
>>>>>>> On Oct 27, 2014 12:26 AM, "Vidya Sujeet" <sjayatheertha@gmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Hi,
>>>>>>>>
>>>>>>>> The date time format coming from the source is* "25-FEB-01'
.* I
>>>>>>>> want to convert it to the following format. *'MM/DD/YYYY'
*. How
>>>>>>>> can we do this in Hive?
>>>>>>>>
>>>>>>>>
>>>>>>>> I see that as per the documentation
>>>>>>>>
>>>>>>>>
>>>>>>>> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions
>>>>>>>>
>>>>>>>> I could possibly convert the string date to a Unix time stamp
in
>>>>>>>> seconds using the below UDF. However, what is the string
pattern if the
>>>>>>>> date at source is coming this way* "25-FEB-01'* ? The link
>>>>>>>> provided to look up for the patterns does not work.
>>>>>>>>
>>>>>>>> Please help.
>>>>>>>>
>>>>>>>> *Name:* unix_timestamp (string date, string pattern) function.
>>>>>>>> *Description: *Convert time string with given pattern (see
[
>>>>>>>> http://java.sun.com/j2se/1.4.2/docs/api/java/text/SimpleDateFormat.html])
>>>>>>>> to Unix time stamp (in seconds), return 0 if fail:
>>>>>>>> unix_timestamp('2009-03-20', 'yyyy-MM-dd') = 1237532400.
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Vidya
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>
>> --
>> Harpreet Bedi
>>
>>
>
>

Mime
View raw message