[ClickHouse] 클릭하우스 MergeTree 테이블 엔진

728x90

클릭하우스 테이블 엔진

클릭하우스에서는 다양한 테이블 엔진을 지원한다.
유용한 테이블 엔진이 많기 때문에 조사해볼 필요가 있었다.
클릭하우스를 적용하려는 사람들은 반드시 읽어보면 좋다.

MergeTree 테이블 종류

이보다 더 많은 mergetree 가 있지만 실제로 필요한것들 위주로 정리를 했습니다.

MergeTree
SummingMergeTree
AggregatingMergeTree
ReplacingMergeTree
CollapsingMergeTree (이해가 쉽게 되질 않아 이부분은 다시 정리를 할 예정입니다.)

MergeTree

클릭하우스 기능의 핵심.
pk 별로 데이터를 정렬하여 저장한다.
대용량 데이터를 테이블에 삽입 할 수 있도록 설계됨.
데이터 수집시 자동 데이터 중복 제거 기능이 있다.
- ReplacingMergeTree, CollapsingMergeTree 사용하면 중복제거 가능
- insert 중에 네트워크 문제가 발생한 경우 소프트웨어가 안전하게 다시 insert를 시도해도 된다.
- 알아서 중복을 제거해 주기 때문에 여러번 데이터를 삽입 해도 문제가 없다.
머지는 자동으로 백그라운드에서 진행된다.
OPTIMZE TABLE table_name FINAL 로 수동머지 가능하지만 추천은 하지 않는다.

Create table

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) [GRANULARITY value1],
    INDEX index_name2 expr2 TYPE type2(...) [GRANULARITY value2],
    ...
    PROJECTION projection_name_1 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY]),
    PROJECTION projection_name_2 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY])
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr
    [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx' [, ...] ]
    [WHERE conditions]
    [GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ] ]
[SETTINGS name=value, ...]

파라미터 설명

Engine
- 어떤 엔진을 적용할것인가.
ORDER_BY
- 클릭하우스는 sorting key 를 기본적으로 PK 로 사용한다.
- PK가 지정되지 않으면 ORDER_BY 절에 들어간 컬럼이 기본키가 된다.
- 기본키가 필요 없다면 ORDER BY tuple 을 사용하면된다.
PARTITION BY
- 대분의 경우 지정할 필요는 없다.
- 파티션은 쿼리의 속도를 높이진 않는다,.
- 고객 아이디 또는 이름(중복도가 낮은값들)으로 파티션을 지정 하지 않는게 좋다.
  - 대신 ORDER BY 에 넣어주는게 효과적이다.
PRIMARY KEY
- SORTING KEY(ORDER_BY 안에 있는) 와 동일하기 때문에 거의 사용하지 않는다.
TTL
- Row 의 저장 기간을 지정하고 디스크와 볼륨간 자동으로 데이터를 이동 시키는 로직을 정의하는 룰이다.
- expr 따라 만료된 row 를 삭제하거나 볼륨 또는 디스크로 옮기거나 값을 집계 할 수 있다.
- 기본 규칙은 삭제로 지정이 된다.

SummingMergeTree

데이터 요소를 병합할때 기본키가 같으면 숫자 유형의 값을 요약한다.
정렬 키가 단일 키 값이 많은 행에 해당하는 방식으로 구성된 경우 스토리지 볼륨이 크게 감소하고 데이터 선택 속도가 빨라진다.

Create table

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, ...]

예제

테이블생성

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

데이터 삽입

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

조회

Key를 기준으로 데이터가 합산이 되었다.

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

Nested 버전

테이블 생성시 Nested 로 감싸 주면 . 으로 접근을 할수 있다.
아래와 같이 응용해서 사용할수도 있다

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 │
└──────┴─────────┴─────────────┴────────┘

AggregatingMergeTree

컬럼별로 집계를 할 수 있는 기능을 제공해 준다.
SummingMergeTree 와 차이점은 부분적으로 연산을 할 수 있다.

SimpleAggregateFunction

CREATE TABLE simple // 테이블 생성
(id UInt64, val SimpleAggregateFunction(sum, Double)) 
ENGINE=AggregatingMergeTree ORDER BY id;

