소개
소매업체는 이제 파일을 테이블에 연결할 수 있지만 읽을 수 있는 테이블이라고 해서 바로 보고서에 사용할 수 있는 것은 아닙니다. 상태 값 일부에는 불필요한 공백이 있고, 고객 식별자 하나는 누락되었으며 다른 하나는 빈 텍스트이고, 가격 하나는 알 수 없으며 수량 하나는 0입니다. 이런 차이를 무시하면 쿼리가 잘못된 행을 선택하거나 오해할 만한 값을 표시할 수 있습니다.
수량이 양수이고 가격을 아는 결제 완료 행을 선택하고, 상태 텍스트를 정규화하며, 고객 식별자가 누락되면 눈에 보이는 기본값을 표시하는 작은 검토 목록을 만듭니다. 누락된 가격은 0으로 바꾸지 않고 제외합니다. 알 수 없는 고객이라는 표시를 넣어도 실제 고객 기록이 생기거나 원본 데이터가 바뀌지는 않습니다.
이 실습은 이전의 정상 판매 데이터와 별개인 일곱 행의 데이터 품질 예제를 사용합니다. 먼저 조건을 결합하고, 누락값과 간단한 함수를 살펴본 뒤 CASE로 표시 이름을 추가하며, 조건 하나를 바꾸어 이해도를 확인합니다. SELECT, WHERE, ORDER BY와 SQL 파일은 이미 배웠습니다. 모든 서비스와 테이블 데이터는 독립적으로 준비됩니다. 터미널을 사용하세요.
행 조건을 의도에 맞게 결합하기
이 단계에서는 수량 검사와 여러 상태 조건을 결합하고 괄호가 중요한 이유를 이해합니다.
프로젝트에서 시작합니다.
cd /home/labex/project
조회하기 전에 준비된 CSV를 읽습니다.
cat /home/labex/project/data/quality-orders.csv
헤더 뒤에 업무 행 일곱 개가 있습니다. r1의 paid 주변 공백, r2와 r3의 \N 표시, r6의 빈 고객 필드, r7의 수량 0을 확인하세요. 준비된 테이블은 \N을 SQL NULL로 해석하고 빈 텍스트 필드는 빈 문자열로 유지합니다. 이 차이는 곧 자세히 살펴봅니다.
quantity > 0 같은 SQL 비교는 각 행을 검사합니다. AND는 두 조건이 모두 참이어야 하고 OR는 둘 중 하나가 참이면 됩니다. 괄호로 상태 선택지를 묶으면 수량이 0인 대기 주문이 수량 검사를 우회하지 못합니다.
이미 연습한 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'도, 빈 문자열도 아닙니다. = NULL 대신 IS NULL 또는 IS NOT 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이 아닌 첫 값을 반환하므로 식별자가 NULL일 때 'unknown'을 표시할 수 있습니다. 빈 텍스트는 바꾸지 않으며 실제 테이블도 수정하지 않습니다.
일곱 행 모두에 이 함수를 적용하는 확인용 쿼리를 저장합니다.
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입니다. 이 검토 목록에서 누락된 고객 표시 값에 기본값을 주는 것은 적절하지만 가격을 임의로 0으로 만들면 주문을 잘못 표현합니다. 최종 필터는 대신 알 수 없는 가격을 제외합니다.
표시 이름을 붙이고 검토 목록 선택하기
이 단계에서는 읽기 쉬운 결제 상태 이름을 추가하고 행 품질 검사를 하나의 쿼리에 결합합니다.
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;"
환불 상태라 ELSE 분기로 가는 r5를 찾으세요. r4와 r7은 결제 대기로 표시됩니다. 상태 이름은 상태를 설명할 뿐 그 자체로 수량이나 가격을 검증하지는 않습니다.
이제 목록의 전체 규칙인 양수 수량, 알려진 가격, 정규화된 결제 완료 상태를 구성합니다. 원본 고객 식별자는 바뀌지 않습니다. 쿼리 결과에서는 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은 가격을 알 수 없어 제외되고, 대기, 환불, 수량 0 행도 이 목록 밖에 있습니다. 누락된 가격이 0인 것처럼 처리하지 않고 의도한 행을 정의했습니다.
조건 하나를 바꾸고 결과 예상하기
이 단계에서는 같은 표시 표현식과 품질 규칙을 대기 주문 목록에 재사용합니다.
실행하기 전에 마지막 상태 조건만 pending으로 바꾸면 결과가 어떻게 될지 예상해 보세요. r4는 수량이 양수이고 가격을 알지만 r7의 수량은 0입니다. 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 표시 이름을 추가하고 금액을 임의로 만들거나 원본 데이터를 바꾸지 않으면서 검토 목록을 정의했습니다.
조건 하나를 바꾸어 품질 검사를 유지하면서 예상한 대기 주문 목록을 얻었습니다. 다음에는 정상이며 의도적으로 선택한 주문 행을 판매 지표로 집계합니다.
