user defined functions - Hive obteniendo top n registros en grupo por consulta

user-defined-functions rank

Question

Tengo la siguiente mesa en colmena

ID de usuario, nombre de usuario, dirección de usuario, clics, impresiones, ID de página, nombre de página

Necesito encontrar los 5 principales usuarios [id de usuario, nombre de usuario, dirección de usuario] por clics para cada página [id de página, nombre de página]

Entiendo que primero debemos agrupar por [page-id, page-name] y dentro de cada grupo quiero ordenar por [clics, impresiones] desc y luego emitir solo los 5 principales usuarios [user-id, user-user, user- dirección] para cada página, pero me resulta difícil construir la consulta.

¿Cómo podemos hacer esto usando HIve UDF?

Answer 1

A partir de Hive 0.11, puede hacer esto usando la función de rango () incorporada de Hive y usando una semántica más simple usando las funciones de Análisis y Ventanas integradas de Hive . Lamentablemente, no pude encontrar tantos ejemplos con estos como me hubiera gustado, pero son realmente, realmente útiles. Usando esos, tanto rank () como WhereWithRankCond están incorporados, por lo que simplemente puede hacer:

SELECT page-id, user-id, clicks
FROM (
    SELECT page-id, user-id, rank() 
           over (PARTITION BY page-id ORDER BY clicks DESC) as rank, clicks 
    FROM my table
) ranked_mytable
WHERE ranked_mytable.rank < 5
ORDER BY page-id, rank

No se requiere UDF, y solo una subconsulta! Además, toda la lógica de rango está localizada.

Puedes encontrar algunos ejemplos más (aunque no lo suficiente para mi gusto) de estas funciones en esta Jira y en el blog de este chico .

Answer 2

Digamos que sus datos se parecen a los siguientes:

page-id   user-id   clicks
page1     user1     10
page1     user2     10
page1     user3     9
page1     user4     8
page1     user5     7
page1     user6     7
page1     user7     6
page1     user8     5
page2     user1     20
page2     user2     19
page2     user3     18

A continuación la consulta le dará:

SELECT page-id, user-id, clicks, rank
FROM (
    SELECT page-id, user-id, rank() 
           over (PARTITION BY page-id ORDER BY clicks DESC) as rank, clicks 
    FROM your_table
) ranked_table
WHERE ranked_table.rank <= 5

Resultado:

page-id   user-id   clicks  rank
page1     user1     10      1
page1     user2     10      1 
page1     user3     9       3 
page1     user4     8       4
page1     user5     7       5 
page1     user6     7       5 
page2     user1     20      1
page2     user2     19      2  
page2     user3     18      3

Por lo tanto, para page1 está obteniendo 6 usuarios, ya que los usuarios con la misma cantidad de clics tienen la misma calificación.

Pero, si está buscando exactamente 5 usuarios, elija aleatoriamente en caso de que varios usuarios caigan en el mismo rango. Puedes usar la siguiente consulta

SELECT page-id, user-id, clicks, rank
FROM (
    SELECT page-id, user-id, row_number() 
           over (PARTITION BY page-id ORDER BY clicks DESC) as rank, clicks 
    FROM your_table
) ranked_table
WHERE ranked_table.rank <= 5

Resultado:

page-id   user-id   clicks  rank
page1     user1     10      1
page1     user2     10      2 
page1     user3     9       3 
page1     user4     8       4
page1     user5     7       5 
page2     user1     20      1
page2     user2     19      2  
page2     user3     18      3

Answer 3

Puede hacerlo con un rango () UDF descrito aquí: http://ragrawal.wordpress.com/2011/11/18/extract-top-n-records-in-each-group-in-hadoophive/

SELECT page-id, user-id, clicks
FROM (
    SELECT page-id, user-id, rank(user-id) as rank, clicks
    FROM mytable
    DISTRIBUTE BY page-id, user-id
    SORT BY page-id, user-id, clicks desc
) a 
WHERE rank < 5
ORDER BY page-id, rank

Answer 4

Puede utilizar la función each_top_k de hivemall para un cálculo eficiente de top-k en Apache Hive.

select page-id, user-id, clicks from ( select each_top_k(5, page-id, clicks, page-id, user-id) as (rank, clicks, page-id, user-id) from ( select page-id, user-id, clicks from mytable DISTRIBUTE BY page-id SORT BY page-id ) t1 ) t2 order by page-id ASC, clicks DESC

La each_top_k each_top_k es muy rápida en comparación con otros métodos que ejecutan consultas top-k (por ejemplo, distributed by/rank ) en Hive porque no tiene la clasificación completa para el resultado intermedio.

Answer 5

Respuesta revisada, solucionando el error mencionado por @Himanshu Gahlot

SELECT page-id, user-id, clicks
FROM (
    SELECT page-id, user-id, rank(page-id) as rank, clicks FROM (
        SELECT page-id, user-id, clicks FROM mytable
        DISTRIBUTE BY page-id
        SORT BY page-id, clicks desc
) a ) b
WHERE rank < 5
ORDER BY page-id, rank

Tenga en cuenta que el UDAF de rango () se aplica a la columna de ID de página, cuyo nuevo valor se usa para restablecer o aumentar el contador de rango (por ejemplo, el contador de reinicio para cada partición de ID de página)