[ETL] RDB에서 데이터 ETL을 위한 최소한의 테이블 설계

안녕하세요.

데이터엔지니어 주형권입니다. 

 

오랜만에 꽤나 길고 범용적인 주제에 관해서 글을 쓰려고 합니다. 많은 회사에서 데이터를 활용하여 많은 업무를 하고 데이터를 이용해서 많은 의사결정을 하고 있습니다. 그러면서 데이터의 양도 방대하고 종류도 꽤나 다양해졌습니다. 제가 처음에 일할 때는 RDB(Relational Database)에서 발생하는 데이터만 주로 다루었습니다. 하지만 최근에는 너무도 많고 너무도 다양한 데이터를 이용해서 의사결정과 업무를 진행합니다. 

 

하지만 데이터가 아무리 다양하고 많아져도 RDB에서 발생하는 트랜잭션 데이터는 절대적으로 필요합니다. 그렇기에 RDB에서 데이터를 가져오는 것은 절대로 필요하다고 볼 수 있습니다. 회원 데이터 또는 빌링 데이터등은 무결성이 무조건이기 때문에 RDB에서 사용하는 것이 대부분입니다. (실제로 RDB 이외에 본 적이 없습니다...) 

 

물론 제가 제시하는 방법이 절대적인 방법은 아닙니다. CDC를 이용해서 데이터를 가져가는 파이프라인도 있고, 이외에 제가 알지 못하는 다양한 파이프라인이 있을 것으로 생각 합니다. 제가 제시하는 방법은 어디까지나 제가 알고 있는 방법이며 몇 년간 사용하였으나 문제없이 잘 사용하고 있어서 공유 차원에서 글을 작성하였습니다. 또한 이 방법은 최소한의 방법이지 최선의 방법이 아닙니다. 구조변경을 최소화하고 개발일정에 무리가 없는 방향에서 최소한의 방법을 설명 하는 글이므로, 약간 의아하게 보실 수 있습니다. 이 부분을 꼭 감안해서 글을 봐주세요.

 

---

어떠한 컬럼이 필요한가?

 

많은 스타트업이나 많은 회사에서 기존의 데이터의 구조를 변경하기란 여간 힘든 게 아닙니다. RDB의 구조를 변경하는 경우 개발을 다시 하거나 추가 개발의 공수가 들어가서 예기치 못한 비용이 들어가는 경우가 많습니다. 그렇기 때문에 이를 최대한 변경하지 않고 할 수 있는 방법에 대해서 이야기하려고 합니다. 

 

기억 하자... 4가지

 

결론부터 말씀드리면 3가지의 컬럼이 필요합니다. 조금 더 가능하다면 4가지입니다.

또한 여기서 말씀 드리는 id , created_at , updated_at 컬럼에는 인덱스가 필수적으로 각각 있어야 합니다.

 

컬럼명	컬럼 설명	필요한 이유
id	row의 고유한 숫자 형태의 key값	1. APPEND 형태의 데이터를 순차적으로 가져올 수 있음  2. 데이터를 잘라서 청크(chunk) 단위로 가져올수 있음  3. 데이터의 중복을 판단 할 수 있음
created_at	데이터 row의 입력 시간	1. 위의 내용과 같이 APPEND 형태의 데이터를 순차적으로 가져올 수 있음  2. 데이터가 언제 입력되었는지 정확히 알 수 있으며 건수 체크 및 데이터의 정합성 체크를 가능 하게 함 3. 데이터 분석시 정확한 데이터 분석이 가능하고 FACT, MART등과 같은 데이터를 만들때 필요
updated_at	데이터 row의 업데이트 시간	1. 데이터가 upsert 할때 정확하게 변경 된 정보만 가져와서 upsert가 가능  2. 데이터 분석시 정확한 데이터의 최신 변경 시점을 알 수 있음
description	테이블 컬럼 설명,테이블 설명	1. 어떠한 테이블인지 알 수 있고 어떠한 컬럼인지 알 수 있음  2. 데이터를 사용하는 사용자로 하여금 정확한 의사 결정 및 데이터 분석을 가능 하도록 함

 

위의 내용에서 하나씩 살펴 보도록 하겠습니다. 

---

첫 번째, id 컬럼 

id컬럼은 대부분의 RDB를 설계할 때 들어가 있을 것입니다. 고유값을 잡을 때 보통 AUTO_INCREMENT 옵션을 주는 int , bigint 같은 데이터 타입으로 잡고 이를 고유한 값으로 만듭니다. 또한 Primary Key(이하 PK)등을 id값에 걸어주는 경우도 일반적으로 하는 테이블 설계 방법입니다. 그 이유는 고유한 row의 값을 만듦으로써 중복을 방지 할 수 있고 PK는 물리적으로 정렬이 일어나기 때문에 자동증가를 이용하여 성능상의 이점을 가져 올 수 있습니다. (이 이야기를 자세히 하려면 B Tree , RDB Index를 검색하면 좋습니다. 궁금하시면 링크)  

 

