Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id DED5E106ED for ; Fri, 19 Jul 2013 07:51:07 +0000 (UTC) Received: (qmail 70059 invoked by uid 500); 19 Jul 2013 07:51:05 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 69513 invoked by uid 500); 19 Jul 2013 07:50:59 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 69500 invoked by uid 99); 19 Jul 2013 07:50:58 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 19 Jul 2013 07:50:58 +0000 X-ASF-Spam-Status: No, hits=1.7 required=5.0 tests=FREEMAIL_ENVFROM_END_DIGIT,HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of verdier.jerome66@gmail.com designates 209.85.219.43 as permitted sender) Received: from [209.85.219.43] (HELO mail-oa0-f43.google.com) (209.85.219.43) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 19 Jul 2013 07:50:51 +0000 Received: by mail-oa0-f43.google.com with SMTP id i7so5570831oag.16 for ; Fri, 19 Jul 2013 00:50:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=FY0qK8kA82ElOtFCb7UlvjWSTFXcEl3ulXm5W+PzDQQ=; b=FidAnDrtqxkhD4pgGJzvj/DBlmBVfSQSH/p0j6V+KVP9wl0j4//qHj7ZlNQXVb8zbV /QZKFfpXSqouR7Se+JP+p5HcxXOx/9ezOhcTHKy1mG/jcAcU+21JzpxNVZ6WlzWtgUoV XttZtyQRb1T220zWJNLL/olSv/0hSOPWEFI5QtqBEO9w8Tc9ZUexnLY0hfkh/oH9COlN gTG+wFJa8gUiRX7F5bwJGS0aCft6I4vtJZH4IlNsnQZ2SQPYfkZtsDhatucHHxdgpBIO BRXJ/PjLCpt/CJqnQuTR6ZAFW1/C4s0av5214l4cnhbZmt7O77efjnWK5SBfzb52QgjR gGdw== MIME-Version: 1.0 X-Received: by 10.182.66.77 with SMTP id d13mr11323612obt.32.1374220229685; Fri, 19 Jul 2013 00:50:29 -0700 (PDT) Received: by 10.76.76.197 with HTTP; Fri, 19 Jul 2013 00:50:29 -0700 (PDT) In-Reply-To: References: Date: Fri, 19 Jul 2013 09:50:29 +0200 Message-ID: Subject: Re: Use RANK OVER PARTITION function in Hive 0.11 From: =?ISO-8859-1?Q?J=E9r=F4me_Verdier?= To: user@hive.apache.org Content-Type: multipart/alternative; boundary=e89a8fb1eab6a2380804e1d894aa X-Virus-Checked: Checked by ClamAV on apache.org --e89a8fb1eab6a2380804e1d894aa Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi, The error is resolved. I have 3 RANK() function in my script. 2 are working with arguments, but one is working without arguments. I don't understand it, but the fact is it's working =3D) Thanks everyone for your help. 2013/7/18 J=E9r=F4me Verdier > Hi, > > Since we saw that we have to give arguments in RANK() function, i'm tryin= g > to translate this one (working on Oracle 10g) to be functionnally in Hive= : > > > RANK() OVER (PARTITION BY mag.co_magasin, dem.id_produit ORDER BY > pnvente.dt_debut_commercial DESC, > COALESCE(pnvente.id_produit,dem.id_produit) DESC) as rang > > i try this : > > RANK(pnvente.dt_debut_commercial, > COALESCE(pnvente.id_produit,dem.id_produit)) OVER (PARTITION BY > mag.co_magasin, dem.id_produit ORDER BY pnvente.dt_debut_commercial DESC, > COALESCE(pnvente.id_produit,dem.id_produit) DESC) as rang > > and this : > > > RANK(pnvente.dt_debut_commercial, pnvente.id_produit, dem.id_produit) > OVER (PARTITION BY mag.co_magasin, dem.id_produit ORDER BY > pnvente.dt_debut_commercial DESC, > COALESCE(pnvente.id_produit,dem.id_produit) DESC) as rang > > But Hive is giving me another error : > > FAILED: SemanticException Failed to breakup Windowing invocations into > Groups. At least 1 group must only depend on input columns. Also check fo= r > circular dependencies. > Underlying error: Ranking Functions can take no arguments > > i don't understand this error, in the first try, he said that he can't > work without arguments, and now, rank function is falling because of the > arguments. > > what is wrong now ? > > > > 2013/7/17 Richa Sharma > >> my bad ... in relational databases we generally do not give a column nam= e >> inside rank() ... but the one in (partition by .... order by..) is >> sufficient. >> >> But looks like that's not the case in Hive >> >> >> Jerome, >> >> Please look at the examples in link below. See if you are able to make i= t >> work >> >> >> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Windowin= gAndAnalytics#LanguageManualWindowingAndAnalytics-PARTITIONBYwithpartitioni= ng%2CORDERBY%2Candwindowspecification >> >> >> >> Cant help you beyond this as i don't have Hive 0.11 :-( >> >> >> Richa >> >> >> On Wed, Jul 17, 2013 at 3:08 PM, J=E9r=F4me Verdier < >> verdier.jerome66@gmail.com> wrote: >> >>> Hi Richa, >>> >>> I have tried one query, with what i've understand of Vijay's tips. >>> >>> SELECT code_entite, RANK(mag.me_vente_ht) OVER (PARTITION BY >>> mag.co_societe ORDER BY mag.me_vente_ht) AS rank FROM >>> default.thm_renta_rgrp_produits_n_1 mag; >>> >>> This query is working, it gives me results. >>> >>> You say that maybe i'm hitting the same bug of JIRA HIVE-4663, but quer= y >>> is also failling when i put analytical columns in... >>> >>> >>> 2013/7/17 Richa Sharma >>> >>>> Vijay >>>> >>>> Jerome has already passed column -> mag.co_societe for rank. >>>> >>>> syntax -> RANK() OVER (PARTITION BY mag.co_societe ORDER BY >>>> mag.me_vente_ht) >>>> This will generate a rank for column mag.co_societe based on column >>>> value me_vente_ht >>>> >>>> Jerome, >>>> >>>> Its possible you are also hitting the same bug as I mentioned in my >>>> email before. >>>> >>>> >>>> Richa >>>> >>>> >>>> On Wed, Jul 17, 2013 at 2:31 PM, Vijay wrote: >>>> >>>>> As the error message states: "One ore more arguments are expected," >>>>> you have to pass a column to the rank function. >>>>> >>>>> >>>>> On Wed, Jul 17, 2013 at 1:12 AM, J=E9r=F4me Verdier < >>>>> verdier.jerome66@gmail.com> wrote: >>>>> >>>>>> Hi Richa, >>>>>> >>>>>> I have tried a simple query without joins, etc.... >>>>>> >>>>>> SELECT RANK() OVER (PARTITION BY mag.co_societe ORDER BY >>>>>> mag.me_vente_ht),mag.co_societe, mag.me_vente_ht FROM >>>>>> default.thm_renta_rgrp_produits_n_1 mag; >>>>>> >>>>>> Unfortunately, the error is the same like previously. >>>>>> >>>>>> Error: Query returned non-zero code: 40000, cause: FAILED: >>>>>> SemanticException Failed to breakup Windowing invocations into Group= s. At >>>>>> least 1 group must only depend on input columns. Also check for circ= ular >>>>>> dependencies. >>>>>> >>>>>> Underlying error: >>>>>> org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException: One or more >>>>>> arguments are expected. >>>>>> SQLState: 42000 >>>>>> ErrorCode: 40000 >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> 2013/7/17 Richa Sharma >>>>>> >>>>>>> Jerome >>>>>>> >>>>>>> I would recommend that you try Rank function with columns from just >>>>>>> one table first. >>>>>>> Once it is established that rank is working fine then add all the >>>>>>> joins. >>>>>>> >>>>>>> I am still on Hive 0.10 so cannot test it myself. >>>>>>> However, I can find a similar issue on following link - so its >>>>>>> possible you are facing issues due to this reported bug. >>>>>>> >>>>>>> https://issues.apache.org/jira/browse/HIVE-4663 >>>>>>> >>>>>>> >>>>>>> Richa >>>>>>> >>>>>>> >>>>>>> On Tue, Jul 16, 2013 at 6:41 PM, J=E9r=F4me Verdier < >>>>>>> verdier.jerome66@gmail.com> wrote: >>>>>>> >>>>>>>> You can see my query below : >>>>>>>> >>>>>>>> SELECT >>>>>>>> mag.co_magasin, >>>>>>>> dem.id_produit as >>>>>>>> id_produit_orig, >>>>>>>> pnvente.dt_debut_commercial as >>>>>>>> dt_debut_commercial, >>>>>>>> COALESCE(pnvente.id_produit,dem.id_produit) as >>>>>>>> id_produit, >>>>>>>> RANK() OVER (PARTITION BY mag.co_magasin, >>>>>>>> dem.id_produit ORDER BY pnvente.dt_debut_commercial DESC, >>>>>>>> COALESCE(pnvente.id_produit,dem.id_produit) DESC) as rang >>>>>>>> >>>>>>>> FROM default.demarque_mag_jour dem >>>>>>>> >>>>>>>> LEFT OUTER JOIN default.produit_norm pn >>>>>>>> ON pn.co_societe =3D dem.co_societe >>>>>>>> AND pn.id_produit =3D dem.id_produit >>>>>>>> LEFT OUTER JOIN default.produit_norm pnvente >>>>>>>> ON pnvente.co_societe =3D pn.co_societe >>>>>>>> AND pnvente.co_produit_rfu =3D pn.co_produit_lip >>>>>>>> INNER JOIN default.kpi_magasin mag >>>>>>>> ON mag.id_magasin =3D dem.id_magasin >>>>>>>> >>>>>>>> >>>>>>>> GROUP BY >>>>>>>> mag.co_magasin, >>>>>>>> dem.id_produit, >>>>>>>> pnvente.dt_debut_commercial, >>>>>>>> COALESCE(pnvente.id_produit,dem.id_produit); >>>>>>>> >>>>>>>> >>>>>>>> 2013/7/16 Richa Sharma >>>>>>>> >>>>>>>>> Can you share query with just RANK(). >>>>>>>>> >>>>>>>>> Richa >>>>>>>>> >>>>>>>>> >>>>>>>>> On Tue, Jul 16, 2013 at 6:08 PM, J=E9r=F4me Verdier < >>>>>>>>> verdier.jerome66@gmail.com> wrote: >>>>>>>>> >>>>>>>>>> Hi Richa, >>>>>>>>>> >>>>>>>>>> I tried to execute the rank function alone, but the result is th= e >>>>>>>>>> same >>>>>>>>>> >>>>>>>>>> Thanks >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> 2013/7/16 Richa Sharma >>>>>>>>>> >>>>>>>>>>> Hi Jerome >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> I think the problem is you are trying to use MIN, SUM and RANK >>>>>>>>>>> function in a single query. >>>>>>>>>>> >>>>>>>>>>> Try to get the rank first in a query and on top of it apply >>>>>>>>>>> these aggregate functions >>>>>>>>>>> >>>>>>>>>>> Richa >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> On Tue, Jul 16, 2013 at 2:15 PM, J=E9r=F4me Verdier < >>>>>>>>>>> verdier.jerome66@gmail.com> wrote: >>>>>>>>>>> >>>>>>>>>>>> Hi, >>>>>>>>>>>> >>>>>>>>>>>> I have a problem while using RANK OVER PARTITION function with >>>>>>>>>>>> Hive. >>>>>>>>>>>> >>>>>>>>>>>> Hive is in version 0.11 and, as we can see here : >>>>>>>>>>>> https://cwiki.apache.org/Hive/languagemanual-windowingandanaly= tics.html, >>>>>>>>>>>> we can now use these functions in Hive. >>>>>>>>>>>> >>>>>>>>>>>> But, when i use it, i encountered this error : >>>>>>>>>>>> >>>>>>>>>>>> FAILED: SemanticException Failed to breakup Windowing >>>>>>>>>>>> invocations into Groups. At least 1 group must only depend on = input >>>>>>>>>>>> columns. Also check for circular dependencies. >>>>>>>>>>>> Underlying error: >>>>>>>>>>>> org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException: One o= r more >>>>>>>>>>>> arguments are expected. >>>>>>>>>>>> >>>>>>>>>>>> Here is my script : >>>>>>>>>>>> >>>>>>>>>>>> SELECT >>>>>>>>>>>> mag.co_magasin, >>>>>>>>>>>> dem.id_produit as >>>>>>>>>>>> id_produit_orig, >>>>>>>>>>>> pnvente.dt_debut_commercial as >>>>>>>>>>>> dt_debut_commercial, >>>>>>>>>>>> COALESCE(pnvente.id_produit,dem.id_produit) as >>>>>>>>>>>> id_produit, >>>>>>>>>>>> min( >>>>>>>>>>>> CASE WHEN dem.co_validation IS NULL THEN 0 ELSE = 1 >>>>>>>>>>>> END >>>>>>>>>>>> ) as >>>>>>>>>>>> flg_demarque_valide, >>>>>>>>>>>> sum(CASE WHEN dem.co_validation IS NULL THEN 0 ELS= E >>>>>>>>>>>> CAST(dem.mt_revient_ope AS INT) END) >>>>>>>>>>>> as >>>>>>>>>>>> me_dem_con_prx_cs, >>>>>>>>>>>> 0 as >>>>>>>>>>>> me_dem_inc_prx_cs, >>>>>>>>>>>> 0 as >>>>>>>>>>>> me_dem_prov_stk_cs, >>>>>>>>>>>> sum(CASE WHEN dem.co_validation IS NULL THEN 0 ELS= E >>>>>>>>>>>> CAST(dem.qt_demarque AS INT) END) >>>>>>>>>>>> as >>>>>>>>>>>> qt_dem_con, >>>>>>>>>>>> 0 as >>>>>>>>>>>> qt_dem_inc, >>>>>>>>>>>> 0 as >>>>>>>>>>>> qt_dem_prov_stk, -- !!!!!!!! VIRGULE >>>>>>>>>>>> RANK() OVER (PARTITION BY mag.co_magasin, >>>>>>>>>>>> dem.id_produit ORDER BY pnvente.dt_debut_commercial DESC, >>>>>>>>>>>> COALESCE(pnvente.id_produit,dem.id_produit) DESC) as rang >>>>>>>>>>>> from default.calendrier cal >>>>>>>>>>>> INNER JOIN default.demarque_mag_jour dem >>>>>>>>>>>> ON CASE WHEN dem.co_societe =3D 1 THEN 1 ELSE 2 END= =3D >>>>>>>>>>>> '${hiveconf:in_co_societe}' -- A modifier >>>>>>>>>>>> AND dem.dt_jour =3D cal.dt_jour >>>>>>>>>>>> LEFT OUTER JOIN default.produit_norm pn >>>>>>>>>>>> ON pn.co_societe =3D dem.co_societe >>>>>>>>>>>> AND pn.id_produit =3D dem.id_produit >>>>>>>>>>>> LEFT OUTER JOIN default.produit_norm pnvente >>>>>>>>>>>> ON pnvente.co_societe =3D pn.co_societe >>>>>>>>>>>> AND pnvente.co_produit_rfu =3D pn.co_produit_lip >>>>>>>>>>>> AND pnvente.co_type_motif=3D'05' >>>>>>>>>>>> INNER JOIN default.kpi_magasin mag >>>>>>>>>>>> ON mag.co_societe =3D '${hiveconf:in_co_societe}' >>>>>>>>>>>> AND mag.id_magasin =3D dem.id_magasin >>>>>>>>>>>> WHERE cal.dt_jour =3D '${hiveconf:in_dt_jour}' >>>>>>>>>>>> AND NOT (dem.co_validation IS NULL AND cal.dt_jour > >>>>>>>>>>>> unix_timestamp()-3*60*60*24) -- A verifier >>>>>>>>>>>> -- JYP 4.4 >>>>>>>>>>>> AND dem.co_operation_magasin IN ('13','14','32') >>>>>>>>>>>> GROUP BY >>>>>>>>>>>> mag.co_magasin, >>>>>>>>>>>> dem.id_produit, >>>>>>>>>>>> pnvente.dt_debut_commercial, >>>>>>>>>>>> COALESCE(pnvente.id_produit,dem.id_produit) >>>>>>>>>>>> >>>>>>>>>>>> Thanks. >>>>>>>>>>>> >>>>>>>>>>>> -- >>>>>>>>>>>> *J=E9r=F4me VERDIER* >>>>>>>>>>>> 06.72.19.17.31 >>>>>>>>>>>> verdier.jerome66@gmail.com >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> -- >>>>>>>>>> *J=E9r=F4me VERDIER* >>>>>>>>>> 06.72.19.17.31 >>>>>>>>>> verdier.jerome66@gmail.com >>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> -- >>>>>>>> *J=E9r=F4me VERDIER* >>>>>>>> 06.72.19.17.31 >>>>>>>> verdier.jerome66@gmail.com >>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> *J=E9r=F4me VERDIER* >>>>>> 06.72.19.17.31 >>>>>> verdier.jerome66@gmail.com >>>>>> >>>>>> >>>>> >>>> >>> >>> >>> -- >>> *J=E9r=F4me VERDIER* >>> 06.72.19.17.31 >>> verdier.jerome66@gmail.com >>> >>> >> > > > -- > *J=E9r=F4me VERDIER* > 06.72.19.17.31 > verdier.jerome66@gmail.com > > --=20 *J=E9r=F4me VERDIER* 06.72.19.17.31 verdier.jerome66@gmail.com --e89a8fb1eab6a2380804e1d894aa Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Hi,

