•
PDF 버젼 다운로드하기
•
SQL BOOSTER 이어지는 이야기 몰아보기: https://cafe.naver.com/dbian/6444
•
StartUP Tuning For PostgreSQL 오프라인 주말 교육 모집중:
데이터 이행(Migration)의 꽃은 무엇일까요? 주변의 20년 넘게 산전, 수전, 공중전, 우주전까지 겪은 데이터 전문가들에게 뜬금없이 이러한 질문을 했습니다. 그 분들의 답변은 모두 ‘키맵’이었습니다.
최근에 저는 데이터 이행 프로젝트에 참여하고 있습니다. 이행 프로젝트는 가능하면 피하는 것을 목표로 하고 있지만, 이행을 리딩해 줄 야전 사령관님과, 함께 이행 개발을 해줄 분이 너무 좋았기 때문입니다. 이러한 팀 구성이면, 간만에 이행 할만하다라고 생각이 들었습니다.
데이터 이행은 매우 다양한 상황과 매우 다양한 방법이 존재합니다. 그렇기 때문에 어떤 방법이 정답이라고 말할 수 없습니다. 데이터 이행에 있어 제가 이야기하는 내용이 무조건 정답이라 할 수 없다는 뜻입니다.
1. 키맵 테이블의 필요성
커피주문 시스템을 고도화하려고 합니다. 이와 함께, AS-IS 데이터도 TO-BE 구조에 맞게 이행을 수행해야 합니다. 간단하게 예를 들면 아래 그림과 같습니다. AS-IS의 TR_ORD는 TO-BE에서 TR_ORD_TB란 테이블로 변경이 되며, TR_ORD_DET는 TR_ORD_DET_TB란 테이블로 변경이 됩니다. 무엇보다, AS-IS의 ORD_NO란 주문번호는 TO-BE에서는 ORD_ID라는 주문ID로 변경이 되어야 합니다.
여기서는 AS-IS의 ORD_NO가 TO-BE의 ORD_ID로 이행되면서 다음과 같은 규칙으로 변환된다고 가정합니다.
•
AS-IS의 ORD_NO: 1, 2, 3, 4와 같은 숫자 자료형의 순번 형태
•
TO-BE의 ORD_ID: YYYYMMDD + NNNN (년월일(ORD_DTM)별 네 자리 순번의 문자 자료형)
사실, ORD_ID와 같이 일자 + 순번 형태의 키 구조는 추천하지 않습니다. 이행 과정이 번거롭기도 하며, 데이터에 대한 관리나 개발이 조금 더 어렵기 때문입니다. 특별한 이유가 없다면, 시퀀스 객체를 사용한 시퀀스 방식의 ID가 가장 실용적입니다. 어쨌든, 여기서는 TO-BE에서는 일자 + 순번 형태의 키 구조로 정해져 있다고 가정합니다.
위와 같이 키가 변환된다고 가정하면, 분석함수를 활용해 어렵지 않게 TO-BE 구조의 키를 만들어 낼 수 있습니다. 아래 SQL과 같이 처리하면 TR_ORD 테이블의 데이터에 새로운 ORD_ID를 부여해 TR_ORD_TB로 이행할 수 있습니다.
[SQL-1]
INSERT INTO STARTDBORA.TR_ORD_TB (...생략...)
SELECT T2.ORD_YMD || LPAD(T2.ORD_YMD_NO,4,'0') ORD_ID
,T2.ORD_DTM ,T2.PREP_CMP_DTM ,T2.PKUP_DTM ,T2.MBR_ID ,T2.SHOP_ID
,T2.ORD_ST ,T2.ORD_AMT ,T2.PAY_TP
FROM (
SELECT T1.ORD_NO
,TO_CHAR(T1.ORD_DTM,'YYYYMMDD') ORD_YMD
-- > 분석함수를 활용해 주문일자별 순번 구하기
,ROW_NUMBER() OVER(PARTITION BY TO_CHAR(T1.ORD_DTM,'YYYYMMDD')
ORDER BY T1.ORD_NO ASC) ORD_YMD_NO
,T1.ORD_DTM ,T1.PREP_CMP_DTM ,T1.PKUP_DTM ,T1.MBR_ID ,T1.SHOP_ID
,T1.ORD_ST ,T1.ORD_AMT ,T1.PAY_TP
FROM STARTDBORA.TR_ORD T1
) T2;
SQL
복사
하지만, 위와 같이 주요 키의 변경 로직(ORD_NOàORD_ID)을 해당 테이블을 이행하는 SQL에 심어 놓으면, TR_ORD를 참조하는 TR_ORD_DET를 이행할 때 문제가 발생합니다.
TR_ORD_DET는 TR_ORD를 참조합니다. 그러므로 TR_ORD의 PK인 ORD_NO가 TR_ORD_DET에도 존재합니다. 다시 말해, TR_ORD_DET의 ORD_NO도 ORD_ID로 변경해 이행해야 합니다. 앞에서 ORD_NO 변경 로직을 TR_ORD를 이행하는 SQL에 직접 구현했기 했기 때문에, TR_ORD_DET를 이행할 때도 ORD_NO의 변경 로직을 별도로 구현해야 합니다. 결국, 아래와 같이 복잡한 SQL을 사용해야 TR_ORD_DET를 이행할 수 있습니다. 앞에서 사용한 ORD_NO를 ORD_ID로 채번하는 SQL이 TR_ORD_DET를 이행하는 SQL 내부에 포함되어 있습니다.
[SQL-2]
INSERT INTO STARTDBORA.TR_ORD_DET_TB(...생략...)
SELECT T2.ORD_ID
,T3.ORD_DET_NO ,T3.ITEM_ID ,T3.ORD_QTY ,T3.SALE_PRC
FROM (
SELECT T1.ORD_NO
,TO_CHAR(T1.ORD_DTM,'YYYYMMDD') ORD_YMD
-- > 분석함수를 활용해 주문일자별 순번 구하기
,ROW_NUMBER() OVER(PARTITION BY TO_CHAR(T1.ORD_DTM,'YYYYMMDD')
ORDER BY T1.ORD_NO ASC) ORD_YMD_NO
FROM STARTDBORA.TR_ORD T1
) T2
INNER JOIN STARTDBORA.TR_ORD_DET T3
ON (T3.ORD_NO = T2.ORD_NO);
SQL
복사
TR_ORD를 바라보는 테이블이 TR_ORD_DET 하나라면, 위와 같은 복잡함을 어느정도 감당할 수 있습니다. 하지만, 실전에서는 주요 테이블을 바라보는 테이블이 매우 많을 수 있습니다. 또한, 키를 변경하는 로직이 프로젝트 진행 중에 변경이 될 수도 있습니다. TR_ORD를 바라보는 모든 테이블의 이행 로직을 모두 찾아서 변경해야 한다고 생각해보기 바랍니다. 그 이행 프로젝트는 망하게 될 것입니다.
키 구조가 변경되는 데이터 이행은, 되도록 키맵 테이블을 추가로 생성해 처리해야 합니다. 업무 테이블을 이행하기 전에, 아래와 같이 키맵 테이블을 별도로 생성하도록 합니다.
[SQL-3]
CREATE TABLE STARTDBORA.KM_ORD_NO AS
SELECT T2.ORD_NO
,T2.ORD_YMD || LPAD(T2.ORD_YMD_NO,4,'0') ORD_ID
FROM (
SELECT T1.ORD_NO
,TO_CHAR(T1.ORD_DTM,'YYYYMMDD') ORD_YMD
-- > 분석함수를 활용해 주문일자별 순번 구하기
,ROW_NUMBER() OVER(PARTITION BY TO_CHAR(T1.ORD_DTM,'YYYYMMDD')
ORDER BY T1.ORD_NO ASC) ORD_YMD_NO
FROM STARTDBORA.TR_ORD T1
) T2;
SQL
복사
이제, ORD_NO 컬럼이 존재하는 테이블을 이행할 때는 키맵 테이블과 조인을 이용해 이행을 처리하면 됩니다. 아래는 TR_ORD와 TR_ORD_DET를 이행하는 SQL입니다. 기존의 [SQL-1]과 [SQL-2]에 비해 이행 SQL이 단순해졌습니다.
[SQL-4]
INSERT INTO STARTDBORA.TR_ORD_TB (...생략...)
SELECT KM.ORD_ID -- 키맵을 이용
,T1.ORD_DTM ,T1.PREP_CMP_DTM ,T1.PKUP_DTM ,T1.MBR_ID ,T1.SHOP_ID ,T1.ORD_ST ,T1.ORD_AMT ,T1.PAY_TP
FROM STARTDBORA.TR_ORD T1
LEFT OUTER JOIN STARTDBORA.KM_ORD_NO KM ON (KM.ORD_NO = T1.ORD_NO);
INSERT INTO STARTDBORA.TR_ORD_DET_TB (...생략...)
SELECT KM.ORD_ID -- 키맵을 이용
,T1.ORD_DET_NO ,T1.ITEM_ID ,T1.ORD_QTY ,T1.SALE_PRC
FROM STARTDBORA.TR_ORD_DET T1
LEFT OUTER JOIN STARTDBORA.KM_ORD_NO KM ON (KM.ORD_NO = T1.ORD_NO);
SQL
복사
이처럼 키맵 테이블을 활용하면, 이행 프로젝트 중간에 TO-BE 키의 채번 로직이 변경되어도 키맵 테이블을 생성하는 로직만 변경을 하면 됩니다. 위 SQL에서는 키맵 테이블을 아우터 조인의 참조 집합으로 사용하고 있습니다. 이 부분도 중요한 포인트입니다. 하지만, 설명이 약간 복잡해 이 문서에서는 설명을 생략하도록 하겠습니다. 양해 부탁드립니다.
2. 키맵 데이터 보존하기
키맵 생성 과정을 더욱 발전시켜야 하는 경우가 있습니다. 특별한 경우로, 선이행과 본이행, 후이행으로 나누어 데이터가 이행되는 경우입니다. 이처럼 이행이 나누어져 진행되면 이행 난이도가 급격히 올라갑니다. 특히나, 지금처럼 TO-BE의 키 형태가 특정 일자별 순번이라면 더욱 난이도가 올라갑니다. 선이행, 본이행, 후이행이 나누어지는 작업은 키맵 외에도 다양한 이슈가 있지만, 여기서는 키맵에 대해서만 이야기하려고 합니다.
선이행, 본이행, 후이행이 나누어져 진행될 때, 중요한 점은, 한번 만들어진 키맵이 변경되면 안 된다는 점입니다. 선이행때 만들어진 키맵이 본이행 때 변경돼서는 안 됩니다. 마찬가지로 본이행 시점에 만들어진 키맵이 후이행에 변경되어서도 안 됩니다. 만약에 본이행에 만들어진 키맵이 후이행 시점에 틀어지게 되면, 이행된 데이터가 꼬여버리는 심각한 문제가 발생하게 됩니다.
AS-IS의 ORD_NO가 무조건 시퀀스하게 만들어진다고 보장된다면 키맵 로직이 좀 수월할 수 있습니다. 하지만 이는 절대 장담할 수 없습니다. 예를 들어, AS-IS 시스템에서 선이행 시점에 2025년 1월 1일의 ORD_NO가 100, 101, 105 이렇게 세 건이 있다고 가정하면, TO-BE로의 키맵은 다음과 같습니다. (일자별 순번 형태로 ORD_ID가 만들어지기 때문에, AS-IS의 105가 TO-BE에서는 20250101의 0003 순번을 갖는다는 점에 주의가 필요합니다.)
•
101 -> 202501010001
•
102 -> 202501010002
•
105 -> 202501010003
선이행을 완료하고, 몇일 지나, 본이행 때는 데이터가 늘어나, 2025년 1월 1일의 ORD_NO가 100, 101, 102, 103, 105 이렇게 다섯 건이 될 수도 있습니다. 이 경우 키맵은 다음과 같이 구성해야 합니다.
•
101 -> 202501010001 -> 선이행때 만들어진 데이터
•
102 -> 202501010002 -> 선이행때 만들어진 데이터
•
105 -> 202501010003 -> 선이행때 만들어진 데이터
•
103 -> 202501010004 -> 본이행때 새로 들어온 데이터(AS-IS는 세번째이지만, TO-BE는 네 번째)
•
104 -> 202501010005 -> 본이행때 새로 들어온 데이터(AS-IS는 네번째이지만, TO-BE는 다섯 번째)
본이행때 순번이 중간에 들어왔다고 기존 키맵을 변경해서는 안 됩니다. 기존의 키맵으로 데이터가 이미 이행된 상태이기 때문입니다. 기존의 키맵을 유지하면서 새로운 키맵을 추가하기 위해서는 다음과 같이 키맵 구현 SQL을 변경해야 합니다.
[SQL-5]
INSERT INTO STARTDBORA.KM_ORD_NO(ORD_NO ,ORD_ID)
SELECT T3.ORD_NO
,T3.ORD_YMD || LPAD(T3.MAX_NO_BY_YMD + T3.ORD_YMD_NO,4,'0') ORD_ID
FROM (
SELECT T1.ORD_NO
-- > 일자별 새로운 데이터를 위해, 아우터 조인과 NVL처리가 필수다.
,NVL(KM.MAX_NO_BY_YMD,'0000') MAX_NO_BY_YMD
,TO_CHAR(T1.ORD_DTM,'YYYYMMDD') ORD_YMD
-- > 분석함수를 활용해 주문일자별 순번 구하기
,ROW_NUMBER() OVER(PARTITION BY TO_CHAR(T1.ORD_DTM,'YYYYMMDD')
ORDER BY T1.ORD_NO ASC) ORD_YMD_NO
FROM STARTDBORA.TR_ORD T1
LEFT OUTER JOIN (
-- > 기존 키맵에서 일자별 최대값 가져오기
SELECT SUBSTR(Y.ORD_ID,1,8) YMD
,SUBSTR(MAX(Y.ORD_ID),9,4) MAX_NO_BY_YMD
FROM STARTDBORA.KM_ORD_NO Y
GROUP BY SUBSTR(Y.ORD_ID,1,8)
) KM ON (TO_CHAR(T1.ORD_DTM,'YYYYMMDD') = KM.YMD)
WHERE NOT EXISTS(
-- > 이미 키맵 처리된 데이터 제외
SELECT *
FROM STARTDBORA.KM_ORD_NO X WHERE X.ORD_NO = T1.ORD_NO)
) T3;
SQL
복사
위 SQL이 이해가 안 된다면 지금 당장 이해하려고 노력할 필요는 없습니다. 이 문서에 이런 내용이 있었다는 것만 기억하고 있다가, 나중에 비슷한 일을 처리할 때 찾아보면 됩니다.
위 SQL은 TO-BE의 채번이 일자별 순번이기 때문에 더욱 복잡합니다. 만약에 TO-BE가 단순 순번형태라면 조금은 더 간단해질 수 있습니다. 가능하면 TO-BE 시스템에서 키를 구성할 때 일자별 순번과 같은 형태를 사용하지 않도록 의사 결정되는 것이 가장 좋습니다.
3. 우린 키맵이 필요없어! 과연?
ORD_NO를 ORD_ID로 변환할 때, 일자별 순번이 아닌, AS-IS 키 값을 보존한 형태로 이행을 하게 된다면 이행이 제법 간단할 수 있습니다. 예를 들어, TO-BE의 ORD_ID는 가장 앞에 OD라는 프리픽스가 붙고, 뒤쪽에 8자리 순번이 문자형태로 만들어진다고 가정해 봅니다. 다음과 같습니다.
•
101 -> OD00000101
•
102 -> OD00000102
•
2789 -> OD00002789
위와 같이 AS-IS 값에 특정 문자를 결합하는 방식으로 이행 룰이 정해져 있다면, 키맵 없이도 간단히 이행을 처리할 수 있습니다. TR_ORD와 TR_ORD_DET를 이행하기 위해 다음과 같이 SQL을 작성할 수 있습니다.
[SQL-6]
INSERT INTO STARTDBORA.TR_ORD_TB (...생략...)
SELECT 'OD' || LPAD(T1.ORD_NO,8,'0') ORD_ID
,T1.ORD_DTM ,T1.PREP_CMP_DTM ,T1.PKUP_DTM ,T1.MBR_ID ,T1.SHOP_ID ,T1.ORD_ST ,T1.ORD_AMT ,T1.PAY_TP
FROM STARTDBORA.TR_ORD T1;
INSERT INTO STARTDBORA.TR_ORD_DET_TB (...생략...)
SELECT 'OD' || LPAD(T1.ORD_NO,8,'0') ORD_ID
,T1.ORD_DET_NO ,T1.ITEM_ID ,T1.ORD_QTY ,T1.SALE_PRC
FROM STARTDBORA.TR_ORD_DET T1;
SQL
복사
하지만, 과연 이행 프로젝트가 그렇게 순탄할까요? 위와 같이 각 테이블마다 직접 로직을 구현하게 되면, 결국 실수할 여지가 생깁니다. 이행 테이블에 따라 LPAD를 8이 아닌, 7이나 9를 사용하는 실수를 하게 될 수도 있는 것이죠. 무엇보다도, 키를 변환하는 룰이 오픈전까지 안 바뀔 가능성이 있을까요? 확인에 확인을 거듭하더라도, 다양한 이유로 인해 TO-BE의 채번 규칙이 변할 수 있습니다. 그러므로 많이 사용될 키 변환 로직은 별도의 키맵 테이블로 구현하는 것이 좋습니다.
이번에는 반대로, 모든 키 값에 키맵 테이블을 만들어 데이터 이행을 하게 되면 어떻게 될까요? 업무 테이블만큼 키맵 테이블이 늘어나 관리가 안될 것입니다. 주로 많이 사용하는 키, 다시 말해 많은 테이블이 참조하는 주요 테이블의 PK에 대해서만 키맵을 고려하는 것이 좋습니다.
오늘 설명한 내용은, 조금 어려운 내용입니다. 실제 경험한 상황이 아니면 쉽게 이해가 되지 않을 수 있습니다. 오늘 설명 드린 내용을 한마디로 요약하자면, ‘키가 변경되는 데이터 이행의 꽃은 키맵 테이블이다.’ 정도가 될 거 같습니다. 특히나, 오늘 다루진 않았지만, 데이터가 통합되는 구조의 이행에서는 키맵 테이블이 더욱 중요하다는 점도 인식하고 있기 바랍니다. 언젠가 데이터 이행을 담당하게 된다면 이 문서를 꼭 꺼내서 한 번쯤 읽어 보시기 바랍니다.
데이터 이행은 꼭 경험해 볼 필요가 있는 프로젝트입니다. 데이터 모델링, SQL 개발, SQL 튜닝, 데이터 검증, 이러한 모든 스킬을 전반적으로 알아야 제대로 할 수 있는 것이 데이터 이행입니다. 반대로, 데이터 이행 프로젝트를 경험한다면, 이 모든 스킬의 경험치를 올릴 수 있습니다. 안타깝게도 IT 프로젝트 시장에서, 모델러와 튜너, DBA에 비해, 이행 개발자가 저평가 되고 있습니다. 이행 개발자는 매우 중요하며 힘든 역할입니다. 모델러보다 더욱 높은 수준의 AS-IS, TO-BE 업무 이해가 필요할 수 있으며, 일을 진행하는 과정에서 치밀함과 꾸준함이 필요하며, 높은 체력도 필요합니다. 그리고 현업과 개발팀과의 의사 소통도 잘 해야 합니다. 무엇보다 이행 개발이 어려운 점은, 프로젝트가 잘되면 각자가 자신 덕이라고 말하지만, 프로젝트가 잘 안되면 이행 탓이라고 하는 경우가 많습니다. 이행 개발자가 좀 더 존중받을 수 있는 IT 환경이 되었으면 합니다. 물론, 이행 개발자 역시 존중받을 수 있을 만큼 자신의 일을 철저하게 준비하고 진행할 필요가 있습니다.
끝으로, 앞에서도 이야기했듯이 데이터 이행 방법론은 정해진 규칙이 없습니다. 프로젝트에 따라서, 데이터 이행 SQL 자체를 개발자가 모두 개발하고 이행 개발자는 취합한 SQL을 단순 실행만 하는 프로젝트도 있습니다. 유연성을 가지고 현재 프로젝트에서 적절한 이행 방법이 무엇인지 고민하고 이행 안을 세울 필요가 있습니다.