Einführung
Der Einzelhändler kann Dateien nun Tabellen zuordnen. Eine lesbare Tabelle ist aber nicht automatisch für Berichte geeignet. Einige Statuswerte enthalten zusätzliche Leerzeichen, eine Kundenkennung fehlt, eine andere ist leerer Text, ein Preis ist unbekannt und eine Menge ist null. Eine Abfrage, die diese Unterschiede ignoriert, kann falsche Zeilen auswählen oder irreführende Werte anzeigen.
Sie erstellen eine kleine Prüfliste: bezahlte Zeilen mit positiver Menge und bekannten Preisen, normalisiertem Statustext und einem sichtbaren Standardwert für eine fehlende Kundenkennung. Fehlende Preise werden ausgeschlossen und nicht durch null ersetzt. Die Anzeige einer Bezeichnung für einen unbekannten Kunden erzeugt weder einen echten Kundendatensatz noch ändert sie die Quelldaten.
Dieses Lab verwendet ein separates Beispiel mit sieben Zeilen zur Datenqualität und nicht die sauberen Verkaufsdaten aus früheren Labs. Sie kombinieren zunächst Bedingungen, untersuchen dann fehlende Werte und einfache Funktionen, ergänzen Bezeichnungen mit CASE und ändern eine Bedingung, um Ihr Verständnis zu prüfen. SELECT, WHERE, ORDER BY und SQL-Dateien kennen Sie bereits. Alle Dienste und Tabellenbeispiele werden unabhängig vorbereitet; verwenden Sie das Terminal.
Kombinieren Sie Zeilenbedingungen gezielt
In diesem Schritt kombinieren Sie eine Mengenprüfung mit Statusalternativen und verstehen, warum Klammern wichtig sind.
Beginnen Sie in Ihrem Projekt:
cd /home/labex/project
Lesen Sie vor der Abfrage die vorbereitete CSV:
cat /home/labex/project/data/quality-orders.csv
Nach der Kopfzeile folgen sieben Geschäftszeilen. Beachten Sie die Leerzeichen um paid in r1, die Marker \N in r2 und r3, das leere Kundenfeld in r6 und die Menge null in r7. Die vorbereitete Tabelle interpretiert \N als SQL-NULL; ein leeres Textfeld bleibt eine leere Zeichenfolge. Diese Unterschiede untersuchen Sie gleich genauer.
SQL-Vergleiche wie quantity > 0 prüfen jede Zeile. AND verlangt, dass beide Bedingungen wahr sind; OR akzeptiert eine der Alternativen. Klammern fassen die Statusalternativen zusammen, damit eine ausstehende Zeile mit Menge null die Mengenprüfung nicht umgehen kann.
Speichern Sie einen ersten Filter mit dem bereits geübten Muster für SQL-Dateien:
cat > /home/labex/project/queries/conditions.sql <<'SQL'
USE retail_filters;
SELECT order_id, quantity, order_status
FROM quality_orders
WHERE quantity > 0
AND (order_status = 'paid' OR order_status = 'pending')
ORDER BY order_id;
SQL
Führen Sie die Datei mit der bekannten Beeline-Verbindung und den Optionen für tabulatorgetrennte Ausgabe aus:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/conditions.sql > /home/labex/project/output/conditions.txt
Lesen Sie das Ergebnis:
cat /home/labex/project/output/conditions.txt
Die Bestellzeilen sollten so lauten:
r2 1 paid
r3 1 paid
r4 1 pending
r6 1 paid
r7 wird wegen seiner Menge ausgeschlossen, obwohl sein Status pending ist. r1 wird ausgeschlossen, weil sein gespeicherter Text Leerzeichen enthält und nicht genau 'paid' entspricht. Der fehlende Preis in r3 wurde von diesem Filter noch nicht behandelt. Jede Bedingung löst ein bestimmtes Problem; die nächsten Schritte ergänzen die fehlenden Teile.
Unterscheiden Sie fehlende Werte von leerem Text
In diesem Schritt prüfen Sie NULL korrekt und normalisieren Anzeigewerte, ohne gespeicherte Zeilen zu ändern.
NULL steht für einen fehlenden oder unbekannten Wert. Es ist weder der Text 'NULL' noch eine leere Zeichenfolge. Prüfen Sie es mit IS NULL oder IS NOT NULL, nicht mit = NULL.
Fragen Sie fehlende und leere Kundenkennungen getrennt ab. Die erste Anweisung wählt r2, die zweite r6 aus:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex -e "SELECT order_id FROM retail_filters.quality_orders WHERE customer_id IS NULL; SELECT order_id FROM retail_filters.quality_orders WHERE customer_id = '';"
Zwei kleine Funktionen helfen, diese Datensätze verständlich anzuzeigen. TRIM(text) entfernt Leerzeichen an den Enden einer Zeichenfolge. COALESCE(a,b) liefert den ersten Wert, der nicht NULL ist, und kann daher 'unknown' anzeigen, wenn die Kennung NULL ist. Es ersetzt keinen leeren Text und ändert die zugrunde liegende Tabelle nicht.
Speichern Sie eine Prüf-Abfrage, die diese Funktionen auf alle sieben Zeilen anwendet:
cat > /home/labex/project/queries/normalized-values.sql <<'SQL'
USE retail_filters;
SELECT order_id,
COALESCE(customer_id, 'unknown') AS customer_label,
TRIM(order_status) AS clean_status,
unit_price
FROM quality_orders
ORDER BY order_id;
SQL
Führen Sie sie aus:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/normalized-values.sql > /home/labex/project/output/normalized-values.txt
Lesen Sie die Mitschrift:
cat /home/labex/project/output/normalized-values.txt
Finden Sie drei Beobachtungen: r1 zeigt jetzt paid ohne umgebende Leerzeichen, r2 zeigt die Kundenbezeichnung unknown, und r6 hat weiterhin ein leeres Kundenfeld. r3 besitzt einen NULL-Preis. Ein Anzeigestandardwert ist für die fehlende Kundenbezeichnung in dieser Prüfliste angemessen; einen Preis von null zu erfinden, würde die Bestellung hingegen falsch darstellen. Der abschließende Filter schließt unbekannte Preise stattdessen aus.
Beschriften und wählen Sie eine Prüfliste aus
In diesem Schritt ergänzen Sie eine verständliche Zahlungsstatusbezeichnung und kombinieren die Prüfungen der Zeilenqualität in einer Abfrage.
Ein SQL-Ausdruck CASE wählt anhand von Bedingungen einen Wert aus. Er prüft die WHEN-Zweige der Reihe nach, verwendet den passenden THEN-Wert und greift auf ELSE zurück, wenn kein Zweig passt. Sie beschriften bezahlte Zeilen mit Payment received, ausstehende mit Awaiting payment und andere Statuswerte mit Other status.
Sehen Sie sich die Bezeichnungen über alle Zeilen an, bevor Sie den Filter für die Prüfliste hinzufügen:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex -e "SELECT order_id, CASE WHEN TRIM(order_status) = 'paid' THEN 'Payment received' WHEN TRIM(order_status) = 'pending' THEN 'Awaiting payment' ELSE 'Other status' END AS payment_label FROM retail_filters.quality_orders ORDER BY order_id;"
Finden Sie r5, dessen Status refunded den ELSE-Zweig nimmt. r4 und r7 werden als auf Zahlung wartend beschriftet. Eine Statusbezeichnung beschreibt den Status; sie validiert für sich genommen weder Menge noch Preis.
Stellen Sie nun die vollständige Regel der Liste zusammen: positive Menge, bekannter Preis und normalisierter Status paid. Die Quellkundenkennung bleibt unverändert. Im Abfrageergebnis zeigen Sie mit COALESCE eine Kundenbezeichnung an. Das angezeigte Ergebnis enthält keine separate Kopie der ursprünglichen Kennung. Speichern Sie die Abfrage:
cat > /home/labex/project/queries/filtered-orders.sql <<'SQL'
USE retail_filters;
SELECT order_id,
COALESCE(customer_id, 'unknown') AS customer_label,
TRIM(order_status) AS clean_status,
CASE
WHEN TRIM(order_status) = 'paid' THEN 'Payment received'
WHEN TRIM(order_status) = 'pending' THEN 'Awaiting payment'
ELSE 'Other status'
END AS payment_label
FROM quality_orders
WHERE quantity > 0
AND unit_price IS NOT NULL
AND TRIM(order_status) = 'paid'
ORDER BY order_id;
SQL
Führen Sie sie unabhängig aus:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/filtered-orders.sql > /home/labex/project/output/filtered-orders.txt
Lesen Sie das Ergebnis:
cat /home/labex/project/output/filtered-orders.txt
Erwartete Bestellzeilen:
r1 c1 paid Payment received
r2 unknown paid Payment received
r6 paid Payment received
Das leere Kundenanzeigefeld in r6 ist beabsichtigt: Es ist leerer Text statt NULL. r1 wird nach dem Entfernen der Leerzeichen im Status eingeschlossen. r3 wird wegen seines unbekannten Preises ausgeschlossen, während ausstehende und erstattete Bestellungen sowie Zeilen mit Menge null nicht zu dieser Liste gehören. Sie haben die vorgesehenen Zeilen definiert, ohne vorzugeben, fehlende Preise seien null.
Ändern Sie eine Bedingung und sagen Sie das Ergebnis voraus
In diesem Schritt verwenden Sie dieselben Anzeigeausdrücke und Qualitätsregeln für eine Prüfliste ausstehender Bestellungen erneut.
Sagen Sie vor der Ausführung das Ergebnis voraus, wenn sich nur die letzte Statusbedingung zu pending ändert. r4 hat eine positive Menge und einen bekannten Preis, während r7 die Menge null hat. Nur r4 sollte übrig bleiben, mit der CASE-Bezeichnung Awaiting payment.
Bewahren Sie die erste Abfrage auf und speichern Sie eine zweite Datei. Ihre ausgewählten Ausdrücke und Qualitätsprüfungen sind unverändert; nur der letzte Statuswert in WHERE unterscheidet sich:
cat > /home/labex/project/queries/pending-review.sql <<'SQL'
USE retail_filters;
SELECT order_id,
COALESCE(customer_id, 'unknown') AS customer_label,
TRIM(order_status) AS clean_status,
CASE
WHEN TRIM(order_status) = 'paid' THEN 'Payment received'
WHEN TRIM(order_status) = 'pending' THEN 'Awaiting payment'
ELSE 'Other status'
END AS payment_label
FROM quality_orders
WHERE quantity > 0
AND unit_price IS NOT NULL
AND TRIM(order_status) = 'pending'
ORDER BY order_id;
SQL
Führen Sie sie aus:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/pending-review.sql > /home/labex/project/output/pending-review.txt
Lesen Sie die Ausgabe:
cat /home/labex/project/output/pending-review.txt
Erwartete Bestellzeile:
r4 c1 pending Awaiting payment
Die Tabelle wurde nicht verändert; die Abfrage wählte eine andere Zeilenmenge aus, und der vorhandene CASE-Ausdruck wählte die entsprechende Bezeichnung. Im nächsten Lab bilden Sie Summen aus korrekt ausgewählten Zeilen, statt Datenqualitätsregeln und erstmalige Aggregation auf einmal einzuführen.
Zusammenfassung
Sie haben Bedingungen mit AND, OR und Klammern kombiniert, NULL von leerem Text unterschieden und TRIM und COALESCE für ausdrückliches Anzeigeverhalten verwendet. Sie haben CASE-Bezeichnungen ergänzt und eine Prüfliste definiert, ohne Geldbeträge zu erfinden oder Quelldaten zu verändern.
Eine geänderte Bedingung erzeugte die vorhergesagte Liste ausstehender Bestellungen bei unveränderten Qualitätsprüfungen. Als Nächstes aggregieren Sie saubere, bewusst ausgewählte Bestellzeilen zu Verkaufskennzahlen.
