Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 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]
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB