증분 머티얼라이즈드 뷰

증분 머티얼라이즈드 뷰 (Incremental materialized view)

증분 머티얼라이즈드 뷰(Materialized View)는 계산 비용을 쿼리 시간에서 삽입 시간으로 옮겨 SELECT 쿼리를 더 빠르게 만들어 줍니다. 테이블에 데이터가 들어올 때마다 트리거처럼 동작해 집계·필터·변환 결과를 타깃 테이블에 쌓아 두는 방식이에요.

출처: 문서

본문

배경 (Background)

증분 머티얼라이즈드 뷰(Materialized Views)는 계산 비용을 쿼리 시간에서 삽입 시간으로 옮겨 SELECT 쿼리를 더 빠르게 만들어 줍니다.

Postgres 같은 트랜잭션 데이터베이스와 달리, ClickHouse 머티얼라이즈드 뷰는 테이블에 데이터 블록이 삽입될 때 그 블록에 대해 쿼리를 실행하는 트리거일 뿐입니다. 이 쿼리의 결과는 두 번째 "타깃" 테이블에 삽입됩니다. 행이 더 삽입되면 결과가 다시 타깃 테이블로 보내져 중간 결과가 갱신되고 병합됩니다. 이 병합된 결과는 원본 데이터 전체에 대해 쿼리를 실행한 것과 동일합니다.

머티얼라이즈드 뷰의 주요 동기는 타깃 테이블에 삽입된 결과가 행에 대한 집계·필터·변환의 결과를 나타낸다는 점입니다. 이 결과는 흔히 원본 데이터보다 작은 표현입니다(집계의 경우 부분 스케치(partial sketch)). 이것과 더불어 타깃 테이블에서 결과를 읽는 쿼리가 단순하다는 점 덕분에, 같은 계산을 원본 데이터에서 수행하는 것보다 쿼리 시간이 더 빠르며 계산(그리고 쿼리 지연)을 쿼리 시간에서 삽입 시간으로 옮깁니다.

ClickHouse의 머티얼라이즈드 뷰는 데이터가 기반 테이블로 흘러들어오는 대로 실시간으로 갱신되어, 계속 갱신되는 인덱스처럼 동작합니다. 이것은 머티얼라이즈드 뷰가 보통 리프레시해야 하는 쿼리의 정적 스냅샷인 다른 데이터베이스와 대조적입니다(ClickHouse 리프레시 가능 머티얼라이즈드 뷰와 유사).

예제

예제를 위해 "Schema Design"에 문서화된 Stack Overflow 데이터셋을 사용하겠습니다.

게시물(Post)에 대한 일별 up/down 투표 수를 얻고 싶다고 가정해 봅시다.

CREATE TABLE votes
(
    `Id` UInt32,
    `PostId` Int32,
    `VoteTypeId` UInt8,
    `CreationDate` DateTime64(3, 'UTC'),
    `UserId` Int32,
    `BountyAmount` UInt8
)
ENGINE = MergeTree
ORDER BY (VoteTypeId, CreationDate, PostId)

