Postgresql을 사용하여 OLAP성 대량의 데이터를 집계(Insert)해보자.
하지만 데이터의 공백이 발생하면 안되는 경우에는 Partition Switching을 통해 순간적으로 집계도 가능하다.
예를 들어서 오라클에서 A테이블에 데이터를 넣어야 할 경우 다음과 같은 방식으로 처리할 수 있다.
1. A 테이블을 파티션으로 만들고, 동일한 스키마의 A_TEMP 테이블을 만든다.
2. A_TEMP 테이블에 먼저 데이터를 넣고 A의 파티션과 A_TEMP 테이블을 파티션 SWAP한다.
이 처럼하면 순간적으로 데이터를 바꿔치기하여 데이터의 공백을 없앨 수 있다.
하지만 Postgresql은 그런 Partition Switching 기능이 없다.
그래서 가장 쉬운 Delete 후 Insert 방식을 쓰자니 데이터의 공백이 발생하는 위험이 있다.
Bulk Load 개념의 COPY 명령어가 있기는 하지만 개발할 때 신경쓸게 너무 많아진다. (드라이버 문제라던지, CSV 파일을 저장할 공간/주기 등)
결국 채택한 방법은 Merge이며 Upsert + Delete 형식의 로직을 Function(Procedure)으로 구현기로 하기로 하였다. 물론 ETL Tool(SSIS 등)로 Merge를 구현하는 방법도 있는데 필자는 써보지는 않았고 추후에 시도해보고 싶은 방법이다.
Postgresql Merge Function 구현하기
1. 원본테이블(Destination table)과 같은 스키마의 임시 테이블(Stage Table)을 만들고 그 곳에 데이터 적재하기
2. 원본 테이블에 lock table tbl_nm in exclusive mode 걸기
3. 원본 테이블과 임시테이블을 비교하여 필요없는 데이터 DELETE
4. 원본 테이블과 임시테이블을 비교하여 기존데이터 UPDATE
5. 원본 테이블과 임시테이블을 비교하여 신규데이터 INSERT
샘플은 다음과 같다.
Destination Table은 test_tbl이며 pk는 pk_col이다.
Stage Table은 tmp_test_tbl이고 스키마는 동일하다.
CREATE OR REPLACE FUNCTION ufn_merge_test_tbl()
RETURNS void
LANGUAGE plpgsql
AS $function$
begin
lock table test_tbl in exclusive mode;
/* 삭제된 데이터 삭제 */
delete
from test_tbl
where not exists ( select 1
from tmp_test_tbl
where test_tbl.pk_col = tmp_test_tbl.pk_col ) ;
/* 기존 데이터 업데이트 */
update test_tbl
set pk_col = tmp_test_tbl.pk_col
, col2 = tmp_test_tbl.col2
, col3 = tmp_test_tbl.col3
, col4 = tmp_test_tbl.col4
from tmp_test_tbl
where test_tbl.pk_col = tmp_test_tbl.pk_col ;
/* 신규 데이터 입력 */
insert into test_tbl (
pk_col
, col2
, col3
, col4
) select
tmp_test_tbl.pk_col
, tmp_test_tbl.col2
, tmp_test_tbl.col3
, tmp_test_tbl.col4
from tmp_test_tbl
left join test_tbl
on test_tbl.pk_col = tmp_test_tbl.pk_col
where test_tbl.pk_col is null;
end;
$function$
;
이렇게 하면 데이터의 공백이 없이 운영 중에도 재집계가 가능하다. 하지만 역시 Merge는 속도면에서 매우 비효율적이다. DB 하드웨어가 어느 정도 받쳐줘야 대량 INSERT가 가능하다.
위에서 설명한 ETL 도구(SSIS)에서 MERGE를 구현하게 되면 다음과 같은 그림이 나온다.
 |
| 사진 참조 - mssqltips site |
즉 여러 단계의 Step으로 ETL을 그려야하며 ETL Tool에 Logic이 들어가서 조금 복잡해보이지만 Merge Function을 구현하는 것과 내용은 같다. 개인적으로 ETL에 Logic이 들어가면 복잡해보여서 선호하지 않지만 잘 보면 Step6의 Insert-Update-Delete가 병렬로 수행되어 Merge Function보다 성능이 좋을 것으로 보인다.
또한 Merge function 방식은 DB 서버에서 많은 리소스가 필요하고 ETL Merge 방법은 ETL Server에서 많은 리소스가 필요할 것이다.
결국 필자는 개발하기에 시간관계상, 그리고 유지보수 하는 입장에서 ETL보다는 아무래도 Script가 편하기 때문에 Function으로 구현하였지만 다시 처음부터 개발할 기회가 온다면 ETL로 Merge를 구현하는 것이 성능에 더 좋을 것 같다는 생각이 든다.
댓글 없음:
댓글 쓰기