Фильтрация строк Hive и обработка пропущенных значений

Введение

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

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

Здесь используется отдельный пример качества данных из семи строк, а не чистые данные продаж предыдущих работ. Сначала вы объедините условия, затем изучите пропуски и простые функции, добавите метки через CASE и измените одно условие для проверки понимания. Вы уже знаете SELECT, WHERE, ORDER BY и SQL-файлы. Все службы и таблицы подготовлены независимо; используйте терминал.

Осознанно объединяйте условия строк

На этом шаге вы объедините проверку количества с вариантами статуса и поймёте важность скобок.

Начните в своём проекте:

cd /home/labex/project

Прочитайте подготовленный CSV перед запросом:

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

После заголовка идут семь записей заказов. Заметьте пробелы вокруг paid у r1, маркеры \N у r2 и r3, пустое поле клиента у r6 и нулевое количество у r7. Подготовленная таблица интерпретирует \N как SQL NULL; пустое текстовое поле остаётся пустой строкой. Скоро вы изучите эти различия.

Сравнения SQL, например quantity > 0, проверяют каждую строку. AND требует истинности обоих условий; OR принимает любой из вариантов. Скобки объединяют варианты статуса, чтобы ожидающий заказ с нулевым количеством не обошёл проверку количества.

Сохраните первый фильтр, используя уже отработанный способ SQL-файла:

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

Выполните файл с известным подключением Beeline и параметрами вывода с табуляцией:

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

Прочитайте результат:

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

Строки заказов должны быть такими:

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

r7 исключён из-за количества, хотя его статус pending. r1 исключён, поскольку сохранённый текст содержит пробелы и не равен в точности 'paid'. Неизвестная цена r3 этим фильтром пока не обработана. Каждое условие решает определённую проблему; следующие шаги добавят недостающие части.

Отличайте пропуски от пустого текста

На этом шаге вы правильно проверите NULL и нормализуете отображаемые значения, не меняя сохранённые строки.

NULL представляет отсутствующее или неизвестное значение. Это не текст 'NULL' и не пустая строка. Для проверки используйте IS NULL или IS NOT NULL, а не = NULL.

Запросите отсутствующие и пустые идентификаторы клиентов отдельно. Первый оператор выбирает r2, второй — 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 = '';"

Две небольшие функции помогают ясно отобразить записи. TRIM(text) удаляет пробелы по краям строки. COALESCE(a,b) возвращает первое значение, не равное NULL, поэтому может показать 'unknown', когда идентификатор равен NULL. Она не заменяет пустой текст и не меняет исходную таблицу.

Сохраните запрос просмотра, применяющий эти функции ко всем семи строкам:

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

Выполните его:

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

Прочитайте протокол:

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

Найдите три наблюдения: r1 теперь показывает paid без пробелов, r2 — метку клиента unknown, а у r6 поле клиента всё ещё пустое. У r3 цена равна NULL. Значение отображения по умолчанию подходит для отсутствующей метки клиента в этой очереди проверки, но выдуманная нулевая цена исказила бы заказ. Вместо этого итоговый фильтр исключит неизвестные цены.

Добавьте метки и выберите очередь проверки

На этом шаге вы добавите понятную метку статуса оплаты и объедините проверки качества строк в один запрос.

Выражение SQL CASE выбирает значение по условиям. Оно проверяет ветви WHEN по порядку, использует значение соответствующей THEN и обращается к ELSE, если ни одна ветвь не подошла. Вы присвоите оплаченным строкам метку Payment received (оплата получена), ожидающим — Awaiting payment (ожидание оплаты), а остальным статусам — Other status (другой статус).

Просмотрите метки всех строк перед добавлением фильтра очереди:

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

Найдите r5, чей статус refunded попадает в ветвь ELSE. r4 и r7 помечены как ожидающие оплаты. Метка описывает статус, но сама по себе не проверяет количество или цену.

Теперь составьте полное правило очереди: положительное количество, известная цена и нормализованный статус paid. Исходный идентификатор клиента остаётся неизменным. В результате запроса показывайте метку клиента через COALESCE. Показанный результат не включает отдельную копию исходного идентификатора. Сохраните запрос:

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

Выполните его независимо:

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

Прочитайте результат:

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

Ожидаемые строки заказов:

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

Пустое отображаемое поле клиента у r6 намеренно: это пустой текст, а не NULL. r1 включён после удаления пробелов статуса. r3 исключён из-за неизвестной цены, а ожидающие, возвращённые заказы и строки с нулевым количеством находятся вне очереди. Вы определили нужные строки, не притворяясь, что отсутствующие цены равны нулю.

Измените одно условие и предположите результат

На этом шаге вы повторно используете те же выражения отображения и правила качества для очереди ожидающих заказов.

Перед выполнением предположите результат, если только последнее условие статуса изменится на pending. У r4 положительное количество и известная цена, а у r7 количество равно нулю. Должен остаться только r4, и его метка CASE должна быть Awaiting payment.

Сохраните первый запрос нетронутым и создайте второй файл. Выбранные выражения и проверки качества не меняются; отличается лишь последнее значение статуса WHERE:

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

Выполните его:

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

Прочитайте вывод:

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

Ожидаемая строка заказа:

r4 c1 pending Awaiting payment

Таблица не изменилась; запрос выбрал другой набор строк, а существующее выражение CASE — соответствующую метку. В следующей работе вы построите итоги по правильно выбранным строкам, вместо одновременного знакомства с качеством данных и агрегацией.

Итоги

Вы объединили условия с AND, OR и скобками, отличили NULL от пустого текста и использовали TRIM и COALESCE для явного поведения отображения. Вы добавили метки CASE и определили очередь проверки, не выдумывая денежные значения и не меняя источник.

Изменение одного условия дало предсказанную очередь ожидающих заказов с сохранением проверок качества. Далее вы агрегируете чистые, осознанно выбранные строки заказов в показатели продаж.