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)