하지만 우리가 지금 보고 있는 글은 RDB의 성능이 아닌데? 왜? 언급을 하지?라고 생각 할 수 있습니다. 하지만 우리가 RDB에서 데이터를 ETL 할때 가장 많이 놓치는 부분이 바로 RDB 성능을 고려하지 않고 ETL을 만든다는 것입니다. RDB의 경우는 무제한의 하드웨어 성능과 엄청난 분산처리등의 구조가 아닙니다. 무결성을 보장하고 빠르게 응답을 하기 위해서 어쩔 수 없는 제약이 많이 있습니다. 실시간 트랜잭션 처리를 해야 하므로 Hadoop , BigQuery 등과 같은 데이터웨어하우스 시스템과는 다른 저장법과 처리법을 가지고 있습니다. 

 

실제로 많은 회사에서 이것을 고려하지 않고 무조건 MySQL , AWS Aurora와 같은 RDB에서 replica서버를 이용하면 무조건 괜찮다고 생각하는데, 이는 초반에는 별문제가 없지만 데이터가 계속해서 증가함에 따라서 언젠가는 ETL 자체가 불가능하거나 느려져서 결국은 쓸 수 없습니다. 그렇기 때문에 id값을 이용하여 성능을 고려하여 조금씩 작은 청크 단위로 가져와야 합니다.

 

RDB의 성능을 고려한 청크 단위로 가져오기

 

그림으로 보시면 더욱 이해가 빠를 거 같아서 그림을 첨부하였습니다. 우리가 인덱스가 없이 RDB에서 테이블을 가져오면 왼쪽과 같이 Full Scan이라는 행위를 통해서 모든 테이블을 한 번에 읽으려고 합니다. 이는 앞서 말씀드렸듯이 초기에 데이터가 작을 때는 큰 문제가 없으나 갈수록 데이터가 많아지면서 ETL의 속도가 느려지고 결국에는 읽는 과정에서 RDB가 장애를 일으킵니다. 그리고 위에서 보여드리는 예시는 1개지만 RDB에서 테이블은 당연히 여러 개를 한 번에 읽어서 처리를 합니다. (테이블 여러 개를 모두 읽어서 DW에 저장하므로...)  

 

그리고 id값이 있으면 가장 좋은 것은 APPEND 방식으로 데이터를 ETL 할 때 일정한 청크 단위로 데이터를 옮길 수 있어서 RDB에 부하를 주지 않을 수 있고, 데이터가 아무리 많아져도 큰 문제없이 일정하게 id값을 기반으로 데이터를 ETL 할 수 있습니다. 하지만 어디까지나 이 방법은 APPEND만 가능합니다. 

 

---

두 번째, created_at, updated_at 컬럼 

created_at 컬럼의 경우는 updated_at 컬럼과 한쌍으로 묶여서 들어갑니다. 물론 APPEND 방식만 있는 완전한 Log성 테이블의 경우는 created_at 또는 id값만 있어도 상관없습니다. (쌓이기만 하기 때문에 마지막 id값 or 일자단위로 가져오면 되기 때문에) 

 

하지만 RDB에서 APPEND만 일어나는 것이 아닌 UPSERT도 일어나기 때문에 이를 가져오기 위해서는 언제 입력되었는지 알기 위한 created_at과 언제 업데이트되었는지 알기 위한 updated_at이 필요합니다. 가장 중요한 것은 업데이트가 되었을 때 데이터를 업데이트된 부분만 가져오기 위해서 updated_at이 필요합니다. 앞서 이야기하였듯이 최소한의 테이블 설계이기 때문에 굳이 created_at과 updated_at을 개발 쪽에서 처리하지 않고 RDB 쪽에서 처리하는 방법으로 소개를 하고자 합니다. (이 방법은 MySQL이나 AWS의 Aurora에서 적용 가능 합니다.) 

 

컬럼	옵션
created_at	`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
updated_at	`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

 

위와 같이 할 경우 굳이 개발쪽에서 고치지 않고, 테이블의 옵션을 추가하여 컬럼이 생성될 경우에 자동으로 현재 시간을 입력할 수 있고, 업데이트가 일어날 경우에 업데이트 시간을 입력할 수 있습니다.  (개발단에서 처리해 주는 것도 좋은 방법이지만 공수가 많이 들기 때문에..)

 

