데이터 백필
데이터 백필 (Backfilling Data)
ClickHouse를 처음 접하든 기존 배포를 운영하든, 이력 데이터로 테이블을 백필(backfill)해야 하는 경우는 피할 수 없어요. 어떤 경우에는 비교적 단순하지만, 매터리얼라이즈드 뷰를 채워야 할 때는 더 복잡해질 수 있어요. 이 가이드는 사용 사례에 적용할 수 있는 몇 가지 과정을 문서화할게요.
출처: 문서
본문
이 가이드는 증분 매터리얼라이즈드 뷰(Incremental Materialized Views)와 s3, gcs 같은 테이블 함수를 사용한 데이터 적재의 개념에 이미 익숙하다고 가정해요. 또한 객체 스토리지에서의 insert 성능 최적화 가이드를 읽어볼 것을 권장하는데, 그 조언은 이 가이드 전체의 insert에 적용될 수 있어요.
예시 데이터셋
이 가이드 전체에서 PyPI 데이터셋을 사용해요. 이 데이터셋의 각 행은 pip 같은 도구를 사용한 Python 패키지 다운로드를 나타내요. 예를 들어 이 부분집합은 하루인 2024-12-17을 커버하며 https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/2024-12-17/에서 공개적으로 사용할 수 있어요. 다음으로 쿼리할 수 있어요.
SELECT count()
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/2024-12-17/*.parquet', NOSIGN)
┌────count()─┐
│ 2039988137 │ -- 2.04 billion
└────────────┘
1 row in set. Elapsed: 32.726 sec. Processed 2.04 billion rows, 170.05 KB (62.34 million rows/s., 5.20 KB/s.)
Peak memory usage: 239.50 MiB.
이 버킷의 전체 데이터셋은 320GB가 넘는 parquet 파일을 포함해요. 아래 예시에서는 glob 패턴으로 의도적으로 부분집합을 겨냥해요. 이 날짜 이후의 데이터에 대해서는 사용자가 Kafka나 객체 스토리지 같은 곳에서 이 데이터의 스트림을 소비한다고 가정해요. 이 데이터의 스키마는 아래와 같아요.
DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/2024-12-17/*.parquet', NOSIGN)
FORMAT PrettyCompactNoEscapesMonoBlock
SETTINGS describe_compact_output = 1
┌─name───────────────┬─type────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ timestamp │ Nullable(DateTime64(6)) │
│ country_code │ Nullable(String) │
│ url │ Nullable(String) │
│ project │ Nullable(String) │
│ file │ Tuple(filename Nullable(String), project Nullable(String), version Nullable(String), type Nullable(String)) │
│ installer │ Tuple(name Nullable(String), version Nullable(String)) │
│ python │ Nullable(String) │
│ implementation │ Tuple(name Nullable(String), version Nullable(String)) │
│ distro │ Tuple(name Nullable(String), version Nullable(String), id Nullable(String), libc Tuple(lib Nullable(String), version Nullable(String))) │
│ system │ Tuple(name Nullable(String), release Nullable(String)) │
│ cpu │ Nullable(String) │
│ openssl_version │ Nullable(String) │
│ setuptools_version │ Nullable(String) │
│ rustc_version │ Nullable(String) │
│ tls_protocol │ Nullable(String) │
│ tls_cipher │ Nullable(String) │
└────────────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1조 개가 넘는 행으로 구성된 전체 PyPI 데이터셋은 공개 데모 환경 clickpy.clickhouse.com에서 사용할 수 있어요. 이 데이터셋에 대한 더 자세한 내용 — 데모가 성능을 위해 매터리얼라이즈드 뷰를 어떻게 활용하는지, 데이터가 매일 어떻게 채워지는지 포함 — 은 여기를 참고하세요.
백필 시나리오
백필은 일반적으로 어떤 시점부터 데이터 스트림을 소비할 때 필요해요. 이 데이터는 삽입되는 블록에 따라 트리거되는 증분 매터리얼라이즈드 뷰가 있는 ClickHouse 테이블에 삽입돼요. 이 뷰들은 삽입 전에 데이터를 변환하거나, 집계를 계산하고 그 결과를 대상 테이블로 보내 나중에 다운스트림 애플리케이션에서 사용할 수 있게 해요. 다음 시나리오들을 다룰게요.
- 기존 데이터 수집으로 데이터 백필 — 새 데이터가 적재되고 있고, 이력 데이터를 백필해야 해요. 이 이력 데이터는 식별되어 있어요.
- 기존 테이블에 매터리얼라이즈드 뷰 추가 — 이력 데이터가 채워지고 데이터가 이미 스트리밍 중인 설정에 새 매터리얼라이즈드 뷰를 추가해야 해요.
데이터는 객체 스토리지에서 백필된다고 가정해요. 모든 경우에서 데이터 삽입의 중단을 피하는 것을 목표로 해요. 객체 스토리지에서 이력 데이터를 백필하는 것을 권장해요. 최적의 읽기 성능과 압축(네트워크 전송 감소)을 위해 가능하면 데이터를 Parquet으로 내보내야 해요. 약 150MB의 파일 크기가 일반적으로 선호되지만, ClickHouse는 70개가 넘는 파일 포맷을 지원하고 모든 크기의 파일을 처리할 수 있어요.
중복 테이블과 뷰 사용
모든 시나리오에서 "중복 테이블과 뷰(duplicate tables and views)" 개념에 의존해요. 이 테이블과 뷰는 라이브 스트리밍 데이터에 사용되는 것들의 복사본을 나타내며, 실패가 발생해도 복구가 쉬운 방식으로 백필을 격리해 수행할 수 있게 해요. 예를 들어 다음의 주 pypi 테이블과 Python 프로젝트별 다운로드 수를 계산하는 매터리얼라이즈드 뷰가 있어요.
CREATE TABLE pypi
(
`timestamp` DateTime,
`country_code` LowCardinality(String),
`project` String,
`type` LowCardinality(String),
`installer` LowCardinality(String),
`python_minor` LowCardinality(String),
`system` LowCardinality(String),
`on` String
)
ENGINE = MergeTree
ORDER BY (project, timestamp)
CREATE TABLE pypi_downloads
(
`project` String,
`count` Int64
)
ENGINE = SummingMergeTree
ORDER BY project
CREATE MATERIALIZED VIEW pypi_downloads_mv TO pypi_downloads
AS SELECT
project,
count() AS count
FROM pypi
GROUP BY project
주 테이블과 관련 뷰를 데이터 부분집합으로 채워요.
INSERT INTO pypi SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/2024-12-17/1734393600-000000000{000..100}.parquet', NOSIGN)
0 rows in set. Elapsed: 15.702 sec. Processed 41.23 million rows, 3.94 GB (2.63 million rows/s., 251.01 MB/s.)
Peak memory usage: 977.49 MiB.
SELECT count() FROM pypi
┌──count()─┐
│ 20612750 │ -- 20.61 million
└──────────┘
1 row in set. Elapsed: 0.004 sec.
SELECT sum(count)
FROM pypi_downloads
┌─sum(count)─┐
│ 20612750 │ -- 20.61 million
└────────────┘
1 row in set. Elapsed: 0.006 sec. Processed 96.15 thousand rows, 769.23 KB (16.53 million rows/s., 132.26 MB/s.)
Peak memory usage: 682.38 KiB.
다른 부분집합 {101..200}을 적재하고 싶다고 가정해 볼게요. pypi에 직접 삽입할 수도 있지만, 중복 테이블을 만들어 이 백필을 격리해서 수행할 수 있어요. 백필이 실패하면 주 테이블에 영향이 없고 중복 테이블을 truncate하고 다시 시도하면 돼요. 이 뷰들의 새 복사본을 만들려면 _v2 접미사와 함께 CREATE TABLE AS 절을 사용할 수 있어요.
CREATE TABLE pypi_v2 AS pypi
CREATE TABLE pypi_downloads_v2 AS pypi_downloads
CREATE MATERIALIZED VIEW pypi_downloads_mv_v2 TO pypi_downloads_v2
AS SELECT
project,
count() AS count
FROM pypi_v2
GROUP BY project
이것을 거의 같은 크기의 두 번째 부분집합으로 채우고 성공적인 적재를 확인해요.
INSERT INTO pypi_v2 SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/2024-12-17/1734393600-000000000{101..200}.parquet', NOSIGN)
0 rows in set. Elapsed: 17.545 sec. Processed 40.80 million rows, 3.90 GB (2.33 million rows/s., 222.29 MB/s.)
Peak memory usage: 991.50 MiB.
SELECT count()
FROM pypi_v2
┌──count()─┐
│ 20400020 │ -- 20.40 million
└──────────┘
1 row in set. Elapsed: 0.004 sec.
SELECT sum(count)
FROM pypi_downloads_v2
┌─sum(count)─┐
│ 20400020 │ -- 20.40 million
└────────────┘
1 row in set. Elapsed: 0.006 sec. Processed 95.49 thousand rows, 763.90 KB (14.81 million rows/s., 118.45 MB/s.)
Peak memory usage: 688.77 KiB.
이 두 번째 적재 동안 어느 시점에서든 실패했다면, pypi_v2와 pypi_downloads_v2를 truncate하고 데이터 적재를 반복하면 됐어요. 데이터 적재가 완료되면 ALTER TABLE MOVE PARTITION 절로 중복 테이블에서 주 테이블로 데이터를 옮길 수 있어요.
ALTER TABLE pypi_v2 MOVE PARTITION () TO pypi
0 rows in set. Elapsed: 1.401 sec.
ALTER TABLE pypi_downloads_v2 MOVE PARTITION () TO pypi_downloads
0 rows in set. Elapsed: 0.389 sec.
파티션 이름 위 MOVE PARTITION 호출은 파티션 이름 ()을 사용해요. 이것은 이 테이블의 단일 파티션(파티셔닝되지 않은)을 나타내요. 파티셔닝된 테이블에서는 각 파티션마다 하나씩 여러 MOVE PARTITION 호출을 해야 해요. 현재 파티션의 이름은 system.parts 테이블에서 확인할 수 있어요. 예: SELECT DISTINCT partition FROM system.parts WHERE (table = 'pypi_v2').
이제 pypi와 pypi_downloads가 완전한 데이터를 포함하는지 확인할 수 있어요. pypi_downloads_v2와 pypi_v2는 안전하게 삭제할 수 있어요.
SELECT count()
FROM pypi
┌──count()─┐
│ 41012770 │ -- 41.01 million
└──────────┘
1 row in set. Elapsed: 0.003 sec.
SELECT sum(count)
FROM pypi_downloads
┌─sum(count)─┐
│ 41012770 │ -- 41.01 million
└────────────┘
1 row in set. Elapsed: 0.007 sec. Processed 191.64 thousand rows, 1.53 MB (27.34 million rows/s., 218.74 MB/s.)
SELECT count()
FROM pypi_v2
중요한 것은 MOVE PARTITION 연산이 가볍고(하드 링크 활용) 원자적이라는 점이에요. 즉 중간 상태 없이 실패하거나 성공해요. 아래 백필 시나리오에서 이 과정을 많이 활용할게요. 이 과정은 사용자가 각 insert 연산의 크기를 선택해야 한다는 점에 주목하세요. 더 큰 insert, 즉 더 많은 행은 더 적은 MOVE PARTITION 연산이 필요하게 해요. 그러나 실패(예: 네트워크 중단) 시 복구 비용과 균형을 맞춰야 해요. 파일 배치(batching)로 이 과정을 보완해 위험을 줄일 수 있어요. WHERE timestamp BETWEEN 2024-12-17 09:00:00 AND 2024-12-17 10:00:00 같은 범위 쿼리나 glob 패턴으로 이 작업을 수행할 수 있어요. 예를 들어,
INSERT INTO pypi_v2 SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/2024-12-17/1734393600-000000000{101..200}.parquet', NOSIGN)
INSERT INTO pypi_v2 SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/2024-12-17/1734393600-000000000{201..300}.parquet', NOSIGN)
INSERT INTO pypi_v2 SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/2024-12-17/1734393600-000000000{301..400}.parquet', NOSIGN)
--continued to all files loaded OR MOVE PARTITION call is performed
ClickPipes는 객체 스토리지에서 데이터를 적재할 때 이 방식을 사용해, 대상 테이블과 그 매터리얼라이즈드 뷰의 중복을 자동으로 만들고 사용자가 위 단계를 수행할 필요를 없애요. 또한 각각 다른 부분집합(glob 패턴)을 처리하는 여러 worker 스레드와 자체 중복 테이블을 사용함으로써, 데이터를 exactly-once 의미로 빠르게 적재할 수 있어요. 관심 있는 분들을 위해 더 자세한 내용은 이 블로그에서 확인할 수 있어요.
시나리오 1: 기존 데이터 수집으로 데이터 백필
이 시나리오에서는 백필할 데이터가 격리된 버킷에 없다고 가정하므로 필터링이 필요해요. 데이터는 이미 삽입되고 있고, 이력 데이터를 백필해야 하는 타임스탬프나 단조 증가 컬럼을 식별할 수 있어요. 이 과정은 다음 단계를 따릅니다.
- 체크포인트 식별 — 이력 데이터를 복원해야 하는 타임스탬프나 컬럼 값.
- 주 테이블과 매터리얼라이즈드 뷰 대상 테이블의 중복 생성.
- (2)에서 만든 대상 테이블을 가리키는 매터리얼라이즈드 뷰의 복사본 생성.
- (2)에서 만든 중복 주 테이블에 삽입.
- 중복 테이블의 모든 파티션을 원래 버전으로 이동. 중복 테이블 삭제.
예를 들어 PyPI 데이터에서 데이터가 적재되어 있다고 가정해 볼게요. 최소 타임스탬프, 즉 우리의 "체크포인트"를 식별할 수 있어요.
SELECT min(timestamp)
FROM pypi
┌──────min(timestamp)─┐
│ 2024-12-17 09:00:00 │
└─────────────────────┘
1 row in set. Elapsed: 0.163 sec. Processed 1.34 billion rows, 5.37 GB (8.24 billion rows/s., 32.96 GB/s.)
Peak memory usage: 227.84 MiB.
위에서 2024-12-17 09:00:00 이전의 데이터를 적재해야 한다는 것을 알 수 있어요. 앞선 과정을 사용해 중복 테이블과 뷰를 만들고 타임스탬프 필터로 부분집합을 적재해요.
CREATE TABLE pypi_v2 AS pypi
CREATE TABLE pypi_downloads_v2 AS pypi_downloads
CREATE MATERIALIZED VIEW pypi_downloads_mv_v2 TO pypi_downloads_v2
AS SELECT project, count() AS count
FROM pypi_v2
GROUP BY project
INSERT INTO pypi_v2 SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/2024-12-17/1734393600-*.parquet', NOSIGN)
WHERE timestamp < '2024-12-17 09:00:00'
0 rows in set. Elapsed: 500.152 sec. Processed 2.74 billion rows, 364.40 GB (5.47 million rows/s., 728.59 MB/s.)
Parquet의 타임스탬프 컬럼 필터링은 매우 효율적일 수 있어요. ClickHouse는 적재할 전체 데이터 범위를 식별하기 위해 타임스탬프 컬럼만 읽어 네트워크 트래픽을 최소화해요. min-max 같은 Parquet 인덱스도 ClickHouse 쿼리 엔진이 활용할 수 있어요. 이 insert가 완료되면 관련 파티션을 이동할 수 있어요.
ALTER TABLE pypi_v2 MOVE PARTITION () TO pypi
ALTER TABLE pypi_downloads_v2 MOVE PARTITION () TO pypi_downloads
이력 데이터가 격리된 버킷이라면 위의 시간 필터는 필요 없어요. 시간 또는 단조 컬럼을 사용할 수 없다면 이력 데이터를 격리하세요. ClickHouse Cloud에서는 그냥 ClickPipes를 사용하세요 ClickHouse Cloud를 사용한다면, 데이터가 자체 버킷으로 격리될 수 있다면(필터가 필요 없다면) 이력 백업 복원에 ClickPipes를 사용해야 해요. 여러 worker로 적재를 병렬화해 적재 시간을 줄이는 것에 더해, ClickPipes는 위 과정을 자동화하고 주 테이블과 매터리얼라이즈드 뷰 모두에 중복 테이블을 만들어요.
시나리오 2: 기존 테이블에 매터리얼라이즈드 뷰 추가
상당한 데이터가 채워지고 데이터가 삽입되고 있는 설정에 새 매터리얼라이즈드 뷰를 추가해야 하는 것은 드물지 않아요. 스트림의 지점을 식별하는 데 사용할 수 있는 타임스탬프나 단조 증가 컬럼이 여기서 유용하며, 데이터 수집의 중단을 피해요. 아래 예시에서는 두 경우를 모두 가정하고, 수집 중단을 피하는 접근을 선호해요. POPULATE 피하기 POPULATE 명령을 수집이 중단된 소규모 데이터셋이 아닌 경우 매터리얼라이즈드 뷰 백필에 사용하는 것은 권장하지 않아요. 이 연산은 소스 테이블에 삽입된 행을 놓칠 수 있는데, populate 해시가 끝난 후에 생성된 매터리얼라이즈드 뷰이기 때문이에요. 게다가 이 populate는 모든 데이터에 대해 실행되며 대규모 데이터셋에서 중단이나 메모리 제한에 취약해요.
타임스탬프 또는 단조 증가 컬럼 사용 가능
이 경우 새 매터리얼라이즈드 뷰가 임의의 미래 데이터보다 큰 행만 제한하는 필터를 포함하도록 권장해요. 이후 이 날짜부터 주 테이블의 이력 데이터로 매터리얼라이즈드 뷰를 백필할 수 있어요. 백필 접근 방식은 데이터 크기와 관련 쿼리의 복잡성에 따라 달라요. 가장 단순한 접근은 다음 단계를 포함해요.
- 가까운 미래의 임의 시간보다 큰 행만 고려하는 필터로 매터리얼라이즈드 뷰를 생성.
- 매터리얼라이즈드 뷰의 대상 테이블로 삽입하는
INSERT INTO SELECT쿼리 실행 — 소스 테이블에서 뷰의 집계 쿼리로 읽기.
이것은 (2)에서 데이터 부분집합을 겨냥하도록 더 향상될 수 있고/또는 매터리얼라이즈드 뷰에 중복 대상 테이블을 사용해(insert가 끝나면 원본에 파티션 attach) 실패 후 복구를 더 쉽게 할 수 있어요. 시간당 가장 인기 있는 프로젝트를 계산하는 다음 매터리얼라이즈드 뷰를 고려해 볼게요.
CREATE TABLE pypi_downloads_per_day
(
`hour` DateTime,
`project` String,
`count` Int64
)
ENGINE = SummingMergeTree
ORDER BY (project, hour)
CREATE MATERIALIZED VIEW pypi_downloads_per_day_mv TO pypi_downloads_per_day
AS SELECT
toStartOfHour(timestamp) as hour,
project,
count() AS count
FROM pypi
GROUP BY
hour,
project
대상 테이블을 추가할 수 있지만, 매터리얼라이즈드 뷰를 추가하기 전에 그 SELECT 절을 수정해 가까운 미래의 임의 시간보다 큰 행만 고려하는 필터를 포함시켜요 — 이 경우 2024-12-17 09:00:00이 몇 분 후라고 가정해요.
CREATE MATERIALIZED VIEW pypi_downloads_per_day_mv TO pypi_downloads_per_day
AS SELECT
toStartOfHour(timestamp) AS hour,
project, count() AS count
FROM pypi WHERE timestamp >= '2024-12-17 09:00:00'
GROUP BY hour, project
이 뷰가 추가되면 이 데이터 이전의 매터리얼라이즈드 뷰 데이터를 모두 백필할 수 있어요. 가장 단순한 방법은 매터리얼라이즈드 뷰의 쿼리를 최근에 추가된 데이터를 무시하는 필터로 주 테이블에 실행하고, INSERT INTO SELECT로 결과를 뷰의 대상 테이블에 삽입하는 거예요. 예를 들어 위 뷰의 경우:
INSERT INTO pypi_downloads_per_day SELECT
toStartOfHour(timestamp) AS hour,
project,
count() AS count
FROM pypi
WHERE timestamp < '2024-12-17 09:00:00'
GROUP BY
hour,
project
Ok.
0 rows in set. Elapsed: 2.830 sec. Processed 798.89 million rows, 17.40 GB (282.28 million rows/s., 6.15 GB/s.)
Peak memory usage: 543.71 MiB.
위 예시에서 대상 테이블은 SummingMergeTree예요. 이 경우 원래 집계 쿼리를 그냥 사용할 수 있어요. AggregatingMergeTree를 활용하는 더 복잡한 사용 사례에서는 집계에 -State 함수를 사용할 거예요. 그 예시는 이 통합 가이드에서 찾을 수 있어요.
우리 경우 이것은 3초 미만에 완료되고 600MiB 미만을 사용하는 비교적 가벼운 집계예요. 더 복잡하거나 오래 실행되는 집계의 경우, 앞선 중복 테이블 접근으로 이 과정을 더 복원력 있게 만들 수 있어요. 즉, pypi_downloads_per_day_v2 같은 그림자 대상 테이블을 만들어 여기에 삽입하고, 결과 파티션을 pypi_downloads_per_day에 attach하는 거예요. 종종 매터리얼라이즈드 뷰의 쿼리는 더 복잡할 수 있고(그렇지 않으면 사용자가 뷰를 쓰지 않을 테니까 드물지 않아요) 리소스를 소비해요. 드물게는 이 쿼리의 리소스가 서버의 능력을 넘어설 수도 있어요. 이것은 ClickHouse 매터리얼라이즈드 뷰의 장점 중 하나를 강조해요. 증분적이라 전체 데이터셋을 한 번에 처리하지 않아요! 이 경우 사용자에게 여러 옵션이 있어요.
- 범위를 백필하도록 쿼리를 수정. 예:
WHERE timestamp BETWEEN 2024-12-17 08:00:00 AND 2024-12-17 09:00:00,WHERE timestamp BETWEEN 2024-12-17 07:00:00 AND 2024-12-17 08:00:00등. - Null table engine을 사용해 매터리얼라이즈드 뷰를 채움. 이것은 매터리얼라이즈드 뷰의 일반적인 증분 채움을 재현해서 (구성 가능한 크기의) 데이터 블록에 걸쳐 뷰의 쿼리를 실행해요.
(1)이 가장 단순한 접근이며 종종 충분해요. 간결함을 위해 예시는 포함하지 않아요. 아래에서 (2)를 더 살펴볼게요.
매터리얼라이즈드 뷰 채우기에 Null table engine 사용
Null table engine은 데이터를 지속하지 않는 저장 엔진을 제공해요 (테이블 엔진 세계의 /dev/null로 생각하면 돼요). 모순적으로 보이지만, 매터리얼라이즈드 뷰는 이 테이블 엔진에 삽입된 데이터에 여전히 실행돼요. 이것은 원본 데이터를 지속하지 않고도 — I/O와 관련 저장을 피하면서 — 매터리얼라이즈드 뷰를 구성할 수 있게 해줘요. 중요한 것은 이 테이블 엔진에 연결된 매터리얼라이즈드 뷰들이 여전히 삽입되는 데이터 블록에 걸쳐 실행되어, 그 결과를 대상 테이블로 보낸다는 거예요. 이 블록들은 구성 가능한 크기예요. 더 큰 블록은 잠재적으로 더 효율적일(그리고 처리하기 더 빠를) 수 있지만, 더 많은 리소스(주로 메모리)를 소비해요. 이 테이블 엔진을 사용하면 매터리얼라이즈드 뷰를 점진적으로 — 한 번에 한 블록씩 — 만들 수 있어, 전체 집계를 메모리에 유지할 필요를 없애요.
다음 예시를 고려해 볼게요.
CREATE TABLE pypi_v2
(
`timestamp` DateTime,
`project` String
)
ENGINE = Null
CREATE MATERIALIZED VIEW pypi_downloads_per_day_mv_v2 TO pypi_downloads_per_day
AS SELECT
toStartOfHour(timestamp) as hour,
project,
count() AS count
FROM pypi_v2
GROUP BY
hour,
project
여기서 매터리얼라이즈드 뷰를 만드는 데 사용될 행을 받기 위해 Null 테이블 pypi_v2를 만들어요. 필요한 컬럼만으로 스키마를 제한한 것을 주목하세요. 우리의 매터리얼라이즈드 뷰는 이 테이블에 삽입되는 행(한 번에 한 블록)에 대해 집계를 수행해, 결과를 대상 테이블 pypi_downloads_per_day로 보내요.
여기서는 pypi_downloads_per_day를 대상 테이블로 사용했어요. 더 큰 복원력을 위해 사용자는 이전 예시들에서 보았듯 중복 테이블 pypi_downloads_per_day_v2를 만들어 뷰의 대상 테이블로 사용할 수 있어요. insert가 완료되면 pypi_downloads_per_day_v2의 파티션을 pypi_downloads_per_day로 이동할 수 있어요. 이렇게 하면 메모리 문제나 서버 중단으로 insert가 실패할 경우 복구할 수 있어요. 즉 pypi_downloads_per_day_v2를 truncate하고 설정을 튜닝한 뒤 다시 시도하면 돼요.
이 매터리얼라이즈드 뷰를 채우려면 백필할 관련 데이터를 pypi에서 pypi_v2로 삽입하기만 하면 돼요.
INSERT INTO pypi_v2 SELECT timestamp, project FROM pypi WHERE timestamp < '2024-12-17 09:00:00'
0 rows in set. Elapsed: 27.325 sec. Processed 1.50 billion rows, 33.48 GB (54.73 million rows/s., 1.23 GB/s.)
Peak memory usage: 639.47 MiB.
여기서 메모리 사용량이 639.47 MiB인 것을 주목하세요. 위 시나리오에서 성능과 리소스를 결정하는 요소는 여러 가지예요. 튜닝을 시도하기 전에 독자들은 Optimizing for S3 Insert and Read Performance guide의 Using Threads for Reads 섹션에 자세히 문서화된 insert 메커니즘을 이해할 것을 권장해요. 요약하면:
- 읽기 병렬성 (Read Parallelism) — 읽기에 사용되는 스레드 수.
max_threads로 제어해요. ClickHouse Cloud에서는 인스턴스 크기에 따라 결정되며 기본값은 vCPU 수예요. 이 값을 늘리면 더 많은 메모리 사용을 대가로 읽기 성능이 향상될 수 있어요. - 삽입 병렬성 (Insert Parallelism) — 삽입에 사용되는 insert 스레드 수.
max_insert_threads로 제어해요. 양수 값의 경우 유효한 삽입 병렬성은min(max_insert_threads, max_threads)예요. 값0은 자동으로 CPU 코어 수로 정해지며, 메모리 압박 시max_insert_threads_min_free_memory_per_thread로 줄어들 수 있어요. ClickHouse Cloud에서 기본값은 인스턴스 크기에 따라 결정돼요: 8GiB 메모리 노드에1, 16GiB 노드에2, 더 큰 노드에4. OSS에서는 26.8부터 기본값이0이에요. 이 값을 늘리면 더 많은 메모리 사용을 대가로 성능이 향상될 수 있어요. - Insert 블록 크기 (Insert Block Size) — 데이터는 파티셔닝 키를 기반으로 가져오고 파싱하고 인메모리 insert 블록으로 형성되는 루프로 처리돼요. 이 블록들은 정렬·최적화·압축되어 새 데이터 파트로 저장에 쓰여요.
min_insert_block_size_rows와min_insert_block_size_bytes(비압축) 설정으로 제어되는 insert 블록의 크기는 메모리 사용과 디스크 I/O에 영향을 줘요. 더 큰 블록은 더 많은 메모리를 쓰지만 파트를 더 적게 만들어 I/O와 백그라운드 병합을 줄여요. 이 설정들은 최소 임계값을 나타내요 (먼저 도달하는 쪽이 flush를 트리거해요). - 매터리얼라이즈드 뷰 블록 크기 (Materialized view block size) — 주 insert의 위 메커니즘에 더해, 매터리얼라이즈드 뷰로 삽입되기 전에 블록들이 더 효율적인 처리를 위해 squash돼요. 이 블록들의 크기는
min_insert_block_size_bytes_for_materialized_views와min_insert_block_size_rows_for_materialized_views설정으로 결정돼요. 더 큰 블록은 더 많은 메모리 사용을 대가로 더 효율적인 처리를 허용해요. 기본적으로 이 설정들은 소스 테이블 설정min_insert_block_size_rows와min_insert_block_size_bytes의 값으로 되돌아가요.
단순 INSERT SELECT 쿼리 팁: 복잡한 변환이 없는 단순한 INSERT INTO t1 SELECT * FROM t2 쿼리의 경우 optimize_trivial_insert_select=1을 켜는 것을 고려해 보세요. 이 설정(24.7 이후 기본적으로 비활성화)은 SELECT 병렬성을 max_insert_threads에 맞게 자동 조정해 리소스 사용과 생성되는 파트 수를 줄여요. 이것은 테이블 간 대량 데이터 마이그레이션에 특히 유용해요.
성능 개선을 위해 Optimizing for S3 Insert and Read Performance guide의 Tuning Threads and Block Size for Inserts 섹션에 설명된 지침을 따를 수 있어요. 대부분의 경우 성능 개선을 위해 min_insert_block_size_bytes_for_materialized_views와 min_insert_block_size_rows_for_materialized_views를 수정할 필요는 없어요. 수정한다면 min_insert_block_size_rows와 min_insert_block_size_bytes에 대해 논의된 것과 같은 모범 사례를 사용하세요. 메모리를 최소화하려면 이 설정들을 실험해 볼 수 있어요. 이것은 필연적으로 성능을 낮출 거예요. 앞선 쿼리로 아래에 예시를 보여드릴게요. max_insert_threads를 1로 낮추면 메모리 오버헤드가 줄어요.
INSERT INTO pypi_v2
SELECT
timestamp,
project
FROM pypi
WHERE timestamp < '2024-12-17 09:00:00'
SETTINGS max_insert_threads = 1
0 rows in set. Elapsed: 27.752 sec. Processed 1.50 billion rows, 33.48 GB (53.89 million rows/s., 1.21 GB/s.)
Peak memory usage: 506.78 MiB.
max_threads 설정을 1로 줄여 메모리를 더 낮출 수 있어요.
INSERT INTO pypi_v2
SELECT timestamp, project
FROM pypi
WHERE timestamp < '2024-12-17 09:00:00'
SETTINGS max_insert_threads = 1, max_threads = 1
Ok.
0 rows in set. Elapsed: 43.907 sec. Processed 1.50 billion rows, 33.48 GB (34.06 million rows/s., 762.54 MB/s.)
Peak memory usage: 272.53 MiB.
마지막으로 min_insert_block_size_rows를 0으로(블록 크기 결정 요인으로 비활성화) 하고 min_insert_block_size_bytes를 10485760(10MiB)으로 설정해 메모리를 더 줄일 수 있어요.
INSERT INTO pypi_v2
SELECT
timestamp,
project
FROM pypi
WHERE timestamp < '2024-12-17 09:00:00'
SETTINGS max_insert_threads = 1, max_threads = 1, min_insert_block_size_rows = 0, min_insert_block_size_bytes = 10485760
0 rows in set. Elapsed: 43.293 sec. Processed 1.50 billion rows, 33.48 GB (34.54 million rows/s., 773.36 MB/s.)
Peak memory usage: 218.64 MiB.
마지막으로 블록 크기를 낮추면 더 많은 파트가 생기고 병합 압력이 커진다는 점을 알아두세요. 여기에 논의된 대로 이 설정들은 신중하게 변경해야 해요.
타임스탬프 또는 단조 증가 컬럼 없음
위 과정들은 사용자가 타임스탬프나 단조 증가 컬럼을 가진 것에 의존해요. 어떤 경우에는 그것이 단순히 없을 수도 있어요. 이 경우 다음 과정을 권장하는데, 앞서 설명한 여러 단계를 활용하지만 수집을 중단해야 해요.
- 주 테이블로의 삽입을 중단.
CREATE AS문법으로 주 대상 테이블의 중복을 생성.ALTER TABLE ATTACH로 원본 대상 테이블에서 중복으로 파티션을 attach. 참고: 이 attach 연산은 앞서 사용한 move와 다릅니다. 하드 링크에 의존하지만 원본 테이블의 데이터는 보존됩니다.- 새 매터리얼라이즈드 뷰 생성.
- 삽입 재시작. 참고: 삽입은 대상 테이블만 업데이트하고, 원본 데이터만 참조하는 중복은 업데이트하지 않습니다.
- 중복 테이블을 소스로 사용해, 타임스탬프가 있는 데이터에 대해 위에서 사용한 것과 같은 과정으로 매터리얼라이즈드 뷰를 백필.
PyPI와 우리의 이전 새 매터리얼라이즈드 뷰 pypi_downloads_per_day를 사용한 다음 예시를 고려해 볼게요 (타임스탬프를 사용할 수 없다고 가정):
SELECT count() FROM pypi
┌────count()─┐
│ 2039988137 │ -- 2.04 billion
└────────────┘
1 row in set. Elapsed: 0.003 sec.
-- (1) Pause inserts
-- (2) Create a duplicate of our target table
CREATE TABLE pypi_v2 AS pypi
SELECT count() FROM pypi_v2
┌────count()─┐
│ 2039988137 │ -- 2.04 billion
└────────────┘
1 row in set. Elapsed: 0.004 sec.
-- (3) Attach partitions from the original target table to the duplicate.
ALTER TABLE pypi_v2
(ATTACH PARTITION tuple() FROM pypi)
-- (4) Create our new materialized views
CREATE TABLE pypi_downloads_per_day
(
`hour` DateTime,
`project` String,
`count` Int64
)
ENGINE = SummingMergeTree
ORDER BY (project, hour)
CREATE MATERIALIZED VIEW pypi_downloads_per_day_mv TO pypi_downloads_per_day
AS SELECT
toStartOfHour(timestamp) as hour,
project,
count() AS count
FROM pypi
GROUP BY
hour,
project
-- (4) Restart inserts. We replicate here by inserting a single row.
INSERT INTO pypi SELECT *
FROM pypi
LIMIT 1
SELECT count() FROM pypi
┌────count()─┐
│ 2039988138 │ -- 2.04 billion
└────────────┘
1 row in set. Elapsed: 0.003 sec.
-- notice how pypi_v2 contains same number of rows as before
SELECT count() FROM pypi_v2
┌────count()─┐
│ 2039988137 │ -- 2.04 billion
└────────────┘
-- (5) Backfill the view using the backup pypi_v2
INSERT INTO pypi_downloads_per_day SELECT
toStartOfHour(timestamp) as hour,
project,
count() AS count
FROM pypi_v2
GROUP BY
hour,
project
0 rows in set. Elapsed: 3.719 sec. Processed 2.04 billion rows, 47.15 GB (548.57 million rows/s., 12.68 GB/s.)
DROP TABLE pypi_v2;
마지막 두 번째 단계에서는 앞서 설명한 단순 INSERT INTO SELECT 접근으로 pypi_downloads_per_day를 백필해요. 이것은 더 큰 복원력을 위해 선택적으로 중복 테이블을 사용한 위에서 문서화한 Null 테이블 접근으로도 향상될 수 있어요. 이 연산은 삽입 중단이 필요하지만, 중간 연산은 일반적으로 빠르게 완료될 수 있어 데이터 중단을 최소화해요.