博客 Clickhouse表引擎介绍丨技术干货

Clickhouse表引擎介绍丨技术干货

   数栈君   发表于 2024-02-21 10:18  110  0

引擎分类

ClickHouse表引擎一共分为四个系列，分别是Log、MergeTree、Integration、Special。其中包含了两种特殊的表引擎Replicated、Distributed，功能上与其他表引擎正交，根据场景组合使用。


http://dtstack-static.oss-cn-hangzhou.aliyuncs.com/2021bbs/files_user1/article/fbed7f2ceba3a401dd5e31f04ef44fbd..png

Log系列

Log系列表引擎功能相对简单，主要用于快速写入小表（1百万行左右的表），然后全部读出的场景。

几种Log表引擎的共性是：

  • 数据被顺序append写到磁盘上。

  • 不支持delete、update。

  • 不支持index。

  • 不支持原子性写。

  • insert会阻塞select操作。

它们彼此之间的区别是：

  • TinyLog：不支持并发读取数据文件，查询性能较差；格式简单，适合用来暂存中间数据。

  • StripLog：支持并发读取数据文件，查询性能比TinyLog好；将所有列存储在同一个大文件中，减少了文件个数。

  • Log：支持并发读取数据文件，查询性能比TinyLog好；每个列会单独存储在一个独立文件中。


Integration系列


该系统表引擎主要用于将外部数据导入到ClickHouse中，或者在ClickHouse中直接操作外部数据源。

  • Kafka：将Kafka Topic中的数据直接导入到ClickHouse。

  • MySQL：将Mysql作为存储引擎，直接在ClickHouse中对MySQL表进行select等操作。

  • JDBC/ODBC：通过指定jdbc、odbc连接串读取数据源。

  • HDFS：直接读取HDFS上的特定格式的数据文件；

Special系列

Special系列的表引擎，大多是为了特定场景而定制的：

  • Memory：将数据存储在内存中，重启后会导致数据丢失。查询性能极好，适合于对于数据持久性没有要求的1亿以下的小表。在ClickHouse中，通常用来做临时表。

  • Buffer：为目标表设置一个内存buffer，当buffer达到了一定条件之后会flush到磁盘。

  • File：直接将本地文件作为数据存储。

  • Null：写入数据被丢弃、读取数据为空。


MergeTree系列

一、MergeTree

MergeTree表引擎主要用于海量数据分析，支持数据分区、存储有序、主键索引、稀疏索引、数据TTL等。MergeTree支持所有ClickHouse SQL语法，但是有些功能与MySQL并不一致，比如在MergeTree中主键并不用于去重。


1.1 数据TTL

1、基本语法

SQL
TTL time_col + INTERVAL ${num} [SECOND|MONTH]

目前TTL可对具体指定列级别、或者表级别进行设置，但是设置后无法取消。


2、实现原理

1）TTL处理逻辑

  • 若表定义中设置了TTL相关设置，在数据写入时，在分区目录下会额外生成一个ttl.txt文件，该文件通过json的格式记录了当前表列级别、表级别的TTL设置，以及当前分区目录下TTL指定时间字段的最小最大值。

  • MergeTree 以分区目录为单位，通过 ttl.txt文件记录过期时间，并将其作为后续的 判断依据 。

  • 每当写入一批数据时，都会基于INTERVAL表达式的计算结果为这个分区生成 ttl. txt 文件 。

  • 只有在MergeTree合并分区时，才会触发删除 TTL过期数据的逻辑。

  • 在选择删除的分区时，会使用贪婪算法，它的算法规则是尽可能找到会最早过期 的，同时年纪又是最老的分区(合并次数更多 ， MaxBlockNum更大的分区目录) 。

  • 如果一个分区内某一列数据因为 TTL 到期全部被删除了，那么在合并之后生成的 新分区目录中，将不会包含这个列字段的数据文件( .bin 和 .mrk)。

2）如何触发TTL

  • TTL默认合并频率，由参数merge_with_ttl_timeout控制

  • 使用 optimize命令强制触发合并

