SummingMergeTree 테이블 엔진

이 엔진은 MergeTree에서 상속해요. 차이점은 SummingMergeTree 테이블의 데이터 파트를 병합할 때 ClickHouse가 같은 기본 키(더 정확히는 같은 정렬 키)를 가진 모든 행을, 숫자 데이터 타입의 컬럼에 대한 합산 값을 포함하는 하나의 행으로 대체한다는 점이에요. 정렬 키가 단일 키 값이 많은 수의 행에 대응하도록 구성되면 저장 볼륨이 크게 줄어들고 데이터 선택이 빨라져요.

이 엔진을 MergeTree와 함께 사용할 것을 권장해요. 완전한 데이터는 MergeTree 테이블에 저장하고, 예를 들어 보고서를 준비할 때 집계 데이터 저장에는 SummingMergeTree를 사용해요. 이러한 접근 방식은 잘못 구성된 기본 키로 인해 귀중한 데이터를 잃는 것을 방지해줘요.

출처: 문서

본문

테이블 생성하기

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = SummingMergeTree([columns])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

요청 매개변수에 대한 설명은 request description을 참고해요.

SummingMergeTree의 매개변수

Columns

columns - 값이 합산될 컬럼 이름의 튜플. 선택 매개변수. 컬럼은 숫자 타입이어야 하고 파티션이나 정렬 키에 있으면 안 돼요. columns를 지정하지 않으면 ClickHouse는 정렬 키에 없는 숫자 데이터 타입의 모든 컬럼의 값을 합산해요.

쿼리 절

SummingMergeTree 테이블을 만들 때 MergeTree 테이블을 만들 때와 같은 절들이 필요해요.

사용 예시

다음 테이블을 고려해요.

CREATE TABLE summtt
(
    key UInt32,
    value UInt32
)
ENGINE = SummingMergeTree()
ORDER BY key

데이터를 삽입해요:

INSERT INTO summtt VALUES(1,1),(1,2),(2,1)

ClickHouse는 모든 행을 완전히 합산하지 않을 수 있으므로(아래 참고) 쿼리에서 집계 함수 sum과 GROUP BY 절을 사용해요.

SELECT key, sum(value) FROM summtt GROUP BY key

┌─key─┬─sum(value)─┐
│   2 │          1 │
│   1 │          3 │
└─────┴────────────┘

데이터 처리

데이터가 테이블에 삽입되면 있는 그대로 저장돼요. ClickHouse는 삽입된 데이터 파트를 주기적으로 병합하며, 이때 같은 기본 키를 가진 행이 합산되어 각 결과 데이터 파트마다 하나로 대체돼요. ClickHouse는 데이터 파트를 병합하여 서로 다른 결과 데이터 파트가 같은 기본 키를 가진 행으로 구성될 수 있게 해요. 즉 합산이 불완전할 수 있어요. 따라서 위 예시에서 설명한 대로 쿼리에서 집계 함수 sum()과 GROUP BY 절을 사용해야 해요.

합산의 일반 규칙

숫자 데이터 타입 컬럼의 값이 합산돼요. 컬럼 집합은 매개변수 columns로 정의돼요. 합산용 컬럼 모두의 값이 0이면 행이 삭제돼요. 기본 키에 없고 합산되지 않는 컬럼은 기존 값 중에서 임의 값이 선택돼요. 기본 키의 컬럼은 합산되지 않아요.

AggregateFunction 컬럼의 합산

AggregateFunction 타입의 컬럼에 대해 ClickHouse는 AggregatingMergeTree 엔진처럼 함수에 따라 집계해요.

중첩 구조(Nested structures)

테이블은 특별한 방식으로 처리되는 중첩 데이터 구조를 가질 수 있어요. 중첩 테이블 이름이 Map으로 끝나고 다음 기준을 충족하는 컬럼이 최소 두 개 있으면:

첫 번째 컬럼이 숫자(*Int*, Date, DateTime) 또는 문자열(String, FixedString)이고, key라고 부르자
나머지 컬럼이 산술(*Int*, Float32/64)이고, (values...)라고 부르자

이 중첩 테이블은 key => (values...)의 매핑으로 해석되고, 행을 병합할 때 두 데이터셋의 요소가 해당 (values...)의 합산과 함께 key로 병합돼요.

