Hive Join & SubQuery Tutorial mit Beispielen

Inhaltsverzeichnis:

Anonim

In diesem Tutorial lernen Sie:

  • Join-Abfragen
  • Verschiedene Arten von Verknüpfungen
  • Unterabfragen
  • Benutzerdefinierte Skripte einbetten
  • UDFs (User Define Functions)

An Anfragen teilnehmen:

Join-Abfragen können für zwei in Hive vorhandene Tabellen ausgeführt werden. Zum besseren Verständnis von Join Concepts erstellen wir hier zwei Tabellen:

  • Sample_joins (Bezogen auf Kundendetails)
  • Sample_joins1 (Bezogen auf Auftragsdetails von Mitarbeitern)

Schritt 1) Erstellung der Tabelle "sample_joins" mit Spaltennamen ID, Name, Alter, Adresse und Gehalt der Mitarbeiter

Schritt 2) Laden und Anzeigen von Daten

Aus dem obigen Screenshot

  1. Laden von Daten aus sampleers.txt in sample_joins
  2. Anzeigen des Tabelleninhalts sample_joins

Schritt 3) Erstellen der Tabelle sample_joins1 und Laden, Anzeigen von Daten

Aus dem obigen Screenshot können wir Folgendes beobachten

  1. Erstellung der Tabelle sample_joins1 mit den Spalten Orderid, Date1, Id, Amount
  2. Laden von Daten aus sample.txt in sample_joins1
  3. Anzeigen von Datensätzen in sample_joins1

In Zukunft werden wir verschiedene Arten von Verknüpfungen sehen, die für von uns erstellte Tabellen ausgeführt werden können. Zuvor müssen Sie jedoch die folgenden Punkte für Verknüpfungen berücksichtigen.

Einige Punkte, die in Joins zu beachten sind:

  • In Joins sind nur Gleichheitsverknüpfungen zulässig
  • In derselben Abfrage können mehr als zwei Tabellen verknüpft werden
  • Es gibt LINKS-, RECHTS-, VOLL-OUTER-Verknüpfungen, um mehr Kontrolle über die ON-Klausel zu erhalten, für die es keine Übereinstimmung gibt
  • Joins sind nicht kommutativ
  • Verknüpfungen sind linksassoziativ, unabhängig davon, ob es sich um Verknüpfungen nach LINKS oder RECHTS handelt

Verschiedene Arten von Verknüpfungen

Es gibt 4 Arten von Joins

  • Innere Verbindung
  • Linke äußere Verbindung
  • Right Outer Join
  • Vollständige äußere Verbindung

Inner Join:

Die beiden Tabellen gemeinsamen Datensätze werden von diesem Inner Join abgerufen.

Aus dem obigen Screenshot können wir Folgendes beobachten

  1. Hier führen wir eine Join-Abfrage mit dem Schlüsselwort JOIN zwischen den Tabellen sample_joins und sample_joins1 mit der Übereinstimmungsbedingung (c.Id = o.Id) durch.
  2. Die Ausgabe zeigt gemeinsame Datensätze an, die in beiden Tabellen vorhanden sind, indem die in der Abfrage erwähnte 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 aus der linken Tabelle zurück, obwohl die rechte Tabelle keine Übereinstimmungen enthält
  • Wenn die ON-Klausel mit null Datensätzen in der rechten Tabelle übereinstimmt, geben die Verknüpfungen immer noch einen Datensatz im Ergebnis mit NULL in jeder Spalte aus der rechten Tabelle zurück