SQL
-- 触发一个分区的合并

optimize TABLE table_name



-- 触发所有分区的合并

optimize TABLE table_name FINAL

1.2 多路径存储策略


二、ReplacingMergeTree

为了解决MergeTree相同主键无法去重的问题，ClickHouse提供了ReplacingMergeTree引擎，用来做去重。虽然ReplacingMergeTree提供了主键去重的能力，但是仍旧有以下限制：

  • 在没有彻底optimize之前，可能无法达到主键去重的效果，比如部分数据已经被去重，而另外一部分数据仍旧有主键重复。

  • 在分布式场景下，相同primary key的数据可能被sharding到不同节点上，不同shard间可能无法去重。

  • optimize是后台动作，无法预测具体执行时间点。

  • 手动执行optimize在海量数据场景下要消耗大量时间，无法满足业务即时查询的需求。

  • ReplacingMergeTree更多被用于确保数据最终被去重，而无法保证查询过程中主键不重复。

三、SummingMergeTree

ClickHouse通过SummingMergeTree来支持对主键列进行预先聚合。在后台Compaction时，会将主键相同的多行进行sum求和，然后使用一行数据取而代之，从而大幅度降低存储空间占用，提升聚合计算性能。值得注意的是：

  • ClickHouse只在后台Compaction时才会进行数据的预先聚合，而compaction的执行时机无法预测，所以可能存在部分数据已经被预先聚合、部分数据尚未被聚合的情况。因此，在执行聚合计算时，SQL中仍需要使用GROUP BY子句。

  • 在预先聚合时，ClickHouse会对主键列之外的其他所有列进行预聚合。如果这些列是可聚合的（比如数值类型），则直接sum；如果不可聚合（比如String类型），则随机选择一个值。

  • 通常建议将SummingMergeTree与MergeTree配合使用，使用MergeTree来存储具体明细，使用SummingMergeTree来存储预先聚合的结果加速查询。

SQL
-- 建表

CREATE TABLE summtt

(

    key UInt32,

    value UInt32

)

ENGINE = SummingMergeTree()

ORDER BY key



-- 插入数据

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



-- compaction前查询，仍存在多行

select * from summtt;

┌─key─┬─value─┐

│   1 │     1 │

│   1 │     2 │

│   2 │     1 │

└─────┴───────┘



-- 通过GROUP BY进行聚合计算

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

┌─key─┬─sum(value)─┐

│   2 │          1 │

│   1 │          3 │

└─────┴────────────┘



-- 强制compaction

optimize table summtt final;



-- compaction后查询，可以看到数据已经被预先聚合

select * from summtt;

┌─key─┬─value─┐

│   1 │     3 │

│   2 │     1 │

└─────┴───────┘





-- compaction后，仍旧需要通过GROUP BY进行聚合计算

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

┌─key─┬─sum(value)─┐

│   2 │          1 │

│   1 │          3 │

└─────┴────────────┘

四、AggregatingMergeTree

AggregatingMergeTree也是预先聚合引擎的一种，用于提升聚合计算的性能。与SummingMergeTree的区别在于：SummingMergeTree对非主键列进行sum聚合，而AggregatingMergeTree则可以指定各种聚合函数。

AggregatingMergeTree的语法比较复杂，需要结合物化视图或ClickHouse的特殊数据类型AggregateFunction一起使用。在insert和select时，也有独特的写法和要求：写入时需要使用-State语法，查询时使用-Merge语法。

示例一：配合物化视图使用。

SQL
-- 建立明细表

CREATE TABLE visits

(

    UserID UInt64,

    CounterID UInt8,

    StartDate Date,

    Sign Int8

)

ENGINE = CollapsingMergeTree(Sign)

ORDER BY UserID;



-- 对明细表建立物化视图，该物化视图对明细表进行预先聚合

-- 注意：预先聚合使用的函数分别为： sumState, uniqState。对应于写入语法<agg>-State.

