데이터 이관·검증 쿼리

2. 핵심 원리

2.1 검증의 두 시점

같은 데이터를 두 번 확인합니다. 시점마다 쓰는 쿼리가 다릅니다.

이관 전에 걸러 두면 이관 중 오류로 멈추는 일이 줄고, 오류 행을 어떻게 처리할지 미리 정할 수 있습니다. 이관 후 대사는 처리 결과가 원천과 맞는지 마지막으로 확인하는 관문입니다. 이관 전 점검에서 나온 오류 행이 신 표에 없는 것이 정상이라는 점도 함께 기억합니다.

2.2 대사는 큰 것에서 작은 것으로

대사는 값싼 확인부터 합니다. 한 번의 집계로 끝나는 확인이 먼저이고, 행 단위 비교는 마지막입니다.

1 단계에서 어긋나면 3 단계 이후를 볼 필요 없이 원인부터 찾습니다. 양쪽 값이 같아 보여도 단계 1, 2 만 통과하고 끝내지 말고 3 단계의 누락 행 검사까지는 꼭 합니다.

2.3 차이 행 찾기: 양방향 EXCEPT

EXCEPT 는 앞 결과에서 뒤 결과와 같은 행을 뺍니다. 방향이 있으므로 구에서 신을 빼면 누락, 신에서 구를 빼면 초과가 나옵니다. 한쪽만 보면 반대쪽 오류를 놓치므로 항상 양방향으로 확인합니다.

키 컬럼만 비교하면 누락과 초과를 찾고, 컬럼 여러 개를 함께 비교하면 값이 다른 행도 찾습니다. 다만 EXCEPT 는 어떤 컬럼이 달랐는지는 알려 주지 않으므로 값이 다른 행은 키 기준 조인으로 다시 확인합니다. MySQL 에는 FULL OUTER JOIN 이 없어서 두 번의 EXCEPT 나 LEFT JOIN 두 번을 씁니다.

2.4 NULL 비교는 = 로 안 잡힌다

a = b 와 a <> b 는 한쪽이 NULL 이면 결과가 알 수 없음이라 WHERE 에서 거짓처럼 취급됩니다. 그래서 새 값이 NULL 로 바뀐 행이 <> 비교에서 조용히 빠집니다. 두 값이 같다는 뜻으로 양쪽 NULL 도 같다고 보려면 조건을 넓힙니다.

• a = b OR (a IS NULL AND b IS NULL) 로 같음을 정의하고 그 반대를 불일치로 봅니다.
• COALESCE(a, '~') = COALESCE(b, '~') 처럼 자리 표시 값으로 바꿉니다. 실제 데이터에 없는 값이어야 합니다.
• MySQL 은 a <=> b 가 NULL 끼리 같다고 봅니다.
• 표준 SQL 의 a IS DISTINCT FROM b 는 같은 뜻을 한 번에 씁니다. MSSQL 은 2022 부터 지원하고 MySQL 에는 없어 <=> 를 씁니다. Oracle 은 버전별로 확인합니다.

주의

WHERE o.col <> n.col 은 값이 NULL 로 사라진 행을 찾지 못합니다. 대사에서는 이런 행이 가장 위험한 누락이므로 NULL 안전 비교를 기본으로 씁니다.

2.5 안전 변환 함수

이관 전에 "변환하면 오류가 나는가" 를 조회로 알아내는 도구입니다. 실패하면 오류로 문장이 멈추는 CAST 대신 실패 시 NULL 을 주거나 성공 여부를 알려 줍니다.

Oracle 은 12c 릴리스 2 부터 위 두 가지가 모두 됩니다. MySQL 에는 안전 변환 함수가 없어서 숫자는 정규식으로, 날짜는 STR_TO_DATE 가 NULL 을 주는지로 검사합니다. STR_TO_DATE 는 잘못된 날짜에 NULL 과 경고를 주지만 sql_mode 에 따라 오류가 나기도 합니다.

2.6 해시로 행 전체를 대사한다

컬럼이 많거나 행이 수억 건이면 컬럼별 비교보다 행 전체를 이어 붙여 해시 하나로 비교하는 방식이 빠릅니다. 양쪽에서 같은 규칙으로 문자열을 만들어 해시를 뽑고 키로 맞물려 다른 것만 봅니다.

해시는 규칙이 조금만 어긋나도 모두 다르다고 나옵니다. 구분자를 값에 나오지 않는 문자로 통일하고, 날짜와 숫자는 같은 문자 형식으로 맞추고, NULL 은 빈 문자열과 구분되는 자리 표시로 처리합니다. 해시가 다른 행이 나오면 그 행만 컬럼별로 다시 비교합니다.