INSERT INTO votes SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/votes/*.parquet')

0 rows in set. Elapsed: 29.359 sec. Processed 238.98 million rows, 2.13 GB (8.14 million rows/s., 72.45 MB/s.)

toStartOfDay 함수 덕분에 ClickHouse에서는 상당히 단순한 쿼리입니다:

SELECT toStartOfDay(CreationDate) AS day,
       countIf(VoteTypeId = 2) AS UpVotes,
       countIf(VoteTypeId = 3) AS DownVotes
FROM votes
GROUP BY day
ORDER BY day ASC
LIMIT 10

┌─────────────────day─┬─UpVotes─┬─DownVotes─┐
│ 2008-07-31 00:00:00 │       6 │         0 │
│ 2008-08-01 00:00:00 │     182 │        50 │
│ 2008-08-02 00:00:00 │     436 │       107 │
│ 2008-08-03 00:00:00 │     564 │       100 │
│ 2008-08-04 00:00:00 │    1306 │       259 │
│ 2008-08-05 00:00:00 │    1368 │       269 │
│ 2008-08-06 00:00:00 │    1701 │       211 │
│ 2008-08-07 00:00:00 │    1544 │       211 │
│ 2008-08-08 00:00:00 │    1241 │       212 │
│ 2008-08-09 00:00:00 │     576 │        46 │
└─────────────────────┴─────────┴───────────┘

10 rows in set. Elapsed: 0.133 sec. Processed 238.98 million rows, 2.15 GB (1.79 billion rows/s., 16.14 GB/s.)
Peak memory usage: 363.22 MiB.

이 쿼리는 ClickHouse 덕분에 이미 빠르지만, 더 잘할 수 있을까요?

머티얼라이즈드 뷰로 삽입 시간에 이 계산을 수행하려면 결과를 받을 테이블이 필요합니다. 이 테이블은 날짜별로 행을 하나만 유지해야 합니다. 기존 날짜에 대한 갱신이 오면 다른 컬럼을 기존 날짜의 행으로 병합해야 합니다. 이 증분 상태 병합이 일어나려면 다른 컬럼에 대해 부분 상태(partial state)가 저장되어 있어야 합니다.

이를 위해 ClickHouse의 특수 엔진 타입, SummingMergeTree가 필요합니다. 이것은 같은 정렬 키를 가진 모든 행을, 숫자 컬럼의 합산 값을 포함하는 하나의 행으로 대체합니다. 다음 테이블은 같은 날짜를 가진 모든 행을 병합해 숫자 컬럼을 합산합니다:

CREATE TABLE up_down_votes_per_day
(
  `Day` Date,
  `UpVotes` UInt32,
  `DownVotes` UInt32
)
ENGINE = SummingMergeTree
ORDER BY Day

머티얼라이즈드 뷰를 시연하기 위해 votes 테이블이 비어 있고 아직 데이터를 받지 않았다고 가정합시다. 우리의 머티얼라이즈드 뷰는 votes에 삽입된 데이터에 대해 위 SELECT를 수행하고 결과를 up_down_votes_per_day로 보냅니다:

CREATE MATERIALIZED VIEW up_down_votes_per_day_mv TO up_down_votes_per_day AS
SELECT toStartOfDay(CreationDate)::Date AS Day,
       countIf(VoteTypeId = 2) AS UpVotes,
       countIf(VoteTypeId = 3) AS DownVotes
FROM votes
GROUP BY Day

여기서 TO 절이 핵심입니다. 결과가 어디로 보내질지, 즉 up_down_votes_per_day를 나타냅니다.

이전 삽입에서 votes 테이블을 다시 채울 수 있습니다:

INSERT INTO votes SELECT toUInt32(Id) AS Id, toInt32(PostId) AS PostId, VoteTypeId, CreationDate, UserId, BountyAmount
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/votes/*.parquet')

0 rows in set. Elapsed: 111.964 sec. Processed 477.97 million rows, 3.89 GB (4.27 million rows/s., 34.71 MB/s.)
Peak memory usage: 283.49 MiB.

완료되면 up_down_votes_per_day의 크기를 확인할 수 있습니다 — 날짜당 한 행이어야 합니다:

SELECT count()
FROM up_down_votes_per_day
FINAL

┌─count()─┐
│    5723 │
└─────────┘

우리는 쿼리 결과를 저장해 행 수를 votes의 2억 3800만 개에서 약 5000개로 줄였습니다. 그러나 핵심은 votes 테이블에 새 투표가 삽입되면 새 값이 해당 날짜의 up_down_votes_per_day로 보내져 비동기적으로 백그라운드에서 자동 병합된다는 점입니다 — 날짜당 한 행만 유지하면서요. 따라서 up_down_votes_per_day는 항상 작고 최신 상태입니다.

행 병합은 비동기적이므로, 사용자가 쿼리할 때 날짜당 여러 투표가 있을 수 있습니다. 쿼리 시점에 남은 행을 확실히 병합하려면 두 가지 옵션이 있습니다:

• 테이블 이름에 FINAL 수정자를 사용합니다. 위의 count 쿼리에서 그렇게 했습니다.
• 최종 테이블에 사용된 정렬 키, 즉 CreationDate로 집계하고 메트릭을 합산합니다. 보통 이 방식이 더 효율적이고 유연하지만(테이블을 다른 용도로 쓸 수 있음), 전자가 일부 쿼리에는 더 단순할 수 있습니다. 아래에 둘 다 보여 줍니다:

SELECT
        Day,
        UpVotes,
        DownVotes
FROM up_down_votes_per_day
FINAL
ORDER BY Day ASC
LIMIT 10

10 rows in set. Elapsed: 0.004 sec. Processed 8.97 thousand rows, 89.68 KB (2.09 million rows/s., 20.89 MB/s.)
Peak memory usage: 289.75 KiB.

SELECT Day, sum(UpVotes) AS UpVotes, sum(DownVotes) AS DownVotes
FROM up_down_votes_per_day
GROUP BY Day
ORDER BY Day ASC
LIMIT 10

┌────────Day─┬─UpVotes─┬─DownVotes─┐
│ 2008-07-31 │       6 │         0 │
│ 2008-08-01 │     182 │        50 │
│ 2008-08-02 │     436 │       107 │
│ 2008-08-03 │     564 │       100 │
│ 2008-08-04 │    1306 │       259 │
│ 2008-08-05 │    1368 │       269 │
│ 2008-08-06 │    1701 │       211 │
│ 2008-08-07 │    1544 │       211 │
│ 2008-08-08 │    1241 │       212 │
│ 2008-08-09 │     576 │        46 │
└────────────┴─────────┴───────────┘

10 rows in set. Elapsed: 0.010 sec. Processed 8.97 thousand rows, 89.68 KB (907.32 thousand rows/s., 9.07 MB/s.)
Peak memory usage: 567.61 KiB.

이로써 쿼리가 0.133초에서 0.004초로 빨라졌습니다 — 25배 이상의 개선입니다!

중요: ORDER BY = GROUP BY. 대부분의 경우, SummingMergeTree나 AggregatingMergeTree 테이블 엔진을 사용할 때 머티얼라이즈드 뷰 변환의 GROUP BY 절에 사용된 컬럼은 타깃 테이블의 ORDER BY 절에 사용된 컬럼과 일치해야 합니다. 이 엔진들은 백그라운드 병합 연산 중에 ORDER BY 컬럼에 의존해 동일한 값을 가진 행을 병합합니다. GROUP BY와 ORDER BY 컬럼 간의 불일치는 비효율적인 쿼리 성능, 최적이 아닌 병합, 심지어 데이터 불일치로 이어질 수 있습니다.

더 복잡한 예제

위 예제는 머티얼라이즈드 뷰를 사용해 하루에 두 개의 합을 계산하고 유지합니다. 합(sums)은 부분 상태를 유지하기 가장 단순한 집계 형태입니다 — 새 값이 도착하면 기존 값에 더하기만 하면 됩니다. 그러나 ClickHouse 머티얼라이즈드 뷰는 어떤 집계 타입에도 사용할 수 있습니다.

게시물에 대한 통계를 각 날짜별로 계산하고 싶다고 가정해 봅시다: Score의 99.9번째 백분위수와 CommentCount의 평균. 이 계산 쿼리는 다음과 같을 것입니다:

SELECT
        toStartOfDay(CreationDate) AS Day,
        quantile(0.999)(Score) AS Score_99th,
        avg(CommentCount) AS AvgCommentCount
FROM posts
GROUP BY Day
ORDER BY Day DESC
LIMIT 10

┌─────────────────Day─┬────────Score_99th─┬────AvgCommentCount─┐
│ 2024-03-31 00:00:00 │  5.23700000000008 │ 1.3429811866859624 │
│ 2024-03-30 00:00:00 │                 5 │ 1.3097158891616976 │
│ 2024-03-29 00:00:00 │  5.78899999999976 │ 1.2827635327635327 │
│ 2024-03-28 00:00:00 │                 7 │  1.277746158224246 │
│ 2024-03-27 00:00:00 │ 5.738999999999578 │ 1.2113264918282023 │
│ 2024-03-26 00:00:00 │                 6 │ 1.3097536945812809 │
│ 2024-03-25 00:00:00 │                 6 │ 1.2836721018539201 │
│ 2024-03-24 00:00:00 │ 5.278999999999996 │ 1.2931667891256429 │
│ 2024-03-23 00:00:00 │ 6.253000000000156 │  1.334061135371179 │
│ 2024-03-22 00:00:00 │ 9.310999999999694 │ 1.2388059701492538 │
└─────────────────────┴───────────────────┴────────────────────┘

10 rows in set. Elapsed: 0.113 sec. Processed 59.82 million rows, 777.65 MB (528.48 million rows/s., 6.87 GB/s.)
Peak memory usage: 658.84 MiB.

앞에서처럼, posts 테이블에 새 게시물이 삽입될 때 위 쿼리를 실행하는 머티얼라이즈드 뷰를 만들 수 있습니다.

예제를 위해, 그리고 S3에서 posts 데이터를 불러오는 것을 피하기 위해, posts와 같은 스키마의 복제 테이블 posts_null을 만들겠습니다. 그러나 이 테이블은 데이터를 저장하지 않고 행이 삽입될 때 머티얼라이즈드 뷰가 사용하기만 합니다. 데이터 저장을 막으려면 Null 테이블 엔진 타입을 사용할 수 있습니다.

CREATE TABLE posts_null AS posts ENGINE = Null

Null 테이블 엔진은 강력한 최적화입니다. /dev/null이라고 생각하면 됩니다. 우리의 머티얼라이즈드 뷰는 posts_null 테이블이 삽입 시점에 행을 받으면 요약 통계를 계산하고 저장합니다 — 트리거일 뿐입니다. 그러나 원시 데이터는 저장되지 않습니다. 우리 경우에는 원본 posts를 여전히 저장하고 싶겠지만, 이 접근 방식은 원시 데이터의 저장 오버헤드를 피하면서 집계를 계산하는 데 사용할 수 있습니다.

머티얼라이즈드 뷰는 따라서 다음과 같아집니다:

CREATE MATERIALIZED VIEW post_stats_mv TO post_stats_per_day AS
       SELECT toStartOfDay(CreationDate) AS Day,
       quantileState(0.999)(Score) AS Score_quantiles,
       avgState(CommentCount) AS AvgCommentCount
FROM posts_null
GROUP BY Day

집계 함수 끝에 State 접미사를 붙인 것에 주목하세요. 이렇게 하면 함수의 집계 상태가 최종 결과 대신 반환됩니다. 이것은 이 부분 상태가 다른 상태와 병합될 수 있게 하는 추가 정보를 담습니다. 예를 들어 평균의 경우 컬럼의 count와 sum이 포함됩니다.

부분 집계 상태는 올바른 결과를 계산하는 데 필요합니다. 예를 들어 평균을 계산할 때 부분 범위의 평균들을 그냥 평균내는 것은 잘못된 결과를 만듭니다.

이제 이 뷰의 타깃 테이블 post_stats_per_day를 만드는데, 이것은 부분 집계 상태를 저장합니다:

CREATE TABLE post_stats_per_day
(
  `Day` Date,
  `Score_quantiles` AggregateFunction(quantile(0.999), Int32),
  `AvgCommentCount` AggregateFunction(avg, UInt8)
)
ENGINE = AggregatingMergeTree
ORDER BY Day

앞서 SummingMergeTree로 count를 저장하기에 충분했지만, 다른 함수에는 더 고급 엔진 타입 AggregatingMergeTree가 필요합니다. ClickHouse가 집계 상태가 저장될 것임을 알도록 Score_quantiles와 AvgCommentCount를 AggregateFunction 타입으로 정의하고, 부분 상태의 함수 소스와 소스 컬럼의 타입을 지정합니다. SummingMergeTree처럼 같은 ORDER BY 키 값을 가진 행(위 예제의 Day)은 병합됩니다.

post_stats_per_day를 머티얼라이즈드 뷰로 채우려면, posts의 모든 행을 posts_null에 삽입하면 됩니다:

INSERT INTO posts_null SELECT * FROM posts

0 rows in set. Elapsed: 13.329 sec. Processed 119.64 million rows, 76.99 GB (8.98 million rows/s., 5.78 GB/s.)

프로덕션에서는 머티얼라이즈드 뷰를 posts 테이블에 붙일 것입니다. 여기서는 null 테이블을 시연하기 위해 posts_null을 사용했습니다.

우리의 최종 쿼리는 함수에 Merge 접미사를 사용해야 합니다(컬럼이 부분 집계 상태를 저장하므로):

SELECT
        Day,
        quantileMerge(0.999)(Score_quantiles),
        avgMerge(AvgCommentCount)
FROM post_stats_per_day
GROUP BY Day
ORDER BY Day DESC
LIMIT 10

여기서 FINAL 대신 GROUP BY를 사용한 것에 주목하세요.

다른 응용

위 내용은 주로 머티얼라이즈드 뷰를 사용해 데이터의 부분 집계를 증분 갱신함으로써 계산을 쿼리 시간에서 삽입 시간으로 옮기는 것에 초점을 맞췄습니다. 이 일반적인 사용 사례를 넘어, 머티얼라이즈드 뷰에는 다른 응용도 많이 있습니다.

필터링과 변환

어떤 상황에서는 삽입 시 행과 컬럼의 부분집합만 삽입하고 싶을 수 있습니다. 이 경우 posts_null 테이블이 삽입을 받고, SELECT 쿼리가 posts 테이블에 삽입되기 전에 행을 필터링할 수 있습니다. 예를 들어 posts 테이블의 Tags 컬럼을 변환하고 싶다고 가정해 봅시다. 이것은 파이프로 구분된 태그 이름 목록을 담고 있습니다. 이것들을 배열로 변환하면 개별 태그 값으로 더 쉽게 집계할 수 있습니다.

이 변환은 INSERT INTO SELECT를 실행할 때 수행할 수도 있습니다. 머티얼라이즈드 뷰는 이 로직을 ClickHouse DDL로 캡슐화하고 INSERT를 단순하게 유지하며, 변환이 새 행에 적용되게 합니다.

이 변환을 위한 우리의 머티얼라이즈드 뷰는 아래와 같습니다:

CREATE MATERIALIZED VIEW posts_mv TO posts AS
        SELECT * EXCEPT Tags, arrayFilter(t -> (t != ''), splitByChar('|', Tags)) as Tags FROM posts_null

룩업 테이블

ClickHouse 정렬 키를 고를 때는 접근 패턴을 고려해야 합니다. 필터와 집계 절에서 자주 사용되는 컬럼을 사용해야 합니다. 이것은 사용자들이 더 다양한 접근 패턴을 갖는 시나리오에서는 제약이 될 수 있는데, 단일 컬럼 집합으로 캡슐화할 수 없기 때문입니다. 예를 들어 다음 comments 테이블을 고려해 보세요:

CREATE TABLE comments
(
    `Id` UInt32,
    `PostId` UInt32,
    `Score` UInt16,
    `Text` String,
    `CreationDate` DateTime64(3, 'UTC'),
    `UserId` Int32,
    `UserDisplayName` LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY PostId

0 rows in set. Elapsed: 46.357 sec. Processed 90.38 million rows, 11.14 GB (1.95 million rows/s., 240.22 MB/s.)

여기 정렬 키는 PostId로 필터링하는 쿼리에 맞게 테이블을 최적화합니다.

사용자가 특정 UserId로 필터링하고 평균 Score를 계산하려 한다고 가정해 봅시다:

SELECT avg(Score)
FROM comments
WHERE UserId = 8592047

┌──────────avg(Score)─┐
│ 0.18181818181818182 │
└─────────────────────┘

1 row in set. Elapsed: 0.778 sec. Processed 90.38 million rows, 361.59 MB (116.16 million rows/s., 464.74 MB/s.)
Peak memory usage: 217.08 MiB.

빠르긴 하지만(데이터가 ClickHouse에겐 작음), 처리된 행 수 9038만 개에서 전체 테이블 스캔이 필요함을 알 수 있습니다. 더 큰 데이터셋에서는 머티얼라이즈드 뷰를 사용해 필터링 컬럼 UserId에 대한 정렬 키 값 PostId를 룩업할 수 있습니다. 이 값들을 사용해 효율적인 룩업을 수행할 수 있습니다.

이 예제에서 우리의 머티얼라이즈드 뷰는 매우 단순할 수 있습니다. 삽입 시 comments에서 PostId와 UserId만 선택합니다. 이 결과는 차례로 UserId로 정렬된 comments_posts_users 테이블로 보내집니다. 아래에 Comments 테이블의 null 버전을 만들고 이를 사용해 뷰와 comments_posts_users 테이블을 채웁니다:

CREATE TABLE comments_posts_users (
  PostId UInt32,
  UserId Int32
) ENGINE = MergeTree ORDER BY UserId

CREATE TABLE comments_null AS comments
ENGINE = Null

CREATE MATERIALIZED VIEW comments_posts_users_mv TO comments_posts_users AS
SELECT PostId, UserId FROM comments_null

INSERT INTO comments_null SELECT * FROM comments

0 rows in set. Elapsed: 5.163 sec. Processed 90.38 million rows, 17.25 GB (17.51 million rows/s., 3.34 GB/s.)

이제 이 뷰를 서브쿼리에서 사용해 이전 쿼리를 가속화할 수 있습니다:

SELECT avg(Score)
FROM comments
WHERE PostId IN (
        SELECT PostId
        FROM comments_posts_users
        WHERE UserId = 8592047
) AND UserId = 8592047

┌──────────avg(Score)─┐
│ 0.18181818181818182 │
└─────────────────────┘

1 row in set. Elapsed: 0.012 sec. Processed 88.61 thousand rows, 771.37 KB (7.09 million rows/s., 61.73 MB/s.)

체이닝 / 캐스케이딩 머티얼라이즈드 뷰

머티얼라이즈드 뷰는 체이닝(또는 캐스케이딩)될 수 있어 복잡한 워크플로를 구축할 수 있습니다. 자세한 내용은 "Cascading materialized views" 가이드를 참고하세요.

머티얼라이즈드 뷰와 JOIN

다음은 증분 머티얼라이즈드 뷰에만 적용됩니다. 리프레시 가능 머티얼라이즈드 뷰는 전체 타깃 데이터셋에 대해 주기적으로 쿼리를 실행하며 JOIN을 완전히 지원합니다. 결과 신선도의 저하를 용인할 수 있다면 복잡한 JOIN에 사용을 고려하세요.

ClickHouse의 증분 머티얼라이즈드 뷰는 JOIN 연산을 완전히 지원하지만, 중요한 제약이 하나 있습니다: 머티얼라이즈드 뷰는 소스 테이블(쿼리에서 가장 왼쪽의 테이블)에 대한 삽입에서만 트리거됩니다. JOIN의 오른쪽 테이블은 데이터가 바뀌어도 업데이트를 트리거하지 않습니다. 이 동작은 삽입 시간에 데이터가 집계되거나 변환되는 증분 머티얼라이즈드 뷰를 구축할 때 특히 중요합니다.

증분 머티얼라이즈드 뷰가 JOIN으로 정의될 때, SELECT 쿼리에서 가장 왼쪽 테이블이 소스 역할을 합니다. 이 테이블에 새 행이 삽입되면 ClickHouse는 새로 삽입된 행들로만 머티얼라이즈드 뷰 쿼리를 실행합니다. JOIN의 오른쪽 테이블은 이 실행 동안 전체를 읽지만, 그것들만의 변화는 뷰를 트리거하지 않습니다.

이 동작은 머티얼라이즈드 뷰의 JOIN을 정적 차원 데이터에 대한 스냅샷 조인과 비슷하게 만듭니다.

이것은 참조 테이블이나 차원 테이블로 데이터를 보강하는 데 잘 동작합니다. 그러나 오른쪽 테이블(예: 사용자 메타데이터)에 대한 어떤 업데이트도 머티얼라이즈드 뷰를 소급해 업데이트하지 않습니다. 갱신된 데이터를 보려면 소스 테이블에 새 삽입이 도착해야 합니다.

예제

Stack Overflow 데이터셋을 사용한 구체적인 예제를 살펴보겠습니다. users 테이블의 사용자 표시 이름을 포함해 사용자별 일별 배지(daily badges)를 계산하는 머티얼라이즈드 뷰를 사용하겠습니다.

상기하자면, 우리의 테이블 스키마는 다음과 같습니다:

CREATE TABLE badges
(
    `Id` UInt32,
    `UserId` Int32,
    `Name` LowCardinality(String),
    `Date` DateTime64(3, 'UTC'),
    `Class` Enum8('Gold' = 1, 'Silver' = 2, 'Bronze' = 3),
    `TagBased` Bool
)
ENGINE = MergeTree
ORDER BY UserId

CREATE TABLE users
(
    `Id` Int32,
    `Reputation` UInt32,
    `CreationDate` DateTime64(3, 'UTC'),
    `DisplayName` LowCardinality(String),
    `LastAccessDate` DateTime64(3, 'UTC'),
    `Location` LowCardinality(String),
    `Views` UInt32,
    `UpVotes` UInt32,
    `DownVotes` UInt32
)
ENGINE = MergeTree
ORDER BY Id;

users 테이블이 미리 채워져 있다고 가정합니다:

INSERT INTO users
SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/users.parquet');

머티얼라이즈드 뷰와 연관 타깃 테이블은 다음과 같이 정의됩니다:

CREATE TABLE daily_badges_by_user
(
    Day Date,
    UserId Int32,
    DisplayName LowCardinality(String),
    Gold UInt32,
    Silver UInt32,
    Bronze UInt32
)
ENGINE = SummingMergeTree
ORDER BY (DisplayName, UserId, Day);

CREATE MATERIALIZED VIEW daily_badges_by_user_mv TO daily_badges_by_user AS
SELECT
    toDate(Date) AS Day,
    b.UserId,
    u.DisplayName,
    countIf(Class = 'Gold') AS Gold,
    countIf(Class = 'Silver') AS Silver,
    countIf(Class = 'Bronze') AS Bronze
FROM badges AS b
LEFT JOIN users AS u ON b.UserId = u.Id
GROUP BY Day, b.UserId, u.DisplayName;

그룹핑과 정렬 정렬 일치. 머티얼라이즈드 뷰의 GROUP BY 절은 SummingMergeTree 타깃 테이블의 ORDER BY와 일치하도록 DisplayName, UserId, Day를 포함해야 합니다. 이렇게 해야 행이 올바르게 집계되고 병합됩니다. 이 중 하나라도 생략하면 잘못된 결과나 비효율적인 병합이 발생할 수 있습니다.

이제 badges를 채우면 뷰가 트리거되어 daily_badges_by_user 테이블이 채워집니다.

INSERT INTO badges SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/badges.parquet')

0 rows in set. Elapsed: 433.762 sec. Processed 1.16 billion rows, 28.50 GB (2.67 million rows/s., 65.70 MB/s.)

특정 사용자가 달성한 배지를 보고 싶다면 다음 쿼리를 쓸 수 있습니다:

SELECT *
FROM daily_badges_by_user
FINAL
WHERE DisplayName = 'gingerwizard'

┌────────Day─┬──UserId─┬─DisplayName──┬─Gold─┬─Silver─┬─Bronze─┐
│ 2023-02-27 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2023-02-28 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2013-10-30 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2024-03-04 │ 2936484 │ gingerwizard │    0 │      1 │      0 │
│ 2024-03-05 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2023-04-17 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2013-11-18 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2023-10-31 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
└────────────┴─────────┴──────────────┴──────┴────────┴────────┘

8 rows in set. Elapsed: 0.018 sec. Processed 32.77 thousand rows, 642.14 KB (1.86 million rows/s., 36.44 MB/s.)

이제 이 사용자가 새 배지를 받아 행이 삽입되면 뷰가 업데이트됩니다:

INSERT INTO badges VALUES (53505058, 2936484, 'gingerwizard', now(), 'Gold', 0);

1 row in set. Elapsed: 7.517 sec.

SELECT *
FROM daily_badges_by_user
FINAL
WHERE DisplayName = 'gingerwizard'

┌────────Day─┬──UserId─┬─DisplayName──┬─Gold─┬─Silver─┬─Bronze─┐
│ 2013-10-30 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2013-11-18 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2023-02-27 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2023-02-28 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2023-04-17 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2023-10-31 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2024-03-04 │ 2936484 │ gingerwizard │    0 │      1 │      0 │
│ 2024-03-05 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2025-04-13 │ 2936484 │ gingerwizard │    1 │      0 │      0 │
└────────────┴─────────┴──────────────┴──────┴────────┴────────┘

9 rows in set. Elapsed: 0.017 sec. Processed 32.77 thousand rows, 642.27 KB (1.96 million rows/s., 38.50 MB/s.)

여기서 삽입의 지연을 주목하세요. 삽입된 사용자 행이 전체 users 테이블과 조인되어 삽입 성능에 큰 영향을 줍니다. 이 문제를 해결하는 접근 방식을 아래 "소스 테이블을 필터와 조인에 사용하기"에서 제안합니다.

반대로, 새 사용자에 대한 배지를 먼저 삽입한 다음 그 사용자의 행을 삽입하면, 머티얼라이즈드 뷰가 사용자의 메트릭을 캡처하지 못합니다.

INSERT INTO badges VALUES (53505059, 23923286, 'Good Answer', now(), 'Bronze', 0);
INSERT INTO users VALUES (23923286, 1, now(),  'brand_new_user', now(), 'UK', 1, 1, 0);

SELECT *
FROM daily_badges_by_user
FINAL
WHERE DisplayName = 'brand_new_user';

0 rows in set. Elapsed: 0.017 sec. Processed 32.77 thousand rows, 644.32 KB (1.98 million rows/s., 38.94 MB/s.)

이 경우 뷰는 사용자 행이 존재하기 전의 배지 삽입에 대해서만 실행됩니다. 사용자에 대해 다른 배지를 삽입하면 기대대로 행이 삽입됩니다:

INSERT INTO badges VALUES (53505060, 23923286, 'Teacher', now(), 'Bronze', 0);

SELECT *
FROM daily_badges_by_user
FINAL
WHERE DisplayName = 'brand_new_user'

┌────────Day─┬───UserId─┬─DisplayName────┬─Gold─┬─Silver─┬─Bronze─┐
│ 2025-04-13 │ 23923286 │ brand_new_user │    0 │      0 │      1 │
└────────────┴──────────┴────────────────┴──────┴────────┴────────┘

1 row in set. Elapsed: 0.018 sec. Processed 32.77 thousand rows, 644.48 KB (1.87 million rows/s., 36.72 MB/s.)

그러나 이 결과는 잘못되었음을 주목하세요.

머티얼라이즈드 뷰에서 JOIN에 대한 모범 사례

• 가장 왼쪽 테이블을 트리거로 사용하세요. SELECT 문의 왼쪽 테이블만 머티얼라이즈드 뷰를 트리거합니다. 오른쪽 테이블의 변경은 업데이트를 트리거하지 않습니다.

• 조인 데이터를 미리 삽입하세요. 조인된 테이블의 데이터가 소스 테이블에 행을 삽입하기 전에 존재하는지 확인하세요. JOIN은 삽입 시점에 평가되므로, 데이터가 없으면 일치하지 않는 행이나 null이 발생합니다.

• 조인에서 가져오는 컬럼을 제한하세요. 조인된 테이블에서 필요한 컬럼만 선택해 메모리 사용을 최소화하고 삽입-시간 지연을 줄이세요(아래 참고).

• 삽입-시간 성능을 평가하세요. JOIN은 특히 오른쪽 테이블이 클 때 삽입 비용을 높입니다. 대표적인 프로덕션 데이터로 삽입률을 벤치마킹하세요.

• 단순 룩업에는 딕셔너리를 선호하세요. 비싼 JOIN 연산을 피하기 위해 키-값 룩업(예: 사용자 ID → 이름)에는 딕셔너리를 사용하세요.

• 병합 효율을 위해 GROUP BY와 ORDER BY를 정렬하세요. SummingMergeTree나 AggregatingMergeTree를 사용할 때는 GROUP BY가 타깃 테이블의 ORDER BY 절과 일치하는지 확인해 효율적인 행 병합을 보장하세요.

• 명시적 컬럼 별칭을 사용하세요. 테이블이 겹치는 컬럼 이름을 가질 때는 별칭을 사용해 모호함을 방지하고 타깃 테이블에서 올바른 결과를 보장하세요.

• 삽입 규모와 빈도를 고려하세요. JOIN은 중간 규모의 삽입 워크로드에서 잘 동작합니다. 높은 처리량의 수집에는 스테이징 테이블, 사전 조인, 또는 딕셔너리와 리프레시 가능 머티얼라이즈드 뷰 같은 다른 접근 방식을 고려하세요.

소스 테이블을 필터와 조인에 사용하기

ClickHouse에서 머티얼라이즈드 뷰를 다룰 때, 소스 테이블이 머티얼라이즈드 뷰 쿼리 실행 중에 어떻게 취급되는지 이해하는 것이 중요합니다. 구체적으로, 머티얼라이즈드 뷰 쿼리의 소스 테이블은 삽입된 데이터 블록으로 대체됩니다. 이 동작을 제대로 이해하지 못하면 예상치 못한 결과가 발생할 수 있습니다.

예제 시나리오

다음 설정을 고려해 보세요:

CREATE TABLE t0 (`c0` Int) ENGINE = Memory;
CREATE TABLE mvw1_inner (`c0` Int) ENGINE = Memory;
CREATE TABLE mvw2_inner (`c0` Int) ENGINE = Memory;

CREATE VIEW vt0 AS SELECT * FROM t0;

CREATE MATERIALIZED VIEW mvw1 TO mvw1_inner
AS SELECT count(*) AS c0
    FROM t0
    LEFT JOIN ( SELECT * FROM t0 ) AS x ON t0.c0 = x.c0;

CREATE MATERIALIZED VIEW mvw2 TO mvw2_inner
AS SELECT count(*) AS c0
    FROM t0
    LEFT JOIN vt0 ON t0.c0 = vt0.c0;

INSERT INTO t0 VALUES (1),(2),(3);

INSERT INTO t0 VALUES (1),(2),(3),(4),(5);

SELECT * FROM mvw1;

┌─c0─┐
│  3 │
│  5 │
└────┘

SELECT * FROM mvw2;

┌─c0─┐
│  3 │
│  8 │
└────┘

설명

위 예제에는 비슷한 연산을 수행하지만 소스 테이블 t0을 참조하는 방식이 약간 다른 두 머티얼라이즈드 뷰 mvw1과 mvw2가 있습니다.

mvw1에서 t0 테이블은 JOIN 오른쪽의 (SELECT * FROM t0) 서브쿼리 안에서 직접 참조됩니다. t0에 데이터가 삽입되면 머티얼라이즈드 뷰 쿼리가 삽입된 데이터 블록으로 t0을 대체한 채 실행됩니다. 이것은 JOIN 연산이 전체 테이블이 아니라 새로 삽입된 행에 대해서만 수행됨을 의미합니다.

vt0과 조인하는 두 번째 경우에는, 뷰가 t0에서 모든 데이터를 읽습니다. 이렇게 하면 JOIN 연산이 새로 삽입된 블록뿐 아니라 t0의 모든 행을 고려합니다.

핵심 차이는 ClickHouse가 머티얼라이즈드 뷰 쿼리에서 소스 테이블을 처리하는 방식에 있습니다. 삽입으로 머티얼라이즈드 뷰가 트리거되면, 소스 테이블(이 경우 t0)은 삽입된 데이터 블록으로 대체됩니다. 이 동작은 쿼리를 최적화하는 데 활용할 수 있지만, 예상치 못한 결과를 피하려면 신중한 고려도 필요합니다.

사용 사례와 주의사항

실무에서는 이 동작을 사용해 소스 테이블 데이터의 부분집합만 처리하면 되는 머티얼라이즈드 뷰를 최적화할 수 있습니다. 예를 들어 다른 테이블과 조인하기 전에 서브쿼리로 소스 테이블을 필터링할 수 있습니다. 이렇게 하면 머티얼라이즈드 뷰가 처리하는 데이터 양을 줄이고 성능을 개선하는 데 도움이 됩니다.

CREATE TABLE t0 (id UInt32, value String) ENGINE = MergeTree() ORDER BY id;
CREATE TABLE t1 (id UInt32, description String) ENGINE = MergeTree() ORDER BY id;
INSERT INTO t1 VALUES (1, 'A'), (2, 'B'), (3, 'C');

CREATE TABLE mvw1_target_table (id UInt32, value String, description String) ENGINE = MergeTree() ORDER BY id;

CREATE MATERIALIZED VIEW mvw1 TO mvw1_target_table AS
SELECT t0.id, t0.value, t1.description
FROM t0
JOIN (SELECT * FROM t1 WHERE t1.id IN (SELECT id FROM t0)) AS t1
ON t0.id = t1.id;

이 예제에서 IN (SELECT id FROM t0) 서브쿼리로 만들어진 집합에는 새로 삽입된 행만 있으며, 이는 t1을 그 집합에 대비해 필터링하는 데 도움이 됩니다.

Stack Overflow를 사용한 예제

users 테이블에서 사용자 표시 이름을 포함해 사용자별 일별 배지를 계산하는 앞의 머티얼라이즈드 뷰 예제를 고려해 보세요.

CREATE MATERIALIZED VIEW daily_badges_by_user_mv TO daily_badges_by_user
AS SELECT
    toDate(Date) AS Day,
    b.UserId,
    u.DisplayName,
    countIf(Class = 'Gold') AS Gold,
    countIf(Class = 'Silver') AS Silver,
    countIf(Class = 'Bronze') AS Bronze
FROM badges AS b
LEFT JOIN users AS u ON b.UserId = u.Id
GROUP BY Day, b.UserId, u.DisplayName;

이 뷰는 badges 테이블의 삽입 지연에 큰 영향을 줍니다. 예:

INSERT INTO badges VALUES (53505058, 2936484, 'gingerwizard', now(), 'Gold', 0);

1 row in set. Elapsed: 7.517 sec.

위 접근 방식을 사용해 이 뷰를 최적화할 수 있습니다. 삽입된 배지 행의 사용자 id로 users 테이블에 필터를 추가하겠습니다:

CREATE MATERIALIZED VIEW daily_badges_by_user_mv TO daily_badges_by_user
AS SELECT
    toDate(Date) AS Day,
    b.UserId,
    u.DisplayName,
    countIf(Class = 'Gold') AS Gold,
    countIf(Class = 'Silver') AS Silver,
    countIf(Class = 'Bronze') AS Bronze
FROM badges AS b
LEFT JOIN
(
    SELECT
        Id,
        DisplayName
    FROM users
    WHERE Id IN (
        SELECT UserId
        FROM badges
    )
) AS u ON b.UserId = u.Id
GROUP BY
    Day,
    b.UserId,
    u.DisplayName

이것은 초기 badges 삽입을 빠르게 할 뿐 아니라:

INSERT INTO badges SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/badges.parquet')

0 rows in set. Elapsed: 132.118 sec. Processed 323.43 million rows, 4.69 GB (2.45 million rows/s., 35.49 MB/s.)
Peak memory usage: 1.99 GiB.

이후의 배지 삽입도 효율적이라는 뜻입니다:

INSERT INTO badges VALUES (53505058, 2936484, 'gingerwizard', now(), 'Gold', 0);

1 row in set. Elapsed: 0.583 sec.

위 연산에서는 사용자 id 2936484에 대해 users 테이블에서 한 행만 검색합니다. 이 룩업도 Id 테이블 정렬 키로 최적화됩니다.

머티얼라이즈드 뷰와 union

UNION ALL 쿼리는 여러 소스 테이블의 데이터를 단일 결과 집합으로 결합하는 데 흔히 사용됩니다.

UNION ALL은 증분 머티얼라이즈드 뷰에서 직접 지원되지는 않지만, 각 SELECT 분기에 대해 별도의 머티얼라이즈드 뷰를 만들고 결과를 공유 타깃 테이블에 쓰면 같은 결과를 얻을 수 있습니다.

예제를 위해 Stack Overflow 데이터셋을 사용하겠습니다. 사용자가 얻은 배지와 게시물에 남긴 댓글을 나타내는 badges와 comments 테이블을 고려해 보세요:

CREATE TABLE stackoverflow.comments
(
    `Id` UInt32,
    `PostId` UInt32,
    `Score` UInt16,
    `Text` String,
    `CreationDate` DateTime64(3, 'UTC'),
    `UserId` Int32,
    `UserDisplayName` LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY CreationDate

CREATE TABLE stackoverflow.badges
(
    `Id` UInt32,
    `UserId` Int32,
    `Name` LowCardinality(String),
    `Date` DateTime64(3, 'UTC'),
    `Class` Enum8('Gold' = 1, 'Silver' = 2, 'Bronze' = 3),
    `TagBased` Bool
)
ENGINE = MergeTree
ORDER BY UserId

다음 INSERT INTO 명령으로 채울 수 있습니다:

INSERT INTO stackoverflow.badges SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/badges.parquet')
INSERT INTO stackoverflow.comments SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/comments/*.parquet')

이 두 테이블을 결합해 각 사용자의 마지막 활동을 보여주는 사용자 활동 통합 뷰를 만들고 싶다고 가정해 봅시다:

SELECT
 UserId,
 argMax(description, event_time) AS last_description,
 argMax(activity_type, event_time) AS activity_type,
    max(event_time) AS last_activity
FROM
(
    SELECT
 UserId,
 CreationDate AS event_time,
        Text AS description,
        'comment' AS activity_type
    FROM stackoverflow.comments
    UNION ALL
    SELECT
 UserId,
        Date AS event_time,
        Name AS description,
        'badge' AS activity_type
    FROM stackoverflow.badges
)
GROUP BY UserId
ORDER BY last_activity DESC
LIMIT 10

이 쿼리의 결과를 받을 타깃 테이블이 있다고 가정해 봅시다. 결과가 올바르게 병합되도록 하는 AggregatingMergeTree 테이블 엔진과 AggregateFunction의 사용에 주목하세요:

CREATE TABLE user_activity
(
    `UserId` String,
    `last_description` AggregateFunction(argMax, String, DateTime64(3, 'UTC')),
    `activity_type` AggregateFunction(argMax, String, DateTime64(3, 'UTC')),
    `last_activity` SimpleAggregateFunction(max, DateTime64(3, 'UTC'))
)
ENGINE = AggregatingMergeTree
ORDER BY UserId

badges나 comments에 새 행이 삽입될 때 이 테이블이 업데이트되기를 원한다면, 순진한 접근 방식은 이전 union 쿼리로 머티얼라이즈드 뷰를 만들어 보는 것입니다:

CREATE MATERIALIZED VIEW user_activity_mv TO user_activity AS
SELECT
 UserId,
 argMaxState(description, event_time) AS last_description,
 argMaxState(activity_type, event_time) AS activity_type,
    max(event_time) AS last_activity
FROM
(
    SELECT
 UserId,
 CreationDate AS event_time,
        Text AS description,
        'comment' AS activity_type
    FROM stackoverflow.comments
    UNION ALL
    SELECT
 UserId,
        Date AS event_time,
        Name AS description,
        'badge' AS activity_type
    FROM stackoverflow.badges
)
GROUP BY UserId
ORDER BY last_activity DESC

이것은 문법적으로 유효하지만, 의도하지 않은 결과를 만들 것입니다 — 뷰는 comments 테이블에 대한 삽입에서만 트리거됩니다. 예:

INSERT INTO comments VALUES (99999999, 23121, 1, 'The answer is 42', now(), 2936484, 'gingerwizard');

SELECT
 UserId,
 argMaxMerge(last_description) AS description,
 argMaxMerge(activity_type) AS activity_type,
    max(last_activity) AS last_activity
FROM user_activity
WHERE UserId = '2936484'
GROUP BY UserId

┌─UserId──┬─description──────┬─activity_type─┬───────────last_activity─┐
│ 2936484 │ The answer is 42 │ comment       │ 2025-04-15 09:56:19.000 │
└─────────┴──────────────────┴───────────────┴─────────────────────────┘

1 row in set. Elapsed: 0.005 sec.

badges 테이블에 대한 삽입은 뷰를 트리거하지 못해 user_activity가 업데이트를 받지 못합니다:

INSERT INTO badges VALUES (53505058, 2936484, 'gingerwizard', now(), 'Gold', 0);

SELECT
 UserId,
 argMaxMerge(last_description) AS description,
 argMaxMerge(activity_type) AS activity_type,
    max(last_activity) AS last_activity
FROM user_activity
WHERE UserId = '2936484'
GROUP BY UserId;

┌─UserId──┬─description──────┬─activity_type─┬───────────last_activity─┐
│ 2936484 │ The answer is 42 │ comment       │ 2025-04-15 09:56:19.000 │
└─────────┴──────────────────┴───────────────┴─────────────────────────┘

1 row in set. Elapsed: 0.005 sec.

이 문제를 해결하려면 각 SELECT 문에 대해 머티얼라이즈드 뷰를 만들면 됩니다:

DROP TABLE user_activity_mv;
TRUNCATE TABLE user_activity;

CREATE MATERIALIZED VIEW comment_activity_mv TO user_activity AS
SELECT
 UserId,
 argMaxState(Text, CreationDate) AS last_description,
 argMaxState('comment', CreationDate) AS activity_type,
    max(CreationDate) AS last_activity
FROM stackoverflow.comments
GROUP BY UserId;

CREATE MATERIALIZED VIEW badges_activity_mv TO user_activity AS
SELECT
 UserId,
 argMaxState(Name, Date) AS last_description,
 argMaxState('badge', Date) AS activity_type,
    max(Date) AS last_activity
FROM stackoverflow.badges
GROUP BY UserId;

이제 어느 테이블에든 삽입하면 올바른 결과가 나옵니다. 예를 들어 comments 테이블에 삽입하면:

INSERT INTO comments VALUES (99999999, 23121, 1, 'The answer is 42', now(), 2936484, 'gingerwizard');

SELECT
 UserId,
 argMaxMerge(last_description) AS description,
 argMaxMerge(activity_type) AS activity_type,
    max(last_activity) AS last_activity
FROM user_activity
WHERE UserId = '2936484'
GROUP BY UserId;

┌─UserId──┬─description──────┬─activity_type─┬───────────last_activity─┐
│ 2936484 │ The answer is 42 │ comment       │ 2025-04-15 10:18:47.000 │
└─────────┴──────────────────┴───────────────┴─────────────────────────┘

1 row in set. Elapsed: 0.006 sec.

마찬가지로 badges 테이블에 대한 삽입도 user_activity 테이블에 반영됩니다:

INSERT INTO badges VALUES (53505058, 2936484, 'gingerwizard', now(), 'Gold', 0);

SELECT
 UserId,
 argMaxMerge(last_description) AS description,
 argMaxMerge(activity_type) AS activity_type,
    max(last_activity) AS last_activity
FROM user_activity
WHERE UserId = '2936484'
GROUP BY UserId

┌─UserId──┬─description──┬─activity_type─┬───────────last_activity─┐
│ 2936484 │ gingerwizard │ badge         │ 2025-04-15 10:20:18.000 │
└─────────┴──────────────┴───────────────┴─────────────────────────┘

1 row in set. Elapsed: 0.006 sec.

병렬 vs 순차 처리

이전 예제에서 보았듯이, 테이블 하나가 여러 머티얼라이즈드 뷰의 소스가 될 수 있습니다. 이것들이 실행되는 순서는 parallel_view_processing 설정에 따라 달라집니다.

기본적으로 이 설정은 0(false)과 같아, 머티얼라이즈드 뷰가 uuid 순서로 순차 실행됨을 의미합니다.

예를 들어 다음 source 테이블과 3개의 머티얼라이즈드 뷰를 고려해 보세요. 각각 행을 target 테이블로 보냅니다:

CREATE TABLE source
(
    `message` String
)
ENGINE = MergeTree
ORDER BY tuple();

CREATE TABLE target
(
    `message` String,
    `from` String,
    `now` DateTime64(9),
    `sleep` UInt8
)
ENGINE = MergeTree
ORDER BY tuple();

CREATE MATERIALIZED VIEW mv_2 TO target
AS SELECT
    message,
    'mv2' AS from,
    now64(9) as now,
    sleep(1) as sleep
FROM source;

CREATE MATERIALIZED VIEW mv_3 TO target
AS SELECT
    message,
    'mv3' AS from,
    now64(9) as now,
    sleep(1) as sleep
FROM source;

CREATE MATERIALIZED VIEW mv_1 TO target
AS SELECT
    message,
    'mv1' AS from,
    now64(9) as now,
    sleep(1) as sleep
FROM source;

각 뷰는 target 테이블에 행을 삽입하기 전에 1초 멈추면서 자신의 이름과 삽입 시간도 포함한다는 점에 주목하세요.

source 테이블에 행을 삽입하는 것은 약 3초가 걸리며, 각 뷰는 순차 실행됩니다:

INSERT INTO source VALUES ('test')

1 row in set. Elapsed: 3.786 sec.

SELECT로 각 행의 도착을 확인할 수 있습니다:

SELECT
    message,
    from,
    now
FROM target
ORDER BY now ASC

┌─message─┬─from─┬───────────────────────────now─┐
│ test    │ mv3  │ 2025-04-15 14:52:01.306162309 │
│ test    │ mv1  │ 2025-04-15 14:52:02.307693521 │
│ test    │ mv2  │ 2025-04-15 14:52:03.309250283 │
└─────────┴──────┴───────────────────────────────┘

3 rows in set. Elapsed: 0.015 sec.

이것은 뷰들의 uuid와 일치합니다:

SELECT
    name,
 uuid
FROM system.tables
WHERE name IN ('mv_1', 'mv_2', 'mv_3')
ORDER BY uuid ASC

┌─name─┬─uuid─────────────────────────────────┐
│ mv_3 │ ba5e36d0-fa9e-4fe8-8f8c-bc4f72324111 │
│ mv_1 │ b961c3ac-5a0e-4117-ab71-baa585824d43 │
│ mv_2 │ e611cc31-70e5-499b-adcc-53fb12b109f5 │
└──────┴──────────────────────────────────────┘

3 rows in set. Elapsed: 0.004 sec.

반대로 parallel_view_processing=1을 활성화한 상태로 행을 삽입하면 어떻게 되는지 고려해 보세요. 이것을 활성화하면 뷰가 병렬 실행되어 타깃 테이블에 행이 도착하는 순서에 대해 어떤 보장도 하지 않습니다:

TRUNCATE target;
SET parallel_view_processing = 1;

INSERT INTO source VALUES ('test');

1 row in set. Elapsed: 1.588 sec.

SELECT
    message,
    from,
    now
FROM target
ORDER BY now ASC

┌─message─┬─from─┬───────────────────────────now─┐
│ test    │ mv3  │ 2025-04-15 19:47:32.242937372 │
│ test    │ mv1  │ 2025-04-15 19:47:32.243058183 │
│ test    │ mv2  │ 2025-04-15 19:47:32.337921800 │
└─────────┴──────┴───────────────────────────────┘

3 rows in set. Elapsed: 0.004 sec.

각 뷰에서 행이 도착하는 순서는 같지만, 이것은 보장되지 않습니다 — 각 행의 삽입 시간이 비슷한 것으로 설명됩니다. 삽입 성능이 개선된 것에도 주목하세요.

병렬 처리를 언제 사용하나요?

parallel_view_processing=1을 활성화하면 위에서 본 것처럼 삽입 처리량을 크게 개선할 수 있습니다. 특히 단일 테이블에 여러 머티얼라이즈드 뷰가 붙어 있을 때요. 그러나 트레이드오프를 이해하는 것이 중요합니다:

• 삽입 압력 증가: 모든 머티얼라이즈드 뷰가 동시에 실행되어 CPU와 메모리 사용량이 늘어납니다. 각 뷰가 무거운 계산이나 JOIN을 수행하면 시스템이 과부하될 수 있습니다.
• 엄격한 실행 순서 필요: 뷰 실행 순서가 중요한 드문 워크플로(예: 체인 의존성)에서는 병렬 실행이 일관되지 않은 상태나 경쟁 조건을 만들 수 있습니다. 설계로 피할 수는 있지만, 그런 설정은 취약하고 향후 버전에서 깨질 수 있습니다.

과거 기본값과 안정성. 순차 실행은 한동안 기본값이었습니다. 부분적으로 오류 처리의 복잡성 때문입니다. 역사적으로 한 머티얼라이즈드 뷰의 실패가 다른 뷰의 실행을 막을 수 있었습니다. 최신 버전은 블록별로 실패를 격리해 개선했지만, 순차 실행이 여전히 더 명확한 실패 의미를 제공합니다.

일반적으로 다음 경우에 parallel_view_processing=1을 활성화하세요:

• 독립적인 머티얼라이즈드 뷰가 여러 개 있을 때
• 삽입 성능을 극대화하려 할 때
• 시스템이 동시 뷰 실행을 처리할 용량이 있음을 알고 있을 때

다음 경우에는 비활성화해 두세요:

• 머티얼라이즈드 뷰들이 서로 의존할 때
• 예측 가능하고 순서 있는 실행이 필요할 때
• 삽입 동작을 디버깅하거나 감사하며 결정적 재생(deterministic replay)을 원할 때

머티얼라이즈드 뷰와 Common Table Expression (CTE)

비재귀 Common Table Expression(CTE)은 머티얼라이즈드 뷰에서 지원됩니다.

ClickHouse는 CTE를 구체화(materialize)하지 않습니다. 대신 CTE 정의를 쿼리 안에 직접 대입하는데, 이는 같은 표현식이 여러 번 평가될 수 있음을 의미합니다(CTE가 두 번 이상 사용될 경우).

각 게시물 타입의 일별 활동을 계산하는 다음 예제를 고려해 보세요.

CREATE TABLE daily_post_activity
(
    Day Date,
 PostType String,
 PostsCreated SimpleAggregateFunction(sum, UInt64),
 AvgScore AggregateFunction(avg, Int32),
 TotalViews SimpleAggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (Day, PostType);

CREATE MATERIALIZED VIEW daily_post_activity_mv TO daily_post_activity AS
WITH filtered_posts AS (
    SELECT
 toDate(CreationDate) AS Day,
 PostTypeId,
 Score,
 ViewCount
    FROM posts
    WHERE Score > 0 AND PostTypeId IN (1, 2)  -- Question or Answer
)
SELECT
    Day,
    CASE PostTypeId
        WHEN 1 THEN 'Question'
        WHEN 2 THEN 'Answer'
    END AS PostType,
    count() AS PostsCreated,
    avgState(Score) AS AvgScore,
    sum(ViewCount) AS TotalViews
FROM filtered_posts
GROUP BY Day, PostTypeId;

여기서 CTE는 엄밀히 필요하지 않지만, 예제 목적으로 뷰는 기대대로 동작합니다:

INSERT INTO posts
SELECT *
FROM s3Cluster('default', 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/by_month/*.parquet')

SELECT
    Day,
    PostType,
    avgMerge(AvgScore) AS AvgScore,
    sum(PostsCreated) AS PostsCreated,
    sum(TotalViews) AS TotalViews
FROM daily_post_activity
GROUP BY
    Day,
    PostType
ORDER BY Day DESC
LIMIT 10

┌────────Day─┬─PostType─┬───────────AvgScore─┬─PostsCreated─┬─TotalViews─┐
│ 2024-03-31 │ Question │ 1.3317757009345794 │          214 │       9728 │
│ 2024-03-31 │ Answer   │ 1.4747191011235956 │          356 │          0 │
│ 2024-03-30 │ Answer   │ 1.4587912087912087 │          364 │          0 │
│ 2024-03-30 │ Question │ 1.2748815165876777 │          211 │       9606 │
│ 2024-03-29 │ Question │ 1.2641509433962264 │          318 │      14552 │
│ 2024-03-29 │ Answer   │ 1.4706927175843694 │          563 │          0 │
│ 2024-03-28 │ Answer   │  1.601637107776262 │          733 │          0 │
│ 2024-03-28 │ Question │ 1.3530864197530865 │          405 │      24564 │
│ 2024-03-27 │ Question │ 1.3225806451612903 │          434 │      21346 │
│ 2024-03-27 │ Answer   │ 1.4907539118065434 │          703 │          0 │
└────────────┴──────────┴────────────────────┴──────────────┴────────────┘

10 rows in set. Elapsed: 0.013 sec. Processed 11.45 thousand rows, 663.87 KB (866.53 thousand rows/s., 50.26 MB/s.)
Peak memory usage: 989.53 KiB.

ClickHouse에서 CTE는 인라인됩니다. 즉 최적화 중에 쿼리 안에 복사-붙여넣기되며 구체화되지 않습니다. 이것은 다음을 의미합니다:

• CTE가 소스 테이블(머티얼라이즈드 뷰가 붙어 있는 테이블)과 다른 테이블을 참조하고 JOIN이나 IN 절에서 사용되면, 트리거가 아니라 서브쿼리나 조인처럼 동작합니다.
• 머티얼라이즈드 뷰는 여전히 주 소스 테이블에 대한 삽입에서만 트리거되지만, CTE는 매 삽입에서 재실행될 수 있어 불필요한 오버헤드가 발생할 수 있습니다. 특히 참조된 테이블이 크면 더 그렇습니다.

예를 들어,

WITH recent_users AS (
  SELECT Id FROM stackoverflow.users WHERE CreationDate > now() - INTERVAL 7 DAY
)
SELECT * FROM stackoverflow.posts WHERE OwnerUserId IN (SELECT Id FROM recent_users)

이 경우 users CTE가 posts에 대한 매 삽입에서 재평가되고, 새 사용자가 삽입될 때 머티얼라이즈드 뷰가 업데이트되지 않습니다 — posts일 때만 업데이트됩니다.

일반적으로 머티얼라이즈드 뷰가 붙어 있는 동일한 소스 테이블에 대해 동작하는 로직에 CTE를 사용하거나, 참조된 테이블이 작아 성능 병목을 일으키지 않음을 보장하세요. 또는 머티얼라이즈드 뷰의 JOIN과 같은 최적화를 고려하세요.

더 알아보기 (Learn more)

• 리프레시 가능 머티얼라이즈드 뷰 (Refreshable materialized view)
• 캐스케이딩 머티얼라이즈드 뷰 (Cascading materialized views)
• 머티얼라이즈드 뷰 개요