webentwicklung-frage-antwort-db.com.de

Hive erhält Top-N-Datensätze in Gruppen für Abfrage

Ich habe folgende Tabelle in Hive

benutzer-ID, Benutzername, Benutzeradresse, Klicks, Impressionen, Seiten-ID, Seitenname

Ich muss die Top-5-Benutzer (Benutzer-ID, Benutzername, Benutzeradresse) durch Klicks für jede Seite herausfinden [Seiten-ID, Seitenname].

Ich verstehe, dass wir zuerst nach [Seiten-ID, Seitenname] gruppieren müssen, und innerhalb jeder Gruppe, die ich bestellen möchte, [Klicks, Impressionen]. Adresse] für jede Seite, aber ich finde es schwierig, die Abfrage zu erstellen.

Wie können wir das mit Hive UDF tun?

user-defined-functionshiverank
22
TopCoder

Sie können dies mit einer hier beschriebenen Rang () - UDF tun: 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
9
Maxime Brugidou

Überarbeitete Antwort, behebt den Fehler, wie von @Himanshu Gahlot erwähnt

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

Beachten Sie, dass UDAF rank () auf die Page-ID-Spalte angewendet wird, deren neuer Wert zum Zurücksetzen oder Erhöhen des Rank-Zählers verwendet wird (z. B. Reset-Zähler für jede Seiten-ID-Partition).

15
Hai-Anh Trinh

Ab Hive 0.11 können Sie dies mit der integrierten rank () - Funktion von Hive und der einfacheren Semantik mit den integrierten Analytics- und Windowing-Funktionen von Hive durchführen. Leider konnte ich nicht so viele Beispiele finden, wie ich mir gewünscht hätte, aber sie sind wirklich sehr nützlich. Wenn Sie diese verwenden, sind sowohl rank () als auch WhereWithRankCond integriert. Sie können also Folgendes tun:

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

Keine UDF erforderlich und nur eine Unterabfrage! Außerdem ist die gesamte Ranglogik lokalisiert.

Einige weitere (aber nicht genug für meinen Geschmack) Beispiele dieser Funktionen finden Sie in diesem Jira und auf diesem Blog dieses Kerls .

10
Eli

Sie können each_top_k function of hivemall für eine effiziente Top-k-Berechnung in Apache Hive verwenden.

wählen
 Seiten-ID, 
 Benutzeridentifikation,
 Klicks 
 von (
 Auswahl 
 each_top_k (5, Seiten-ID, Klicks, Seiten-ID, Benutzer-ID) 
 as (Rang, Klicks, Seiten-ID, Benutzer-ID) 
 from (
 select 
 page-id, benutzer-id, klickt 
 aus 
 mytable 
 DISTRIBUTE BY page-id SORT BY page-id 
) t1 
) t2 
 Reihenfolge nach Seiten-ID ASC, klickt auf DESC

Die each_top_k-UDTF ist im Vergleich zu anderen Methoden, die top-k-Abfragen (z. B. distributed by/rank) in Hive ausführen, sehr schnell, da sie nicht die gesamte Rangfolge für das Zwischenergebnis enthält.

2
myui

Nehmen wir an, Ihre Daten sehen folgendermaßen aus:

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

Unter Query erhalten Sie: 

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

Ergebnis:

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

Für page1 erhalten Sie also 6 Nutzer, da Nutzer mit der gleichen Anzahl von Klicks gleich bewertet werden. 

Wenn Sie jedoch nach genau 5 Benutzern suchen, wählen Sie nach dem Zufallsprinzip aus, falls mehrere Benutzer in denselben Rang fallen. Sie können die folgende Abfrage verwenden 

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

Ergebnis:

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
1
greperror