Hive の注文と顧客データを結合する

はじめに

小売業者の注文から何が売れたかは分かりますが、顧客ごとの地域は別のテーブルにあります。すべての注文に地域の文字列をコピーすると、情報が繰り返されます。代わりに、クエリ時に注文の顧客 ID と顧客レコードをつなぐことができます。この操作が結合（JOIN）です。

関係の両側を調べ、支払い済み注文に地域を加え、顧客が一致しない場合を確かめ、地域別売上レポートを作ります。結合には意味のある一致ルールが必要です。ルールがないと無関係な行が組み合わさり、レポートの値が水増しされかねません。準備済みの顧客テーブルでは、顧客 ID ごとにちょうど1行なので、一致する各注文には顧客が1件対応します。

この実験では、正常な6注文、2顧客、一致しない顧客の独立した例を準備します。Hive と Beeline は使用可能です。SELECT、WHERE、グループ化、十進数の売上計算は既習とし、結合の構文はここで説明します。Java、Python、ほかの実験のファイルは不要です。ターミナルを使用してください。

一致させるキーを見つける

このステップでは、2つのテーブルが同じ顧客をどう表しているか確認します。ローカルの作業ディレクトリから始めます。

cd /home/labex/project

提供された注文と顧客の記録を確認します。

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

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

1人の顧客が複数回注文するため、注文側では customer_id が繰り返されます。顧客テーブルは customer_id ごとに1件で、c1 は East、c2 は West です。この ID が結合キーです。同じ行番号や似た名前は、信頼できる一致ルールではありません。

Hive を通じて顧客の参照データを読み、保存します。Beeline の -u はサービス、-n はユーザー名、-e は SQL 文を選びます。TSV 出力とヘッダーの無効化で結果行を保存しやすくし、> で指定したローカルファイルを置き換えます。

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

タブ区切りの2行で、c1 East と c2 West が期待されます。注文 o1 の一致先を予想できるようになりました。c1 が East につながります。o2 は c2 から West につながります。この表は、注文記録に説明のための情報を加えるので、ディメンションまたは参照テーブルと呼ばれることがあります。

この演習の参照キーは一意です。実データで顧客キーが重複すると、注文ごとに複数の結合行ができ、売上が膨らむ可能性があります。意図した関係を把握することも結合の検証の一部です。

INNER JOIN で支払い済み注文に情報を加える

このステップでは、各支払い済み注文に地域を加えます。orders o はテーブルに短い別名 o を付け、customers c はもう一方に c を付けます。o.customer_id のような参照で、どちらの表の列かを示せます。特に両方に同名の列がある場合に役立ちます。

INNER JOIN は両側で一致する行を残します。明示した ON o.customer_id = c.customer_id が一致ルールです。完全なクエリをヒアドキュメントで保存します。引用符付きの開始マーカー SQL から最後の SQL までの行を、シェルがそのままファイルに書き込みます。

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

ファイル実行オプション -f が保存した SQL を実行します。期待されるのは4行です。

4つの支払い済み注文すべてに、顧客が1件見つかりました。結合は説明用の情報を加えましたが、計算した金額は変わらず、合計は125.00です。顧客が一致しない注文は INNER JOIN では除外されます。正常なレポート用データを変えず、独立した例でその場合を調べます。

LEFT JOIN で一致しない注文を残す

このステップでは、小さな独立した例で2種類の結合を比べます。2件の記録を読みます。

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

example1 は c1 を参照し、example2 は参照先の記録がない c_missing を参照します。実行前に INNER JOIN の結果を予想してください。

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

East の example1 だけが表示されるはずです。INNER JOIN は一致を必要とするため、一致しない注文はなくなりました。

LEFT JOIN は、右側にキーが一致する行がなくても、左側の表の全行を残します。不足する右側の記録から来る列は NULL になります。結合の種類だけを変えます。

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

今度は example1/c1/East と example2/c_missing/NULL の両方が期待されます。NULL は、一致する参照データがないことを示し、「NULL」という既知の地域を意味するのではありません。元の顧客 ID は左の表から残るため、未解決の顧客を特定するのに役立ちます。

INNER と LEFT の選択は業務上の判断です。正常ですべて一致するデータの地域別レポートなら INNER JOIN を使えます。データ品質の確認では、未解決の記録を残すため LEFT JOIN が必要かもしれません。この例のテーブルは orders とは別なので、6行の売上データやその売上を変えません。

地域別売上レポートを照合する

このステップでは、情報を追加した記録に既習のグループ化を適用します。結合が地域を加え、WHERE が支払い済み注文を選び、GROUP BY がその注文を地域別にまとめます。集計を加えても、明示した一致条件を保ってください。

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

East 70.00、West 55.00 が期待されます。合計は、結合前の入力と同じ支払い済み売上125.00に一致します。顧客キーが一意ですべて一致するため、支払い済み注文が増幅されたり失われたりしませんでした。

小さな変更の練習として、10月14日だけの地域別金額を予想してください。既習の日付条件を、グループ化前の注文行に適用します。

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

今度は East 20.00、West 25.00 となり、その日の45.00と一致するはずです。一致ルールは同じまま、選択する注文行だけが変わりました。主となる regional-sales.sql は、完全な2日分のレポートとして残っています。

追加した説明列と、変わらない業務上の合計の両方を説明できるようになりました。次は、日付パーティションを使い、SQL の日付条件を、日次ファイルの整理と登録に結び付けます。

まとめ

一意の顧客キーで注文を対応させ、別名で関係を明示し、一致しない例で INNER JOIN と LEFT JOIN を比べました。地域別の支払い済み売上を入力と照合し、結合を変えずに日付条件を変えました。次は Hive のパーティションメタデータで日次データを整理します。