INSERT INTO simple(id, val) VALUES (1, 11.1) // 데이터 삽입
INSERT INTO simple(id, val) VALUES (1, 11.1) // 데이터 삽입

OPTIMEZ TABLE simple FINAL // 수동으로 머지

사용가능한 함수들

any

amyLast

min

max

sum

sumWithOverflow

groupBitAnd

groupBitOr

groupBitXor

groupArrayArray

groupArrayUniqArrayArray

sumMap

minMap

maxMap

MT + AMT(AggregatingMergeTree)

머지트리를 베이스로 하고 집계 뷰를 만들어 사용할수도 있다.

MergeeTree 테이블 생성

CREATE TABLE test.visits
 (
    StartDate DateTime64 NOT NULL,
    CounterID UInt64,
    Sign Nullable(Int32),
    UserID Nullable(Int32)
) ENGINE = MergeTree ORDER BY (StartDate, CounterID);

뷰 생성

엔진은 AggregatingMergeTree 로 생성해준다.
쿼리의 결과 값을로 별도의 테이블 생성
반드시 AS SELECT 를 선언해 줘야 한다.

CREATE MATERIALIZED VIEW test.mv_visits
(
    StartDate DateTime64 NOT NULL,
    CounterID UInt64,
    Visits AggregateFunction(sum, Nullable(Int32)),
    Users AggregateFunction(uniq, Nullable(Int32))
)
ENGINE = AggregatingMergeTree() ORDER BY (StartDate, CounterID)
AS SELECT
    StartDate,
    CounterID,
    sumState(Sign) AS Visits,
    uniqState(UserID) AS Users
FROM test.visits
GROUP BY StartDate, CounterID;

데이터 삽입

INSERT INTO test.visits (StartDate, CounterID, Sign, UserID)
 VALUES (1667446031, 1, 3, 4)
INSERT INTO test.visits (StartDate, CounterID, Sign, UserID)
 VALUES (1667446031, 1, 3, 4)
INSERT INTO test.visits (StartDate, CounterID, Sign, UserID)
 VALUES (1667446031, 1, 6, 3)

조회

Visits sum 으로 계산
Users 는 uniq 로 계산되어 중복제거됨.

┌───────────────StartDate─┬─Visits─┬─Users─┐
│ 1970-01-20 07:10:46.031 │     12 │     2 │
└─────────────────────────┴────────┴───────┘

ReplacingMergeTree

동일한 키값에 대한 중복 레코드를 제거 한다.
머지가 될때 중복제거가 발생한다.
참고로 ReplacingMergeTree(eventTime) 이렇게 지정해 주면
중복 제거시 기준되는 값을 정할수 가 있다. 값이 없다면 마지막에 들어온 데이터가 남는다.

테이블 생성

-- 삽입되는 순서 중복제거
CREATE TABLE myFirstReplacingMT
(
    `key` Int64,
    `someCol` String,
    `eventTime` DateTime
)
ENGINE = ReplacingMergeTree
ORDER BY key;


-- 이벤트 타임 기준으로 중복제거 시간이 더 느린 값이 저장이됨.
CREATE TABLE mySecondReplacingMT
(
    `key` Int64,
    `someCol` String,
    `eventTime` DateTime
)
ENGINE = ReplacingMergeTree(eventTime)
ORDER BY key;

중복된 데이터 삽입

INSERT INTO myFirstReplacingMT Values (1, 'first', '2020-01-01 01:01:01');
INSERT INTO myFirstReplacingMT Values (1, 'second', '2020-01-01 00:00:00');

조회

OPTIMIZE TABLE myFirstREplacingMT FINAL

┌─key─┬─someCol─┬───────────eventTime─┐
│   1 │ first   │ 2020-01-01 01:00:01 │
└─────┴─────────┴─────────────────────┘

CollapsingMergeTree

