Hive Join- und SubQuery-Tutorial mit Beispielen
David Carter
Schließen Sie sich Abfragen an
Join-Abfragen können für zwei in Hive vorhandene Tabellen ausgeführt werden. Zum Verständnis beitreten Concepts Im Klartext hier erstellen wir hier zwei Tabellen,
- Sample_joins (Bezogen auf Kundendetails)
- Sample_joins1 (Bezieht sich auf Bestelldetails, die von Mitarbeitern getätigt wurden)
Schritt 1) Erstellung der Tabelle „sample_joins“ mit den Spaltennamen ID, Name, Alter, Adresse und Gehalt der Mitarbeiter
Schritt 2) Daten laden und anzeigen
Aus dem obigen Screenshot
- Daten werden aus „Customers.txt“ in „sample_joins“ geladen
- Inhalte der Tabelle „sample_joins“ werden angezeigt
Schritt 3) Erstellung der Tabelle „sample_joins1“ und Laden und Anzeigen von Daten
Aus dem obigen Screenshot können wir Folgendes erkennen:
- Erstellung der Tabelle sample_joins1 mit den Spalten Orderid, Date1, Id, Amount
- Daten werden aus der Datei „orders.txt“ in „sample_joins1“ geladen
- In „sample_joins1“ vorhandene Datensätze werden angezeigt
Im weiteren Verlauf werden wir uns verschiedene Verknüpfungstypen ansehen, die für die von uns erstellten Tabellen durchgeführt werden können. Zuvor müssen Sie jedoch die folgenden Punkte für Verknüpfungen berücksichtigen.
Einige Punkte, die Sie bei Joins beachten sollten:
- In Joins sind nur Gleichheits-Joins zulässig
- In derselben Abfrage können mehr als zwei Tabellen verknüpft werden
- Es gibt LEFT-, RIGHT- und FULL OUTER-Joins, um eine bessere Kontrolle über die ON-Klausel zu ermöglichen, für die es keine Übereinstimmung gibt
- Verknüpfungen sind nicht kommutativ
- Joins sind linksassoziativ, unabhängig davon, ob es sich um LEFT- oder RIGHT-Joins handelt
Verschiedene Arten von Verbindungen
Es gibt vier Arten von Verknüpfungen:
- Innere Verbindung
- Linke äußere Verbindung
- Rechte äußere Verknüpfung
- Vollständige äußere Verbindung
Innere Verbindung:
Die für beide Tabellen gemeinsamen Datensätze werden durch diesen Inner Join abgerufen.
Aus dem obigen Screenshot können wir Folgendes erkennen:
- Hier führen wir eine Join-Abfrage mit dem Schlüsselwort JOIN zwischen den Tabellen „sample_joins“ und „sample_joins1“ mit der übereinstimmenden Bedingung „(c.Id= o.Id)“ durch.
- Die Ausgabe zeigt gemeinsame Datensätze an, die in beiden Tabellen vorhanden sind, indem die in der Abfrage genannte Bedingung überprüft wird
Abfrage:
SELECT c.Id, c.Name, c.Age, o.Amount FROM sample_joins c JOIN sample_joins1 o ON(c.Id=o.Id);
Linke äußere Verbindung:
- Hive-Abfragesprache LEFT OUTER JOIN gibt alle Zeilen der linken Tabelle zurück, auch wenn es in der rechten Tabelle keine Übereinstimmungen gibt
- Wenn die ON-Klausel mit null Datensätzen in der rechten Tabelle übereinstimmt, geben die Verknüpfungen im Ergebnis immer noch einen Datensatz mit NULL in jeder Spalte der rechten Tabelle zurück
Aus dem obigen Screenshot können wir Folgendes erkennen:
- Hier führen wir eine Join-Abfrage mit dem Schlüsselwort „LEFT OUTER JOIN“ zwischen den Tabellen „sample_joins“ und „sample_joins1“ mit der übereinstimmenden Bedingung „(c.Id= o.Id)“ durch.Zum Beispiel Hier verwenden wir die Mitarbeiter-ID als Referenz. Es wird überprüft, ob die ID sowohl rechts als auch links in der Tabelle üblich ist oder nicht. Es fungiert als Matching-Bedingung.
- Die Ausgabe zeigt gemeinsame Datensätze an, die in beiden Tabellen vorhanden sind, indem die in der Abfrage genannte Bedingung überprüft wird. NULL-Werte in der obigen Ausgabe sind Spalten ohne Werte aus der rechten Tabelle, also sample_joins1
Abfrage:
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c LEFT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Rechter äußerer Join:
- Die Hive-Abfragesprache RIGHT OUTER JOIN gibt alle Zeilen aus der rechten Tabelle zurück, auch wenn es in der linken Tabelle keine Übereinstimmungen gibt
- Wenn die ON-Klausel mit null Datensätzen in der linken Tabelle übereinstimmt, geben die Joins im Ergebnis immer noch einen Datensatz mit NULL in jeder Spalte der linken Tabelle zurück
- RIGHT-Joins geben immer Datensätze aus einer rechten Tabelle und übereinstimmende Datensätze aus der linken Tabelle zurück. Wenn die linke Tabelle keine der Spalte entsprechenden Werte enthält, werden an dieser Stelle NULL-Werte zurückgegeben.
Aus dem obigen Screenshot können wir Folgendes erkennen:
- Hier führen wir eine Join-Abfrage mit dem Schlüsselwort „RIGHT OUTER JOIN“ zwischen den Tabellen „sample_joins“ und „sample_joins1“ mit der übereinstimmenden Bedingung „(c.Id= o.Id)“ durch.
- Die Ausgabe zeigt gemeinsame Datensätze an, die in beiden Tabellen vorhanden sind, indem die in der Abfrage genannte Bedingung überprüft wird
Abfrage:
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c RIGHT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Vollständiger äußerer Join:
Es kombiniert Datensätze der Tabellen „sample_joins“ und „sample_joins1“ basierend auf der in der Abfrage angegebenen JOIN-Bedingung.
Es gibt alle Datensätze aus beiden Tabellen zurück und trägt NULL-Werte für die Spalten ein, in denen auf beiden Seiten übereinstimmende Werte fehlen.
Aus dem obigen Screenshot können wir Folgendes erkennen:
- Hier führen wir eine Join-Abfrage mit dem Schlüsselwort „FULL OUTER JOIN“ zwischen den Tabellen „sample_joins“ und „sample_joins1“ mit der übereinstimmenden Bedingung „(c.Id= o.Id)“ durch.
- Die Ausgabe zeigt alle in beiden Tabellen vorhandenen Datensätze an, indem die in der Abfrage genannte Bedingung überprüft wird. Nullwerte in der Ausgabe weisen hier auf die fehlenden Werte in den Spalten beider Tabellen hin.
Abfrage
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c FULL OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Unterabfragen
Eine in einer Abfrage vorhandene Abfrage wird als Unterabfrage bezeichnet. Die Hauptabfrage hängt von den von den Unterabfragen zurückgegebenen Werten ab.
Unterabfragen können in zwei Typen eingeteilt werden
- Unterabfragen in der FROM-Klausel
- Unterabfragen in der WHERE-Klausel
Wann zu verwenden:
- Um einen bestimmten Wert aus zwei Spaltenwerten aus verschiedenen Tabellen zu kombinieren
- Abhängigkeit der Werte einer Tabelle von anderen Tabellen
- Vergleichende Überprüfung von Werten einer Spalte aus anderen Tabellen
Syntax:
Subquery in FROM clause SELECT <column names 1, 2…n>From (SubQuery) <TableName_Main > Subquery in WHERE clause SELECT <column names 1, 2…n> From<TableName_Main>WHERE col1 IN (SubQuery);
Beispiel:
SELECT col1 FROM (SELECT a+b AS col1 FROM t1) t2
Hier sind t1 und t2 Tabellennamen. Die farbige ist die Unterabfrage, die für Tabelle t1 ausgeführt wird. Hier sind a und b Spalten, die in einer Unterabfrage hinzugefügt und col1 zugewiesen werden. Col1 ist der Spaltenwert in der Haupttabelle. Diese in der Unterabfrage vorhandene Spalte „col1“ entspricht der Haupttabellenabfrage in Spalte col1.
Einbetten benutzerdefinierter Skripte
Bienenstock Bietet die Möglichkeit, benutzerspezifische Skripte für die Kundenanforderungen zu schreiben. Die Benutzer können ihre eigene Karte schreiben und Skripte entsprechend den Anforderungen reduzieren. Diese werden als eingebettete benutzerdefinierte Skripte bezeichnet. Die Codierungslogik ist in den benutzerdefinierten Skripten definiert und wir können dieses Skript in der ETL-Zeit verwenden.
Wann Sie sich für eingebettete Skripte entscheiden sollten:
- Bei kundenspezifischen Anforderungen müssen Entwickler Skripte in Hive schreiben und bereitstellen
- Wo integrierte Hive-Funktionen für bestimmte Domänenanforderungen nicht funktionieren
Zu diesem Zweck verwendet es in Hive die TRANSFORM-Klausel, um sowohl Map- als auch Reducer-Skripte einzubetten.
In diesen eingebetteten benutzerdefinierten Skripten müssen wir die folgenden Punkte beachten
- Spalten werden in Zeichenfolgen umgewandelt und durch TAB getrennt, bevor sie an das Benutzerskript übergeben werden
- Die Standardausgabe des Benutzerskripts wird als durch Tabulatoren getrennte Zeichenfolgenspalten behandelt
Beispiel für ein eingebettetes Skript,
FROM ( FROM pv_users MAP pv_users.userid, pv_users.date USING 'map_script' AS dt, uid CLUSTER BY dt) map_output INSERT OVERWRITE TABLE pv_users_reduced REDUCE map_output.dt, map_output.uid USING 'reduce_script' AS date, count;
Aus dem obigen Skript können wir Folgendes beobachten
Dies ist nur das Beispielskript zum Verständnis
- pv_users ist die Benutzertabelle, die Felder wie Benutzer-ID und Datum enthält, wie in map_script erwähnt
- Reduzierskript, definiert für Datum und Anzahl der pv_users-Tabellen
