ClickHouse数据库引擎

本文只记录MergeTree(合并树)与Distributed(分布式表), 其他表引擎想了解的话
官网传送门
https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/

集群部署传送门
https://blog.csdn.net/xushijie89/article/details/123039704

2.支持分区

3.支持数据复制
ReplicatedMergeTree表家族提供数据复制功能。

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 — 引擎名称, MergeTree()引擎没有参数

ORDER BY — 排序, 必填, 如果没有指定PRIMARY KEY, 会使用此字段作为主键, 如果不需要排序, ORDER BY tuple()

PARTITION BY — 分区键, 一般情况按日期分区, PARTITION BY toYYYYMM(create_time), create_time字段需要为时间类型

PRIMARY KEY — 默认情况下，主键与排序键相同(排序键由ORDER By子句指定)。因此，在大多数情况下，没有必要指定一个单独的PRIMARY KEY子句。

SAMPLE BY — 抽样, 如果使用了抽样表达式，则主键必须包含它。抽样表达式的结果必须是一个无符号整数。例如:SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID))。

TTL — 过期时间, 必须时间类型, TTL column + INTERVAL 1 DAY
规则的类型 DELETE|TO DISK ‘xxx’|TO VOLUME 'xxx’指定了当满足条件（到达指定时间）时所要执行的动作：移除过期的行，还是将数据片段移动到指定的磁盘（TO DISK ‘xxx’) 或 卷（TO VOLUME ‘xxx’）。默认的规则是移除（DELETE）。可以在列表中指定多个规则，但最多只能有一个DELETE的规则。

SETTINGS — 控制MergeTree行为的附加参数
详情参考
https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/

注意:
1.重复数据删除仅在合并时发生。合并发生在一个未知的时间, 通常是10-15分钟。
2.ReplacingMergeTree适用于在后台清除重复数据以节省空间，但不能保证没有重复。并且, 在分片的数据表中, 去重只能针对于当前分片

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

ReplacingMergeTree([ver])参数
ver:
带有版本号的ver列。类型UInt*，日期，DateTime或DateTime64。可选参数。
当合并时，从所有具有相同排序键的行替换mergetree只留下一个:
注意:
1.以ORDER BY作为去重标识
2.去重不能跨分区
3.同一批插入的数据会去重或分区合并时才会进行去重
4.数据保留版本字段值最大的
5.如果版本字段相同则保留最后插入的
6.所有列相同的数据是无法插入的

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

复制表:
ReplicatedSummingMergeTree(’/clickhouse/tables/{shard}/table_name_sum2’, ‘{replica}’, (total_amount, total_1))

SummingMergeTree([columns])参数
columns:
具有列名的元组，其中的数字类型将被汇总。可选参数。列必须为数字类型，且不能在主键中。

注意:
1.以 SummingMergeTree()中指定的列作为汇总数据列
2.可以填写多列, 但必须数字列, 如果不填, 以所有非主键列且为数字类型的字段为汇总数据列
3.以 order by 的列为准，作为维度列
4.其他的列按插入顺序保留第一行
5.不在一个分区的数据不会被聚合
6.只有在同一批次插入(新版本)或分片合并时才会进行聚合
7.合并后非ORDER BY及非累计字段, 保留最初的值
8.新增的数据如果所有字段全部相同, 自动去重; 合并后的数据不影响

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

sign:
字段需要有sign列, 字段类型Int8, 取值1为生效状态, 取值-1为取消状态

注意:
1.一般情况下更新时需要插入两行数据, 第一行取消数据先前的状态, 它应该复制ORDER BY关键字段, 并且sign为-1
第二行是最新状态
2.分片中不会进行合并
3.数据合并后, 在短时间内, 无法再插入之前的原始数据

create table db_name.table_name_agg on cluster cluster_3shards_2replicas
(
    id           UInt32,
    sku_id       String,
    spu_id       AggregateFunction(uniq,String),
    total_amount AggregateFunction(sum,UInt32),
    create_time  Datetime
) engine = ReplicatedAggregatingMergeTree('/clickhouse/tables/{shard}/table_name_agg', '{replica}')
      partition by toYYYYMMDD(create_time)
      primary key (id)
      order by (id, sku_id);

聚合函数方法汇总
https://clickhouse.com/docs/en/sql-reference/aggregate-functions/

数据插入:
插入数据时, 使用带有 聚合函数方法名+State(), 通过INSERT SELECT语句, 例如字段使用的函数是uniq, 插入时就是uniqState()

INSERT INTO TABLE table_name_agg SELECT 1, 'sku111', uniqState('spu111'), sumState(toUInt32(1000)), '2020-06-01 12:00:00';
INSERT INTO TABLE table_name_agg SELECT 1, 'sku111', uniqState('spu222'), sumState(toUInt32(2000)), '2020-06-01 12:00:00';
INSERT INTO TABLE table_name_agg SELECT 1, 'sku111', uniqState('spu333'), sumState(toUInt32(3000)), '2020-06-01 12:00:00';
INSERT INTO TABLE table_name_agg SELECT 1, 'sku111', uniqState('spu444'), sumState(toUInt32(4000)), '2020-06-01 12:00:00';

INSERT INTO TABLE table_name_agg SELECT 2, 'sku222', uniqState('spu555'), sumState(toUInt32(5000)), '2020-06-01 12:00:00';
INSERT INTO TABLE table_name_agg SELECT 2, 'sku222', uniqState('spu666'), sumState(toUInt32(6000)), '2020-06-01 12:00:00';

数据查询:
查询时, 使用带有 聚合函数方法名+Merge()

SELECT id, sku_id, uniqMerge(spu_id), sumMerge(total_amount) FROM table_name_agg GROUP BY id, sku_id;

在数据库工具DataGrid中, 可以直观的看到, 聚合列乱码且无法直接编辑