Join Hive Orders with Customer Data

Introduction

The retailer's orders tell you what sold, but the region for each customer lives in a different table. Copying region text into every order would repeat information. Instead, you can connect an order's customer identifier to a customer record when you query the data. This operation is a join.

You will inspect both sides of that relationship, enrich paid orders with regions, explore what happens when a customer cannot be matched, and build a regional revenue report. A join needs a meaningful matching rule: without one, unrelated rows can combine and inflate a report. The prepared customer table has exactly one row per customer identifier, so each matching order has one customer match.

This lab independently prepares the six clean orders, two customers, and a separate missing-match example. Hive and Beeline are ready. You should know SELECT, WHERE, grouping, and decimal sales calculations; join syntax is introduced here. No Java, Python, or files from another lab are required. Use the Terminal.

Find the Matching Key

In this step, you will identify how the two tables describe the same customers. Start in your local workspace:

cd /home/labex/project

Inspect the supplied order and customer records:

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

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

The orders repeat customer_id: a customer may place several orders. The customer table has one record per customer_id, with c1 in East and c2 in West. This identifier is the join key. Equal row positions or similar-looking names are not reliable matching rules.

Read and save the customer lookup through Hive. Beeline's -u chooses the service, -n the username, and -e the SQL statement. TSV output and disabled headers make result rows easy to save; > replaces the named local file:

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

Expect c1 East and c2 West as two tab-separated rows. For order o1, you can now predict the match: its c1 identifier connects to East. For o2, c2 connects to West. This table is often called a dimension or lookup table because it adds descriptive context to the order records.

This exercise provides unique lookup keys. In a real dataset, duplicate customer keys could produce several joined rows per order and inflate revenue; knowing the intended relationship is part of validating a join.

Enrich Paid Orders with INNER JOIN

In this step, you will add a region to each paid order. orders o gives the table a short alias, o; customers c gives the other table alias c. A reference such as o.customer_id identifies which table supplies a column, especially when both tables have the same column name.

INNER JOIN keeps rows with a match on both sides. The explicit ON o.customer_id = c.customer_id condition states the matching rule. Save this complete query with a here-document: the shell writes the lines between the quoted SQL opening marker and final SQL into the file literally:

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

The file-execution option -f runs the saved SQL. Expect four rows:

All four paid orders found one customer. The join added descriptive information without changing their calculated amounts; the amounts still sum to 125.00. An INNER JOIN would drop an order with no customer match. You will observe that case separately so the clean reporting fixture stays unchanged.

Keep an Unmatched Order with LEFT JOIN

In this step, you will compare the two join types on a small independent example. Read its two records:

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

example1 refers to c1, while example2 refers to c_missing, which has no lookup record. Predict the INNER JOIN result before running it:

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

Only example1 should appear, with East. The unmatched order disappeared because INNER JOIN requires a match.

LEFT JOIN keeps every row from the table on its left, even if the right table has no matching key. Columns contributed by the missing right-hand record become NULL. Change only that join choice:

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

Now expect both rows: example1/c1/East and example2/c_missing/NULL. The NULL describes a missing lookup match, not a known region named "NULL". The original customer identifier is retained from the left table, which helps identify the unresolved customer.

Choosing INNER or LEFT JOIN is a business decision. A complete-region report over clean, fully matched data can use INNER JOIN; a data-quality review may need LEFT JOIN to retain unresolved records. The example table is separate from orders, so it does not change the six-row sales fixture or its revenue.

Reconcile a Regional Sales Report

In this step, you will apply the grouping already learned to the enriched records. The join supplies a region, WHERE selects paid orders, and GROUP BY combines those orders by region. Keep the explicit matching condition when adding aggregation:

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

Expect East 70.00 and West 55.00. Together they reconcile to the same 125.00 paid revenue as the unjoined source. The unique customer keys and complete matches mean no paid order was multiplied or lost.

For a small practice change, predict the regional amounts for October 14 alone. Apply the already learned date predicate to the order rows before grouping:

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

The amounts should now be East 20.00 and West 25.00, reconciling to that day's 45.00. The matching rule stayed the same; only the selected order rows changed. Your main regional-sales.sql retains the complete two-day report.

You can now explain both the new descriptive column and the unchanged business totals. Next, date partitions will connect these SQL date conditions to how daily files are organized and registered.

Summary

You matched orders to unique customer keys, used aliases to make the relationship explicit, and compared INNER and LEFT JOIN on a missing match. You then reconciled regional paid-sales totals with the source and changed a date condition without changing the join. Next, you will organize daily data with Hive's partition metadata.