sign 이라는 값을 제외하고 정렬키로 지정된 값이 동일하다면 쌍이 되는 행을 비동기적으로 삭제 한다.
- sign 값은 1 또는 -1 값을 가질수 있다.
왜 이걸 쓸까?
- 업데이트 문은 비용이 비싸고 느리다.
- 데이터를 업데이트 하지 않고 이전 행들을 비활성화 시키는 방식으로 행을 제거하면?
  - 속도는 향상될것이다.
삭제가 아닌 추가라는 아이디어를 통해 행의 데이터를 삭제하는 방식으로 업데이트를 지원하는 테이블 엔진이다.

sign의 의미

merging 이 발생 할때 해당 값을 보고 값을 삭제한다.
sign → 1 : 활성화
sign → -1: 비활성화(데이터를 삭제해야됨.)

동작과정

동일한 값이 들어와 있고 1, -1 값이 존재 하기 때문에 두 행을 접는다.
접는다는 것은 중복을 제거 한다는 의미다.

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         3 │       17 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         3 │       17 │   -1 │
└─────────────────────┴───────────┴──────────┴──────┘
-- 머지후
삭제됨

CollapsingMergeTree가 데이터를 접는 규칙

positive sign(1) 이 negative sign(-1) 데이터 보다 하나더 많은 경우 positive sign인 데이터의 마지막 행을 유지한다.

- 데이터가 삽입된 상태 각각의 파티션에 저장되어 있음
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         3 │       17 │   -1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         3 │       17 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         3 │       17 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

-머지후
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         3 │       17 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

negative sign(-1) 가 하나더 많은 경우 negative sign(-1) 의 첫번 째 행의 데이터를 유지한다.
- 머지전 negative sign(-1) 하나더 많음

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         3 │       17 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         3 │       17 │   -1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         3 │       17 │   -1 │
└─────────────────────┴───────────┴──────────┴──────┘

- 머지후
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         3 │       17 │   -1 │
└─────────────────────┴───────────┴──────────┴──────┘

참고:
https://clickhouse.com/docs/en/engines/table-engines/mergetree-family

MergeTree Engine Family | ClickHouse Docs

Table engines from the MergeTree family are the core of ClickHouse data storage capabilities. They provide most features for resilience and high-performance data retrieval: columnar storage, custom partitioning, sparse primary index, secondary data-skippin

clickhouse.com

728x90

'DataBase > Clickhouse' 카테고리의 다른 글

[ClickHouse] 클릭하우스 Memory exceeded (0)	2024.01.20
[ClickHouse] ORDER BY, PARTITION BY, Granules 적용기 (0)	2023.08.30
[ClickHouse] 클릭하우스 클러스터 구축 (0)	2023.05.27
[ClickHouse] 클릭하우스 란? (0)	2023.03.18

[ClickHouse] 클릭하우스 MergeTree 테이블 엔진

클릭하우스 테이블 엔진

MergeTree 테이블 종류

MergeTree

Create table

파라미터 설명

SummingMergeTree

Create table

예제

테이블생성

데이터 삽입

조회

Nested 버전

AggregatingMergeTree

SimpleAggregateFunction

MT + AMT(AggregatingMergeTree)

MergeeTree 테이블 생성

뷰 생성

데이터 삽입

조회

ReplacingMergeTree

테이블 생성

중복된 데이터 삽입

조회

CollapsingMergeTree

sign의 의미

동작과정

CollapsingMergeTree가 데이터를 접는 규칙

'DataBase > Clickhouse' 카테고리의 다른 글

댓글

티스토리툴바

[ClickHouse] 클릭하우스 MergeTree 테이블 엔진

클릭하우스 테이블 엔진

MergeTree 테이블 종류

MergeTree

Create table

파라미터 설명

SummingMergeTree

Create table

예제

테이블생성

데이터 삽입

조회

Nested 버전

AggregatingMergeTree

SimpleAggregateFunction

MT + AMT(AggregatingMergeTree)

MergeeTree 테이블 생성

뷰 생성

데이터 삽입

조회

ReplacingMergeTree

테이블 생성

중복된 데이터 삽입

조회

CollapsingMergeTree

sign의 의미

동작과정

CollapsingMergeTree가 데이터를 접는 규칙

'DataBase > Clickhouse' 카테고리의 다른 글

관련글

댓글

티스토리툴바