CREATE MATERIALIZED VIEW visits_agg_view

ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)

AS SELECT

    CounterID,

    StartDate,

    sumState(Sign)    AS Visits,

    uniqState(UserID) AS Users

FROM visits

GROUP BY CounterID, StartDate;



-- 插入明细数据

INSERT INTO visits VALUES(0, 0, '2019-11-11', 1);

INSERT INTO visits VALUES(1, 1, '2019-11-12', 1);



-- 对物化视图进行最终的聚合操作

-- 注意：使用的聚合函数为 sumMerge， uniqMerge。对应于查询语法<agg>-Merge.

SELECT

    StartDate,

    sumMerge(Visits) AS Visits,

    uniqMerge(Users) AS Users

FROM visits_agg_view

GROUP BY StartDate

ORDER BY StartDate;



-- 普通函数 sum, uniq不再可以使用

-- 如下SQL会报错： Illegal type AggregateFunction(sum, Int8) of argument 

SELECT

    StartDate,

    sum(Visits),

    uniq(Users)

FROM visits_agg_view

GROUP BY StartDate

ORDER BY StartDate;

示例二：配合特殊数据类型AggregateFunction使用。

SQL
-- 建立明细表

CREATE TABLE detail_table

(   CounterID UInt8,

    StartDate Date,

    UserID UInt64

) ENGINE = MergeTree() 

PARTITION BY toYYYYMM(StartDate) 

ORDER BY (CounterID, StartDate);



-- 插入明细数据

INSERT INTO detail_table VALUES(0, '2019-11-11', 1);

INSERT INTO detail_table VALUES(1, '2019-11-12', 1);



-- 建立预先聚合表，

-- 注意：其中UserID一列的类型为：AggregateFunction(uniq, UInt64)

CREATE TABLE agg_table

(   CounterID UInt8,

    StartDate Date,

    UserID AggregateFunction(uniq, UInt64)

) ENGINE = AggregatingMergeTree() 

PARTITION BY toYYYYMM(StartDate) 

ORDER BY (CounterID, StartDate);



-- 从明细表中读取数据，插入聚合表。

-- 注意：子查询中使用的聚合函数为 uniqState， 对应于写入语法<agg>-State

INSERT INTO agg_table

select CounterID, StartDate, uniqState(UserID)

from detail_table

group by CounterID, StartDate



-- 不能使用普通insert语句向AggregatingMergeTree中插入数据。

-- 本SQL会报错：Cannot convert UInt64 to AggregateFunction(uniq, UInt64)

INSERT INTO agg_table VALUES(1, '2019-11-12', 1);



-- 从聚合表中查询。

-- 注意：select中使用的聚合函数为uniqMerge，对应于查询语法<agg>-Merge

SELECT uniqMerge(UserID) AS state 

FROM agg_table 

GROUP BY CounterID, StartDate;

五、CollapsingMergeTree

ClickHouse实现了CollapsingMergeTree来消除ReplacingMergeTree的功能限制。该引擎要求在建表语句中指定一个标记列Sign，后台Compaction时会将主键相同、Sign相反的行进行折叠，也即删除。

  • CollapsingMergeTree将行按照Sign的值分为两类：Sign=1的行称之为状态行，Sign=-1的行称之为取消行。

  • 每次需要新增状态时，写入一行状态行；需要删除状态时，则写入一行取消行。

  • 在后台Compaction时，状态行与取消行会自动做折叠（删除）处理。而尚未进行Compaction的数据，状态行与取消行同时存在。

