중복 제거는 데이터셋에서 중복된 행을 제거하는 과정을 의미합니다. OLTP 데이터베이스에서는 각 행에 고유한 기본 키가 있으므로 이를 쉽게 수행할 수 있지만, 그 대가로 삽입 속도는 느려집니다. 삽입되는 모든 행은 먼저 기존에 있는지 확인해야 하며, 이미 있으면 대체해야 합니다.
ClickHouse는 데이터 삽입 속도를 우선하도록 설계되었습니다. 저장 파일은 변경할 수 없으며, ClickHouse는 행을 삽입하기 전에 기존 기본 키를 확인하지 않으므로 중복 제거에 조금 더 많은 작업이 필요합니다. 이는 또한 중복 제거가 즉시 이루어지는 것이 아니라 최종적으로 이루어진다는 뜻이며, 이에 따라 몇 가지 부작용이 있습니다:
- 어느 시점이든 테이블에 중복이 여전히 남아 있을 수 있습니다(동일한 정렬 키를 가진 행)
- 중복된 행이 실제로 제거되는 시점은 파트 병합 중입니다
- 쿼리는 중복이 있을 가능성을 고려할 수 있어야 합니다
ClickHouse에서는 다음 테이블 엔진을 사용해 중복 제거를 구현할 수 있습니다.
-
ReplacingMergeTree 테이블 엔진: 이 테이블 엔진에서는 동일한 정렬 키(sorting key)를 가진 중복 행이 머지 과정에서 제거됩니다. ReplacingMergeTree는 업서트 동작을 구현하는 데 적합한 선택지입니다(즉, 쿼리에서 마지막으로 삽입된 행이 반환되기를 원하는 경우).
-
행 축약:
CollapsingMergeTree 및 VersionedCollapsingMergeTree 테이블 엔진은 기존 행을 “취소”하고 새 행을 삽입하는 방식으로 동작합니다. ReplacingMergeTree보다 구현은 더 복잡하지만, 데이터가 아직 머지되었는지 여부를 신경 쓰지 않고도 쿼리와 집계를 더 간단하게 작성할 수 있습니다. 이 두 테이블 엔진은 데이터를 자주 업데이트해야 할 때 유용합니다.
아래에서 이 두 기법을 모두 살펴보겠습니다. 자세한 내용은 무료 온디맨드 Deleting and Updating Data training module을 확인하십시오.
업서트를 위해 ReplacingMergeTree 사용
views 컬럼이 있는 Hacker News 댓글 테이블의 간단한 예시를 살펴보겠습니다. 기사 게시 시 새 행을 삽입하고, 값이 증가하면 총 조회 수를 반영하여 하루에 한 번 새 행으로 업서트한다고 가정하겠습니다:
CREATE TABLE hackernews_rmt (
id UInt32,
author String,
comment String,
views UInt64
)
ENGINE = ReplacingMergeTree
PRIMARY KEY (author, id)
INSERT INTO hackernews_rmt VALUES
(1, 'ricardo', 'This is post #1', 0),
(2, 'ch_fan', 'This is post #2', 0)
views 컬럼을 업데이트하려면 동일한 기본 키(primary key)를 사용하는 새 행을 삽입하십시오(views 컬럼의 새 값에 유의하십시오):
INSERT INTO hackernews_rmt VALUES
(1, 'ricardo', 'This is post #1', 100),
(2, 'ch_fan', 'This is post #2', 200)
SELECT *
FROM hackernews_rmt
┌─id─┬─author──┬─comment─────────┬─views─┐
│ 2 │ ch_fan │ This is post #2 │ 0 │
│ 1 │ ricardo │ This is post #1 │ 0 │
└────┴─────────┴─────────────────┴───────┘
┌─id─┬─author──┬─comment─────────┬─views─┐
│ 2 │ ch_fan │ This is post #2 │ 200 │
│ 1 │ ricardo │ This is post #1 │ 100 │
└────┴─────────┴─────────────────┴───────┘
SELECT 쿼리에서 FINAL 키워드를 사용해 보겠습니다. 그러면 쿼리 결과가 논리적으로 머지됩니다:
SELECT *
FROM hackernews_rmt
FINAL
┌─id─┬─author──┬─comment─────────┬─views─┐
│ 2 │ ch_fan │ This is post #2 │ 200 │
│ 1 │ ricardo │ This is post #1 │ 100 │
└────┴─────────┴─────────────────┴───────┘
데이터 양이 적다면 FINAL을 사용해도 무방합니다. 데이터 양이 많다면,
FINAL을 사용하는 것은 최선의 선택이 아닐 수 있습니다. 컬럼의 최신 값을
찾는 더 나은 방법을 살펴보겠습니다.
views 컬럼을 다시 업데이트해 보겠습니다:
INSERT INTO hackernews_rmt VALUES
(1, 'ricardo', 'This is post #1', 150),
(2, 'ch_fan', 'This is post #2', 250)
FINAL을 사용했을 때 수행된 쿼리 시점 머지만 적용되었습니다).
SELECT *
FROM hackernews_rmt
┌─id─┬─author──┬─comment─────────┬─views─┐
│ 2 │ ch_fan │ This is post #2 │ 200 │
│ 1 │ ricardo │ This is post #1 │ 100 │
└────┴─────────┴─────────────────┴───────┘
┌─id─┬─author──┬─comment─────────┬─views─┐
│ 2 │ ch_fan │ This is post #2 │ 0 │
│ 1 │ ricardo │ This is post #1 │ 0 │
└────┴─────────┴─────────────────┴───────┘
┌─id─┬─author──┬─comment─────────┬─views─┐
│ 2 │ ch_fan │ This is post #2 │ 250 │
│ 1 │ ricardo │ This is post #1 │ 150 │
└────┴─────────┴─────────────────┴───────┘
FINAL을 사용하는 대신 비즈니스 로직을 활용해 보겠습니다. views 컬럼은 항상 증가하므로, 원하는 컬럼으로 그룹화한 다음 max 함수를 사용해 가장 큰 값을 가진 행을 선택할 수 있습니다:
SELECT
id,
author,
comment,
max(views)
FROM hackernews_rmt
GROUP BY (id, author, comment)
┌─id─┬─author──┬─comment─────────┬─max(views)─┐
│ 2 │ ch_fan │ This is post #2 │ 250 │
│ 1 │ ricardo │ This is post #1 │ 150 │
└────┴─────────┴─────────────────┴────────────┘
FINAL 키워드를 사용하는 것보다 쿼리 성능 측면에서 더 효율적일 수 있습니다.
Deleting and Updating Data training module에서는 이 예시를 더 자세히 설명하며, ReplacingMergeTree와 함께 version 컬럼을 사용하는 방법도 다룹니다.
컬럼을 자주 업데이트할 때 CollapsingMergeTree 사용하기
ALTER TABLE..UPDATE를 피하고, 기존 데이터와 함께 새 데이터를 그냥 삽입하는 편이 실제로 더 효율적일 수 있습니다. 데이터가 오래된 것인지 새로운 것인지를 나타내는 컬럼을 추가할 수도 있습니다. 그리고 이런 동작을 아주 잘 구현해 주는 테이블 엔진이 이미 있으며, 특히 오래된 데이터를 자동으로 삭제해 준다는 점에서 유용합니다. 어떻게 동작하는지 살펴보겠습니다.
외부 시스템으로 Hacker News 댓글의 조회 수를 추적하고, 몇 시간마다 이 데이터를 ClickHouse로 푸시한다고 가정해 보겠습니다. 기존 행은 삭제되고, 새 행이 각 Hacker News 댓글의 새로운 상태를 나타내도록 하려고 합니다. 이런 동작은 CollapsingMergeTree를 사용해 구현할 수 있습니다.
조회 수를 저장할 테이블을 정의해 보겠습니다:
CREATE TABLE hackernews_views (
id UInt32,
author String,
views UInt64,
sign Int8
)
ENGINE = CollapsingMergeTree(sign)
PRIMARY KEY (id, author)
hackernews_views 테이블에는 sign이라는 이름의 Int8 컬럼이 있으며, 이를 sign 컬럼이라고 합니다. sign 컬럼의 이름은 임의로 정할 수 있지만, Int8 데이터 타입은 필수입니다. 또한 이 컬럼 이름이 CollapsingMergeTree 테이블의 생성자에 전달된 점도 확인하십시오.
CollapsingMergeTree 테이블의 sign 컬럼은 무엇일까요? 이 컬럼은 행의 state를 나타내며, sign 컬럼에는 1 또는 -1만 올 수 있습니다. 동작 방식은 다음과 같습니다.
- 두 행의 기본 키(또는 기본 키와 다를 경우 정렬 순서)는 같지만 sign 컬럼 값이 다르면, 마지막으로 삽입된 +1 행이 state 행이 되고 나머지 행은 서로 상쇄됩니다
- 서로 상쇄되는 행은 머지 중에 삭제됩니다
- 짝이 없는 행은 유지됩니다
hackernews_views 테이블에 행을 추가해 보겠습니다. 이 기본 키에 해당하는 유일한 행이므로 state를 1로 설정합니다:
INSERT INTO hackernews_views VALUES
(123, 'ricardo', 0, 1)
views 컬럼을 변경한다고 가정해 보겠습니다. 기존 행을 취소하는 행 하나와 행의 새 상태를 담은 행 하나, 이렇게 두 개의 행을 삽입합니다:
INSERT INTO hackernews_views VALUES
(123, 'ricardo', 0, -1),
(123, 'ricardo', 150, 1)
(123, 'ricardo')인 행이 3개 있습니다:
SELECT *
FROM hackernews_views
┌──id─┬─author──┬─views─┬─sign─┐
│ 123 │ ricardo │ 0 │ -1 │
│ 123 │ ricardo │ 150 │ 1 │
└─────┴─────────┴───────┴──────┘
┌──id─┬─author──┬─views─┬─sign─┐
│ 123 │ ricardo │ 0 │ 1 │
└─────┴─────────┴───────┴──────┘
FINAL을 추가하면 현재 state 행이 반환됩니다:
SELECT *
FROM hackernews_views
FINAL
┌──id─┬─author──┬─views─┬─sign─┐
│ 123 │ ricardo │ 150 │ 1 │
└─────┴─────────┴───────┴──────┘
FINAL을 사용하는 것이 권장되지 않습니다.
예시의 views 컬럼에 전달하는 값은 실제로 필요하지 않으며, 이전 행의 현재 views 값과 일치할 필요도 없습니다. 실제로는 기본 키와 -1만으로도 행을 상쇄할 수 있습니다:INSERT INTO hackernews_views(id, author, sign) VALUES
(123, 'ricardo', -1)
CollapsingMergeTree 테이블에서는 sign 컬럼을 사용해 행들이 서로 상쇄되며, 행의 state는 마지막으로 삽입된 행으로 결정됩니다. 하지만 서로 다른 스레드에서 행을 삽입하면 행이 순서와 다르게 삽입될 수 있어 문제가 될 수 있습니다. 이런 상황에서는 “마지막” 행을 기준으로 삼는 방식이 제대로 동작하지 않습니다.
이럴 때 VersionedCollapsingMergeTree가 유용합니다. 이 엔진은 CollapsingMergeTree처럼 행을 축약하지만, 마지막으로 삽입된 행을 유지하는 대신 사용자가 지정한 version 컬럼 값이 가장 큰 행을 유지합니다.
예시를 살펴보겠습니다. Hacker News 댓글의 조회 수를 추적하려고 하며, 데이터가 자주 업데이트된다고 가정해 보겠습니다. 머지를 강제하거나 머지가 끝날 때까지 기다리지 않고도 최신 값을 기반으로 보고할 수 있어야 합니다. 먼저 CollapsedMergeTree와 비슷한 테이블에서 시작하되, 행 state의 version을 저장할 컬럼을 하나 추가합니다:
CREATE TABLE hackernews_views_vcmt (
id UInt32,
author String,
views UInt64,
sign Int8,
version UInt32
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
PRIMARY KEY (id, author)
VersionsedCollapsingMergeTree를 사용하며 sign 컬럼과 version 컬럼을 전달한다는 점에 유의하십시오. 이 테이블의 동작 방식은 다음과 같습니다:
- 같은 기본 키와 버전을 가지면서 sign이 다른 각 행 쌍을 삭제합니다
- 행이 삽입된 순서는 중요하지 않습니다
- version 컬럼이 기본 키의 일부가 아니면 ClickHouse는 이를 기본 키의 마지막 필드로 암묵적으로 추가합니다
쿼리를 작성할 때도 동일한 방식의 로직을 사용합니다. 기본 키로 그룹화하고, 취소되었지만 아직 삭제되지 않은 행을 제외하도록 적절한 로직을 사용합니다. 이제 hackernews_views_vcmt 테이블에 몇 개의 행을 추가해 보겠습니다:
INSERT INTO hackernews_views_vcmt VALUES
(1, 'ricardo', 0, 1, 1),
(2, 'ch_fan', 0, 1, 1),
(3, 'kenny', 0, 1, 1)
INSERT INTO hackernews_views_vcmt VALUES
(1, 'ricardo', 0, -1, 1),
(1, 'ricardo', 50, 1, 2),
(2, 'ch_fan', 0, -1, 1),
(3, 'kenny', 0, -1, 1),
(3, 'kenny', 1000, 1, 2)
SELECT
id,
author,
sum(views * sign)
FROM hackernews_views_vcmt
GROUP BY (id, author)
HAVING sum(sign) > 0
ORDER BY id ASC
┌─id─┬─author──┬─sum(multiply(views, sign))─┐
│ 1 │ ricardo │ 50 │
│ 3 │ kenny │ 1000 │
└────┴─────────┴────────────────────────────┘
OPTIMIZE TABLE hackernews_views_vcmt
SELECT *
FROM hackernews_views_vcmt
┌─id─┬─author──┬─views─┬─sign─┬─version─┐
│ 1 │ ricardo │ 50 │ 1 │ 2 │
│ 3 │ kenny │ 1000 │ 1 │ 2 │
└────┴─────────┴───────┴──────┴─────────┘
VersionedCollapsingMergeTree 테이블(table)이 매우 유용합니다.
내 행이 중복 제거되지 않는 이유는 무엇인가요?
INSERT 문에서 비멱등 함수나 표현식을 사용하기 때문일 수 있습니다. 예를 들어 createdAt DateTime64(3) DEFAULT now() 컬럼으로 행을 삽입하는 경우, 각 행은 createdAt 컬럼에 대해 고유한 기본값을 가지므로 고유해질 수밖에 없습니다. 삽입된 각 행이 고유한 체크섬을 생성하므로 MergeTree / ReplicatedMergeTree 테이블 엔진은 이러한 행을 중복 제거해야 할 대상으로 인식하지 못합니다.
이 경우 동일한 배치를 여러 번 삽입하더라도 같은 행이 다시 삽입되지 않도록 각 행 배치에 대해 자체 insert_deduplication_token을 지정할 수 있습니다. 이 설정을 사용하는 방법에 대한 자세한 내용은 insert_deduplication_token 문서를 참조하십시오. 
