Verknüpfen Sie Hive-Bestellungen mit Kundendaten

Einführung

Die Bestellungen des Einzelhändlers zeigen, was verkauft wurde, aber die Region jedes Kunden steht in einer anderen Tabelle. Würde man den Regionstext in jede Bestellung kopieren, würden Informationen wiederholt. Stattdessen können Sie beim Abfragen der Daten die Kundenkennung einer Bestellung mit einem Kundendatensatz verbinden. Diese Operation heißt Join.

Sie prüfen beide Seiten dieser Beziehung, ergänzen bezahlte Bestellungen um Regionen, untersuchen fehlende Kundenzuordnungen und erstellen einen regionalen Umsatzbericht. Ein Join benötigt eine sinnvolle Zuordnungsregel: Ohne sie können sich nicht zusammengehörige Zeilen verbinden und einen Bericht aufblähen. Die vorbereitete Kundentabelle hat genau eine Zeile pro Kundenkennung, daher findet jede zuordenbare Bestellung genau einen Kunden.

Dieses Lab bereitet unabhängig sechs saubere Bestellungen, zwei Kunden und ein separates Beispiel mit fehlender Zuordnung vor. Hive und Beeline sind bereit. SELECT, WHERE, Gruppierung und dezimale Verkaufsberechnungen sollten Sie kennen; die Join-Syntax wird hier eingeführt. Java, Python oder Dateien aus einem anderen Lab sind nicht erforderlich. Verwenden Sie das Terminal.

Finden Sie den Zuordnungsschlüssel

In diesem Schritt erkennen Sie, wie die beiden Tabellen dieselben Kunden beschreiben. Beginnen Sie im lokalen Arbeitsverzeichnis:

cd /home/labex/project

Prüfen Sie die bereitgestellten Bestell- und Kundendatensätze:

cat /home/labex/project/data/orders.csv

cat /home/labex/project/data/customers.csv

In den Bestellungen wiederholt sich customer_id: Ein Kunde kann mehrere Bestellungen aufgeben. Die Kundentabelle hat einen Datensatz pro customer_id, mit c1 in East und c2 in West. Diese Kennung ist der Verknüpfungsschlüssel (Join Key). Gleiche Zeilenpositionen oder ähnlich aussehende Namen sind keine verlässlichen Zuordnungsregeln.

Lesen und speichern Sie die Kundenzuordnung über Hive. Beelines -u wählt den Dienst, -n den Benutzernamen und -e die SQL-Anweisung. TSV-Ausgabe ohne Kopfzeilen erleichtert das Speichern der Ergebniszeilen; > ersetzt die benannte lokale Datei:

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "SELECT customer_id, region FROM retail_joins.customers ORDER BY customer_id;" > /home/labex/project/output/customers.tsv

cat /home/labex/project/output/customers.tsv

Erwarten Sie c1 East und c2 West als zwei tabulatorgetrennte Zeilen. Für Bestellung o1 können Sie die Zuordnung nun vorhersagen: Ihre Kennung c1 verbindet sie mit East. Bei o2 verbindet c2 mit West. Diese Tabelle wird oft Dimensions- oder Nachschlagetabelle genannt, weil sie die Bestelldatensätze um beschreibenden Kontext ergänzt.

Diese Übung stellt eindeutige Nachschlageschlüssel bereit. In echten Daten könnten doppelte Kundenschlüssel mehrere verknüpfte Zeilen pro Bestellung erzeugen und den Umsatz aufblähen. Die beabsichtigte Beziehung zu kennen gehört zur Prüfung eines Joins.

Ergänzen Sie bezahlte Bestellungen mit INNER JOIN

In diesem Schritt fügen Sie jeder bezahlten Bestellung eine Region hinzu. orders o gibt der Tabelle den kurzen Alias o; customers c gibt der anderen Tabelle den Alias c. Ein Verweis wie o.customer_id kennzeichnet die Tabelle, aus der eine Spalte stammt, insbesondere wenn beide Tabellen denselben Spaltennamen besitzen.

INNER JOIN behält Zeilen mit einer Zuordnung auf beiden Seiten. Die ausdrückliche Bedingung ON o.customer_id = c.customer_id legt die Zuordnungsregel fest. Speichern Sie diese vollständige Abfrage mit einem Here-Dokument: Die Shell schreibt die Zeilen zwischen dem öffnenden Marker 'SQL' und dem abschließenden SQL wörtlich in die Datei:

cat > /home/labex/project/queries/enriched-orders.sql <<'SQL'
SELECT o.order_id, o.customer_id, c.region,
       o.quantity * o.unit_price AS order_amount
FROM retail_joins.orders o
INNER JOIN retail_joins.customers c ON o.customer_id = c.customer_id
WHERE o.order_status = 'paid'
ORDER BY o.order_id;
SQL

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/enriched-orders.sql > /home/labex/project/output/enriched-orders.tsv

cat /home/labex/project/output/enriched-orders.tsv

Die Dateioption -f führt das gespeicherte SQL aus. Erwarten Sie vier Zeilen:

Alle vier bezahlten Bestellungen fanden genau einen Kunden. Der Join ergänzte beschreibende Informationen, ohne die berechneten Beträge zu ändern; sie ergeben weiterhin 125.00. Ein INNER JOIN würde eine Bestellung ohne Kundenzuordnung verwerfen. Diesen Fall beobachten Sie separat, damit die sauberen Berichtsdaten unverändert bleiben.

