Re: Problème with min function in HiveQL
there should be no limitation AFAIK. example + error message is always
helpful - even if you might regret it. :)
On Fri, Aug 30, 2013 at 8:16 AM, Jérôme Verdier
wrote:
> Hi,
>
> Is it possible to have multiple conditions in Having clause ?
>
> I get an error when i'm trying this.
>
> Thanks a lot.
>
>
> 2013/8/30 Jérôme Verdier
>
>> Thanks Stephen,
>>
>> Yes i realise that it was so a stupid questionMaybe i wasn't really
>> awaked this morning ;-)
>>
>> now it's working well.
>>
>> Thanks everyone.
>>
>>
>> 2013/8/30 Stephen Sprague
>>
>>> awright Jerome. look closely at the error message. you can figure this
>>> one out.
>>>
>>>
>>> On Fri, Aug 30, 2013 at 1:17 AM, Jérôme Verdier <
>>> verdier.jerom...@gmail.com> wrote:
>>>
Ok, thanks for this solution.
Unfortunately, i have rewrited my script like this :
INSERT INTO TABLE default.THM_CA_RGRP_PRODUITS_SEM
SELECT
'${hiveconf:in_co_societe}' as co_societe,
'${hiveconf:in_co_an_semaine}'as co_an_semaine,
a.type_entite as type_entite,
a.code_entite as code_entite,
a.type_rgrp_produits as type_rgrp_produits,
a.co_rgrp_produitsas co_rgrp_produits,
SUM(a.MT_CA_NET_TTC) as MT_CA_NET_TTC,
SUM(a.MT_OBJ_CA_NET_TTC) as MT_OBJ_CA_NET_TTC,
SUM(a.NB_CLIENTS) as NB_CLIENTS,
SUM(a.MT_CA_NET_TTC_COMP) as MT_CA_NET_TTC_COMP,
SUM(a.MT_OBJ_CA_NET_TTC_COMP) as
MT_OBJ_CA_NET_TTC_COMP,
SUM(a.NB_CLIENTS_COMP)as NB_CLIENTS_COMP
from
default.THM_CA_RGRP_PRODUITS_JOUR a
JOIN default.CALENDRIER b
-- A verifier
WHERE a.co_societe = '${hiveconf:in_co_societe}'
AND ('${hiveconf:flg_reprise_entite}' = 0 OR a.type_entite<>'MAG')
GROUP BY
a.type_entite,
a.code_entite,
a.type_rgrp_produits,
a.co_rgrp_produits;
HAVING
a.dt_jour >= MIN(b.dt_jour)
AND a.dt_jour < MIN(b.dt_jour)+1
And i have a new error :
FAILED: ParseException line 2:6 cannot recognize input near 'HAVING'
'a' '.'
My Hive version is 0.11 and HAVING function have been implemented in
0.7 so it would work fine.
2013/8/29 Stephen Sprague
> indeed. you nailed it.
>
>
> On Thu, Aug 29, 2013 at 11:53 AM, John Meagher > wrote:
>
>> Aggregate functions need to go in a HAVING clause instead of the WHERE
>> clause. WHERE clauses are applied prior to aggregation, HAVING is
>> applied post aggregation.
>>
>> select ...
>> from ...
>> where some row level filter
>> group by ...
>> having some aggregate level filter
>>
>>
>> On Thu, Aug 29, 2013 at 2:49 PM, Jason Dere
>> wrote:
>> > Looks like the issue is the use of min() within the WHERE clause -
>> the place
>> > where the exception is being thrown has the following comment:
>> > // UDAF in filter condition, group-by caluse, param of
>> funtion, etc.
>> >
>> >
>> > On Aug 29, 2013, at 3:01 AM, Jérôme Verdier <
>> verdier.jerom...@gmail.com>
>> > wrote:
>> >
>> > Hi everybody,
>> >
>> > I am coding some HiveQL script to do some calculations.
>> >
>> > I have a problem with the min() function.
>> >
>> > My hive script is below :
>> >
>> > INSERT INTO TABLE default.THM_CA_RGRP_PRODUITS_SEM
>> >
>> > SELECT
>> > '${hiveconf:in_co_societe}' as co_societe,
>> > '${hiveconf:in_co_an_semaine}'as co_an_semaine,
>> > a.type_entite as type_entite,
>> > a.code_entite as code_entite,
>> > a.type_rgrp_produits as
>> type_rgrp_produits,
>> > a.co_rgrp_produitsas
>> co_rgrp_produits,
>> > SUM(a.MT_CA_NET_TTC) as MT_CA_NET_TTC,
>> > SUM(a.MT_OBJ_CA_NET_TTC) as
>> MT_OBJ_CA_NET_TTC,
>> > SUM(a.NB_CLIENTS) as NB_CLIENTS,
>> > SUM(a.MT_CA_NET_TTC_COMP) as
>> MT_CA_NET_TTC_COMP,
>> > SUM(a.MT_OBJ_CA_NET_TTC_COMP) as
>> MT_OBJ_CA_NET_TTC_COMP,
>> > SUM(a.NB_CLIENTS_COMP)as NB_CLIENTS_COMP
>> > from
>> > default.THM_CA_RGRP_PRODUITS_JOUR a
>> > JOIN default.CALENDRIER b
>> > -- A verifier
>> > WHERE CAST(a.d
Re: Problème with min function in HiveQL
Hi,
Is it possible to have multiple conditions in Having clause ?
I get an error when i'm trying this.
Thanks a lot.
2013/8/30 Jérôme Verdier
> Thanks Stephen,
>
> Yes i realise that it was so a stupid questionMaybe i wasn't really
> awaked this morning ;-)
>
> now it's working well.
>
> Thanks everyone.
>
>
> 2013/8/30 Stephen Sprague
>
>> awright Jerome. look closely at the error message. you can figure this
>> one out.
>>
>>
>> On Fri, Aug 30, 2013 at 1:17 AM, Jérôme Verdier <
>> verdier.jerom...@gmail.com> wrote:
>>
>>> Ok, thanks for this solution.
>>>
>>> Unfortunately, i have rewrited my script like this :
>>>
>>> INSERT INTO TABLE default.THM_CA_RGRP_PRODUITS_SEM
>>>
>>> SELECT
>>> '${hiveconf:in_co_societe}' as co_societe,
>>> '${hiveconf:in_co_an_semaine}'as co_an_semaine,
>>> a.type_entite as type_entite,
>>> a.code_entite as code_entite,
>>> a.type_rgrp_produits as type_rgrp_produits,
>>> a.co_rgrp_produitsas co_rgrp_produits,
>>> SUM(a.MT_CA_NET_TTC) as MT_CA_NET_TTC,
>>> SUM(a.MT_OBJ_CA_NET_TTC) as MT_OBJ_CA_NET_TTC,
>>> SUM(a.NB_CLIENTS) as NB_CLIENTS,
>>> SUM(a.MT_CA_NET_TTC_COMP) as MT_CA_NET_TTC_COMP,
>>> SUM(a.MT_OBJ_CA_NET_TTC_COMP) as
>>> MT_OBJ_CA_NET_TTC_COMP,
>>> SUM(a.NB_CLIENTS_COMP)as NB_CLIENTS_COMP
>>> from
>>> default.THM_CA_RGRP_PRODUITS_JOUR a
>>> JOIN default.CALENDRIER b
>>> -- A verifier
>>> WHERE a.co_societe = '${hiveconf:in_co_societe}'
>>>
>>> AND ('${hiveconf:flg_reprise_entite}' = 0 OR a.type_entite<>'MAG')
>>> GROUP BY
>>> a.type_entite,
>>> a.code_entite,
>>> a.type_rgrp_produits,
>>> a.co_rgrp_produits;
>>>HAVING
>>> a.dt_jour >= MIN(b.dt_jour)
>>> AND a.dt_jour < MIN(b.dt_jour)+1
>>>
>>> And i have a new error :
>>>
>>> FAILED: ParseException line 2:6 cannot recognize input near 'HAVING' 'a'
>>> '.'
>>>
>>> My Hive version is 0.11 and HAVING function have been implemented in 0.7
>>> so it would work fine.
>>>
>>>
>>>
>>> 2013/8/29 Stephen Sprague
>>>
indeed. you nailed it.
On Thu, Aug 29, 2013 at 11:53 AM, John Meagher
wrote:
> Aggregate functions need to go in a HAVING clause instead of the WHERE
> clause. WHERE clauses are applied prior to aggregation, HAVING is
> applied post aggregation.
>
> select ...
> from ...
> where some row level filter
> group by ...
> having some aggregate level filter
>
>
> On Thu, Aug 29, 2013 at 2:49 PM, Jason Dere
> wrote:
> > Looks like the issue is the use of min() within the WHERE clause -
> the place
> > where the exception is being thrown has the following comment:
> > // UDAF in filter condition, group-by caluse, param of
> funtion, etc.
> >
> >
> > On Aug 29, 2013, at 3:01 AM, Jérôme Verdier <
> verdier.jerom...@gmail.com>
> > wrote:
> >
> > Hi everybody,
> >
> > I am coding some HiveQL script to do some calculations.
> >
> > I have a problem with the min() function.
> >
> > My hive script is below :
> >
> > INSERT INTO TABLE default.THM_CA_RGRP_PRODUITS_SEM
> >
> > SELECT
> > '${hiveconf:in_co_societe}' as co_societe,
> > '${hiveconf:in_co_an_semaine}'as co_an_semaine,
> > a.type_entite as type_entite,
> > a.code_entite as code_entite,
> > a.type_rgrp_produits as
> type_rgrp_produits,
> > a.co_rgrp_produitsas
> co_rgrp_produits,
> > SUM(a.MT_CA_NET_TTC) as MT_CA_NET_TTC,
> > SUM(a.MT_OBJ_CA_NET_TTC) as
> MT_OBJ_CA_NET_TTC,
> > SUM(a.NB_CLIENTS) as NB_CLIENTS,
> > SUM(a.MT_CA_NET_TTC_COMP) as
> MT_CA_NET_TTC_COMP,
> > SUM(a.MT_OBJ_CA_NET_TTC_COMP) as
> MT_OBJ_CA_NET_TTC_COMP,
> > SUM(a.NB_CLIENTS_COMP)as NB_CLIENTS_COMP
> > from
> > default.THM_CA_RGRP_PRODUITS_JOUR a
> > JOIN default.CALENDRIER b
> > -- A verifier
> > WHERE CAST(a.dt_jour AS TIMESTAMP) >=
> > CAST(min(UNIX_TIMESTAMP(b.dt_jour)) AS BIGINT)
> > AND CAST(a.dt_jour AS TIMESTAMP) < CAST(min(b.dt_jour)+1 AS
> TIMESTAMP)
> > AND a.co_societe = '${hiveconf:in_co_societe}'
> > AND ('${hiveconf:flg_reprise_entite}' = 0 OR
> a.type_entite<>'MAG')
>
Re: Problème with min function in HiveQL
Thanks Stephen,
Yes i realise that it was so a stupid questionMaybe i wasn't really
awaked this morning ;-)
now it's working well.
Thanks everyone.
2013/8/30 Stephen Sprague
> awright Jerome. look closely at the error message. you can figure this
> one out.
>
>
> On Fri, Aug 30, 2013 at 1:17 AM, Jérôme Verdier <
> verdier.jerom...@gmail.com> wrote:
>
>> Ok, thanks for this solution.
>>
>> Unfortunately, i have rewrited my script like this :
>>
>> INSERT INTO TABLE default.THM_CA_RGRP_PRODUITS_SEM
>>
>> SELECT
>> '${hiveconf:in_co_societe}' as co_societe,
>> '${hiveconf:in_co_an_semaine}'as co_an_semaine,
>> a.type_entite as type_entite,
>> a.code_entite as code_entite,
>> a.type_rgrp_produits as type_rgrp_produits,
>> a.co_rgrp_produitsas co_rgrp_produits,
>> SUM(a.MT_CA_NET_TTC) as MT_CA_NET_TTC,
>> SUM(a.MT_OBJ_CA_NET_TTC) as MT_OBJ_CA_NET_TTC,
>> SUM(a.NB_CLIENTS) as NB_CLIENTS,
>> SUM(a.MT_CA_NET_TTC_COMP) as MT_CA_NET_TTC_COMP,
>> SUM(a.MT_OBJ_CA_NET_TTC_COMP) as
>> MT_OBJ_CA_NET_TTC_COMP,
>> SUM(a.NB_CLIENTS_COMP)as NB_CLIENTS_COMP
>> from
>> default.THM_CA_RGRP_PRODUITS_JOUR a
>> JOIN default.CALENDRIER b
>> -- A verifier
>> WHERE a.co_societe = '${hiveconf:in_co_societe}'
>>
>> AND ('${hiveconf:flg_reprise_entite}' = 0 OR a.type_entite<>'MAG')
>> GROUP BY
>> a.type_entite,
>> a.code_entite,
>> a.type_rgrp_produits,
>> a.co_rgrp_produits;
>>HAVING
>> a.dt_jour >= MIN(b.dt_jour)
>> AND a.dt_jour < MIN(b.dt_jour)+1
>>
>> And i have a new error :
>>
>> FAILED: ParseException line 2:6 cannot recognize input near 'HAVING' 'a'
>> '.'
>>
>> My Hive version is 0.11 and HAVING function have been implemented in 0.7
>> so it would work fine.
>>
>>
>>
>> 2013/8/29 Stephen Sprague
>>
>>> indeed. you nailed it.
>>>
>>>
>>> On Thu, Aug 29, 2013 at 11:53 AM, John Meagher
>>> wrote:
>>>
Aggregate functions need to go in a HAVING clause instead of the WHERE
clause. WHERE clauses are applied prior to aggregation, HAVING is
applied post aggregation.
select ...
from ...
where some row level filter
group by ...
having some aggregate level filter
On Thu, Aug 29, 2013 at 2:49 PM, Jason Dere
wrote:
> Looks like the issue is the use of min() within the WHERE clause -
the place
> where the exception is being thrown has the following comment:
> // UDAF in filter condition, group-by caluse, param of
funtion, etc.
>
>
> On Aug 29, 2013, at 3:01 AM, Jérôme Verdier <
verdier.jerom...@gmail.com>
> wrote:
>
> Hi everybody,
>
> I am coding some HiveQL script to do some calculations.
>
> I have a problem with the min() function.
>
> My hive script is below :
>
> INSERT INTO TABLE default.THM_CA_RGRP_PRODUITS_SEM
>
> SELECT
> '${hiveconf:in_co_societe}' as co_societe,
> '${hiveconf:in_co_an_semaine}'as co_an_semaine,
> a.type_entite as type_entite,
> a.code_entite as code_entite,
> a.type_rgrp_produits as
type_rgrp_produits,
> a.co_rgrp_produitsas co_rgrp_produits,
> SUM(a.MT_CA_NET_TTC) as MT_CA_NET_TTC,
> SUM(a.MT_OBJ_CA_NET_TTC) as
MT_OBJ_CA_NET_TTC,
> SUM(a.NB_CLIENTS) as NB_CLIENTS,
> SUM(a.MT_CA_NET_TTC_COMP) as
MT_CA_NET_TTC_COMP,
> SUM(a.MT_OBJ_CA_NET_TTC_COMP) as
MT_OBJ_CA_NET_TTC_COMP,
> SUM(a.NB_CLIENTS_COMP)as NB_CLIENTS_COMP
> from
> default.THM_CA_RGRP_PRODUITS_JOUR a
> JOIN default.CALENDRIER b
> -- A verifier
> WHERE CAST(a.dt_jour AS TIMESTAMP) >=
> CAST(min(UNIX_TIMESTAMP(b.dt_jour)) AS BIGINT)
> AND CAST(a.dt_jour AS TIMESTAMP) < CAST(min(b.dt_jour)+1 AS
TIMESTAMP)
> AND a.co_societe = '${hiveconf:in_co_societe}'
> AND ('${hiveconf:flg_reprise_entite}' = 0 OR
a.type_entite<>'MAG')
> GROUP BY
> a.type_entite,
> a.code_entite,
> a.type_rgrp_produits,
> a.co_rgrp_produits;
>
> And, when i try to launch this, i get this error :
>
> FAILED: SemanticException [Error 10128]: Line 20:62 Not yet supp
Re: Problème with min function in HiveQL
awright Jerome. look closely at the error message. you can figure this one
out.
On Fri, Aug 30, 2013 at 1:17 AM, Jérôme Verdier
wrote:
> Ok, thanks for this solution.
>
> Unfortunately, i have rewrited my script like this :
>
> INSERT INTO TABLE default.THM_CA_RGRP_PRODUITS_SEM
>
> SELECT
> '${hiveconf:in_co_societe}' as co_societe,
> '${hiveconf:in_co_an_semaine}'as co_an_semaine,
> a.type_entite as type_entite,
> a.code_entite as code_entite,
> a.type_rgrp_produits as type_rgrp_produits,
> a.co_rgrp_produitsas co_rgrp_produits,
> SUM(a.MT_CA_NET_TTC) as MT_CA_NET_TTC,
> SUM(a.MT_OBJ_CA_NET_TTC) as MT_OBJ_CA_NET_TTC,
> SUM(a.NB_CLIENTS) as NB_CLIENTS,
> SUM(a.MT_CA_NET_TTC_COMP) as MT_CA_NET_TTC_COMP,
> SUM(a.MT_OBJ_CA_NET_TTC_COMP) as
> MT_OBJ_CA_NET_TTC_COMP,
> SUM(a.NB_CLIENTS_COMP)as NB_CLIENTS_COMP
> from
> default.THM_CA_RGRP_PRODUITS_JOUR a
> JOIN default.CALENDRIER b
> -- A verifier
> WHERE a.co_societe = '${hiveconf:in_co_societe}'
>
> AND ('${hiveconf:flg_reprise_entite}' = 0 OR a.type_entite<>'MAG')
> GROUP BY
> a.type_entite,
> a.code_entite,
> a.type_rgrp_produits,
> a.co_rgrp_produits;
>HAVING
> a.dt_jour >= MIN(b.dt_jour)
> AND a.dt_jour < MIN(b.dt_jour)+1
>
> And i have a new error :
>
> FAILED: ParseException line 2:6 cannot recognize input near 'HAVING' 'a'
> '.'
>
> My Hive version is 0.11 and HAVING function have been implemented in 0.7
> so it would work fine.
>
>
>
> 2013/8/29 Stephen Sprague
>
>> indeed. you nailed it.
>>
>>
>> On Thu, Aug 29, 2013 at 11:53 AM, John Meagher wrote:
>>
>>> Aggregate functions need to go in a HAVING clause instead of the WHERE
>>> clause. WHERE clauses are applied prior to aggregation, HAVING is
>>> applied post aggregation.
>>>
>>> select ...
>>> from ...
>>> where some row level filter
>>> group by ...
>>> having some aggregate level filter
>>>
>>>
>>> On Thu, Aug 29, 2013 at 2:49 PM, Jason Dere
>>> wrote:
>>> > Looks like the issue is the use of min() within the WHERE clause - the
>>> place
>>> > where the exception is being thrown has the following comment:
>>> > // UDAF in filter condition, group-by caluse, param of
>>> funtion, etc.
>>> >
>>> >
>>> > On Aug 29, 2013, at 3:01 AM, Jérôme Verdier <
>>> verdier.jerom...@gmail.com>
>>> > wrote:
>>> >
>>> > Hi everybody,
>>> >
>>> > I am coding some HiveQL script to do some calculations.
>>> >
>>> > I have a problem with the min() function.
>>> >
>>> > My hive script is below :
>>> >
>>> > INSERT INTO TABLE default.THM_CA_RGRP_PRODUITS_SEM
>>> >
>>> > SELECT
>>> > '${hiveconf:in_co_societe}' as co_societe,
>>> > '${hiveconf:in_co_an_semaine}'as co_an_semaine,
>>> > a.type_entite as type_entite,
>>> > a.code_entite as code_entite,
>>> > a.type_rgrp_produits as
>>> type_rgrp_produits,
>>> > a.co_rgrp_produitsas co_rgrp_produits,
>>> > SUM(a.MT_CA_NET_TTC) as MT_CA_NET_TTC,
>>> > SUM(a.MT_OBJ_CA_NET_TTC) as MT_OBJ_CA_NET_TTC,
>>> > SUM(a.NB_CLIENTS) as NB_CLIENTS,
>>> > SUM(a.MT_CA_NET_TTC_COMP) as
>>> MT_CA_NET_TTC_COMP,
>>> > SUM(a.MT_OBJ_CA_NET_TTC_COMP) as
>>> MT_OBJ_CA_NET_TTC_COMP,
>>> > SUM(a.NB_CLIENTS_COMP)as NB_CLIENTS_COMP
>>> > from
>>> > default.THM_CA_RGRP_PRODUITS_JOUR a
>>> > JOIN default.CALENDRIER b
>>> > -- A verifier
>>> > WHERE CAST(a.dt_jour AS TIMESTAMP) >=
>>> > CAST(min(UNIX_TIMESTAMP(b.dt_jour)) AS BIGINT)
>>> > AND CAST(a.dt_jour AS TIMESTAMP) < CAST(min(b.dt_jour)+1 AS
>>> TIMESTAMP)
>>> > AND a.co_societe = '${hiveconf:in_co_societe}'
>>> > AND ('${hiveconf:flg_reprise_entite}' = 0 OR
>>> a.type_entite<>'MAG')
>>> > GROUP BY
>>> > a.type_entite,
>>> > a.code_entite,
>>> > a.type_rgrp_produits,
>>> > a.co_rgrp_produits;
>>> >
>>> > And, when i try to launch this, i get this error :
>>> >
>>> > FAILED: SemanticException [Error 10128]: Line 20:62 Not yet supported
>>> place
>>> > for UDAF 'min'
>>> >
>>> > Obviously, there is a problem with min() function.
>>> >
>>> > How can i pass through this error?
>>> >
>>> > Thanks for your help
>>> >
>>> >
>>> >
>>> > CONFIDENTIALITY NOTICE
>>> > NOTICE: This message is intended for the use of the individual or
>>> entity to
>>> > which it is addressed and may contain in
Re: Problème with min function in HiveQL
Ok, thanks for this solution.
Unfortunately, i have rewrited my script like this :
INSERT INTO TABLE default.THM_CA_RGRP_PRODUITS_SEM
SELECT
'${hiveconf:in_co_societe}' as co_societe,
'${hiveconf:in_co_an_semaine}'as co_an_semaine,
a.type_entite as type_entite,
a.code_entite as code_entite,
a.type_rgrp_produits as type_rgrp_produits,
a.co_rgrp_produitsas co_rgrp_produits,
SUM(a.MT_CA_NET_TTC) as MT_CA_NET_TTC,
SUM(a.MT_OBJ_CA_NET_TTC) as MT_OBJ_CA_NET_TTC,
SUM(a.NB_CLIENTS) as NB_CLIENTS,
SUM(a.MT_CA_NET_TTC_COMP) as MT_CA_NET_TTC_COMP,
SUM(a.MT_OBJ_CA_NET_TTC_COMP) as MT_OBJ_CA_NET_TTC_COMP,
SUM(a.NB_CLIENTS_COMP)as NB_CLIENTS_COMP
from
default.THM_CA_RGRP_PRODUITS_JOUR a
JOIN default.CALENDRIER b
-- A verifier
WHERE a.co_societe = '${hiveconf:in_co_societe}'
AND ('${hiveconf:flg_reprise_entite}' = 0 OR a.type_entite<>'MAG')
GROUP BY
a.type_entite,
a.code_entite,
a.type_rgrp_produits,
a.co_rgrp_produits;
HAVING
a.dt_jour >= MIN(b.dt_jour)
AND a.dt_jour < MIN(b.dt_jour)+1
And i have a new error :
FAILED: ParseException line 2:6 cannot recognize input near 'HAVING' 'a' '.'
My Hive version is 0.11 and HAVING function have been implemented in 0.7 so
it would work fine.
2013/8/29 Stephen Sprague
> indeed. you nailed it.
>
>
> On Thu, Aug 29, 2013 at 11:53 AM, John Meagher wrote:
>
>> Aggregate functions need to go in a HAVING clause instead of the WHERE
>> clause. WHERE clauses are applied prior to aggregation, HAVING is
>> applied post aggregation.
>>
>> select ...
>> from ...
>> where some row level filter
>> group by ...
>> having some aggregate level filter
>>
>>
>> On Thu, Aug 29, 2013 at 2:49 PM, Jason Dere
>> wrote:
>> > Looks like the issue is the use of min() within the WHERE clause - the
>> place
>> > where the exception is being thrown has the following comment:
>> > // UDAF in filter condition, group-by caluse, param of funtion,
>> etc.
>> >
>> >
>> > On Aug 29, 2013, at 3:01 AM, Jérôme Verdier > >
>> > wrote:
>> >
>> > Hi everybody,
>> >
>> > I am coding some HiveQL script to do some calculations.
>> >
>> > I have a problem with the min() function.
>> >
>> > My hive script is below :
>> >
>> > INSERT INTO TABLE default.THM_CA_RGRP_PRODUITS_SEM
>> >
>> > SELECT
>> > '${hiveconf:in_co_societe}' as co_societe,
>> > '${hiveconf:in_co_an_semaine}'as co_an_semaine,
>> > a.type_entite as type_entite,
>> > a.code_entite as code_entite,
>> > a.type_rgrp_produits as type_rgrp_produits,
>> > a.co_rgrp_produitsas co_rgrp_produits,
>> > SUM(a.MT_CA_NET_TTC) as MT_CA_NET_TTC,
>> > SUM(a.MT_OBJ_CA_NET_TTC) as MT_OBJ_CA_NET_TTC,
>> > SUM(a.NB_CLIENTS) as NB_CLIENTS,
>> > SUM(a.MT_CA_NET_TTC_COMP) as MT_CA_NET_TTC_COMP,
>> > SUM(a.MT_OBJ_CA_NET_TTC_COMP) as
>> MT_OBJ_CA_NET_TTC_COMP,
>> > SUM(a.NB_CLIENTS_COMP)as NB_CLIENTS_COMP
>> > from
>> > default.THM_CA_RGRP_PRODUITS_JOUR a
>> > JOIN default.CALENDRIER b
>> > -- A verifier
>> > WHERE CAST(a.dt_jour AS TIMESTAMP) >=
>> > CAST(min(UNIX_TIMESTAMP(b.dt_jour)) AS BIGINT)
>> > AND CAST(a.dt_jour AS TIMESTAMP) < CAST(min(b.dt_jour)+1 AS
>> TIMESTAMP)
>> > AND a.co_societe = '${hiveconf:in_co_societe}'
>> > AND ('${hiveconf:flg_reprise_entite}' = 0 OR a.type_entite<>'MAG')
>> > GROUP BY
>> > a.type_entite,
>> > a.code_entite,
>> > a.type_rgrp_produits,
>> > a.co_rgrp_produits;
>> >
>> > And, when i try to launch this, i get this error :
>> >
>> > FAILED: SemanticException [Error 10128]: Line 20:62 Not yet supported
>> place
>> > for UDAF 'min'
>> >
>> > Obviously, there is a problem with min() function.
>> >
>> > How can i pass through this error?
>> >
>> > Thanks for your help
>> >
>> >
>> >
>> > CONFIDENTIALITY NOTICE
>> > NOTICE: This message is intended for the use of the individual or
>> entity to
>> > which it is addressed and may contain information that is confidential,
>> > privileged and exempt from disclosure under applicable law. If the
>> reader of
>> > this message is not the intended recipient, you are hereby notified
>> that any
>> > printing, copying, dissemination, distribution, disclosure or
>> forwarding of
>> > this communication is strictly prohibited. If you have received
Re: Problème with min function in HiveQL
indeed. you nailed it.
On Thu, Aug 29, 2013 at 11:53 AM, John Meagher wrote:
> Aggregate functions need to go in a HAVING clause instead of the WHERE
> clause. WHERE clauses are applied prior to aggregation, HAVING is
> applied post aggregation.
>
> select ...
> from ...
> where some row level filter
> group by ...
> having some aggregate level filter
>
>
> On Thu, Aug 29, 2013 at 2:49 PM, Jason Dere wrote:
> > Looks like the issue is the use of min() within the WHERE clause - the
> place
> > where the exception is being thrown has the following comment:
> > // UDAF in filter condition, group-by caluse, param of funtion,
> etc.
> >
> >
> > On Aug 29, 2013, at 3:01 AM, Jérôme Verdier
> > wrote:
> >
> > Hi everybody,
> >
> > I am coding some HiveQL script to do some calculations.
> >
> > I have a problem with the min() function.
> >
> > My hive script is below :
> >
> > INSERT INTO TABLE default.THM_CA_RGRP_PRODUITS_SEM
> >
> > SELECT
> > '${hiveconf:in_co_societe}' as co_societe,
> > '${hiveconf:in_co_an_semaine}'as co_an_semaine,
> > a.type_entite as type_entite,
> > a.code_entite as code_entite,
> > a.type_rgrp_produits as type_rgrp_produits,
> > a.co_rgrp_produitsas co_rgrp_produits,
> > SUM(a.MT_CA_NET_TTC) as MT_CA_NET_TTC,
> > SUM(a.MT_OBJ_CA_NET_TTC) as MT_OBJ_CA_NET_TTC,
> > SUM(a.NB_CLIENTS) as NB_CLIENTS,
> > SUM(a.MT_CA_NET_TTC_COMP) as MT_CA_NET_TTC_COMP,
> > SUM(a.MT_OBJ_CA_NET_TTC_COMP) as
> MT_OBJ_CA_NET_TTC_COMP,
> > SUM(a.NB_CLIENTS_COMP)as NB_CLIENTS_COMP
> > from
> > default.THM_CA_RGRP_PRODUITS_JOUR a
> > JOIN default.CALENDRIER b
> > -- A verifier
> > WHERE CAST(a.dt_jour AS TIMESTAMP) >=
> > CAST(min(UNIX_TIMESTAMP(b.dt_jour)) AS BIGINT)
> > AND CAST(a.dt_jour AS TIMESTAMP) < CAST(min(b.dt_jour)+1 AS
> TIMESTAMP)
> > AND a.co_societe = '${hiveconf:in_co_societe}'
> > AND ('${hiveconf:flg_reprise_entite}' = 0 OR a.type_entite<>'MAG')
> > GROUP BY
> > a.type_entite,
> > a.code_entite,
> > a.type_rgrp_produits,
> > a.co_rgrp_produits;
> >
> > And, when i try to launch this, i get this error :
> >
> > FAILED: SemanticException [Error 10128]: Line 20:62 Not yet supported
> place
> > for UDAF 'min'
> >
> > Obviously, there is a problem with min() function.
> >
> > How can i pass through this error?
> >
> > Thanks for your help
> >
> >
> >
> > CONFIDENTIALITY NOTICE
> > NOTICE: This message is intended for the use of the individual or entity
> to
> > which it is addressed and may contain information that is confidential,
> > privileged and exempt from disclosure under applicable law. If the
> reader of
> > this message is not the intended recipient, you are hereby notified that
> any
> > printing, copying, dissemination, distribution, disclosure or forwarding
> of
> > this communication is strictly prohibited. If you have received this
> > communication in error, please contact the sender immediately and delete
> it
> > from your system. Thank You.
>
Re: Problème with min function in HiveQL
Aggregate functions need to go in a HAVING clause instead of the WHERE
clause. WHERE clauses are applied prior to aggregation, HAVING is
applied post aggregation.
select ...
from ...
where some row level filter
group by ...
having some aggregate level filter
On Thu, Aug 29, 2013 at 2:49 PM, Jason Dere wrote:
> Looks like the issue is the use of min() within the WHERE clause - the place
> where the exception is being thrown has the following comment:
> // UDAF in filter condition, group-by caluse, param of funtion, etc.
>
>
> On Aug 29, 2013, at 3:01 AM, Jérôme Verdier
> wrote:
>
> Hi everybody,
>
> I am coding some HiveQL script to do some calculations.
>
> I have a problem with the min() function.
>
> My hive script is below :
>
> INSERT INTO TABLE default.THM_CA_RGRP_PRODUITS_SEM
>
> SELECT
> '${hiveconf:in_co_societe}' as co_societe,
> '${hiveconf:in_co_an_semaine}'as co_an_semaine,
> a.type_entite as type_entite,
> a.code_entite as code_entite,
> a.type_rgrp_produits as type_rgrp_produits,
> a.co_rgrp_produitsas co_rgrp_produits,
> SUM(a.MT_CA_NET_TTC) as MT_CA_NET_TTC,
> SUM(a.MT_OBJ_CA_NET_TTC) as MT_OBJ_CA_NET_TTC,
> SUM(a.NB_CLIENTS) as NB_CLIENTS,
> SUM(a.MT_CA_NET_TTC_COMP) as MT_CA_NET_TTC_COMP,
> SUM(a.MT_OBJ_CA_NET_TTC_COMP) as MT_OBJ_CA_NET_TTC_COMP,
> SUM(a.NB_CLIENTS_COMP)as NB_CLIENTS_COMP
> from
> default.THM_CA_RGRP_PRODUITS_JOUR a
> JOIN default.CALENDRIER b
> -- A verifier
> WHERE CAST(a.dt_jour AS TIMESTAMP) >=
> CAST(min(UNIX_TIMESTAMP(b.dt_jour)) AS BIGINT)
> AND CAST(a.dt_jour AS TIMESTAMP) < CAST(min(b.dt_jour)+1 AS TIMESTAMP)
> AND a.co_societe = '${hiveconf:in_co_societe}'
> AND ('${hiveconf:flg_reprise_entite}' = 0 OR a.type_entite<>'MAG')
> GROUP BY
> a.type_entite,
> a.code_entite,
> a.type_rgrp_produits,
> a.co_rgrp_produits;
>
> And, when i try to launch this, i get this error :
>
> FAILED: SemanticException [Error 10128]: Line 20:62 Not yet supported place
> for UDAF 'min'
>
> Obviously, there is a problem with min() function.
>
> How can i pass through this error?
>
> Thanks for your help
>
>
>
> CONFIDENTIALITY NOTICE
> NOTICE: This message is intended for the use of the individual or entity to
> which it is addressed and may contain information that is confidential,
> privileged and exempt from disclosure under applicable law. If the reader of
> this message is not the intended recipient, you are hereby notified that any
> printing, copying, dissemination, distribution, disclosure or forwarding of
> this communication is strictly prohibited. If you have received this
> communication in error, please contact the sender immediately and delete it
> from your system. Thank You.
Re: Problème with min function in HiveQL
Looks like the issue is the use of min() within the WHERE clause - the place
where the exception is being thrown has the following comment:
// UDAF in filter condition, group-by caluse, param of funtion, etc.
On Aug 29, 2013, at 3:01 AM, Jérôme Verdier wrote:
> Hi everybody,
>
> I am coding some HiveQL script to do some calculations.
>
> I have a problem with the min() function.
>
> My hive script is below :
>
> INSERT INTO TABLE default.THM_CA_RGRP_PRODUITS_SEM
>
> SELECT
> '${hiveconf:in_co_societe}' as co_societe,
> '${hiveconf:in_co_an_semaine}'as co_an_semaine,
> a.type_entite as type_entite,
> a.code_entite as code_entite,
> a.type_rgrp_produits as type_rgrp_produits,
> a.co_rgrp_produitsas co_rgrp_produits,
> SUM(a.MT_CA_NET_TTC) as MT_CA_NET_TTC,
> SUM(a.MT_OBJ_CA_NET_TTC) as MT_OBJ_CA_NET_TTC,
> SUM(a.NB_CLIENTS) as NB_CLIENTS,
> SUM(a.MT_CA_NET_TTC_COMP) as MT_CA_NET_TTC_COMP,
> SUM(a.MT_OBJ_CA_NET_TTC_COMP) as MT_OBJ_CA_NET_TTC_COMP,
> SUM(a.NB_CLIENTS_COMP)as NB_CLIENTS_COMP
> from
> default.THM_CA_RGRP_PRODUITS_JOUR a
> JOIN default.CALENDRIER b
> -- A verifier
> WHERE CAST(a.dt_jour AS TIMESTAMP) >=
> CAST(min(UNIX_TIMESTAMP(b.dt_jour)) AS BIGINT)
> AND CAST(a.dt_jour AS TIMESTAMP) < CAST(min(b.dt_jour)+1 AS TIMESTAMP)
> AND a.co_societe = '${hiveconf:in_co_societe}'
> AND ('${hiveconf:flg_reprise_entite}' = 0 OR a.type_entite<>'MAG')
> GROUP BY
> a.type_entite,
> a.code_entite,
> a.type_rgrp_produits,
> a.co_rgrp_produits;
>
> And, when i try to launch this, i get this error :
>
> FAILED: SemanticException [Error 10128]: Line 20:62 Not yet supported place
> for UDAF 'min'
>
> Obviously, there is a problem with min() function.
>
> How can i pass through this error?
>
> Thanks for your help
>
--
CONFIDENTIALITY NOTICE
NOTICE: This message is intended for the use of the individual or entity to
which it is addressed and may contain information that is confidential,
privileged and exempt from disclosure under applicable law. If the reader
of this message is not the intended recipient, you are hereby notified that
any printing, copying, dissemination, distribution, disclosure or
forwarding of this communication is strictly prohibited. If you have
received this communication in error, please contact the sender immediately
and delete it from your system. Thank You.
Re: Problème with min function in HiveQL
well. i would suggest you test whether or not min() works on timestamp
datatype. it seems like something one should rule out first before going
down the rabbit hole further. My opinion only!
On Thu, Aug 29, 2013 at 9:28 AM, Jérôme Verdier
wrote:
> **
> Hi stephen,
>
> Thanks for your reply.
>
> Effectively, dt_jour is timestamp format.
>
> What is the problem with this?
> --
> *From: * Stephen Sprague
> *Date: *Thu, 29 Aug 2013 09:24:27 -0700
> *To: *user@hive.apache.org
> *ReplyTo: * user@hive.apache.org
> *Subject: *Re: Problème with min function in HiveQL
>
> the min function at column 62 is on on the column b.dt_jour. what
> datatype is that?
>
> if its of type 'timestamp' that might explain it.
>
>
> On Thu, Aug 29, 2013 at 3:01 AM, Jérôme Verdier <
> verdier.jerom...@gmail.com> wrote:
>
>> Hi everybody,
>>
>> I am coding some HiveQL script to do some calculations.
>>
>> I have a problem with the min() function.
>>
>> My hive script is below :
>>
>> INSERT INTO TABLE default.THM_CA_RGRP_PRODUITS_SEM
>>
>> SELECT
>> '${hiveconf:in_co_societe}' as co_societe,
>> '${hiveconf:in_co_an_semaine}'as co_an_semaine,
>> a.type_entite as type_entite,
>> a.code_entite as code_entite,
>> a.type_rgrp_produits as type_rgrp_produits,
>> a.co_rgrp_produitsas co_rgrp_produits,
>> SUM(a.MT_CA_NET_TTC) as MT_CA_NET_TTC,
>> SUM(a.MT_OBJ_CA_NET_TTC) as MT_OBJ_CA_NET_TTC,
>> SUM(a.NB_CLIENTS) as NB_CLIENTS,
>> SUM(a.MT_CA_NET_TTC_COMP) as MT_CA_NET_TTC_COMP,
>> SUM(a.MT_OBJ_CA_NET_TTC_COMP) as
>> MT_OBJ_CA_NET_TTC_COMP,
>> SUM(a.NB_CLIENTS_COMP)as NB_CLIENTS_COMP
>> from
>> default.THM_CA_RGRP_PRODUITS_JOUR a
>> JOIN default.CALENDRIER b
>> -- A verifier
>> WHERE CAST(a.dt_jour AS TIMESTAMP) >=
>> CAST(min(UNIX_TIMESTAMP(b.dt_jour)) AS BIGINT)
>> AND CAST(a.dt_jour AS TIMESTAMP) < CAST(min(b.dt_jour)+1 AS
>> TIMESTAMP)
>> AND a.co_societe = '${hiveconf:in_co_societe}'
>> AND ('${hiveconf:flg_reprise_entite}' = 0 OR a.type_entite<>'MAG')
>> GROUP BY
>> a.type_entite,
>> a.code_entite,
>> a.type_rgrp_produits,
>> a.co_rgrp_produits;
>>
>> And, when i try to launch this, i get this error :
>>
>> FAILED: SemanticException [Error 10128]: Line 20:62 Not yet supported
>> place for UDAF 'min'
>>
>> Obviously, there is a problem with min() function.
>>
>> How can i pass through this error?
>>
>> Thanks for your help
>>
>>
>
Re: Problème with min function in HiveQL
Hi stephen,
Thanks for your reply.
Effectively, dt_jour is timestamp format.
What is the problem with this?
-Original Message-
From: Stephen Sprague
Date: Thu, 29 Aug 2013 09:24:27
To: user@hive.apache.org
Reply-To: user@hive.apache.org
Subject: Re: Problème with min function in HiveQL
the min function at column 62 is on on the column b.dt_jour. what datatype
is that?
if its of type 'timestamp' that might explain it.
On Thu, Aug 29, 2013 at 3:01 AM, Jérôme Verdier
wrote:
> Hi everybody,
>
> I am coding some HiveQL script to do some calculations.
>
> I have a problem with the min() function.
>
> My hive script is below :
>
> INSERT INTO TABLE default.THM_CA_RGRP_PRODUITS_SEM
>
> SELECT
> '${hiveconf:in_co_societe}' as co_societe,
> '${hiveconf:in_co_an_semaine}'as co_an_semaine,
> a.type_entite as type_entite,
> a.code_entite as code_entite,
> a.type_rgrp_produits as type_rgrp_produits,
> a.co_rgrp_produitsas co_rgrp_produits,
> SUM(a.MT_CA_NET_TTC) as MT_CA_NET_TTC,
> SUM(a.MT_OBJ_CA_NET_TTC) as MT_OBJ_CA_NET_TTC,
> SUM(a.NB_CLIENTS) as NB_CLIENTS,
> SUM(a.MT_CA_NET_TTC_COMP) as MT_CA_NET_TTC_COMP,
> SUM(a.MT_OBJ_CA_NET_TTC_COMP) as
> MT_OBJ_CA_NET_TTC_COMP,
> SUM(a.NB_CLIENTS_COMP)as NB_CLIENTS_COMP
> from
> default.THM_CA_RGRP_PRODUITS_JOUR a
> JOIN default.CALENDRIER b
> -- A verifier
> WHERE CAST(a.dt_jour AS TIMESTAMP) >=
> CAST(min(UNIX_TIMESTAMP(b.dt_jour)) AS BIGINT)
> AND CAST(a.dt_jour AS TIMESTAMP) < CAST(min(b.dt_jour)+1 AS
> TIMESTAMP)
> AND a.co_societe = '${hiveconf:in_co_societe}'
> AND ('${hiveconf:flg_reprise_entite}' = 0 OR a.type_entite<>'MAG')
> GROUP BY
> a.type_entite,
> a.code_entite,
> a.type_rgrp_produits,
> a.co_rgrp_produits;
>
> And, when i try to launch this, i get this error :
>
> FAILED: SemanticException [Error 10128]: Line 20:62 Not yet supported
> place for UDAF 'min'
>
> Obviously, there is a problem with min() function.
>
> How can i pass through this error?
>
> Thanks for your help
>
>
Re: Problème with min function in HiveQL
the min function at column 62 is on on the column b.dt_jour. what datatype
is that?
if its of type 'timestamp' that might explain it.
On Thu, Aug 29, 2013 at 3:01 AM, Jérôme Verdier
wrote:
> Hi everybody,
>
> I am coding some HiveQL script to do some calculations.
>
> I have a problem with the min() function.
>
> My hive script is below :
>
> INSERT INTO TABLE default.THM_CA_RGRP_PRODUITS_SEM
>
> SELECT
> '${hiveconf:in_co_societe}' as co_societe,
> '${hiveconf:in_co_an_semaine}'as co_an_semaine,
> a.type_entite as type_entite,
> a.code_entite as code_entite,
> a.type_rgrp_produits as type_rgrp_produits,
> a.co_rgrp_produitsas co_rgrp_produits,
> SUM(a.MT_CA_NET_TTC) as MT_CA_NET_TTC,
> SUM(a.MT_OBJ_CA_NET_TTC) as MT_OBJ_CA_NET_TTC,
> SUM(a.NB_CLIENTS) as NB_CLIENTS,
> SUM(a.MT_CA_NET_TTC_COMP) as MT_CA_NET_TTC_COMP,
> SUM(a.MT_OBJ_CA_NET_TTC_COMP) as
> MT_OBJ_CA_NET_TTC_COMP,
> SUM(a.NB_CLIENTS_COMP)as NB_CLIENTS_COMP
> from
> default.THM_CA_RGRP_PRODUITS_JOUR a
> JOIN default.CALENDRIER b
> -- A verifier
> WHERE CAST(a.dt_jour AS TIMESTAMP) >=
> CAST(min(UNIX_TIMESTAMP(b.dt_jour)) AS BIGINT)
> AND CAST(a.dt_jour AS TIMESTAMP) < CAST(min(b.dt_jour)+1 AS
> TIMESTAMP)
> AND a.co_societe = '${hiveconf:in_co_societe}'
> AND ('${hiveconf:flg_reprise_entite}' = 0 OR a.type_entite<>'MAG')
> GROUP BY
> a.type_entite,
> a.code_entite,
> a.type_rgrp_produits,
> a.co_rgrp_produits;
>
> And, when i try to launch this, i get this error :
>
> FAILED: SemanticException [Error 10128]: Line 20:62 Not yet supported
> place for UDAF 'min'
>
> Obviously, there is a problem with min() function.
>
> How can i pass through this error?
>
> Thanks for your help
>
>
