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 <nitinpawar...@gmail.com> 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 <verdier.jerom...@gmail.com > > 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 <p...@pole-conseils.com> >> >>> Hi jerome, >>> >>> >>> >>> What about the from_unixtime and unix_timestamp Udf ? >>> >>> >>> >>> >>> >>> from_unixtime() which accept bigint >>> >>> >>> >>> my 2 cents >>> >>> >>> >>> Paul >>> >>> >>> >>> *De :* Nitin Pawar [mailto:nitinpawar...@gmail.com] >>> *Envoyé :* mercredi 3 juillet 2013 09:29 >>> *À :* user@hive.apache.org >>> *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 < >>> verdier.jerom...@gmail.com> 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 <sprag...@gmail.com> >>> >>> 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] >>> >>> or am i not even answering your question? >>> >>> >>> >>> >>> >>> On Tue, Jul 2, 2013 at 2:42 AM, Jérôme Verdier < >>> verdier.jerom...@gmail.com> wrote: >>> >>> Hi, >>> >>> i trying to translate some PL/SQL script in HiveQL, and dealing with >>> unusual date format. >>> >>> i added a variable in my hive script : '${hiveconf:in_co_an_mois}' which >>> is a year/month date format, like this : 201307 (INT format). >>> >>> I would like to transform this in date format, because i have to >>> increment this (add one month/one year). >>> >>> Is there a way to do this in hive ? >>> >>> Thanks. >>> >>> >>> >>> >>> -- >>> *Jérôme* >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> -- >>> Nitin Pawar >>> >> >> >> >> -- >> *Jérôme VERDIER* >> 06.72.19.17.31 >> verdier.jerom...@gmail.com >> >> > > > -- > Nitin Pawar >
