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

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


+
Jérôme Verdier 2013-08-29, 10:01
+
Stephen Sprague 2013-08-29, 16:24
+
Jérôme Verdier 2013-08-29, 16:28
+
Stephen Sprague 2013-08-29, 18:01
+
Jason Dere 2013-08-29, 18:49
+
John Meagher 2013-08-29, 18:53
+
Stephen Sprague 2013-08-29, 19:09
+
Jérôme Verdier 2013-08-30, 08:17
+
Stephen Sprague 2013-08-30, 11:50
+
Jérôme Verdier 2013-08-30, 12:50
+
Jérôme Verdier 2013-08-30, 15:16
Copy link to this message
-
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
<[EMAIL PROTECTED]>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 <[EMAIL PROTECTED]>
>
>> Thanks Stephen,
>>
>> Yes i realise that it was so a stupid question....Maybe i wasn't really
>> awaked this morning ;-)
>>
>> now it's working well.
>>
>> Thanks everyone.
>>
>>
>> 2013/8/30 Stephen Sprague <[EMAIL PROTECTED]>
>>
>>> 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 <
>>> [EMAIL PROTECTED]> 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_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
>>>>>