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

Switch to Threaded View
Hive >> mail # user >> Use RANK OVER PARTITION function in Hive 0.11


Copy link to this message
-
Re: Use RANK OVER PARTITION function in Hive 0.11
Hi Richa,

I tried to execute the rank function alone, but the result is the same

Thanks
2013/7/16 Richa Sharma <[EMAIL PROTECTED]>

> 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 <
> [EMAIL PROTECTED]> 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
>> [EMAIL PROTECTED]
>>
>>
>
--
*Jérôme VERDIER*
06.72.19.17.31
[EMAIL PROTECTED]