그리고 위에 한쌍으로 묶어서 만들어야 한다고 했을 때 사람들이 보통 created_at과 updated_at을 복합 인덱스로 하나의 인덱스로 만드는 경우가 있습니다. 이렇게 될 경우 복합 인덱스의 INDEX Seek에 의해서 선행되지 못하는 컬럼으로 INDEX Seek를 못 할 수 있습니다. 그렇기 때문에 created_at에 인덱스 한 개, updated_at에 인덱스 한개 각각 따로 만들어줘야 합니다. 이렇게 만들고 다음과 같이 SELECT를 해서 데이터를 가져갈 때 OR조건으로 WHERE에서 데이터를 읽어서 새롭게 생겼거나 업데이트가 일어난 부분만 부분적으로 가져감으로써 RDB에 부하를 최소화할 수 있습니다. 

 

SELECT col1,col2,col3,col4,col5
FROM table
WHERE (created_at >= '날짜' AND created_at < '날짜') 
	OR (updated_at >= '날짜' AND updated_at < '날짜')

 

위와 같이 created_at과 updated_at을 기반으로 일자 또는 시간별등등으로 데이터를 가져오고 OR조건을 걸어서 두 개 전부 가져오게 할 수 있습니다. 이 경우 복합 인덱스가 아닌 Composite Index로 INDEX Seek를 하여 RDB에 부하를 최소화하여 가져올 수 있습니다. 결론적으로 위의 ID값을 가져오는 것과 마찬가지로 새로 생성되었거나 변경된 부분만 정확하게 가져오기 때문에 Table Scan이 발생하지 않아서 RDB에 부하를 최소화하는 것입니다. 

 

---

세 번째, description

굳이 왜?라는 생각을 많이 할 수 있습니다. 하지만 이는 굉장히 중요합니다. RDB를 만들었던 DBA나 개발자는 본인이 만들었기 때문에 굳이 설명이 없어도 이것이 무엇인지 알 수 있지만 이를 사용하는 사용자나 데이터를 ETL 해야 하는 데이터엔지니어는 이것이 무슨 컬럼인지 무슨 테이블인지 정확히 알기 어렵습니다. 물론 비즈니스 로직을 전부 이해하고 이를 모두 파악하고 있다면 더할 나위 없겠지만 대부분의 사람들은 기존에 여러 담당자가 만들었던 여러 개의 테이블과 여러 번 변하였던 테이블을 봐야 합니다. 

 

설명서 없이 무엇을 만들긴 어렵다...

 

그렇기 때문에 이 테이블과 컬럼이 무엇인지 알 수 있도록 설명을 달아주는 것은 매우 중요합니다. 우리는 무엇을 만들던 설명서를 통해서 이를 어떻게 만드지 알 수 있습니다. description은 우리(데이터분석가, 데이터엔지니어, 데이터사용자)에게 설명서와 같은 역할을 합니다. 이 설명서가 없이 데이터를 조립하기란 여간 어려운 것이 아닙니다. 

 

이런 조회 페이지가 많은 도움이 됩니다.

 

데이터가 어떻게 생겼는지 알기 위해서는 설명이 필수적입니다. 개발에서도 주석을 잘 달아두면 파악하기 쉬운 것과 마찬가지로 테이블도 마찬가지입니다. 우리가 역으로 생각해보면 주석을 잘 달아두면 나에게 질문을 하지 않고, 많은 사용자가 알아서 잘 파악 가능 하기 때문에 너도나도 좋은 방법입니다. 데이터도 마찬가지입니다. 테이블과 컬럼에 주석을 잘 달아두면 데이터를 사용하는 많은 사람들이 나에게 와서 데이터를 어떻게 조합해야 하고 조립해야 하는지 질문하는 빈도가 굉장히 많이 줄어듭니다.

 

---

마치며...

 

데이터의 중요성이 날로 증가하지만 많은 회사에서 RDB의 테이블 설계는 중요하게 생각하지 않는 경향이 많습니다. 물론 스타트업이나 빠르게 변화가 필요한 회사의 경우 몇달이 걸리는 RDB 설계를 감당하고 기다리기 매우 힘든 게 사실입니다. 저도 처음에는 RDB에 설계에서 1~3차 정규화나 여러 가지 문제점 같은 부분으로 인해서 개발자분들과 많은 갈등을 빚었습니다. 하지만 갈수록 IT의 트렌드가 바뀌듯이 저 또한 바뀌는 게 맞다고 생각하여 새로운 방법을 찾아서 적용시켰고, 이 방법으로 데이터를 가져올 때 큰 문제가 없었습니다. 

 

내가 하는게 전부는 아니다.

 

가장 앞에서 언급 하였듯이 다른 좋은 방법이 있을 수 있으며, 이 방법도 하나의 방법론이라고 생각해 주시면서 글을 기억해 주셨으면 합니다. 많은 회사에서 다양한 방법으로 ETL을 하고 있기에 제가 아는 방법이 전부는 아닙니다. 하지만 이 글을 통해서 완전히 처음 시작하는 사람들 또는 어떻게 해야 할지 막막한 사람들에게 하나의 방법론을 제시할 수 있는 글이 되었으면 합니다. 

 

 

감사합니다.