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
Copy link to this message
-
Re: Problème with min function in HiveQL
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}'
> >       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.
>
+
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
+
Stephen Sprague 2013-08-30, 18:49