변경 데이터 캡처로 테이블 업데이트 스트리밍
BigQuery 변경 데이터 캡처(CDC)는 스트리밍된 변경사항을 기존 데이터에 처리하고 적용하여 BigQuery 테이블을 업데이트합니다. 이 동기화는 BigQuery Storage Write API에서 실시간으로 스트리밍하는 행 upsert 및 delete 작업을 통해 수행됩니다. 참고로, 계속하기 전에 BigQuery Storage Write API에 대해서는 반드시 숙지해야 합니다.
시작하기 전에
이 문서의 각 태스크를 수행하는 데 필요한 권한을 사용자에게 제공하는 Identity and Access Management(IAM) 역할을 부여해야 하며, 워크플로가 각 기본 요건을 충족하는지 확인해야 합니다.
필수 권한
Storage Write API를 사용하는 데 필요한 권한을 얻으려면 관리자에게 BigQuery 데이터 편집자(roles/bigquery.dataEditor) IAM 역할을 부여해 달라고 요청하세요.
역할 부여에 대한 자세한 내용은 액세스 관리를 참조하세요.
이 사전 정의된 역할에는 Storage Write API를 사용하는 데 필요한 bigquery.tables.updateData 권한이 포함됩니다.
커스텀 역할이나 다른 사전 정의된 역할을 사용하여 이 권한을 부여받을 수도 있습니다.
BigQuery에서 IAM 역할 및 권한에 대한 자세한 내용은 IAM 소개를 참조하세요.
기본 요건
BigQuery CDC를 사용하려면 워크플로가 다음 조건을 충족해야 합니다.
- 기본 스트림에서 Storage Write API를 사용해야 합니다.
- BigQuery에서 대상 테이블의 기본 키를 선언해야 합니다. 최대 16개의 열이 포함된 복합 기본 키가 지원됩니다.
- CDC 행 작업을 수행할 수 있도록 충분한 BigQuery 컴퓨팅 리소스가 제공되어야 합니다. CDC 행 수정 작업이 실패하면 의도했던 데이터를 의도치 않게 보존할 수 있습니다. 자세한 내용은 삭제된 데이터 고려사항을 참조하세요.
기존 레코드 변경사항 지정
BigQuery CDC에서 유사 열 _CHANGE_TYPE은 각 행에서 처리할 변경사항 유형을 나타냅니다. CDC를 사용하려면 Storage Write API를 사용하여 행 수정사항을 스트리밍할 때 _CHANGE_TYPE를 설정하세요. 유사 열 _CHANGE_TYPE에서는 UPSERT 및 DELETE 값만 허용합니다.
Storage Write API가 이러한 방식으로 테이블에 행 수정을 스트리밍하는 동안 테이블은 CDC가 사용 설정된 것으로 간주됩니다.
UPSERT 및 DELETE 값의 예시
BigQuery에서 다음 테이블을 살펴보세요.
| ID | 이름 | 급여 |
|---|---|---|
| 100 | Bill | 2000 |
| 101 | Lucy | 3000 |
| 102 | Ethan | 5000 |
Storage Write API는 다음 행 수정을 스트리밍합니다.
| ID | 이름 | 급여 | _CHANGE_TYPE |
|---|---|---|---|
| 100 | 삭제 | ||
| 101 | Lucy | 8000 | UPSERT |
| 105 | Max | 6000 | UPSERT |
업데이트된 테이블은 이제 다음과 같습니다.
| ID | 이름 | 급여 |
|---|---|---|
| 101 | Lucy | 8000 |
| 102 | Ethan | 5000 |
| 105 | Max | 6000 |
테이블 비활성 관리
기본적으로 쿼리를 실행할 때마다 BigQuery가 최신 결과를 반환합니다. CDC가 사용 설정된 테이블을 쿼리할 때 최신 결과를 제공하려면 BigQuery가 쿼리 시작 시간까지의 각 스트리밍 행 수정을 적용해야 테이블의 최신 버전이 쿼리될 수 있습니다. 쿼리 실행 시 이러한 행 수정사항을 적용하면 쿼리 지연 시간과 비용이 증가합니다. 하지만 완전히 최신의 쿼리 결과가 필요하지 않으면 테이블에서 max_staleness 옵션을 설정하여 쿼리의 비용과 지연 시간을 줄일 수 있습니다. 이 옵션을 설정하면 BigQuery는 max_staleness 값으로 정의된 간격 내에 행 수정사항을 최소 한 번 적용하므로 업데이트가 적용될 때까지 기다리지 않고 약간의 데이터 비활성 비용으로 쿼리를 실행합니다.
이 동작은 데이터 최신 상태가 필요하지 않은 대시보드와 보고서에 특히 유용합니다. 또한 BigQuery가 행 수정을 적용하는 빈도를 보다 효과적으로 제어할 수 있으므로 비용 관리에 유용합니다.
max_staleness 옵션이 설정된 테이블 쿼리
max_staleness 옵션이 설정된 테이블을 쿼리하면 BigQuery는 max_staleness 값과, 테이블의 upsert_stream_apply_watermark 타임스탬프로 표시되는 마지막 적용 작업이 발생한 시간을 기준으로 결과를 반환합니다.
다음 예시에서 테이블에 max_staleness 옵션이 10분으로 설정되어 있고 최근 적용 작업이 T20에 발생했다고 가정해 보겠습니다.
T25에서 테이블을 쿼리하면 테이블의 현재 버전은 10분의 max_staleness 간격보다 짧은 5분 동안 비활성 상태가 됩니다. 이 경우 BigQuery는 T20의 테이블 버전을 반환합니다. 즉, 반환되는 데이터도 5분 정도 비활성입니다.
테이블에 max_staleness 옵션을 설정하면 BigQuery가 max_staleness 간격 내에서 대기 중인 행 수정을 한 번 이상 적용합니다. 하지만 경우에 따라 BigQuery는 이 간격 동안 대기 중인 행 수정사항을 적용하는 프로세스를 완료하지 못할 수 있습니다.
예를 들어 T35에서 테이블을 쿼리하고 보류 중인 행 수정 적용 프로세스가 완료되지 않으면 현재 버전의 테이블은 15분 동안 비활성 상태이며, 이는 max_staleness 간격인 10분보다 큽니다.
이 경우 쿼리 실행 시 BigQuery는 T20과 T35 사이의 모든 행 수정 사항을 적용합니다. 즉, 쿼리 지연 시간이 추가로 발생하지만 데이터가 완전히 최신 상태로 유지됩니다. 이는 런타임 병합 작업으로 간주됩니다.
권장 테이블 max_staleness 값
테이블의 max_staleness 값은 일반적으로 다음 두 값 중 더 큰 값이어야 합니다.
- 워크플로에 허용되는 최대 데이터 비활성
- upsert된 변경사항을 테이블에 적용하는 데 걸리는 최대 시간의 두 배와 추가 버퍼 시간
upsert된 변경사항을 기존 테이블에 적용하는 데 걸리는 시간을 계산하려면 다음 SQL 쿼리를 사용하여 백그라운드 적용 작업의 95번째 백분위수 기간과 BigQuery 쓰기 최적화 스토리지(스트리밍 버퍼) 변환을 허용하는 7분 버퍼를 확인합니다.
SELECT project_id, destination_table.dataset_id, destination_table.table_id, APPROX_QUANTILES((TIMESTAMP_DIFF(end_time, creation_time,MILLISECOND)/1000), 100)[OFFSET(95)] AS p95_background_apply_duration_in_seconds, CEILING(APPROX_QUANTILES((TIMESTAMP_DIFF(end_time, creation_time,MILLISECOND)/1000), 100)[OFFSET(95)]*2/60)+7 AS recommended_max_staleness_with_buffer_in_minutes FROM `region-us`.INFORMATION_SCHEMA.JOBS AS job WHERE project_id = 'PROJECT_ID' AND DATE(creation_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE() AND job_id LIKE "%cdc_background%" GROUP BY 1,2,3;
PROJECT_ID를 BigQuery CDC를 통해 수정 중인 BigQuery 테이블이 포함된 프로젝트의 ID로 바꿉니다.
백그라운드 적용 작업 기간은 비활성 간격 내에서 실행되는 CDC 작업의 수와 복잡성, 테이블 크기, BigQuery 리소스 가용성을 포함하여 여러 요인의 영향을 받습니다. 리소스 가용성에 대한 자세한 내용은 백그라운드 예약의 크기 및 모니터링을 참조하세요.
max_staleness 옵션을 사용하여 테이블 만들기
max_staleness 옵션을 사용하여 테이블을 만들려면 CREATE TABLE 문을 사용하세요.
다음 예시에서는 max_staleness 한도가 10분인 employees 테이블을 만듭니다.
CREATE TABLE employees (
id INT64 PRIMARY KEY NOT ENFORCED,
name STRING)
CLUSTER BY
id
OPTIONS (
max_staleness = INTERVAL 10 MINUTE);
기존 테이블의 max_staleness 옵션 수정
기존 테이블에서 max_staleness 한도를 추가하거나 수정하려면 ALTER TABLE 문을 사용합니다.
다음 예시에서는 employees 테이블의 max_staleness 한도를 15분으로 변경합니다.
ALTER TABLE employees SET OPTIONS ( max_staleness = INTERVAL 15 MINUTE);
테이블의 현재 max_staleness 값 결정
테이블의 현재 max_staleness 값을 확인하려면 INFORMATION_SCHEMA.TABLE_OPTIONS 뷰를 쿼리합니다.
다음 예시에서는 mytable 테이블의 현재 max_staleness 값을 확인합니다.
SELECT option_name, option_value FROM DATASET_NAME.INFORMATION_SCHEMA.TABLE_OPTIONS WHERE option_name = 'max_staleness' AND table_name = 'TABLE_NAME';
다음을 바꿉니다.
DATASET_NAME: CDC가 사용 설정된 테이블이 있는 데이터 세트의 이름입니다.TABLE_NAME: CDC가 사용 설정된 테이블의 이름입니다.
결과는 max_staleness 값이 10분인 것으로 표시됩니다.
+---------------------+--------------+ | Row | option_name | option_value | +---------------------+--------------+ | 1 | max_staleness | 0-0 0 0:10:0 | +---------------------+--------------+
테이블 upsert 작업 진행 상태 모니터링
테이블 상태를 모니터링하고 행 수정이 마지막으로 적용된 시간을 확인하려면 INFORMATION_SCHEMA.TABLES 뷰를 쿼리하여 upsert_stream_apply_watermark 타임스탬프를 가져옵니다.
다음 예시에서는 mytable 테이블의 upsert_stream_apply_watermark 값을 확인합니다.
SELECT upsert_stream_apply_watermark FROM DATASET_NAME.INFORMATION_SCHEMA.TABLES WHERE table_name = 'TABLE_NAME';
다음을 바꿉니다.
DATASET_NAME: CDC가 사용 설정된 테이블이 있는 데이터 세트의 이름입니다.TABLE_NAME: CDC가 사용 설정된 테이블의 이름입니다.
결과는 다음과 비슷합니다.
[{
"upsert_stream_apply_watermark": "2022-09-15T04:17:19.909Z"
}]
upsert 작업은 bigquery-adminbot@system.gserviceaccount.com 서비스 계정으로 수행되며 CDC가 사용 설정된 테이블이 포함된 프로젝트의 작업 기록에 표시됩니다.
CDC에서 사용할 BigQuery 예약 구성
BigQuery 예약을 사용하여 CDC 행 수정 작업에 전용 BigQuery 컴퓨팅 리소스를 할당할 수 있습니다. 예약을 사용하면 이러한 작업을 수행하는 비용에 상한을 설정할 수 있습니다. 이 접근 방식은 큰 테이블에 대해 CDC 작업이 자주 수행되는 워크플로와 같이 각 작업을 수행할 때 처리되는 바이트 수가 많아서 주문형 비용이 높아질 수 있는 경우에 특히 유용합니다.
max_staleness 간격 내에 보류 중인 행 수정을 적용하는 BigQuery CDC 작업은 백그라운드 작업으로 간주되고 QUERY 할당 유형 대신 BACKGROUND 할당 유형을 활용합니다.
반면에 쿼리 실행 시 행 수정을 적용해야 하는 max_staleness 간격 외부의 쿼리는 QUERY 할당 유형을 활용합니다.
BACKGROUND 할당 없이 수행되는 BigQuery CDC 백그라운드 작업은 주문형 가격 책정을 활용합니다.
이러한 고려 사항은 BigQuery CDC에 대해 워크로드 관리 전략을 설계할 때 중요합니다.
CDC에 사용하도록 BigQuery 예약을 구성하려면 먼저 용량 약정을 구입하고 BigQuery 테이블이 있는 리전에 예약을 구성합니다. 예약 크기에 대한 안내는 BACKGROUND 예약 크기 및 모니터링을 참조하세요.
예약을 만든 후에는 BigQuery 프로젝트를 예약에 할당하고, 다음 CREATE ASSIGNMENT 문을 실행하여 job_type 옵션을 BACKGROUND에 설정하세요.
CREATE ASSIGNMENT `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID` OPTIONS ( assignee = 'projects/PROJECT_ID', job_type = 'BACKGROUND');
다음을 바꿉니다.
ADMIN_PROJECT_ID: 예약을 소유한 관리 프로젝트의 IDLOCATION: 예약 위치RESERVATION_NAME: 예약 이름ASSIGNMENT_ID: 할당 ID. ID는 프로젝트 및 위치에 고유해야 하고, 소문자 또는 숫자로 시작하고 끝나야 하고, 소문자, 숫자, 대시만 포함해야 합니다.PROJECT_ID: BigQuery CDC에서 수정 중인 BigQuery 테이블이 포함된 프로젝트의 ID. 이 프로젝트가 예약에 할당됩니다.
BACKGROUND 예약 크기 조정 및 모니터링
예약은 BigQuery 컴퓨팅 작업을 수행하는 데 사용할 수 있는 컴퓨팅 리소스 양을 결정합니다. 예약을 축소하면 CDC 행 수정 작업의 처리 시간이 늘어날 수 있습니다. 예약의 크기를 정확하게 조정하려면 INFORMATION_SCHEMA.JOBS_TIMELINE 뷰를 쿼리하여 CDC 작업을 수행하는 프로젝트의 이전 슬롯 소비를 모니터링하세요.
SELECT period_start, SUM(period_slot_ms) / (1000 * 60) AS slots_used FROM REGION.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT WHERE DATE(job_creation_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE() AND job_id LIKE '%cdc_background%' GROUP BY period_start ORDER BY period_start DESC;
REGION을 프로젝트가 있는 리전 이름으로 바꿉니다. 예를 들면 region-us입니다.
데이터 고려사항 삭제
- BigQuery CDC 작업은 BigQuery 컴퓨팅 리소스를 활용합니다. CDC 작업이 주문형 결제를 사용하도록 구성된 경우 CDC 작업은 내부 BigQuery 리소스를 사용하여 정기적으로 수행됩니다. CDC 작업이
BACKGROUND예약으로 구성된 경우 CDC 작업에는 구성된 예약의 리소스 가용성이 적용됩니다. 구성된 예약 내에서 사용 가능한 리소스가 충분하지 않으면 삭제를 포함한 CDC 작업을 처리하는 데 예상보다 시간이 오래 걸릴 수 있습니다. - CDC
DELETE작업은 Storage Write API가 작업을 스트리밍한 시점의upsert_stream_apply_watermark타임스탬프를 경과한 경우에만 적용되는 것으로 간주됩니다. 작업이 적용되면 표준 Google Cloud 데이터 삭제 프로세스가 시작됩니다.upsert_stream_apply_watermark타임스탬프에 대한 자세한 내용은 테이블 업데이트 작업 진행 상태 모니터링을 참조하세요.
제한사항
- BigQuery CDC는 키 적용을 수행하지 않으므로 기본 키가 고유해야 합니다.
- 기본 키는 16개의 열을 초과할 수 없습니다.
- CDC가 사용 설정된 테이블은 다음을 지원하지 않습니다.
DELETE,UPDATE,MERGE등의 데이터 조작 언어(DML) 문 변형- 와일드 카드 테이블 쿼리
- 검색 색인
- 테이블의
max_staleness값이 너무 낮기 때문에 런타임 병합 작업을 수행하는 CDC 지원 테이블은 다음을 지원할 수 없습니다. - CDC가 사용 설정된 테이블의 BigQuery 내보내기 작업은 아직 백그라운드 작업에 적용되지 않은 최근에 스트리밍된 행 수정사항을 내보내지 않습니다. 전체 테이블을 내보내려면
EXPORT DATA문을 사용합니다. - 쿼리가 파티션을 나눈 테이블에서 런타임 병합을 트리거하면 쿼리가 파티션의 하위 집합으로 제한되는지 여부에 관계없이 전체 테이블이 스캔됩니다.
- Standard 버전을 사용하는 경우
BACKGROUND예약을 사용할 수 없으므로 대기 중인 행 수정을 적용할 때 주문형 가격 책정 모델을 사용합니다. 하지만 버전에 관계없이 CDC가 사용 설정된 테이블을 쿼리할 수 있습니다.
BigQuery CDC 가격 책정
BigQuery CDC는 데이터 수집용 Storage Write API, 데이터 스토리지용 BigQuery 스토리지, 행 수정 작업에 BigQuery 컴퓨팅을 사용하며, 이러한 모든 작업에는 비용이 발생합니다. 가격 책정 정보는 BigQuery 가격 책정을 참조하세요.
BigQuery CDC 비용 추정
일반적인 BigQuery 비용 예상 권장사항 외에도, 대량의 데이터, 낮은 max_staleness 구성 또는 자주 변경되는 데이터를 가진 워크플로의 경우 BigQuery CDC 비용 예측은 중요할 수 있습니다.
BigQuery 데이터 수집 가격 책정 및 BigQuery 스토리지 가격 책정은 수집하고 저장하는 데이터 양에 따라 직접 계산됩니다. 하지만 BigQuery 컴퓨팅 가격 책정은 BigQuery CDC 작업을 실행하는 데 사용되는 컴퓨팅 리소스 소비와 관련이 있기 때문에 추정하기가 더 어려울 수 있습니다.
BigQuery CDC 작업은 3개 카테고리로 나뉩니다.
- 백그라운드 적용 작업: 테이블의
max_staleness값으로 정의된 일정한 간격으로 백그라운드에서 실행되는 작업입니다. 이러한 작업은 최근에 스트리밍된 행 수정사항을 CDC가 사용 설정된 테이블에 적용합니다. - 쿼리 작업:
max_staleness창 내에서 실행되고 CDC 기준 테이블에서만 읽을 수 있는 GoogleSQL 쿼리입니다. - 런타임 병합 작업:
max_staleness창 외부에서 실행되는 임시 GoogleSQL 쿼리에 의해 트리거되는 작업입니다. 이러한 작업은 CDC 기준 테이블과 최근에 스트리밍된 행 수정을 쿼리 런타임에서 즉석에서 병합해야 합니다.
세 가지 유형의 BigQuery CDC 작업 모두 BigQuery 클러스터링을 사용하지만 쿼리 작업만 BigQuery 파티션 나누기를 활용합니다. 최근에 스트리밍된 행 수정사항을 적용할 때 최근에 스트리밍된 upserts가 적용되는 테이블 파티션이 보장되지 않기 때문에 백그라운드 적용 작업과 런타임 병합 작업에서 파티션 나누기를 사용할 수 없습니다. 즉, 백그라운드 적용 작업 및 런타임 병합 작업 중에 전체 기준 테이블이 읽혀집니다. CDC 작업을 수행하기 위해 읽는 데이터의 양을 이해하면 총 비용을 추정하는 데 도움이 됩니다.
테이블 기준에서 읽는 데이터의 양이 많으면 처리된 데이터의 양을 기반으로 하지 않는 BigQuery 용량 가격 책정 모델을 사용하는 것이 좋습니다.
BigQuery CDC 비용 권장사항
일반 BigQuery 비용 권장사항 외에도 다음 기법을 사용하여 BigQuery CDC 작업 비용을 최적화하세요.
- 필요한 경우가 아니라면 테이블의
max_staleness옵션을 매우 낮은 값으로 구성하지 마세요.max_staleness값은 백그라운드 적용 작업과 런타임 병합 작업의 발생 횟수를 늘릴 수 있으므로 쿼리 작업보다 비용이 높고 속도가 느립니다. 자세한 안내는 권장 테이블max_staleness값을 참조하세요. - CDC 테이블에서 사용할 BigQuery 예약 구성을 고려해 보세요.
그렇지 않으면 백그라운드 적용 작업과 런타임 병합 작업이 주문형 가격 책정 방식을 사용하므로 더 많은 데이터 처리로 인해 비용이 많이 들 수 있습니다. 자세한 내용은 BigQuery 예약을 알아보고 BigQuery CDC 사용 시
BACKGROUND예약 크기를 조정하고 모니터링하는 방법에 대한 안내를 따르세요.
다음 단계
- Storage Write API 기본 스트림을 구현하는 방법 알아보기
- Storage Write API 권장사항 알아보기
- BigQuery CDC로 Datastream을 사용하여 트랜잭션 데이터베이스를 BigQuery에 복제하는 방법 알아보기