[TIL] incremental update 하면서 PK 유일성 보장하는 방법

PK 하나의 필드가 일반적 다수의 필드인 경우 composit key?

CREATE TABLE 사용시 지정하는게 일반적

1. 속성으로 지칭

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(50)
  );

2. 새로운 라인으로 따로 지칭 (하나 or PK값이 두개인 경우)

CREATE TABLE orders(
  order_id INT,
  product_id INT,
  PRIMARY KEY(order_id, product_id),
  FOREIGN KEY (product_id) REFERENCES products (product_id)
 );

FK키 데이터 정합성이나 데이터 관리 할 때 도움? 기재?하는게 좋음 필수는아님

관계형 데이터베이스 시스템은 Primary Key Uniqueness(PK 값 중복 존재하지 않도록) 보장

<-> (빅데이터기반) 데이터 웨어하우스 시스템은 보장해주지 않음

-> why?

- B-tree 형태로 lookup 해줘야 하는데 매번 체크하기 위한 메모리나 시간이 많이 들어서 대용량 데이터 적재에 어려움 

OLTP는 보장 필수, 데이터의 크기가 작기 때문에/ DW는 대용량이라 비효율적

- 이를 보장하는건 데이터 인력의 책임(데이터 엔지니어(ETL), 데이터 분석가(ELT)

- PK를 지정한다고 해서 DW가 보장해주는 것이 아님

- HOw? PK 유지방법

incremental update시 

5/30 6/6 까지 기존 8개

5/31날 호출하면 5/31 6/7 새로운 8개

둘의 중복된 값

어떻게 처리? 어떤 값을 우선으로 해서 중복 제거

하나의 날짜에 대해 두개의 레코드 발생

-> created_date 최신값으로 우선순위 날씨정보기 때문에 최근 정보가 더 정확, 최근정보 선택

-> sql 문법 row_number() 윈도우 함수로 보장

ROW_NUMBER() OVER (partiction by date, order by created_date DESC) seq;

date별로 레코드를 모으고  그 안에서 created_date 를 역순으로 sorting한 뒤 일련번호 부여 해서 새로운 컬럼(seq) 추가

1. 임시테이블(스테이징 테이블) 만들고 현재 모든 레코드들을 복사

2. 임시 테이블에 새로 읽어들인 레코드(DAG) 추가 (중복 존재할 수 있음)

3. 중복 걸러주는 SQL 작성 

- ROW_NUMBER 이용

- PK별로 하나의 레코드 잡아냄

- 최신 레코드를 우선 순위로 선택

<-> DISTINCT 보다 안전한 방식, apple 예제와 차이점?

4. 최종 원본 테이블로 복사

- 원본 테이블에서 레코드 삭제

- 임시 temp 테이블을 원본테이블로 복사

Q. 여기서 transation으로 처리되어야 하는 최소 범위의 SQL들은?

4개의 작업을 어떻게 묶어야할까? 1,2 번은 데이터 정합성이 깨지지 않음 3-4 를 하나의 transation으로 묶기

(autocommit = True)일경우만 / false일경우는 모든 작업이 transction이기 때문에 위의 작업이 의미없음? 뭔말일까용

모든작업 commit? 해주면 좋음? 

 

INSET와 UPDATE의 하이브리드 버전 Upsert

위의 과정을 Upsert구현했다고 볼 수 있음

DW마다 UPSERT를 효율적으로 해주는 문법이 있음