1、ClickHouse使用场景
ClickHouse是一个开源的，用于联机分析（OLAP）的列式数据库管理系统, 它是面向列的，并允许使用SQL查询，实时生成分析报告。ClickHouse适合OLAP数据分析类的场景，数据体量越大，ClickHouse的优势越大。ClickHouse不适合以下场景：
（1）适合读或者大批量写的场景，不适合频繁的写或者修改（这种场景用Doris或者Kudu更为合适），在与Flink结合的场景下可以采用窗口的方式进行批量写；
（2）不适合根据主键进行行粒度查询或删除场景（支持但不建议）；
（3）ClickHouse 不支持事务，事务场景不适合。
2、ClickHouse的MergeTree引擎
在ClickHouse的所有的表引擎中，其中最为核心的当属MergeTree系列表引擎，这些表引擎拥有最为强大的性能和最广泛的使用场合。MergeTree系列表引擎是官方主推的存储引擎，有主键索引、数据分区、数据副本、数据采样、删除和修改等功能，支持几乎所有ClickHouse核心功能。
MergeTree系列表引擎包含：MergeTree、ReplacingMergeTree、SummingMergeTree（汇总求和功能）、AggregatingMergeTree（聚合功能）、CollapsingMergeTree（折叠删除功能）、VersionedCollapsingMergeTree（版本折叠功能）引擎，在这些的基础上还可以叠加Replicated（副本）和Distributed（分片）。
MergeTree在写入一批数据时，数据总会以数据片段的形式写入磁盘，且数据片段在磁盘上不可修改。为了避免片段过多，ClickHouse会通过后台线程，定期合并这些数据片段，属于相同分区的数据片段会被合成一个新的片段。
2.1 MergeTree
2.1.1 MergeTree作为家族系列最基础的表引擎，主要有以下特点：
（1）存储的数据按照主键排序：创建稀疏索引加快数据查询速度。
（2）支持数据分区，可以通过PARTITION BY语句指定分区字段。
（3）支持数据副本。
（4）支持数据采样。
2.1.2 建表语句：
CREATE TABLE t_mt
(
`id` UInt8,
`name` String,
`age` UInt8,
`birthday` Date,
`location` String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(birthday)
ORDER BY (id, age)
ENGINE(必选)：ENGINE = MergeTree()，指定表引擎
ORDER BY(必选)：排序字段。比如ORDER BY (Col1, Col2)，值得注意的是，如果没有使用 PRIMARY KEY 显式的指定主键，则ORDER BY排序字段自动作为主键。
如果不需要排序，则可以使用 ORDER BY tuple() 语法，这样的话，创建的表也就不包含主键。这种情况下，ClickHouse会按照插入的顺序存储数据。
PRIMARY KEY(可选)：指定主键，如果排序字段与主键不一致，可以单独指定主键字段。否则默认主键是排序字段。
大部分情况下不需要再专门指定一个 PRIMARY KEY 子句，注意，在MergeTree中主键并不用于去重，而是用于索引，加快查询速度。
另外，如果指定了PRIMARY KEY与排序字段不一致，要保证PRIMARY KEY 指定的主键是ORDER BY 指定字段的前缀，例如：
ORDER BY (A,B,C)
PRIMARY KEY A
这种写法是正确的
ORDER BY (A,B,C)
PRIMARY KEY B
这种写法是错误的
这种强制约束保障了即便在两者定义不同的情况下，主键仍然是排序键的前缀，不会出现索引与数据顺序混乱的问题。
PARTITION BY(可选)：分区字段，例如要按月分区，可以使用表达式 toYYYYMM(date_column)
SAMPLE BY(可选)：采样字段，如果指定了该字段，那么主键中也必须包含该字段。比如 SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID))。
TTL(可选)：数据的存活时间。在MergeTree中，可以为某个列字段或整张表设置TTL。当时间到达时，如果是列字段级别的TTL，则会删除这一列的数据；如果是表级别的TTL，则会删除整张表的数据。
2.2 ReplacingMergeTree
2.2.1 MergeTree 不 能 对 相 同 主 键 的 数 据 进 行 去 重 ， ClickHouse 提 供 了 ReplacingMergeTree 引擎，可以针对同分区内相同主键的数据进行去重，它能够在合并分区时删除重复的数据。
值得注意的是，ReplacingMergeTree 只是在一定程度上解决了数据重复问题，由于自动分区合并机制在后台定时执行，所以并不能完全保障数据不重复。
（ClickHouse插入数据后不会立刻进行分区的合并，后台会定时执行自动分区机制，也可以手动合并分区 optimize table login_info partition ‘202107’）
ReplacingMergeTree 适用于在后台清除重复的数据以节省空间。
2.2.2 建表语句：
create table t_replacing_mt(
id UInt8,
name String,
age UInt8,
gender String
) engine = ReplacingMergeTree(age)
order by (id,age)
primary key id
partition by gender;
其中age作为ReplacingMergeTree的[ver]参数。
使用 ReplacingMergeTree 是需要注意以下几点：
（1）如何判断数据重复
ReplacingMergeTree 在去除重复数据时，是以 ORDERBY 排序键为基准的，而不是PRIMARY KEY。
（2）何时删除重复数据
在执行分区合并时，会触发删除重复数据。optimize 的合并操作是在后台执行的，无法预测具体执行时间点，除非是手动执行。
（3）不同分区的重复数据不会被去重
ReplacingMergeTree 是以分区为单位删除重复数据的。只有在相同的数据分区内重复的数据才可以被删除，而不同数据分区之间的重复数据依然不能被剔除。
（4）数据去重的策略是什么
如果未设置ver参数，则依据orderby排序键的字段为基准，选择最新插入的数据作为合并后的数据；如果设置ver参数，则依据orderby排序键的字段为基准，选择ver最大的数据作为合并后的数据。
（5）optimize 命令使用
一般在数据量比较大的情况，尽量不要使用该命令。因为在海量数据场景下，执行optimize 要消耗大量时间。
2.3 SummingMergeTree
2.3.1 SummingMergeTree会将所有具有相同主键的行合并为一行，被合并行中数据类型的字段值会被sum合并，其实就是类似于group by + sum的过程，可以显著减少存储空间并加快数据查询速度。
如果用户只需要查询数据的汇总结果，不关心明细数据，并且数据的汇总条件是预先明确的，即 GROUP BY 的分组字段是确定的，可以使用该表引擎。
2.3.2 建表语句
create table t_summing_mt(
id UInt8,
name String,
age UInt8,
loc String,
dept String,
workdays UInt8,
salary Decimal32(2)
) engine = SummingMergeTree(salary)
order by (id,age)
primary key id
partition by loc;
其中salary作为SummingMergeTree的[columns]参数。
[columns]为将要被汇总的列，所选的列必须是数值类型，并且不可位于主键中。如果没有指定[columns]，ClickHouse 会把所有不在主键中的数值类型的列都进行汇总。
使用 ReplacingMergeTree 是需要注意以下几点：
（1）SummingMergeTree 是根据什么对两条数据进行合并的
用 ORBER BY 排序键作为聚合数据的条件 Key。即如果排序 key 是相同的，则会合并
成一条数据，并对指定的合并字段进行聚合。
（2）仅对分区内的相同排序 key 的数据行进行合并
以数据分区为单位来聚合数据。当分区合并时，同一数据分区内聚合 Key 相同的数据
会被合并汇总，而不同分区之间的数据则不会被汇总。
（3）如果没有指定聚合字段，会怎么聚合
如果没有指定聚合字段，则会按照非主键的数值类型字段进行聚合。
（4）对于非汇总字段的数据，该保留哪一条
如果两行数据除了排序字段相同，其他的非聚合字段不相同，那么在聚合发生时，会保留最初的那条数据，新插入的数据对应的那个字段值会被舍弃。
2.4 AggregatingMergeTree
2.4.1 AggregatingMergeTree可以理解为SummingMergeTree的扩展版，SummingMergeTree 只能对非主键列进行 sum 聚合，而 AggregatingMergeTree 则可以指定各种聚合函数。
2.4.2 建表语句
create table t_aggregating_mt(
id UInt8,
name String,
age UInt8,
loc String,
dept String,
workdays UInt8,
salary AggregateFunction(sum,Decimal32(2))
) engine = AggregatingMergeTree()
order by (id,age)
primary key id
partition by loc;
其中指定聚合的字段需要加上AggregateFunction(聚合类型,字段类型)
对于 AggregateFunction 类型的列字段，在进行数据的写入和查询时与其他的表引擎有很大区别，
在写入数据时，需要调用 *-State 函数；而在查询数据时，则需要调用相应的 *-Merge 函数。
写入数据：
insert into t_aggregating_mt select 1,' 张 三 ',18,' 北 京','java',18,sumState(toDecimal32(10000,2));
insert into t_aggregating_mt select 2,' 李 四 ',19,' 上 海','java',22,sumState(toDecimal32(8000,2));
原始数据：
| id | name | age | loc | dept | workdays | salary |
|---|---|---|---|---|---|---|
| 1 | 张三 | 18 | 北京 | java | 18 | 10000.00 |
| 2 | 李四 | 19 | 上海 | java | 22 | 8000.00 |
插入一条新数据:
insert into t_aggregating_mt select 1,'张三三',18,'北京',' 前端',22,sumState(toDecimal32(5000,2));
#使用 optimize 命令合并相同分区数据
optimize table t_aggregating_mt;
查询数据：
#正确方式查询表 t_aggregating_mt 中的数据,注意需要跟上 groupBy
select * ,sumMerge(salary) from t_aggregating_mt group by id,name ,age, loc,dept,workdays,salary ;
合并后的数据：
| id | name | age | loc | dept | workdays | salary | sumMerge(salary) |
|---|---|---|---|---|---|---|---|
| 1 | 张三 | 18 | 北京 | java | 18 | 10000.00 | 15000.00 |
| 2 | 李四 | 19 | 上海 | java | 22 | 8000.00 | 8000.00 |
以上方式使用 AggregatingMergeTree 表引擎比较不方便，更多情况下，将AggregatingMergeTree 作为物化视图的表引擎与 MergeeTree 搭配使用。
2.4.3 示例
创建表 t_merge_base 表，使用 MergeTree 引擎
create table t_merge_base(
id UInt8,
name String,
age UInt8,
loc String,
dept String,
workdays UInt8,
salary Decimal32(2)
)engine = MergeTree()
order by (id,age)
primary key id
partition by loc;
创建物化视图 view_aggregating_mt ,使用 AggregatingMergeTree 引擎
create materialized view view_aggregating_mt
engine = AggregatingMergeTree()
order by id
as select
id,
name,
sumState(salary) as ss
from t_merge_base
group by id ,name ;
向表 t_merge_base 中插入数据
insert into t_merge_base values (1,'张三',18,'北京','大数据',24,10000),(2,'李四',19,'上海','java',22,8000),(3,'王五',20,'北京','java',26,12000);
查看 view_aggregating_mt 视图数据
select *,sumMerge(ss) from view_aggregating_mt group by id,name,ss;
| id | name | ss | sumMerge(ss) |
|---|---|---|---|
| 2 | 李四 | 8000.00 | 8000.00 |
| 3 | 王五 | 12000.00 | 12000.00 |
| 1 | 张三 | 10000.00 | 10000.00 |
继续向表 t_merge_base 中插入排序键相同的数据
insert into t_merge_base values (1,'张三三',18,'北京','前端',22,5000);
手动执行 optimize 命令，合并物化视图 view_aggregating_mt 相同分区数据
optimize table view_aggregating_mt;
查询视图 view_aggregating_mt 数据
select *,sumMerge(ss) from view_aggregating_mt group by id,name,ss;
| id | name | ss | sumMerge(ss) |
|---|---|---|---|
| 2 | 李四 | 8000.00 | 8000.00 |
| 3 | 王五 | 12000.00 | 15000.00 |
| 1 | 张三 | 10000.00 | 10000.00 |
其实这种方式就是类似于kylin的预处理，将经常需要使用的聚合结果自动进行预处理，需要的时候可以直接取出。
通 过 普 通 MergeTree 表 与 AggregatingMergeTree 物 化 视 图 结 合 使 用 ，MergeTree 中存放原子数据，物化视图中存入聚合结果数据，可以提升数据查询效率。
2.5 CollapsingMergeTree
2.5.1 CollapsingMergeTree就是一种通过以增代删的思路，它通过定义一个 sign 标记位字段，记录数据行的状态。如果 sign 标记为 1，则表示这是一行有效的数据；如果 sign 标记为-1，则表示这行数据需要被删除。当CollapsingMergeTree 分区合并时，同一数据分区内，sign 标记为 1 和-1 的一组数据会被抵消删除。
每次需要新增数据时，写入一行 sign 标记为 1 的数据；需要删除数据时，则写入一行 sign 标记为-1 的数据。此外，只有相同分区内的数据才有可能被折叠。
2.5.2 示例1 按照顺序写入需要更新或删除的数据：
#创建表 t_collapsing_mt ,使用 CollapsingMergeTree
create table t_collapsing_mt(
id UInt8,
name String,
loc String,
login_times UInt8,
total_dur UInt8,
sign Int8
)engine = CollapsingMergeTree(sign)
order by (id,total_dur)
primary key id
partition by loc;
向表 t_collapsing_mt 中插入以下数据：
insert into t_collapsing_mt values(1,' 张 三 ',' 北 京',1,30,1),(2,'李四','上海',1,40,1)
查看表 t_collapsing_mt 中的数据
select * from t_collapsing_mt;
| id | name | loc | login_times | total_dur | sign |
|---|---|---|---|---|---|
| 2 | 李四 | 上海 | 1 | 40 | 1 |
| 1 | 张三 | 北京 | 1 | 30 | 1 |
向表 t_collapsing_mt 中继续插入一条数据，删除“张三”数据
insert into t_collapsing_mt values(1,'张三','北京',1,30,-1);
查询表 t_collapsing_mt 中的数据
select * from t_collapsing_mt;
| id | name | loc | login_times | total_dur | sign |
|---|---|---|---|---|---|
| 2 | 李四 | 上海 | 1 | 40 | 1 |
| 1 | 张三 | 北京 | 1 | 30 | 1 |
| 1 | 张三 | 北京 | 1 | 30 | -1 |
手动触发 optimize 合并相同分区数据
optimize table t_collapsing_mt;
查询表 t_collapsing_mt 中的数据
node1 :) select * from t_collapsing_mt;
| id | name | loc | login_times | total_dur | sign |
|---|---|---|---|---|---|
| 2 | 李四 | 上海 | 1 | 40 | 1 |
插入以下两条数据，来更新 “李四”数据
insert into t_collapsing_mt values(2,' 李 四 ',' 上 海',1,40,-1),(2,'李四','上海',2,100,1);
查询表 t_collapsing_mt 中的数据
select * from t_collapsing_mt;
| id | name | loc | login_times | total_dur | sign |
|---|---|---|---|---|---|
| 2 | 李四 | 上海 | 1 | 40 | 1 |
| 2 | 李四 | 上海 | 1 | 40 | -1 |
| 2 | 李四 | 上海 | 2 | 100 | 1 |
| 手动执行 optimize 触发相同分区合并 |
optimize table t_collapsing_mt;
查看表 t_collapsing_mt 中的数据
select * from t_collapsing_mt;
| id | name | loc | login_times | total_dur | sign |
|---|---|---|---|---|---|
| 2 | 李四 | 上海 | 1 | 100 | 1 |
以上功能使用 collapsingMergeTree 实现了分区合并。
2.5.3 示例2 乱序写入需要更新或删除的数据：
删除表 t_collapsing_mt ，重新创建表 t_collapsing_mt，这里建表语句与之前一样
向表 t_collapsing_mt 中插入以下数据：
insert into t_collapsing_mt values(1,' 张 三 ',' 北 京',1,30,-1),(1,'张三','北京',1,30,1),(2,'李四','上海',1,40,1)
查询表 t_collapsing_mt 中的数据
select * from t_collapsing_mt;
| id | name | loc | login_times | total_dur | sign |
|---|---|---|---|---|---|
| 2 | 李四 | 上海 | 1 | 40 | 1 |
| 1 | 张三 | 北京 | 1 | 30 | -1 |
| 1 | 张三 | 北京 | 1 | 30 | 1 |
手动执行 optimize 命令，合并相同分区数据
optimize table t_collapsing_mt;
查询表 t_collapsing_mt 表中的数据，数据没有变化
select * from t_collapsing_mt;
| id | name | loc | login_times | total_dur | sign |
|---|---|---|---|---|---|
| 2 | 李四 | 上海 | 1 | 40 | 1 |
| 1 | 张三 | 北京 | 1 | 30 | -1 |
| 1 | 张三 | 北京 | 1 | 30 | 1 |
注意：当数据插入到表中的顺序标记如果不是 1,-1 这种顺序时，合并相同分区内的数据不能达到修改和更新效果。
如果数据的写入程序是单线程执行的，则能够较好地控制写入顺序；如果需要处理的数据量很大，数据的写入程序通常是多线程执行的，那么此时就不能保障数据的写入顺序了。在这种情况下，CollapsingMergeTree 的工作机制就会出现问题。但是可以通过VersionedCollapsingMergeTree 的表引擎得到解决。
2.6 VersionedCollapsingMergeTree
2.6.1 上面提到 CollapsingMergeTree 表引擎对于数据写入乱序的情况下，不能够实现数据折叠的效果。VersionedCollapsingMergeTree 表 引 擎 的 作 用 与CollapsingMergeTree 完 全 相 同 ， 它 们 的 不 同 之 处 在 于 ，VersionedCollapsingMergeTree 对数据的写入顺序没有要求，在同一个分区内，任意顺序的数据都能够完成折叠操作。
VersionedCollapsingMergeTree 使用 version 列来实现乱序情况下的数据折叠，该引擎除了需要指定一个 sign 标识之外，还需要指定一个 UInt类型的 version 版本号。
2.6.2 示例
创建表 t_version_collapsing_mt ,使用 VersionedCollapsingMergeTree 引擎
create table t_version_collapsing_mt(
id UInt8,
name String,
loc String,
login_times UInt8,
total_dur UInt8,
sign Int8,
version UInt8
) engine = VersionedCollapsingMergeTree(sign,version)
order by (id,total_dur)
primary key id
partition by loc;
向表 t_version_collapsing_mt 中插入以下数据
insert into table t_version_collapsing_mt values(1,'张三',' 北京',1,30,-1,1),(2,'李四','上海',1,40,1,2);
查询表 t_version_collapsing_mt 中的数据
select * from t_version_collapsing_mt;
| id | name | loc | login_times | total_dur | sign | version |
|---|---|---|---|---|---|---|
| 1 | 张三 | 北京 | 1 | 30 | -1 | 1 |
| 2 | 李四 | 上海 | 1 | 40 | 1 | 2 |
向表 t_version_collapsing_mt 中插入以下数据，删除“张三”信息，更新“李四”信息
insert into table t_version_collapsing_mt values(1,'张三',' 北 京 ',1,30,1,1),(2,' 李 四 ',' 上 海 ',1,40,-1,2),(2,' 李 四 ',' 上 海',2,100,1,2);
查询表 t_version_collapsing_mt 中的数据
select * from t_version_collapsing_mt ;
| id | name | loc | login_times | total_dur | sign | version |
|---|---|---|---|---|---|---|
| 1 | 张三 | 北京 | 1 | 30 | -1 | 1 |
| 2 | 李四 | 上海 | 1 | 40 | 1 | 2 |
| 1 | 张三 | 北京 | 1 | 30 | 1 | 1 |
| 2 | 李四 | 上海 | 1 | 40 | -1 | 2 |
| 2 | 李四 | 上海 | 2 | 100 | 1 | 2 |
手动执行 optimize 命令，合并相同分区的数据，这里有可能需要执行多次
optimize table t_version_collapsing_mt final;
查询表 t_version_collapsing_mt 中的数据如下：
select * from t_version_collapsing_mt;
| id | name | loc | login_times | total_dur | sign | version |
|---|---|---|---|---|---|---|
| 2 | 李四 | 上海 | 2 | 100 | 1 | 2 |
对于张三这一行信息，先插入的是-1然后再插入1，也能够实现删除的效果，所以可以实现任意顺序的数据都能完成折叠操作。
扫一扫
634
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