Aus dem obigen Screenshot können wir Folgendes beobachten

  1. Hier führen wir eine Join-Abfrage mit dem Schlüsselwort "LEFT OUTER JOIN" zwischen den Tabellen sample_joins und sample_joins1 mit der Übereinstimmungsbedingung (c.Id = o.Id) durch.

    Zum Beispiel verwenden wir hier die Mitarbeiter-ID als Referenz. Sie prüft, ob die ID sowohl in der rechten als auch in der linken Tabelle gemeinsam ist oder nicht. Es dient als Übereinstimmungsbedingung.

  2. Die Ausgabe zeigt gemeinsame Datensätze an, die in beiden Tabellen vorhanden sind, indem die in der Abfrage erwähnte Bedingung überprüft wird.

    NULL-Werte in der obigen Ausgabe sind Spalten ohne Werte aus der rechten Tabelle 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:

  • Hive-Abfragesprache RIGHT OUTER JOIN gibt alle Zeilen aus der rechten Tabelle zurück, obwohl die linke Tabelle keine Übereinstimmungen enthält
  • Wenn die ON-Klausel mit null Datensätzen in der linken Tabelle übereinstimmt, geben die Verknüpfungen immer noch einen Datensatz im Ergebnis mit NULL in jeder Spalte aus der linken Tabelle zurück
  • RECHTE Verknüpfungen 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 beobachten

  1. Hier führen wir eine Join-Abfrage mit dem Schlüsselwort "RIGHT OUTER JOIN" zwischen den Tabellen sample_joins und sample_joins1 mit der Übereinstimmungsbedingung (c.Id = o.Id) durch.
  2. Die Ausgabe zeigt gemeinsame Datensätze an, die in beiden Tabellen vorhanden sind, indem die in der Abfrage erwähnte 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ändige äußere Verbindung:

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 füllt NULL-Werte für die Spalten aus, in denen Werte fehlen, die auf beiden Seiten übereinstimmen.

Aus dem obigen Screenshot können wir Folgendes beobachten:

  1. Hier führen wir eine Join-Abfrage mit dem Schlüsselwort "FULL OUTER JOIN" zwischen den Tabellen sample_joins und sample_joins1 mit der Übereinstimmungsbedingung (c.Id = o.Id) durch.
  2. Die Ausgabe zeigt alle in beiden Tabellen vorhandenen Datensätze an, indem die in der Abfrage erwähnte Bedingung überprüft wird. Nullwerte in der Ausgabe hier geben die fehlenden Werte in den Spalten beider Tabellen an.

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 Spaltenwerten aus anderen Tabellen

Syntax:

Subquery in FROM clauseSELECT From (SubQuery) Subquery in WHERE clauseSELECT  FromWHERE 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 in Tabelle t1 durchgeführte Unterabfrage. 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.

Benutzerdefinierte Skripte einbetten:

Hive bietet die Möglichkeit, benutzerspezifische Skripte für die Clientanforderungen zu schreiben. Die Benutzer können ihre eigene Karte schreiben und Skripte für die 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 wählen Sie Embedded Scripts:

  • Bei kundenspezifischen Anforderungen müssen Entwickler Skripte in Hive schreiben und bereitstellen
  • Wo integrierte Funktionen von Hive für bestimmte Domänenanforderungen nicht funktionieren

Zu diesem Zweck wird in Hive die TRANSFORM-Klausel verwendet, um sowohl Map- als auch Reducer-Skripte einzubetten.

In diesen eingebetteten benutzerdefinierten Skripten müssen die folgenden Punkte beachtet werden

  • Spalten werden in Zeichenfolgen umgewandelt und von TAB getrennt, bevor sie dem Benutzerskript übergeben werden
  • Die Standardausgabe des Benutzerskripts wird als durch TAB getrennte Zeichenfolgenspalten behandelt

Beispiel für ein eingebettetes Skript,

FROM (FROM pv_usersMAP pv_users.userid, pv_users.dateUSING 'map_script'AS dt, uidCLUSTER BY dt) map_outputINSERT OVERWRITE TABLE pv_users_reducedREDUCE map_output.dt, map_output.uidUSING '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 mit Feldern wie Benutzer-ID und Datum, wie in map_script erwähnt
  • Reduziererskript definiert nach Datum und Anzahl der pv_users-Tabellen