데이터 웨어하우스에서 Primary Key Uniquess 보장하기

데이터베이스에서 Primary Key(기본키, PK)는

테이블에서 하나의 레코드를 유일하게 지칭할 수 있는 필드(들)이다.

RDBMS에서는 PK 값이 중복되지 않도록 유일성(uniqueness)을 보장해 준다.

 

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(50),
    price decimal(7, 2)
);

관계형 DB의 쿼리를 짤 때는 위처럼 필드 명 뒤에 PRIMARY KEY를 지정해 주거나,

PRIMARY KEY (product_id, name)으로 다수의 필드를 PK로 지정해 줄 수 있다.

(이러면 정규화 측면에서 부분 함수 종속 등이 발생할 수 있지만, 이 글에서는 논외로 한다.)

단, 데이터 웨어하우스들은 기본키 유일성을 보장해주지 않는다.

 

---

데이터 웨어하우스에서 유일성을 보장하지 않는 이유는

유일성을 보장하는데 메모리와 시간이 더 들기 때문에

대용량 데이터 적재에 있어 걸림돌이 되기 때문이다.

 

관계형 데이터베이스들은 PK의 유일성을 보장하기 위해

B+ Tree와 같은 자료구조를 사용하게 되고,

B+ Tree를 사용하기 위한 메모리와 시간이 소요되고,

새로운 레코드를 삽입할 때 키 값이 중복되는지 체크하는 연산의 시간도 소요된다.

 

데이터 웨어하우스들은 보통 데이터가 굉장히 많이 들어가는 DB이기 때문에

유일성을 보장하기 위한 연산을 하게 되면 커다란 부하(overload)가 발생하게 된다.

그래서 PK의 유일성을 보장하는 것은 데이터 엔지니어의 책임이라고 볼 수 있다.

 

실제로 PK의 유일성을 보장하지 않기 때문에 아래와 같은 쿼리가 성공한다.

CREATE TABLE hongcana.test (
    date DATE primary key,
    value bigint
);

INSERT INTO hongcana.test VALUES ('2023-06-21', 6000);
INSERT INTO hongcana.test VALUES ('2023-06-21', 7000); -- 이 작업이 성공한다!

 

---

DW에서 PK 유일성을 보장하는 방법을 알아보자.

 

임시 테이블을 새로 만드는 방법이 있는데,

임시 테이블에 timestamp 자료형 등을 이용하여

레코드가 추가된 시간을 기록하는 필드를 추가하고,

이를 가장 최근 날짜에 기록된 (혹은 반대) 레코드만 남겨서

중복을 제거하고 유일성을 보장하는 방법이 있다.

아래 그림을 참고하자.

PK 유지 방법 예시

3번을 보면 ROW_NUMBER() OVER (partition by date order by created_date DESC) seq라는

쿼리문이 있는데 이를 일일이 해석해 보면 다음과 같다.

 

1. ROW_NUMBER() : 이 함수는 각 행에 번호를 할당하는 함수다. 고유의 일련번호를 생성한다.

2. OVER : OVER절은 윈도우 함수가 적용되는 범위를 지정하는 역할을 한다.

3. PARTITION BY : 데이터를 분할하는 역할을 하는데, date 열을 기준으로 date 값이 같은 레코드들을 같은 그룹으로 묶으라는 뜻이다. 보통 primary key를 파티션으로 잡는다.

4. ORDER BY created_date DESC : date 열을 기준으로 레코드들을 분할하고, 분할된 레코드들의 내부 정렬 순서를 created_date의 내림차순으로 정렬하겠다는 이야기다.

5. seq : 정렬된 레코드들의 일련번호를 seq라는 필드명으로 하겠다는 뜻이다.

 

보통 임시 테이블을 만들고 현재 모든 테이블을 임시 테이블로 COPY한 뒤,

임시 테이블에서 위 작업들을 모두 진행하여 중복을 제거한다.

그리고 원본 테이블에서 레코드들을 삭제하고, 임시 테이블의 데이터를 원본 테이블로 복사한다.

( 위 예시의 경우, WHERE seq = 1; 로 중복된 데이터에 대한 복사를 배제할 수 있을 것이다. ) 

 

 

특히 Incremental Update의 경우 위의 과정이 유용할 것이다.