The error is res= olved.

I have 3 RANK() function in my script.

2 a= re working with arguments, but one is working without arguments.

I don't understand it, but the fact is it's working =3D)

Thanks everyone for your help.

<= br>
2013/7/18 J=E9r=F4me Verdier <verdi= er.jerome66@gmail.com>
Hi,

Since we saw that we have to give arguments in RANK() f= unction, i'm trying to translate this one (working on Oracle 10g) to be= functionnally in Hive :


RANK() OVER (PARTITION BY mag.co_magasin, dem.id_produit ORDER BY pnvente.d= t_debut_commercial DESC, COALESCE(pnvente.id_produit,dem.id_produit) DESC) = as rang

i try this :

RANK(pnvente.d= t_debut_commercial, COALESCE(pnvente.id_produit,dem.id_produit)) OVE= R (PARTITION BY mag.co_magasin, dem.id_produit ORDER BY pnvente.dt_debut_commercial=20 DESC, COALESCE(pnvente.id_produit,dem.id_produit) DESC) as rang
<= br>
and this :


RANK(pnvente.dt_debut_commercial, pnv= ente.id_produit, dem.id_produit) OVER (PARTITION BY mag.co_magasin, dem.id_produit ORDER BY pnvente.dt_debut_commercial=20 DESC, COALESCE(pnvente.id_produit,dem.id_produit) DESC) as rang
<= br>
But Hive is giving me another error :

