Filtrer les lignes Hive et traiter les valeurs manquantes

Introduction

Le commerçant sait maintenant relier des fichiers à des tables, mais une table lisible n’est pas automatiquement prête pour le reporting. Certaines valeurs de statut ont des espaces supplémentaires, un identifiant client manque, un autre est du texte vide, un prix est inconnu et une quantité vaut zéro. Une requête qui ignore ces différences peut sélectionner les mauvaises lignes ou afficher des valeurs trompeuses.

Vous construirez une petite file de révision : des lignes payées de quantité positive avec des prix connus, un statut normalisé et une valeur par défaut visible pour un identifiant client manquant. Les prix manquants seront exclus, et non remplacés par zéro. Afficher un libellé de client inconnu ne crée pas de véritable fiche client et ne modifie pas les données sources.

Ce lab utilise un échantillon distinct de sept lignes consacré à la qualité des données, et non les données de vente propres des labs précédents. Vous combinerez d’abord des conditions, puis examinerez les valeurs manquantes et des fonctions simples, ajouterez des libellés avec CASE et changerez une condition pour vérifier votre compréhension. Vous connaissez déjà SELECT, WHERE, ORDER BY et les fichiers SQL. Tous les services et les tables d’exercice sont préparés indépendamment ; utilisez le Terminal.

Combiner les conditions sur les lignes de façon réfléchie

Dans cette étape, vous combinerez un test de quantité avec plusieurs statuts possibles et comprendrez l’importance des parenthèses.

Commencez dans votre projet :

cd /home/labex/project

Lisez le CSV préparé avant de l’interroger :

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

Il y a sept lignes métier après l’en-tête. Remarquez les espaces autour de paid dans r1, les marqueurs \N dans r2 et r3, le champ client vide dans r6 et la quantité nulle de r7. La table préparée interprète \N comme SQL NULL ; un champ texte vide reste une chaîne vide. Vous examinerez bientôt ces différences.

Les comparaisons SQL comme quantity > 0 testent chaque ligne. AND exige que les deux conditions soient vraies ; OR accepte l’une ou l’autre possibilité. Les parenthèses regroupent les alternatives de statut pour qu’une ligne en attente de quantité zéro ne contourne pas le test de quantité.

Enregistrez un premier filtre avec le modèle de fichier SQL déjà pratiqué :

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

Exécutez le fichier avec la connexion Beeline connue et les options de sortie séparée par tabulations :

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

Lisez le résultat :

cat /home/labex/project/output/conditions.txt

Les lignes de commandes doivent être :

r2 1 paid
r3 1 paid
r4 1 pending
r6 1 paid

r7 est exclue par sa quantité, même si son statut est en attente. r1 est exclue, car le texte stocké contient des espaces et n’est pas exactement 'paid'. Le prix manquant de r3 n’a pas encore été pris en compte par ce filtre. Chaque condition résout un problème précis ; les étapes suivantes ajoutent les éléments manquants.

Distinguer les valeurs manquantes du texte vide

Dans cette étape, vous testerez correctement NULL et normaliserez les valeurs d’affichage sans modifier les lignes stockées.

NULL représente une valeur manquante ou inconnue. Ce n’est ni le texte 'NULL', ni une chaîne vide. Utilisez IS NULL ou IS NOT NULL pour la tester, plutôt que = NULL.

Demandez séparément les identifiants clients manquants et vides. La première instruction sélectionne r2 ; la seconde sélectionne r6 :

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 = '';"

Deux petites fonctions permettent d’afficher clairement ces enregistrements. TRIM(text) retire les espaces aux extrémités d’une chaîne. COALESCE(a,b) renvoie la première valeur non NULL ; il peut donc afficher 'unknown' lorsque l’identifiant est NULL. Il ne remplace pas le texte vide et ne modifie pas la table sous-jacente.

Enregistrez une requête d’examen appliquant ces fonctions aux sept lignes :

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