Erhalten Sie eine nicht zugeordnete Bestellung mit LEFT JOIN

In diesem Schritt vergleichen Sie die beiden Join-Typen an einem kleinen unabhängigen Beispiel. Lesen Sie dessen zwei Datensätze:

cat /home/labex/project/data/example-orders.csv

example1 verweist auf c1, während example2 auf c_missing verweist, für das kein Nachschlagedatensatz existiert. Sagen Sie das INNER-JOIN-Ergebnis vor der Ausführung voraus:

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "SELECT o.order_id, o.customer_id, c.region
FROM retail_joins.example_orders o
INNER JOIN retail_joins.customers c ON o.customer_id = c.customer_id
ORDER BY o.order_id;" > /home/labex/project/output/example-inner.tsv

cat /home/labex/project/output/example-inner.tsv

Nur example1 sollte mit East erscheinen. Die nicht zugeordnete Bestellung verschwand, weil INNER JOIN eine Zuordnung verlangt.

LEFT JOIN behält jede Zeile der links stehenden Tabelle, auch wenn die rechte Tabelle keinen passenden Schlüssel enthält. Spalten aus dem fehlenden rechten Datensatz werden NULL. Ändern Sie nur die Join-Auswahl:

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "SELECT o.order_id, o.customer_id, c.region
FROM retail_joins.example_orders o
LEFT JOIN retail_joins.customers c ON o.customer_id = c.customer_id
ORDER BY o.order_id;" > /home/labex/project/output/example-left.tsv

cat /home/labex/project/output/example-left.tsv

Erwarten Sie nun beide Zeilen: example1/c1/East und example2/c_missing/NULL. NULL beschreibt eine fehlende Nachschlagezuordnung, keine bekannte Region namens „NULL“. Die ursprüngliche Kundenkennung aus der linken Tabelle bleibt erhalten und hilft, den ungeklärten Kunden zu identifizieren.

Die Wahl von INNER oder LEFT JOIN ist eine fachliche Entscheidung. Ein Bericht mit vollständigen Regionen über saubere, vollständig zugeordnete Daten kann INNER JOIN verwenden; eine Datenqualitätsprüfung benötigt möglicherweise LEFT JOIN, um ungeklärte Datensätze zu erhalten. Die Beispieltabelle ist von orders getrennt und verändert daher weder die sechs Verkaufszeilen noch deren Umsatz.

Gleichen Sie einen regionalen Verkaufsbericht ab

In diesem Schritt wenden Sie die bereits gelernte Gruppierung auf die angereicherten Datensätze an. Der Join liefert eine Region, WHERE wählt bezahlte Bestellungen und GROUP BY fasst diese nach Region zusammen. Behalten Sie beim Ergänzen der Aggregation die ausdrückliche Zuordnungsbedingung bei:

cat > /home/labex/project/queries/regional-sales.sql <<'SQL'
SELECT c.region, SUM(o.quantity * o.unit_price) AS revenue
FROM retail_joins.orders o
INNER JOIN retail_joins.customers c ON o.customer_id = c.customer_id
WHERE o.order_status = 'paid'
GROUP BY c.region
ORDER BY c.region;
SQL

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/regional-sales.sql > /home/labex/project/output/regional-sales.tsv

cat /home/labex/project/output/regional-sales.tsv

Erwarten Sie East 70.00 und West 55.00. Zusammen ergeben sie dieselben 125.00 bezahlten Umsatz wie die unverknüpfte Quelle. Die eindeutigen Kundenschlüssel und vollständigen Zuordnungen bedeuten, dass keine bezahlte Bestellung vervielfacht wurde oder verloren ging.

Sagen Sie für eine kleine Übungsänderung die regionalen Beträge nur für den 14. Oktober voraus. Wenden Sie die bereits gelernte Datumsbedingung vor der Gruppierung auf die Bestellzeilen an:

cat > /home/labex/project/queries/regional-sales-one-day.sql <<'SQL'
SELECT c.region, SUM(o.quantity * o.unit_price) AS revenue
FROM retail_joins.orders o
INNER JOIN retail_joins.customers c ON o.customer_id = c.customer_id
WHERE o.order_status = 'paid' AND o.order_date = DATE '2026-10-14'
GROUP BY c.region
ORDER BY c.region;
SQL

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/regional-sales-one-day.sql > /home/labex/project/output/regional-sales-one-day.tsv

cat /home/labex/project/output/regional-sales-one-day.tsv

Die Beträge sollten nun East 20.00 und West 25.00 sein und zusammen die 45.00 dieses Tages ergeben. Die Zuordnungsregel blieb gleich; nur die ausgewählten Bestellzeilen änderten sich. Ihre Hauptdatei regional-sales.sql bewahrt den vollständigen Zweitagesbericht.

Sie können nun sowohl die neue beschreibende Spalte als auch die unveränderten Geschäftssummen erklären. Als Nächstes verbinden Datumspartitionen diese SQL-Datumsbedingungen mit der Organisation und Registrierung täglicher Dateien.

Zusammenfassung

Sie haben Bestellungen über eindeutige Kundenschlüssel zugeordnet, mit Aliasen die Beziehung ausdrücklich gemacht und INNER und LEFT JOIN bei fehlender Zuordnung verglichen. Anschließend haben Sie regionale Summen bezahlter Verkäufe mit der Quelle abgeglichen und eine Datumsbedingung geändert, ohne den Join zu ändern. Als Nächstes ordnen Sie tägliche Daten mit den Partitionsmetadaten von Hive.