Соедините заказы Hive с данными клиентов

Введение

Заказы компании сообщают, что продано, но регион каждого клиента находится в другой таблице. Копирование текста региона в каждый заказ повторяло бы информацию. Вместо этого при запросе данных можно связать идентификатор клиента заказа с записью клиента. Эта операция называется соединением (join).

Вы изучите обе стороны этой связи, дополните оплаченные заказы регионами, увидите поведение при отсутствии клиента и создадите отчёт о региональной выручке. Соединению нужно осмысленное правило соответствия: без него несвязанные строки могут объединиться и завысить отчёт. В подготовленной таблице клиентов ровно одна строка на идентификатор, поэтому у каждого подходящего заказа одно совпадение клиента.

Для этой работы независимо подготовлены шесть чистых заказов, два клиента и отдельный пример отсутствующего совпадения. Hive и Beeline готовы. Вам следует знать SELECT, WHERE, группировку и десятичные расчёты продаж; синтаксис соединений вводится здесь. Java, Python и файлы другой работы не требуются. Используйте терминал.

Найдите ключ соответствия

На этом шаге вы определите, как две таблицы описывают одних и тех же клиентов. Начните в локальном рабочем каталоге:

cd /home/labex/project

Изучите предоставленные записи заказов и клиентов:

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

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

В заказах customer_id повторяется: клиент может сделать несколько заказов. В таблице клиентов по одной записи на customer_id: c1 относится к East, а c2 — к West. Этот идентификатор — ключ соединения. Одинаковые позиции строк и похожие имена не являются надёжными правилами соответствия.

Прочитайте и сохраните справочник клиентов через Hive. -u Beeline выбирает службу, -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

Ожидаются 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 задаёт правило соответствия. Сохраните полный запрос через here-документ: оболочка буквально запишет строки между начальным маркером 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. Ожидаются четыре строки:

Все четыре оплаченных заказа нашли одного клиента. Соединение добавило описательную информацию без изменения рассчитанных сумм; их сумма по-прежнему 125.00. INNER JOIN исключил бы заказ без совпадения клиента. Вы увидите этот случай отдельно, чтобы чистые данные отчётности остались неизменными.

Сохраните заказ без совпадения с LEFT JOIN

На этом шаге вы сравните два вида соединения на небольшом независимом примере. Прочитайте его две записи:

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

Должен появиться только example1 с East. Заказ без совпадения исчез, поскольку 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». Исходный идентификатор клиента сохранён из левой таблицы, что помогает определить неразрешённого клиента.

Выбор INNER или LEFT JOIN — бизнес-решение. Отчёт с полными регионами по чистым, полностью сопоставленным данным может использовать INNER JOIN; проверке качества данных может потребоваться LEFT JOIN для сохранения неразрешённых записей. Таблица примера отделена от orders, поэтому не меняет шесть записей продаж и их выручку.

Сверьте региональный отчёт продаж

На этом шаге вы примените уже изученную группировку к обогащённым записям. Соединение даёт регион, 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 оплаченной выручки, что и источник без соединения. Уникальность ключей клиентов и полные совпадения означают, что ни один оплаченный заказ не размножился и не потерялся.

Для небольшого упражнения предположите региональные суммы только за 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 сохраняет полный отчёт за два дня.

Теперь вы можете объяснить и новый описательный столбец, и неизменные бизнес-итоги. Далее разделы по датам свяжут эти условия дат SQL с организацией и регистрацией ежедневных файлов.

Итоги

Вы сопоставили заказы с уникальными ключами клиентов, использовали псевдонимы для явного указания связи и сравнили INNER и LEFT JOIN при отсутствии совпадения. Затем вы сверили региональные итоги оплаченных продаж с источником и изменили условие даты, не меняя соединение. Далее вы организуете ежедневные данные с помощью метаданных разделов Hive.