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érôme Verdier <verdier.jerom...@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 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 or more arguments are expected. > SQLState: 42000 > ErrorCode: 40000 > > > > > 2013/7/17 Richa Sharma <mailtorichasha...@gmail.com> > >> 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érôme Verdier < >> verdier.jerom...@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 = dem.co_societe >>> AND pn.id_produit = dem.id_produit >>> LEFT OUTER JOIN default.produit_norm pnvente >>> ON pnvente.co_societe = pn.co_societe >>> AND pnvente.co_produit_rfu = pn.co_produit_lip >>> INNER JOIN default.kpi_magasin mag >>> ON mag.id_magasin = 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 <mailtorichasha...@gmail.com> >>> >>>> Can you share query with just RANK(). >>>> >>>> Richa >>>> >>>> >>>> On Tue, Jul 16, 2013 at 6:08 PM, Jérôme Verdier < >>>> verdier.jerom...@gmail.com> wrote: >>>> >>>>> Hi Richa, >>>>> >>>>> I tried to execute the rank function alone, but the result is the same >>>>> >>>>> Thanks >>>>> >>>>> >>>>> 2013/7/16 Richa Sharma <mailtorichasha...@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 apply these >>>>>> aggregate functions >>>>>> >>>>>> Richa >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> On Tue, Jul 16, 2013 at 2:15 PM, Jérôme Verdier < >>>>>> verdier.jerom...@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-windowingandanalytics.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 or 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 ELSE >>>>>>> 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 ELSE >>>>>>> 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 = 1 THEN 1 ELSE 2 END = >>>>>>> '${hiveconf:in_co_societe}' -- A modifier >>>>>>> AND dem.dt_jour = cal.dt_jour >>>>>>> LEFT OUTER JOIN default.produit_norm pn >>>>>>> ON pn.co_societe = dem.co_societe >>>>>>> AND pn.id_produit = dem.id_produit >>>>>>> LEFT OUTER JOIN default.produit_norm pnvente >>>>>>> ON pnvente.co_societe = pn.co_societe >>>>>>> AND pnvente.co_produit_rfu = pn.co_produit_lip >>>>>>> AND pnvente.co_type_motif='05' >>>>>>> INNER JOIN default.kpi_magasin mag >>>>>>> ON mag.co_societe = '${hiveconf:in_co_societe}' >>>>>>> AND mag.id_magasin = dem.id_magasin >>>>>>> WHERE cal.dt_jour = '${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érôme VERDIER* >>>>>>> 06.72.19.17.31 >>>>>>> verdier.jerom...@gmail.com >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> >>>>> -- >>>>> *Jérôme VERDIER* >>>>> 06.72.19.17.31 >>>>> verdier.jerom...@gmail.com >>>>> >>>>> >>>> >>> >>> >>> -- >>> *Jérôme VERDIER* >>> 06.72.19.17.31 >>> verdier.jerom...@gmail.com >>> >>> >> > > > -- > *Jérôme VERDIER* > 06.72.19.17.31 > verdier.jerom...@gmail.com > >
