Hadoop 에서 Oracle 이나 MySQL 로 sqoop export 를 돌렸는데 날짜·시각 값이 정확히 9시간 어긋나는 경우가 있다. KST 와 UTC 의 차이다.
원인은 값 자체가 아니라 변환 시점의 기준 시간대다. JDBC 드라이버는 java.sql.Timestamp 를 만들 때 JVM 의 기본 시간대를 쓴다. 맵 태스크가 도는 노드의 JVM 시간대와 DB 세션의 시간대가 다르면, 드라이버가 한 번 변환하고 DB 가 다시 해석하면서 차이가 그대로 남는다.
맵 태스크의 JVM 시간대를 명시해 맞춘다. -D 옵션은 도구 이름 바로 뒤, 다른 인자보다 앞에 와야 한다.
sqoop export \
-Dmapred.child.java.opts="-Duser.timezone=UTC" \
--connect jdbc:oracle:thin:@//db.example.com:1521/ORCLPDB \
--username ${DB_USER} --password-file file:///home/etl/.db.pass \
--table TARGET_TBL \
--export-dir /user/etl/staging/target_tbl
UTC 로 둘지 Asia/Seoul 로 둘지는 원본 데이터가 무엇을 담고 있는지로 정한다. Hive 에 UTC 기준으로 적재해 두었으면 UTC, 로컬 시각 그대로 넣어 두었으면 Asia/Seoul 이다. 모르는 상태로 값을 바꿔 가며 맞추면 서머타임이 있는 지역이나 다른 테이블에서 다시 어긋난다.
확인은 양쪽에서 각각 한다.
# 클러스터 노드
date; timedatectl | grep "Time zone"
-- Oracle
SELECT SESSIONTIMEZONE, DBTIMEZONE FROM dual;
MySQL·MariaDB 계열이면 JDBC URL 에도 시간대를 적을 수 있다. 드라이버 버전에 따라 파라미터 이름이 serverTimezone 과 connectionTimeZone 으로 갈리므로 쓰는 커넥터 문서를 확인한다 (확인 필요).
jdbc:mysql://db.example.com:3306/appdb?serverTimezone=Asia/Seoul
근본적으로는 저장은 UTC 로 통일하고 표시할 때만 변환하는 편이 옮겨 다니는 데이터에서 사고가 적다.
MySQL 은 0000-00-00 이나 0000-00-00 00:00:00 을 날짜 컬럼에 담을 수 있다. 표준 SQL 에는 없는 값이고 java.sql.Date 로도 표현되지 않으므로, 그대로 읽으면 드라이버가 예외를 던진다.
java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date
JDBC URL 파라미터로 처리 방식을 고른다.
| 값 | 동작 |
|---|---|
exception |
예외를 던진다. 최근 커넥터의 기본값 |
convertToNull |
NULL 로 바꿔 돌려준다 |
round |
0001-01-01 로 반올림한다 |
Sqoop 으로 읽어 올 때는 대개 convertToNull 이 맞다.
sqoop import \
--connect "jdbc:mysql://db.example.com:3306/appdb?zeroDateTimeBehavior=convertToNull" \
--username ${DB_USER} --password-file file:///home/etl/.db.pass \
--table orders \
--target-dir /user/etl/staging/orders
round 는 없는 날짜를 실제 날짜처럼 만들어 버려서, 나중에 그 값을 보고 "1년 1월 1일 주문"이라고 오해하게 된다. 결측을 결측으로 남기는 convertToNull 쪽이 안전하다.
URL 에 & 가 들어가므로 셸에서는 반드시 따옴표로 감싼다. 감싸지 않으면 셸이 백그라운드 실행으로 해석해 뒤쪽 파라미터가 통째로 잘린다.
값을 근본적으로 없애려면 원본 쪽 sql_mode 에 NO_ZERO_DATE 와 NO_ZERO_IN_DATE 를 넣어 새 데이터가 더 들어오지 못하게 막는다. 이미 들어간 값은 따로 정리해야 한다.