Joindre les commandes Hive aux données clients

Introduction

Les commandes du commerçant indiquent ce qui a été vendu, mais la région de chaque client se trouve dans une autre table. Copier le texte de région dans chaque commande répéterait l’information. Vous pouvez plutôt relier l’identifiant client d’une commande à un enregistrement client lorsque vous interrogez les données. Cette opération est une jointure.

Vous examinerez les deux côtés de cette relation, enrichirez les commandes payées avec les régions, explorerez ce qui se passe lorsqu’un client n’a pas de correspondance et construirez un rapport de revenus régionaux. Une jointure nécessite une règle de correspondance pertinente : sans elle, des lignes sans lien peuvent se combiner et gonfler un rapport. La table clients préparée possède exactement une ligne par identifiant client ; chaque commande correspondante trouve donc un seul client.

Ce lab prépare indépendamment les six commandes propres, deux clients et un exemple distinct sans correspondance. Hive et Beeline sont prêts. Vous devez connaître SELECT, WHERE, le regroupement et les calculs décimaux de ventes ; la syntaxe de jointure est présentée ici. Aucun Java, Python ou fichier d’un autre lab n’est nécessaire. Utilisez le Terminal.

Trouver la clé de correspondance

Dans cette étape, vous identifierez comment les deux tables décrivent les mêmes clients. Commencez dans votre espace de travail local :

cd /home/labex/project

Examinez les enregistrements de commandes et de clients fournis :

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

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

Les commandes répètent customer_id : un client peut passer plusieurs commandes. La table clients possède un enregistrement par customer_id, avec c1 dans East (Est) et c2 dans West (Ouest). Cet identifiant est la clé de jointure. Des positions de lignes identiques ou des noms qui se ressemblent ne sont pas des règles de correspondance fiables.

Lisez et enregistrez la table de référence clients via Hive. -u de Beeline choisit le service, -n le nom d’utilisateur et -e l’instruction SQL. La sortie TSV sans en-tête facilite l’enregistrement des lignes de résultats ; > remplace le fichier local nommé :

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

Vous devez obtenir c1 East et c2 West sous forme de deux lignes séparées par des tabulations. Pour la commande o1, vous pouvez maintenant prévoir la correspondance : son identifiant c1 la relie à East. Pour o2, c2 la relie à West. Cette table est souvent appelée dimension ou table de référence, car elle ajoute un contexte descriptif aux enregistrements de commandes.

Cet exercice fournit des clés de référence uniques. Dans un véritable jeu de données, des clés clients dupliquées pourraient produire plusieurs lignes jointes par commande et gonfler le revenu ; connaître la relation prévue fait partie de la validation d’une jointure.

Enrichir les commandes payées avec INNER JOIN

Dans cette étape, vous ajouterez une région à chaque commande payée. orders o donne à la table un alias court, o ; customers c donne à l’autre table l’alias c. Une référence comme o.customer_id identifie la table fournissant une colonne, particulièrement lorsque les deux tables ont le même nom de colonne.

INNER JOIN conserve les lignes avec une correspondance des deux côtés. La condition explicite ON o.customer_id = c.customer_id énonce la règle de correspondance. Enregistrez cette requête complète avec un here-document : le shell écrit littéralement dans le fichier les lignes entre le marqueur d’ouverture SQL entre apostrophes et le SQL final :

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

L’option d’exécution de fichier -f lance le SQL enregistré. Vous devez obtenir quatre lignes :

Les quatre commandes payées ont trouvé un client. La jointure a ajouté des informations descriptives sans changer leurs montants calculés ; leur somme reste 125.00. Un INNER JOIN supprimerait une commande sans correspondance client. Vous observerez ce cas séparément pour conserver les données propres du rapport inchangées.

Conserver une commande sans correspondance avec LEFT JOIN

Dans cette étape, vous comparerez les deux types de jointures sur un petit exemple indépendant. Lisez ses deux enregistrements :

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

example1 fait référence à c1, tandis que example2 fait référence à c_missing, qui n’a pas d’enregistrement de référence. Prévoyez le résultat d’INNER JOIN avant de l’exécuter :

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

Seule example1 doit apparaître, avec East. La commande sans correspondance a disparu, car INNER JOIN exige une correspondance.

LEFT JOIN conserve chaque ligne de la table située à sa gauche, même si la table de droite n’a pas de clé correspondante. Les colonnes apportées par l’enregistrement de droite manquant deviennent NULL. Changez uniquement ce choix de jointure :

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

Vous devez maintenant obtenir les deux lignes : example1/c1/East et example2/c_missing/NULL. Le NULL décrit une correspondance de référence manquante, et non une région connue nommée « NULL ». L’identifiant client original est conservé depuis la table de gauche, ce qui aide à identifier le client non résolu.

Choisir INNER JOIN ou LEFT JOIN est une décision métier. Un rapport par région sur des données propres et entièrement associées peut utiliser INNER JOIN ; une révision de qualité peut avoir besoin de LEFT JOIN pour conserver les enregistrements non résolus. La table d’exemple est séparée de orders ; elle ne modifie donc ni les six lignes des données de vente ni leur revenu.

Rapprocher un rapport régional des ventes

Dans cette étape, vous appliquerez le regroupement déjà appris aux enregistrements enrichis. La jointure fournit une région, WHERE sélectionne les commandes payées et GROUP BY combine ces commandes par région. Conservez la condition explicite de correspondance en ajoutant l’agrégation :

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

Vous devez obtenir East 70.00 et West 55.00. Ensemble, ils correspondent au même revenu payé de 125.00 que la source sans jointure. Les clés clients uniques et les correspondances complètes signifient qu’aucune commande payée n’a été multipliée ou perdue.

Pour un petit changement d’exercice, prévoyez les montants régionaux du seul 14 octobre. Appliquez le prédicat de date déjà appris aux lignes de commandes avant le regroupement :

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

Les montants doivent maintenant être East 20.00 et West 25.00, correspondant aux 45.00 de cette journée. La règle de correspondance est restée la même ; seules les lignes de commandes sélectionnées ont changé. Votre fichier principal regional-sales.sql conserve le rapport complet sur deux jours.

Vous savez maintenant expliquer à la fois la nouvelle colonne descriptive et les totaux métier inchangés. Les partitions de date relieront ensuite ces conditions de date SQL à l’organisation et à l’enregistrement des fichiers quotidiens.

Résumé

Vous avez associé les commandes à des clés clients uniques, utilisé des alias pour rendre la relation explicite et comparé INNER JOIN et LEFT JOIN en l’absence de correspondance. Vous avez ensuite rapproché les totaux régionaux des ventes payées avec la source et changé une condition de date sans modifier la jointure. Vous organiserez ensuite les données quotidiennes avec les métadonnées de partition de Hive.