FAILED: SemanticException F= ailed to breakup Windowing invocations into Groups. At least 1 group must o= nly depend on input columns. Also check for circular dependencies.
Underlying error: Ranking Functions can take no arguments

i don't understand this error, in the first try, he said that he can= 't work without arguments, and now, rank function is falling because of= the arguments.

what is wrong now ?


2013/7/17 Richa Sharma <mailtorichas= harma@gmail.com>
my bad ... in relational da= tabases we generally do not give a column name inside rank() ... but the on= e in (partition by .... order by..) is sufficient.

But looks like that's not the case in Hive


Jerome,

Please = look at the examples in link below. See if you are able to make it work



Cant help you beyond this as i don't= have Hive 0.11 :-(

=
Richa


On Wed, Jul 17, 2013 at 3:08 PM, J=E9r=F4me Verdier <<= a href=3D"mailto:verdier.jerome66@gmail.com" target=3D"_blank">verdier.jero= me66@gmail.com> wrote:
Hi Richa,

I have tried one que= ry, with what i've understand of=A0 Vijay's tips.

SELECT code_entite, RANK(mag.me_ven= te_ht) OVER (PARTITION BY mag.co_societe ORDER BY=A0 mag.me_vente_ht) AS ra= nk FROM default.thm_renta_rgrp_produits_n_1 mag;

