[데이터] Primary Key Uniqueness?

출처 : https://datascientest.com/en/primary-key-definition-advantages-and-special-features

Primary Key Uniqueness

• 테이블에서 하나의 레코드의 유일, 최소성을 갖게하는 필드
• 여러 개의 필드로 구성 가능
• RDBMS에서 Primary Key의 중복 방지
  
  

IN 빅데이터 기반 데이터 웨어하우스

• Primary key를 기준으로 유일성, 최소성 미보장
  • 보장하는데 메모리와 시간이 더 들기에 대용량 데이터 적재에 걸림돌

미보장 예시

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

INSERT INTO schema.test VALUES ('2024-11-11', 1);
INSERT INTO schema.test VALUES ('2024-11-11', 5); -- 해당 작업 성공

Primary Key Uniqueness 보장 방법

1. 임시 테이블을 만들고 기존 테이블의 모든 레코드 복사
2. 임시 테이블에 데이터 소스에서 새롭게 읽어들인 레코드 복사
   -> 해당 작업 시 중복 발생 가능
3. 중복을 걸러주는 SQL 작성
   • 최신 레코드를 우선 순위
   • ROW_NUMBER를 이용하여 primary key로 partition을 나누어 다른 필드로 정렬을 통해, primary key 별로 하나의 레코드 지정
4. 해당 SQL을 통해 임시 테이블에서 원본 테이블로 복사
   • 해당 작업 이전 원본 테이블의 모든 레코드 제거

가정

1. created_date가 최신일수록 더 신뢰할 수 있는 정보
2. created_date기준, date는 created_date ~ 일주일 이후의 데이터(총 8개)
3. 매일 새로 데이터의 중복이 발생
4. 매일 새로 덮어쓰는 형식의 업데이트 가정

예시 테이블

CREATE TABLE schema.test ( 
    date date primary key, 
    value bigint,
    created_date date
);

보장 방법 순서

1. 원래 테이블의 데이터를 임시 테이블로 복사

CREATE TEMP TABLE temp AS SELECT * FROM schema.test;

   
   2. 데이터 소스에서 새롭게 읽어들인 데이터 임시 테이블로 복사

INSERT INTO temp VALUES '새로운 데이터';

 

   3. 새롭게 원본 데이터를 입력하기 전 원본 데이터 데이터 모두 삭제

DELETE FROM schema.test;

 

   4. 중복을 제거한 데이터 원본 테이블에 삽입

INSERT INTO schema.test SELECT date, value, created_date FROM ( 
	SELECT *, ROW_NUMBER() OVER (PARTITION BY date ORDER BY created_date DESC) seq 
	FROM temp) 
WHERE seq = 1; -- 각 date별 1개의 최신 데이터만 insert

Upsert

• Primary Key를 기준으로 존재하는 레코드일 경우 UPDATE
• 존재하지 않는 경우 INSERT
• 일반적으로 데이터 웨어하우스마다 UPSERT를 효율적으로 지원하는 문법 제공