Re: Dealing with differents date format
Hi Stephen,
Thanks for your reply.
The problem is that my input date is this : in_co_an_mois (format : MM,
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/1) 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*
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.comwrote:
Hi Stephen,
Thanks for your reply.
The problem is that my input date is this : in_co_an_mois (format :
MM, 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/1) 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
RE: Dealing with differents date format
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 : MM,
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/1) 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
Re: Dealing with differents date format
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 :
MM, 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/1) 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
Re: Dealing with differents date format
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.comwrote:
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 :
MM, 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/1) 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
Re: Dealing with differents date format
well. a couple of comments.
1. you didn't have to change the your hive variable to a date. in your
case year = flocr(/1) and month=cast( % 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 :
MM, 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/1) 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
Re: Dealing with differents date format
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/1) 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.comwrote:
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*