This query is working, it gives me results.

You say = that maybe i'm hitting the same bug of JIRA HIVE-4663, but query is als= o failling when i put analytical columns in...


2013/7/17 Richa Sharma <mailt= orichasharma@gmail.com>
Vijay

Jerome has already passed column = ->=A0mag.co_societe for rank.=A0

= syntax -> RANK() OVER (PARTITION BY mag.co_societe ORDER BY=A0 mag.me_ve= nte_ht)=A0
This will generate a rank for column mag.co= _societe based on column value=A0me_vente_ht

Jerome,

Its possible you are a= lso hitting the same bug as I mentioned in my email before.


Richa


On Wed, Jul 17, 2013 at 2:31 PM, Vijay &= lt;techvd@gmail.com> wrote:
As the error message states: "One ore more arguments = are expected," you have to pass a column to the rank function.


On Wed, Jul 17, 2013 at 1:12 AM, J=E9r=F4me Verdier <<= a href=3D"mailto:verdier.jerome66@gmail.com" target=3D"_blank">verdier.jero= me66@gmail.com> wrote:
Hi Richa,

=
I have tried a simple query without joins, etc....

SELECT RANK() OVER (PARTITION BY mag= .co_societe ORDER BY=A0 mag.me_vente_ht),mag.co_societe, mag.me_vente_ht FR= OM default.thm_renta_rgrp_produits_n_1 mag;

