表引擎

官网说明

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

日志相关

少量数据。

https://clickhouse.com/docs/en/engines/table-engines/log-family/log

外部表

https://clickhouse.com/docs/en/engines/table-engines/integrations/mysql

MergeTree 引擎

MergeTree 是ClickHouse最常用的引擎之一，用于处理时间序列数据。它支持时间分区、合并、删除和快速的范围查询。

示例建表语句：

CREATE TABLE merge_tree_example
(
    event_date Date,
    value Int32
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY event_date;

INSERT语句示例：

INSERT INTO merge_tree_example (event_date, value) VALUES ('2023-08-10', 42);

ReplacingMergeTree 引擎

ReplacingMergeTree 引擎在插入时可以替换相同主键的旧数据，适用于处理需要更新历史数据的情况。

示例建表语句：

-- without ver - the last inserted 'wins'
CREATE TABLE myFirstReplacingMT
(
 `key` Int64,
 `someCol` String,
 `eventTime` DateTime
)
ENGINE = ReplacingMergeTree
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');

SELECT * FROM myFirstReplacingMT FINAL;

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


-- with ver - the row with the biggest ver 'wins'
CREATE TABLE mySecondReplacingMT
(
 `key` Int64,
 `someCol` String,
 `eventTime` DateTime
)
ENGINE = ReplacingMergeTree(eventTime)
ORDER BY key;

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

select * from mySecondReplacingMT;

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

select * from mySecondReplacingMT FINAL;

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

如果不加FINAL那么就不会去重复，只有合并以后才能够去重。

SummingMergeTree 引擎

SummingMergeTree 引擎适用于需要对某一列数据进行聚合的情况，例如存储每日总和。

示例建表语句：

CREATE TABLE summing_merge_tree_example
(
    event_date Date,
    value Int32
) ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY event_date;

INSERT语句示例：

INSERT INTO summing_merge_tree_example (event_date, value) VALUES ('2023-08-10', 50);

AggregatingMergeTree 引擎

AggregatingMergeTree 引擎在写入时自动执行聚合操作，适用于存储汇总数据。

示例建表语句：

CREATE TABLE aggregating_merge_tree_example
(
    event_date Date,
    total_value Int32
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY event_date;

INSERT语句示例：

INSERT INTO aggregating_merge_tree_example (event_date, total_value) VALUES ('2023-08-10', 1000);

CollapsingMergeTree 引擎

CollapsingMergeTree 引擎用于存储具有相同标识的事件，将多个事件折叠为一个事件，并保留某些属性。

示例建表语句：

CREATE TABLE collapsing_merge_tree_example
(
    event_date Date,
    user_id Int32,
    event_type String,
    merged_event_type String
) ENGINE = CollapsingMergeTree(merged_event_type)
PARTITION BY toYYYYMM(event_date)
ORDER BY (user_id, event_date, event_type);

INSERT语句示例：

INSERT INTO collapsing_merge_tree_example (event_date, user_id, event_type, merged_event_type)
VALUES ('2023-08-10', 1, 'Click', 'Click');