Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive, mail # user - Dealing with differents date format


Copy link to this message
-
Re: Dealing with differents date format
Stephen Sprague 2013-07-03, 17:37
well. a couple of comments.

1.  you didn't have to change the your hive variable to a date. in your
case year = flocr(XXXX/10000) and month=cast(XXXX % 100 as int)  just as i
mentioned in my first reply. :)  But given you did maybe that'll make
things easier for you down the road.

2. the 'into' construct in Oracle i believe is a server side variable - in
this case a scalar.  Hive does not have those so you're going to have to
refactor - not just translate - from PL/SQL to HiveQL.   Off the top of my
head - and people might cringe at this - i would investigate the
possibility of storing that min() value in a shell variable and then
reference that shell variable in another query.  eg. var=$(hive -e 'select
min(dt_jour)  from ...')  and then hive -e "your_next_query where
dt_jour=$var" . like i said though its kinda hacky so unless you can come
up with a server-side solution you might have to hold your nose and try it.
On Wed, Jul 3, 2013 at 2:26 AM, Nitin Pawar <[EMAIL PROTECTED]> wrote:

> instead of into we have as in hive
>
> so your query will be select min(dt_jour) as d_debut_semaine from table
> where col = value
> also remember this as is valid only till the query is being executed, it
> wont be preserved once query execution is over
>
>
> On Wed, Jul 3, 2013 at 2:30 PM, Jérôme Verdier <[EMAIL PROTECTED]
> > wrote:
>
>> Hi,
>>
>> Thanks for your help.
>>
>> I resolve the problem by changing my variable in_co_an_mois into a normal
>> date format, and extract month and year by using apporopriate functions :
>> year() and month().
>>
>> But, i  have a new question :
>>
>> the PL/SQL script i have to translate in hive is written like this :
>>
>> SELECT min(dt_jour)
>>     INTO D_debut_semaine
>>     FROM ods.calendrier
>>     WHERE co_an_semaine = in_co_an_sem;
>>
>> I have to record a value in a variable (here : D_debut_semaine) to use
>> this later.
>>
>> Is there a way to do this in Hive ?
>>
>>
>>
>> 2013/7/3 Paul COURTOIS <[EMAIL PROTECTED]>
>>
>>> Hi jerome,
>>>
>>>
>>>
>>> What about the from_unixtime and unix_timestamp  Udf ?
>>>
>>>
>>>
>>>
>>>
>>> from_unixtime() which accept bigint
>>>
>>>
>>>
>>> my 2 cents
>>>
>>>
>>>
>>> Paul
>>>
>>>
>>>
>>> *De :* Nitin Pawar [mailto:[EMAIL PROTECTED]]
>>> *Envoyé :* mercredi 3 juillet 2013 09:29
>>> *À :* [EMAIL PROTECTED]
>>> *Objet :* Re: Dealing with differents date format
>>>
>>>
>>>
>>> easiest way in this kind would be write up a small udf.
>>>
>>> As Stephen suggested, its just a number so you can do maths to extract
>>> year and month out of the number and then do the comparison.
>>>
>>>
>>>
>>> also 201307 is not a supported date format anywhere as per my knowledge
>>>
>>>
>>>
>>> On Wed, Jul 3, 2013 at 12:55 PM, Jérôme Verdier <
>>> [EMAIL PROTECTED]> wrote:
>>>
>>> Hi Stephen,
>>>
>>> Thanks for your reply.
>>>
>>>
>>>
>>> The problem is that my input date is this : in_co_an_mois (format :
>>> YYYYMM, integer), for example, this month, we have 201307
>>>
>>> and i have to deal with this date : add one month, compare to over date,
>>> etc...
>>>
>>> The problem is that apparently, there is no way to do this, because Hive
>>> can't deal with this type of data because it's not a date format.
>>>
>>> For hive, this is just a number.
>>>
>>> Hive can deal with this : 1970-01-01 00:00:00, or this : 2009-03-20, but
>>> not with this unusual format : 201307.
>>>
>>> Thanks.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> 2013/7/2 Stephen Sprague <[EMAIL PROTECTED]>
>>>
>>> not sure i fully understand your dilemma.    have you investigated any
>>> of the date functions listed here?
>>>
>>>
>>> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions
>>>
>>> seems to me you could pull the year and month from a date.  or if you
>>> have an int then do some arithmetic to get the year and month.  eg. year >>> floor( <your int>/10000) and month = cast( <your int> % 100 as int)  [% =>>> modulus operator]
>>>
>>