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 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 query is
also failling when i put analytical columns in...
2013/7/17 Richa Sharma <[EMAIL PROTECTED]>

> 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 <[EMAIL PROTECTED]> 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érôme Verdier <
>> [EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
>>>
>>>> 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 <
>>>> [EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
>>>>>
>>>>>> Can you share query with just RANK().
>>>>>>
>>>>>> Richa
>>>>>>
>>>>>>
>>>>>> On Tue, Jul 16, 2013 at 6:08 PM, Jérôme Verdier <
>>>>>> [EMAIL PROTECTED]> wrote:
>>>>>>
>>>>>>> Hi Richa,
*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