因此为了能够达到主键折叠（删除）的目的，需要业务层进行适当改造：

  • 执行删除操作需要写入取消行，而取消行中需要包含与原始状态行主键一样的数据（Sign列除外）。所以在应用层需要记录原始状态行的值，或者在执行删除操作前先查询数据库获取原始状态行。

  • 由于后台Compaction时机无法预测，在发起查询时，状态行和取消行可能尚未被折叠；另外，ClickHouse无法保证primary

  • key相同的行落在同一个节点上，不在同一节点上的数据无法折叠。因此在进行count()、sum(col)等聚合计算时，可能会存在数据冗余的情况。为了获得正确结果，业务层需要改写SQL，将count()、sum(col)分别改写为sum(Sign)、sum(col Sign)。

  • CollapsingMergeTree虽然解决了主键相同的数据即时删除的问题，但是状态持续变化且多线程并行写入情况下，状态行与取消行位置可能乱序，导致无法正常折叠。

SQL
-- 建表

CREATE TABLE UAct

(

    UserID UInt64,

    PageViews UInt8,

    Duration UInt8,

    Sign Int8

)

ENGINE = CollapsingMergeTree(Sign)

ORDER BY UserID;



-- 插入状态行，注意sign一列的值为1

INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1);



-- 插入一行取消行，用于抵消上述状态行。注意sign一列的值为-1，其余值与状态行一致；

-- 并且插入一行主键相同的新状态行，用来将PageViews从5更新至6，将Duration从146更新为185.

INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1), (4324182021466249494, 6, 185, 1);



-- 查询数据：可以看到未Compaction之前，状态行与取消行共存。

SELECT * FROM UAct;

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐

│ 4324182021466249494 │         5 │      146 │   -1 │

│ 4324182021466249494 │         6 │      185 │    1 │

└─────────────────────┴───────────┴──────────┴──────┘

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐

│ 4324182021466249494 │         5 │      146 │    1 │

└─────────────────────┴───────────┴──────────┴──────┘



-- 为了获取正确的sum值，需要改写SQL： 

-- sum(PageViews) => sum(PageViews * Sign)、 

-- sum(Duration) => sum(Duration * Sign)

SELECT

    UserID,

    sum(PageViews * Sign) AS PageViews,

    sum(Duration * Sign) AS Duration

FROM UAct

GROUP BY UserID

HAVING sum(Sign) > 0;

┌──────────────UserID─┬─PageViews─┬─Duration─┐

│ 4324182021466249494 │         6 │      185 │

└─────────────────────┴───────────┴──────────┘





-- 强制后台Compaction

optimize table UAct final;



-- 再次查询，可以看到状态行、取消行已经被折叠，只剩下最新的一行状态行。

select * from UAct;

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐

│ 4324182021466249494 │         6 │      185 │    1 │

└─────────────────────┴───────────┴──────────┴──────┘

多线程并发写入下导致数据混乱的示例

SQL
-- 建表

CREATE TABLE UAct_order

(

    UserID UInt64,

    PageViews UInt8,

    Duration UInt8,

    Sign Int8

)

ENGINE = CollapsingMergeTree(Sign)

ORDER BY UserID;



-- 先插入取消行

INSERT INTO UAct_order VALUES (4324182021466249495, 5, 146, -1);

-- 后插入状态行

INSERT INTO UAct_order VALUES (4324182021466249495, 5, 146, 1);



-- 强制Compaction

optimize table UAct_order final;



-- 可以看到即便Compaction之后也无法进行主键折叠: 2行数据仍旧都存在。

select * from UAct_order;

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐

│ 4324182021466249495 │         5 │      146 │   -1 │

│ 4324182021466249495 │         5 │      146 │    1 │

└─────────────────────┴───────────┴──────────┴──────┘

六、VersionedCollapsingMergeTree

为了解决CollapsingMergeTree乱序写入情况下无法正常折叠问题，VersionedCollapsingMergeTree表引擎在建表语句中新增了一列Version，用于在乱序情况下记录状态行与取消行的对应关系。主键相同，且Version相同、Sign相反的行，在Compaction时会被删除。与CollapsingMergeTree类似， 为了获得正确结果，业务层需要改写SQL，将count()、sum(col)分别改写为sum(Sign)、sum(col * Sign)。

SQL
-- 建表

CREATE TABLE UAct_version

(

    UserID UInt64,

    PageViews UInt8,

    Duration UInt8,

    Sign Int8,

    Version UInt8

)

