데이터 전환과 ETL

데이터 전환은 쓰고 있던 시스템의 데이터를 새 시스템에서 쓸 수 있는 꼴로 바꾸어 옮기는 일입니다. 추출(Extraction)·변환(Transformation)·적재(Loading) 세 걸음으로 이루어져 ETL이라 부르고, 자료에 따라 데이터 이행(data migration)·데이터 이관이라고도 부릅니다.

새 시스템을 잘 만들어도 데이터베이스가 비어 있으면 업무가 돌지 않습니다. 10년 쓴 학사 시스템을 바꾸면 그 안에 쌓인 학생과 성적을 새 데이터베이스에 채워 넣어야 하고, 이렇게 시스템을 열기 전에 데이터를 채우는 일을 초기 데이터 구축이라 합니다. 그런데 옛 표의 열과 새 표의 열이 그대로 맞는 일은 드뭅니다. 학년을 '3학년'이라는 글자로 두었던 시스템에서 학년을 숫자로 받는 시스템으로 옮기려면 옮기는 중간에 값을 바꿔야 합니다. 아래 그림은 학생 3건이 옛 시스템에서 새 데이터베이스까지 건너가는 길을 보여 줍니다.

1추출·변환·적재 — ETL 세 걸음

전환은 세 걸음입니다. 걸음마다 다루는 데이터가 어디에 있는지가 다릅니다.

데이터를 꺼내 오는 쪽인 쓰던 시스템을 원천 시스템 또는 As-Is 시스템, 데이터를 받는 쪽인 새 시스템을 목적 시스템 또는 To-Be 시스템이라 부릅니다.

변환에서 어느 열을 어느 열로, 어떤 규칙으로 바꿀지 미리 적어 둔 문서를 매핑 정의서라 합니다. 위 표의 '3학년' → 3, 컴퓨터 → CS가 매핑 정의서에 적는 규칙입니다. 전환 프로그램은 이 문서를 보고 만듭니다.

2전환 절차와 계획서

전환은 하루 만에 끝내는 일이 아니라 다음 차례로 진행합니다.

데이터 전환 계획서에는 전환의 목표와 대상·범위, 원천과 목적 시스템의 환경 구성, 조직과 역할, 일정, 전환 방안, 데이터 정비 방안을 적습니다. 전환이 중간에 실패할 때를 대비한 비상 계획과 데이터 복구 대책도 함께 적습니다.

3한 번에 옮기나 나누어 옮기나

전환 방안에서 정하는 것 가운데 하나가 '얼마에 나누어 옮기나'입니다. 두 방식은 업무를 멈추는 시간과 위험을 맞바꿉니다.

자료에 따라 빅뱅 방식을 일괄 전환, 단계적 방식을 점진적 전환이라고도 부릅니다.

예제

옛 학사 시스템에 학생 3건이 있습니다. 학년이 '3학년'처럼 글자이고, 학과는 코드가 아니라 이름으로 들어 있습니다. 블록마다 처음 데이터에서 시작합니다 — 앞 블록이 만들거나 바꾼 것은 남아 있지 않습니다.

새 학생 표는 학년을 숫자로, 학과를 학과코드로 받습니다. 매핑 정의서대로 학년에서 숫자만 떼어 내고, 학과명을 학과 표와 맞춰 학과코드로 바꾼 뒤 적재합니다.

CREATE TABLE 구학생 (
  학번 NUMBER,
  이름 VARCHAR2(10 CHAR),
  학년 VARCHAR2(10 CHAR),
  학과명 VARCHAR2(10 CHAR)
);
INSERT INTO 구학생 VALUES
  (201, '한지우', '3학년',
    '컴퓨터'),
  (202, '오세진', '1학년',
    '전자'),
  (203, '류지호', '2학년',
    '기계');
INSERT INTO 학생
SELECT G.학번, G.이름,
  TO_NUMBER(
    SUBSTR(G.학년, 1, 1)),
  D.학과코드
FROM 구학생 G
  JOIN 학과 D
  ON D.학과명 = G.학과명;
SELECT 학번, 이름, 학년,
  학과코드
FROM 학생
WHERE 학번 >= 201
ORDER BY 학번;

3건을 넣었는데 2건만 들어왔습니다. 학과 표에는 기계 학과가 없어서 203번은 바꿀 코드를 찾지 못하고 조인에서 빠졌습니다. 전환 프로그램은 오류 없이 끝났고 로그에도 실패가 남지 않습니다 — 들어간 건수와 나온 건수를 세어 맞춰 보지 않으면 모릅니다. 그래서 전환 뒤에는 검증을 따로 합니다.

시험 포인트

• 필기: ETL은 추출 → 변환 → 적재 차례입니다. 걸음을 섞어 놓고 차례를 고르게 하거나, 하는 일을 보고 어느 걸음인지 묻습니다. 변환 규칙을 적는 문서는 매핑 정의서, 전환의 목표·대상·일정·비상 계획을 적는 문서는 데이터 전환 계획서입니다. 빅뱅 방식은 한 번에 옮겨 멈추는 시간이 한 번뿐이지만 실패하면 전체를 되돌리고, 단계적 방식은 나누어 옮겨 위험은 작지만 두 시스템을 함께 쓰는 동안 비용이 더 듭니다.
• 실기: 세 걸음의 이름을 한글 이름과 영문 원어로 씁니다 — 추출(Extraction), 변환(Transformation), 적재(Loading). 세 걸음을 묶어 부르는 약어는 ETL입니다.

확인 문제

1. 전국 500개 지점을 쓰는 보험사가 회원 데이터를 새 시스템으로 옮깁니다. 지점 업무를 하루도 멈출 수 없어 수도권 지점부터 한 달에 100개씩 옮기기로 했습니다. 이 방식을 고를 때 함께 받아들여야 하는 것을 고르세요.

• ① 전환이 실패하면 500개 지점을 모두 되돌려야 합니다.
• ② 옛 시스템과 새 시스템을 몇 달 동안 같이 운영하며 양쪽 데이터를 맞춰 주어야 합니다.
• ③ 매핑 정의서를 만들지 않아도 됩니다.
• ④ 업무를 멈추는 시간이 한 번으로 끝납니다.

2. 병원이 쓰던 진료 시스템을 새 시스템으로 바꿉니다. (가)~(다)가 데이터 전환의 어느 걸음인지 한글 이름과 영문으로 함께 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) 옛 시스템에서 성별을 남·여라는 글자로 두었는데, 새 시스템은 M·F로 받습니다. 규칙 문서를 보고 값을 바꿉니다.
• (나) 옛 시스템의 진료 기록을 건드리지 않고 읽어 내어 파일로 내려받습니다.
• (다) 바꾼 진료 기록을 새 데이터베이스의 진료 표에 넣습니다.

3. 도서관이 옛 대출 시스템의 대출 기록 1만건을 새 시스템으로 옮깁니다. 매핑 정의서에 '회원 이름을 회원 표에서 찾아 회원번호로 바꾼다'는 규칙을 적었습니다. 그런데 옛 기록 가운데 300건은 회원 이름이 회원 표에 없는 이름이었습니다. 전환 프로그램을 돌린 뒤 일어나는 일로 알맞은 것을 고르세요.

• ① 프로그램이 멈추고 1만건 모두 적재되지 않습니다.
• ② 300건은 적재되지 않은 채 프로그램이 오류 없이 끝날 수 있어, 건수를 세어 맞춰 보아야 알아냅니다.
• ③ 300건의 회원번호가 NULL로 채워져 모두 적재됩니다.
• ④ 적재한 뒤 새 시스템이 회원 이름을 저절로 만들어 넣습니다.