[DataBase] CDC (Change Data Capture) 용어 정리 및 흐름 파악

자바와 MSSQL을 이용해서 CDC 라는것을 구현 하고자 한다

처음 보는 주제이기도 하고 원리, 흐름을 알아야 수월하게 구현이 가능해서

마주하게될 용어와 구현에 필요한 명령어 라던지 전체 흐름에 대해 살펴 보고자 한다

 

1. 용어정리

- OLTP (online transaction processing)

동시에 발생하는 다수의 트랜잭션을 실행하는 데이터 처리 유형이다
쿼리를 실행하고, 데이터 무결성을 유지하는것을 목표로 한다
ex) 은행의 ATM

 

- CDC (change data capture)

DB에서 데이터가 변경된것을 감지하여
어떠한 행위(Event)를 유발하는 것

쉽게 말해 마지막으로 추출한 이후 변경된 데이터를 골라내는 기술

 

- ETL (extraction, transform, load)

다양한 소스에서 데이터를 추출, 변환 하고
이를 데이터 웨어하우스 또는 기타 통합 데이터 저장소에
로드하는 프로세스
쉽게 말해 한 곳에 저장된 데이터를
필요에 의해 다른곳으로 이동하는 것

 

- LSN (log sequence number)

각 로그 레코드는 고유의 식별자를 가진다
로그 레코드의 식별자를 LSN(log sequence number) 또는 LSA (log sequence address) 라고 부른다
로그는 항상 뒤에 덧붙이는 방식으로 쓰이기 때문에 로그 식별자는 단조 증가 하는 성질을 가진다

 

- 캡처 인스턴스

데이터베이스 내부에 개별 테이블에 대한 변경 내용을 추적하기 위해서
변경 데이터 캡처 (CDC) 를 사용하도록 명시해야 한다
이는 sys.ps_cdc_enable_db를 사용해서 수행한다 (CDC 활성화)

관련 변경 인스턴스 이름에는 _CT 를 붙여 지정한다

모든 변경 내용을 쿼리하는데 사용되는 함수 이름은 fn_cdc_get_all_changes_ 를 붙여 지정한다
(주어진 범위의 LSN에서 변경된 내용을 단계별로 모두 반환해줌)

캡처 인스턴스 순 변경을 지원하도록 구성된 경우 fn_cdc_get_net_changes_ 를 붙여 지정한다
(최종 상태만 반환해 줌)

추가하면 net_changes 쿼리 함수도 만들어지고 이름이 지정된다

1) DB 생성 
2) CDC 활성화 (데이터베이스 범위에서의 활성화) EXECUTE sys.sp_cdc_enable_db;
3) 테이블 생성
4) CDC 활성화 (개별 테이블 단위에서 활성화) EXEC sys.sp_cdc_enable_table
* 꼭 데이터베이스 범위 활성화 가 선행된 후 테이블 단위 활성화가 되야함

 

- CDC 활성화 후 DB에 추가되는 항목들

cdc 스키마
cdc 사용자
메타데이터 테이블, 변경 테이블
추가 정보 조회를 위한 함수
SQL Agent 작업 2개
cdc.db.capture, cdc.db.cleanup

현재 최소 LSN 값 검색하는데 사용 sys.fn_cdc_get_min_lsn
현재 최대 LSN 값 검색하는데 사용 sys.fn_cdc_get_max_lsn

변경 데이터를 쿼리할 때 지정된 LSN 범위가 이러한 두 LSN 값 내에 있지 않으면 
변경 데이터 캡처 쿼리 함수가 실패한다

 

- 변경 테이블

변경 테이블의 처음 5개 열은 메타데이터이다
기록된 변경 내용과 관련된 추가 정보를 제공한다
나머지 열은 원본 테이블에서 이름 및 유형으로 식별된 캡처되 열을 반영한다

 

- CDC 테이블 컬럼

__$start_lsn: CDC 데이터의 시작 LSN 값
__$end_lsn: CDC 데이터의 끝 LSN 값
__$seqval: CDC 데이터의 순차값 (변경 데이터가 생성될 때마다 1씩 증가)
__$operation: CDC 데이터의 변경된 작업의 유형(INSERT, UPDATE, DELETE)
__$update_mask: CDC 데이터의 변경된 컬럼의 마스크 정보 (이진 값)
__$command_id: 데이터 변경 작업을 유일하게 실별하는 값
변경 작업이 특정 트랜잭션 내에서 여러 번 실행될 경우, 동일한 값이 할당됨 (데이터 무결성 검증용 값)

 

- 유효성 간격

데이터베이스에 대한 CDC 유효성 간격은
캡처 인스턴스에 변경 데이터를 사용할 수 있는 시간이다
DB 테이블에 대한 첫 캡처 인스턴스가 만들어질 때 시작되어 현재 시간까지 지속된다

 

- 데이터 정렬 차이점

비 ASCII 문자 (일본어, 중국어) 가 저장될것 같으면
NCHAR 또는 NVARCHAR 데이터 형식을 사용한다
또는 열 및 데이터베이스에 동일한 데이터 정렬을 사용한다

 

2. 데이터 흐름

영문

 

한글

 

1) OLTP에서 DML을 실행한다

 

2) 데이터 원본은 SQL Server 트랜잭션 로그이다

원본 테이블에 삽입, 업데이트, 삭제 가 적용되면

이러한 변경을 설명하는 항목이 로그에 추가 된다

 

3) Log Reader Agent가 로그를 지속적 모니터링 및 캡처 한다

 

4) Capture Process가 캡쳐한 데이터를 Change Table에 저장한다

 

5) ETL 과정을 거쳐 CDC에서 추적 정보를 가공하고

시스템이 사용 가능한 형태(Event)로 제공한다

 

6) Query function을 이용해서 변경 테이블을 액세스 한다