ENGINE = VersionedCollapsingMergeTree(Sign, Version)

ORDER BY UserID;





-- 先插入一行取消行，注意Signz=-1, Version=1

INSERT INTO UAct_version VALUES (4324182021466249494, 5, 146, -1, 1);



-- 后插入一行状态行，注意Sign=1, Version=1；及一行新的状态行注意Sign=1, Version=2，将PageViews从5更新至6，将Duration从146更新为185。

INSERT INTO UAct_version VALUES (4324182021466249494, 5, 146, 1, 1),(4324182021466249494, 6, 185, 1, 2);





-- 查询可以看到未compaction情况下，所有行都可见。

SELECT * FROM UAct_version;

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐

│ 4324182021466249494 │         5 │      146 │   -1 │

│ 4324182021466249494 │         6 │      185 │    1 │

└─────────────────────┴───────────┴──────────┴──────┘

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐

│ 4324182021466249494 │         5 │      146 │    1 │

└─────────────────────┴───────────┴──────────┴──────┘





-- 为了获取正确的sum值，需要改写SQL： 

-- sum(PageViews) => sum(PageViews * Sign)、 

-- sum(Duration) => sum(Duration * Sign)

SELECT

    UserID,

    sum(PageViews * Sign) AS PageViews,

    sum(Duration * Sign) AS Duration

FROM UAct_version

GROUP BY UserID

HAVING sum(Sign) > 0;

┌──────────────UserID─┬─PageViews─┬─Duration─┐

│ 4324182021466249494 │         6 │      185 │

└─────────────────────┴───────────┴──────────┘





-- 强制后台Compaction

optimize table UAct_version final;





-- 再次查询，可以看到即便取消行与状态行位置乱序，仍旧可以被正确折叠。

select * from UAct_version;

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐

│ 4324182021466249494 │         6 │      185 │    1 │       2 │

└─────────────────────┴───────────┴──────────┴──────┴─────────┘




《数据治理行业实践白皮书》下载地址：https://fs80.cn/4w2atu

《数栈V6.0产品白皮书》下载地址：https://fs80.cn/cw0iw1

想了解或咨询更多有关袋鼠云大数据产品、行业解决方案、客户案例的朋友，浏览袋鼠云官网：https://www.dtstack.com/?src=bbs

同时，欢迎对大数据开源项目有兴趣的同学加入「袋鼠云开源框架钉钉技术群」，交流最新开源技术信息，群号码：30537511，项目地址：https://github.com/DTStack 
0条评论
上一篇：Clickhouse MergeTree 原理（一）丨技...
下一篇：

社区公告

  • 大数据领域最专业的产品&技术交流社区，专注于探讨与分享大数据领域有趣又火热的信息，专业又专注的数据人园地

最新活动更多
微信扫码获取数字化转型资料
钉钉扫码加入技术交流群
热门产品
数雁EasyDigit 数栈DTinsight 数驹DTengine 易知微EasyV
解决方案
政务解决方案 港口解决方案 基金解决方案 制造解决方案 保险解决方案 高校解决方案 证券解决方案 文旅解决方案 银行解决方案 大宗商品解决方案
快速入口
合作与生态 开源社区 Github
联系我们

合作咨询 market@dtstack.com

联系电话 400-002-1024

总部地址 杭州市余杭区余杭塘路2301号海智中心8号楼

袋鼠云官方订阅号
袋鼠云官方订阅号
热门搜索：
数据中台 企业数据中台 金融数据中台 离线数据中台 数据中台公司 一站式数据中台 数据中台开发 一站式数据开发 数据中台解决方案 大数据分析 数据分析平台 新基建 大数据开发 大数据开发平台 数据化转型解决方案 信创 数据可视化 数字孪生 可视化大屏 数字化转型

友情链接： 易知微 云掣

@Copyrights 2016-2023 杭州玳数科技有限公司 浙ICP备15044486号-1 浙公网安备33011002011932号