Unfortunately, the error is the same like previously.

Error: Query returned non-zer= o code: 40000, cause: FAILED: SemanticException Failed to breakup Windowing= invocations into Groups. At least 1 group must only depend on input column= s. Also check for circular dependencies.

Underlying error: org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException: = One or more arguments are expected.
SQLState:=A0 42000
ErrorCod= e: 40000




2013/7/17 Richa Sharma <mailtorichasharma@gmail.com>
Jerome

I would recommend that you try R= ank function with columns from just one table first.
Once it is e= stablished that rank is working fine then add all the joins.

I am still on Hive 0.10 so cannot test it myself.=
However, I can find a similar issue on following link - so its p= ossible you are facing issues due to this reported bug.



Richa


On Tue, Jul 16, 2013 at 6:41 PM, J=E9r=F4me Verdier <verdier.jerome66@gmail.com> wrote:
You can see my query below = :

SELECT
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 mag.co_magasin,
=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0 dem.id_produit=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 as id_produit_orig,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 pnvente.dt_debut_commercial=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 as dt_debut_commercial,=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 COALESCE(pnvente.id_produit,dem.id_produ= it)=A0=A0=A0=A0 as id_produit,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 RANK() OVER (PARTITION BY mag.co_magasin, dem.id_produit ORDER BY pnven= te.dt_debut_commercial DESC, COALESCE(pnvente.id_produit,dem.id_produit) DE= SC) as rang
=A0=A0=A0=A0=A0=A0=A0=A0=A0
FROM default.demarque_mag_jour dem
=A0=A0=A0=A0=A0=A0=A0=A0=A0 LEFT OUTER JOIN default.produit_norm pn=A0=A0=A0=A0=A0=A0=A0=A0=A0 ON=A0 pn.co_societe =3D dem.co_societe
=A0= =A0=A0=A0=A0=A0=A0=A0=A0 AND pn.id_produit =3D dem.id_produit
=A0=A0=A0=A0=A0=A0=A0=A0=A0 LEFT OUTER JOIN default.produit_norm pnvente =A0=A0=A0=A0=A0=A0=A0=A0=A0 ON=A0 pnvente.co_societe =3D pn.co_societe
= =A0=A0=A0=A0=A0=A0=A0=A0=A0 AND pnvente.co_produit_rfu =3D pn.co_produit_li= p
=A0=A0=A0=A0=A0=A0=A0=A0=A0 INNER JOIN default.kpi_magasin mag=A0=A0=A0=A0=A0=A0=A0=A0=A0 ON mag.id_magasin =3D dem.id_magasin