예시:

DROP TABLE IF EXISTS nested_sum;
CREATE TABLE nested_sum
(
    date Date,
    site UInt32,
    hitsMap Nested(
        browser String,
        imps UInt32,
        clicks UInt32
    )
) ENGINE = SummingMergeTree
PRIMARY KEY (date, site);

INSERT INTO nested_sum VALUES ('2020-01-01', 12, ['Firefox', 'Opera'], [10, 5], [2, 1]);
INSERT INTO nested_sum VALUES ('2020-01-01', 12, ['Chrome', 'Firefox'], [20, 1], [1, 1]);
INSERT INTO nested_sum VALUES ('2020-01-01', 12, ['IE'], [22], [0]);
INSERT INTO nested_sum VALUES ('2020-01-01', 10, ['Chrome'], [4], [3]);

OPTIMIZE TABLE nested_sum FINAL; -- emulate merge

SELECT * FROM nested_sum;
┌───────date─┬─site─┬─hitsMap.browser───────────────────┬─hitsMap.imps─┬─hitsMap.clicks─┐
│ 2020-01-01 │   10 │ ['Chrome']                        │ [4]          │ [3]            │
│ 2020-01-01 │   12 │ ['Chrome','Firefox','IE','Opera'] │ [20,11,22,5] │ [1,3,0,1]      │
└────────────┴──────┴───────────────────────────────────┴──────────────┴────────────────┘

SELECT
    site,
    browser,
    impressions,
    clicks
FROM
(
    SELECT
        site,
        sumMap(hitsMap.browser, hitsMap.imps, hitsMap.clicks) AS imps_map
    FROM nested_sum
    GROUP BY site
)
ARRAY JOIN
    imps_map.1 AS browser,
    imps_map.2 AS impressions,
    imps_map.3 AS clicks;

┌─site─┬─browser─┬─impressions─┬─clicks─┐
│   12 │ Chrome  │          20 │      1 │
│   12 │ Firefox │          11 │      3 │
│   12 │ IE      │          22 │      0 │
│   12 │ Opera   │           5 │      1 │
│   10 │ Chrome  │           4 │      3 │
└──────┴─────────┴─────────────┴────────┘

데이터를 요청할 때 Map의 집계에는 sumMap(key, value) 함수를 사용해요. 중첩 데이터 구조의 경우 합산용 컬럼 튜플에 그 컬럼을 지정할 필요가 없어요.

Tuple 요소 집계

allow_tuple_element_aggregation 설정이 활성화되면 Tuple 컬럼이 재귀적으로 평면화되어 각 리프 요소가 독립적으로 합산에 참여해요. 이렇게 하면 단일 Tuple 컬럼에 여러 지표를 저장하고 병합 중 요소별로 합산되게 할 수 있어요.

평면화된 하위 컬럼에는 일반 컬럼과 같은 규칙이 적용돼요.

숫자 하위 컬럼만 합산돼요
정렬 키나 파티션 키의 Tuple에 속하는 하위 컬럼은 합산에서 제외돼요
columns가 지정되면 나열된 Tuple 컬럼의 하위 컬럼만 합산돼요
합산 후 행의 모든 숫자 하위 컬럼이 0이면 행이 삭제돼요

이 설정은 불변(immutable)이며 테이블 생성 시 지정해야 해요.

CREATE TABLE summing_tuples
(
    key UInt32,
    metrics Tuple(
        impressions UInt64,
        clicks UInt64,
        nested Tuple(
            conversions UInt64
        )
    )
) ENGINE = SummingMergeTree()
ORDER BY key
SETTINGS allow_tuple_element_aggregation = 1;

INSERT INTO summing_tuples VALUES (1, (100, 10, (1)));
INSERT INTO summing_tuples VALUES (1, (200, 20, (3)));

OPTIMIZE TABLE summing_tuples FINAL;

SELECT key, metrics.impressions, metrics.clicks, metrics.nested.conversions FROM summing_tuples;

┌─key─┬─metrics.impressions─┬─metrics.clicks─┬─metrics.nested.conversions─┐
│   1 │                 300 │             30 │                          4 │
└─────┴─────────────────────┴────────────────┴────────────────────────────┘

SummingMergeTree 테이블 엔진