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

Switch to Threaded View
Hive >> mail # user >> Problème with min function in HiveQL


Copy link to this message
-
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_produits                        as 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 <[EMAIL PROTECTED]>

> indeed. you nailed it.
>
>
> On Thu, Aug 29, 2013 at 11:53 AM, John Meagher <[EMAIL PROTECTED]>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 <[EMAIL PROTECTED]>
>> 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 <[EMAIL PROTECTED]
>> >
>> > 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_produits                        as 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}'
*Jérôme VERDIER*
06.72.19.17.31
[EMAIL PROTECTED]