=A0=A0=A0=A0=A0=A0=A0=A0=A0
GROUP BY
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 mag.co_magasin,
=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0 dem.id_produit,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 pnvente.dt_debut_commercial,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= COALESCE(pnvente.id_produit,dem.id_produit);


2013/7/16 Richa Sharma <mailtorichash= arma@gmail.com>
Can you share query with just RANK().

Richa


On Tue, Jul 16, 2013 at 6:08 PM, J=E9r=F4me Verdier <<= a href=3D"mailto:verdier.jerome66@gmail.com" target=3D"_blank">verdier.jero= me66@gmail.com> wrote:
Hi Richa,

=
I tried to execute the rank function alone, but the result is the sam= e

Thanks


2013/7/16 Richa Sharma <mailtorichasharma= @gmail.com>
Hi Jerome


I think the problem is you are trying to use MIN, SUM and = RANK function in a single query.

Try to get the rank first in a query and on top of it a= pply these aggregate functions

Richa


=


On Tue, Jul 16, 2013 at 2:15 PM, J=E9r=F4me Verdier &l= t;verdier.j= erome66@gmail.com> wrote:
Hi= ,

I have a problem while using RANK OVER PARTITION function wi= th Hive.

Hive is in version 0.11 and, as we can see here : https://cwiki.apache.org/Hive/languagemanual-windowingandanalyt= ics.html, we can now use these functions in Hive.