Exécutez-la :

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

Lisez la transcription :

cat /home/labex/project/output/normalized-values.txt

Repérez trois observations : r1 affiche maintenant paid sans espaces autour, r2 affiche le libellé client unknown (inconnu), et r6 a toujours un champ client vide. r3 possède un prix NULL. Une valeur d’affichage par défaut convient au libellé client manquant dans cette file de révision, mais inventer un prix nul déformerait la commande. Le filtre final exclura plutôt les prix inconnus.

Étiqueter et sélectionner une file de révision

Dans cette étape, vous ajouterez un libellé lisible de statut de paiement et combinerez les contrôles de qualité des lignes dans une requête.

Une expression SQL CASE choisit une valeur à partir de conditions. Elle évalue les branches WHEN dans l’ordre, utilise la valeur THEN correspondante et se rabat sur ELSE si aucune branche ne correspond. Vous étiquetterez les lignes payées Payment received (paiement reçu), les lignes en attente Awaiting payment (en attente de paiement) et les autres statuts Other status (autre statut).

Prévisualisez les libellés sur toutes les lignes avant d’ajouter le filtre de la file de révision :

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;"

Trouvez r5, dont le statut remboursé prend la branche ELSE. r4 et r7 sont étiquetées en attente de paiement. Un libellé de statut décrit le statut ; il ne valide pas à lui seul la quantité ou le prix.

Construisez maintenant la règle complète de la file : quantité positive, prix connu et statut payé normalisé. L’identifiant client source reste inchangé. Dans le résultat de la requête, affichez un libellé client avec COALESCE. Le résultat affiché n’inclut pas de copie séparée de l’identifiant original. Enregistrez la requête :

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

Exécutez-la indépendamment :

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

Lisez le résultat :

cat /home/labex/project/output/filtered-orders.txt

Lignes de commandes attendues :

r1 c1 paid Payment received
r2 unknown paid Payment received
r6  paid Payment received

Le champ client vide affiché pour r6 est intentionnel : il s’agit de texte vide plutôt que de NULL. r1 est incluse après suppression des espaces de son statut. r3 est exclue, car son prix est inconnu, tandis que les lignes en attente, remboursées et de quantité zéro sont hors de cette file. Vous avez défini les lignes prévues sans prétendre que les prix manquants valent zéro.

Modifier une condition et prévoir le résultat

Dans cette étape, vous réutiliserez les mêmes expressions d’affichage et règles de qualité pour une file de commandes en attente.

Avant toute exécution, prévoyez le résultat si seule la condition finale de statut passe à pending. r4 a une quantité positive et un prix connu, tandis que r7 a une quantité zéro. Seule r4 doit rester et son libellé CASE doit être Awaiting payment.

Conservez la première requête intacte et enregistrez un second fichier. Ses expressions sélectionnées et ses contrôles de qualité sont inchangés ; seule la valeur finale de statut dans WHERE diffère :

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

Exécutez-la :

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

Lisez la sortie :

cat /home/labex/project/output/pending-review.txt

Ligne de commande attendue :

r4 c1 pending Awaiting payment

La table n’a pas changé ; la requête a sélectionné un ensemble de lignes différent et l’expression CASE existante a choisi le libellé correspondant. Dans le prochain lab, vous construirez des totaux à partir de lignes correctement sélectionnées, plutôt que de découvrir en même temps les règles de qualité des données et l’agrégation.

Résumé

Vous avez combiné des conditions avec AND, OR et des parenthèses, distingué NULL du texte vide et utilisé TRIM et COALESCE pour un comportement d’affichage explicite. Vous avez ajouté des libellés CASE et défini une file de révision sans inventer de montants ni modifier les données sources.

Un changement d’une condition a produit la file en attente prévue tout en préservant les contrôles de qualité. Vous agrégerez ensuite des lignes de commandes propres et délibérément sélectionnées en indicateurs de vente.