But, when i use it, i encountered this error :

FAILED: SemanticException Failed to = breakup Windowing invocations into Groups. At least 1 group must only depen= d on input columns. Also check for circular dependencies.
Underlying error: org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException: = One or more arguments are expected.

Here is my script :=

SELECT
=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0 mag.co_magasin,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 dem.id_produit=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 as= id_produit_orig,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 pnvente.dt_debut_com= mercial=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 as dt_d= ebut_commercial,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 COALESCE(pnvente.id_p= roduit,dem.id_produit)=A0=A0=A0=A0 as id_produit,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 min(
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0 CASE WHEN dem.co_validation IS NULL THEN 0 ELSE 1 END
=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0 )=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0 as flg_demarque_valide,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 sum(CASE WHEN dem.co_validation IS NULL THEN 0 ELSE CAST(dem.mt_revient= _ope AS INT) END)
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0 as me_dem_con_prx_cs,
=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0 0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 as me_dem_inc_prx_cs,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 as me_dem_prov_stk_c= s,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 sum(CASE WHEN dem.co_validation IS NULL T= HEN 0 ELSE CAST(dem.qt_demarque AS INT) END)
=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= as qt_dem_con,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 as qt_dem_inc,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0 as qt_dem_prov_stk, -- !!!!!!!! VIRGULE
=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0 RANK() OVER (PARTITION BY mag.co_magasin, dem.i= d_produit ORDER BY pnvente.dt_debut_commercial DESC, COALESCE(pnvente.id_pr= oduit,dem.id_produit) DESC) as rang
=A0=A0=A0=A0=A0=A0=A0=A0=A0 from default.calendrier cal
=A0=A0=A0=A0=A0= =A0=A0=A0=A0 INNER JOIN default.demarque_mag_jour dem
=A0=A0=A0=A0=A0=A0= =A0=A0=A0 ON=A0 CASE WHEN dem.co_societe =3D 1 THEN 1 ELSE 2 END =3D '$= {hiveconf:in_co_societe}' -- A modifier
=A0=A0=A0=A0=A0=A0=A0=A0=A0 = AND dem.dt_jour=A0=A0=A0 =3D cal.dt_jour
=A0=A0=A0=A0=A0=A0=A0=A0=A0 LEFT OUTER JOIN default.produit_norm pn
=A0= =A0=A0=A0=A0=A0=A0=A0=A0 ON=A0 pn.co_societe =3D dem.co_societe
=A0=A0= =A0=A0=A0=A0=A0=A0=A0 AND pn.id_produit =3D dem.id_produit
=A0=A0=A0=A0= =A0=A0=A0=A0=A0 LEFT OUTER JOIN default.produit_norm pnvente
=A0=A0=A0= =A0=A0=A0=A0=A0=A0 ON=A0 pnvente.co_societe =3D pn.co_societe
=A0=A0=A0=A0=A0=A0=A0=A0=A0 AND pnvente.co_produit_rfu =3D pn.co_produit_li= p
=A0=A0=A0=A0=A0=A0=A0=A0=A0 AND pnvente.co_type_motif=3D'05'=A0=A0=A0=A0=A0=A0=A0=A0=A0 INNER JOIN default.kpi_magasin mag
=A0=A0= =A0=A0=A0=A0=A0=A0=A0 ON=A0 mag.co_societe =3D '${hiveconf:in_co_societ= e}'
=A0=A0=A0=A0=A0=A0=A0=A0=A0 AND mag.id_magasin =3D dem.id_magasin
=A0=A0= =A0=A0=A0=A0=A0=A0=A0 WHERE cal.dt_jour =3D '${hiveconf:in_dt_jour}'= ;
=A0=A0=A0=A0=A0=A0=A0=A0=A0 AND NOT (dem.co_validation IS NULL AND cal= .dt_jour > unix_timestamp()-3*60*60*24) -- A verifier
=A0=A0=A0=A0=A0=A0=A0=A0=A0 -- JYP 4.4
=A0=A0=A0=A0=A0=A0=A0=A0=A0 AND d= em.co_operation_magasin IN ('13','14','32')
=A0= =A0=A0=A0=A0=A0=A0=A0=A0 GROUP BY
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 mag.= co_magasin,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 dem.id_produit,
=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0 pnvente.dt_debut_commercial,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 COALESCE(pnvente.id_produit,dem.id_produi= t)

Thanks.

--
J=E9r=F4me VERDIER
06.72.19.17.31=





--
J=E9r=F4= me VERDIER
06.72.19.17.31
<= br>




--
J=E9r=F4= me VERDIER
06.72.19.17.31
<= br>




--
J=E9r=F4= me VERDIER
06.72.19.17.31
<= br>





--
J=E9r=F4= me VERDIER
06.72.19.17.31
<= br>




--
J=E9r=F4= me VERDIER
06.72.19.17.31
<= br>



--
J=E9r=F4= me VERDIER
06.72.19.17.31
<= br>
--e89a8fb1eab6a2380804e1d894aa--