ClickHouse表引擎使用总结

ItTalk 2021-09-27

424

概述

本文档对ClickHouse的部分表引擎使用做一个总结。

表引擎总结

主要总结Integration，Log，MergeTree，这几个类别。

Integration相关的系列引擎

主要用于将其它数据源的表链接到ClickHouse，方便数据查询与导入。

MySQL引擎

支持INSERT与SELECT语法创建表的语法：

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],
    ...
) ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);
#说明 MySQL在20.8.7这个版本没有SETTINGS的clause的设置。

测试语句：

CREATE TABLE mysql_table1
(
    id UInt32,
    uid UInt64,
    profile_code String,
    profile_value String,
    last_op_user_id UInt64,
    last_op_user_name String,
    batch_id String,
    create_time DateTime,
    update_time DateTime
)
ENGINE = MySQL('host:port', 'database', 'table1', 'user', 'password');

Mongo引擎

对Mongo引擎来讲，只支持读取，不支持写入，也暂不支持嵌套类型创建表语法：

CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
    name1 [type1],
    name2 [type2],
    ...
) ENGINE = MongoDB(host:port, database, collection1, user, password);

测试语句：

CREATE TABLE mongo_table1
(
    _id UInt32,
    name String,
    age UInt64
) ENGINE = MongoDB('host:port', 'database', 'mongo_table1', 'user', 'password');

Postgres引擎

支持INSERT与SELECT语法，注：ck的版本修改为：21.3.13后可支持，生产环境20.8.7.15还不支持PostgreSQL引擎创建表语法：

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],
    ...
) ENGINE = PostgreSQL('host:port', 'database', 'table', 'user', 'password'[, `schema`]);

测试语句：

CREATE TABLE  postgres_table1
(
    id UInt64,
    name String,
    age UInt32
) ENGINE = PostgreSQL('host:port', 'database', 'postgres_table1', 'user', 'password');

Log家族相关的引擎

该表引擎主要适用于需要写入许多小数据量（少于一百万行）的表的场景。这一类引擎有一些共同点，数据保存到磁盘，追加的方式写入，并发访问时通过加锁的方式进行，不支持索引。

Log引擎

支持并行读取，表存储时，每个列按文件分开保存创建表语法：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    column1_name [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    column2_name [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = Log;
#注意：不支持primary key ,order by等子句

测试语句：

CREATE TABLE log_table1
(
    `name` String,
    `t` DateTime
)
ENGINE = Log;

对应表文件是按列分开保存（name与t是两个文件）：

root@clickhouse-tes-0:/var/lib/clickhouse/data/default/log_table1# ls -lrt
total 16
-rw-r--r--. 1 clickhouse clickhouse  32 Jun 29 03:24 __marks.mrk
-rw-r--r--. 1 clickhouse clickhouse 180 Jun 29 03:24 t.bin
-rw-r--r--. 1 clickhouse clickhouse 178 Jun 29 03:24 name.bin
-rw-r--r--. 1 clickhouse clickhouse  95 Jun 29 03:24 sizes.json

StripeLog引擎

支持并行读取，表存储时，只写入一个文件(data.bin) 创建表语法：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    column1_name [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    column2_name [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = StripeLog;
#注意 不支持primary key ,order by等子句

测试语句：

CREATE TABLE log_table2
(
    `name` String,
    `t` DateTime
)
ENGINE = StrapLog;

对应的表文件：

root@clickhouse-tes-0:/var/lib/clickhouse/data/default/log_table2# ls -lrt
total 12
-rw-r--r--. 1 clickhouse clickhouse 457 Jun 29 03:27 data.bin
-rw-r--r--. 1 clickhouse clickhouse 266 Jun 29 03:27 index.mrk
-rw-r--r--. 1 clickhouse clickhouse  69 Jun 29 03:27 sizes.json

TinyLog引擎

并发不做任何控制。这个引擎的典型用法：写入一次，多次读取，比较适用的场景是小批量处理中间数据。创建表语法:

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    column1_name [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    column2_name [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = TinyLog;
#注意不支持primary key ,order by等子句

测试语句：

CREATE TABLE log_table3
(
    `name` String,
    `t` DateTime
)
ENGINE = TinyLog;

对应的文件是按列分开保存（name与t是两个文件）：

root@clickhouse-tes-0:/var/lib/clickhouse/data/default/log_table3# ls -lrt
total 12
-rw-r--r--. 1 clickhouse clickhouse 120 Jun 29 07:22 t.bin
-rw-r--r--. 1 clickhouse clickhouse 121 Jun 29 07:22 name.bin
-rw-r--r--. 1 clickhouse clickhouse  65 Jun 29 07:22 sizes.json

MergeTree系列引擎

MergeTree系列引擎是ClickHouse提供的核心存储能力的引擎，有副本和高可用的能力，支持列式存储，主键索引，自定义分区，二级索引等等。这一系列引擎主要用于将大量的数据插入到一个表中。数据被快速地一部分一部分地写入表中，然后在后台应用规则来合并这些部分。这种方法比在插入过程中不断地重写存储中的数据要有效得多。主要的特性：* 存储的数据是按primary key排序的（primary key是稀疏索引，很小的索引可快速查找到数据） * 通过指定partitioning key来支持partition * 数据复制的支持 * 数据采样的支持

MergeTree引擎

创建表语法：

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
) 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()，MergeTree不支持任何参数
* ORDER BY :指定排序键，多个则需要定义成元组形式，例如：ORDER BY (CounterID, EventDate),如果没有显示定义PRIMARY KEY，则会用排序键做为主键。
* PARTITION BY：指定分区键，可选参数。如果不定义，则所有的数据都会放入到all的分区内。如要按月分区，可以使用表达式 toYYYYMM(date_column) ，这里的 date_column 是一个Date类型的列。分区名的格式会是 “YYYYMM” 。
* PRIMARY KEY:可选参数，用于指定主键（主要用于建立索引，如果与ORDER BY 指定的KEY不一致，则可设定）。
* SAMPLE BY:指定采样的列，可选参数。如果设置，必须包含主键。
* TTL:可定义一些规则，将数据移动到其它的磁盘（做冷热分离存储），可选参数，详细可参看 [链接](https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/mergetree/#table_engine-mergetree-ttl) 。
* SETTINGS:可设置对表相关的控制参数，可选。

测试语句：

CREATE TABLE my_order1
(
    `uid` UInt64,
    `price` UInt64,
    `d` Date
)
ENGINE = MergeTree
ORDER BY uid

ReplacingMergeTree引擎

与MergeTree引擎的不同之处在于他可以基于ORDER BY 对应的字段，移除重复的数据（注意：不是主键）重复数据删除只发生在合并期间。合并是后台处理，时间并不确定。可以使用OPTIMIZE来手工合并，但OPTIMIZE性能损耗比较大。创建表语法：

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 指定列的版本，类型可以为UInt*, Date, DateTime or DateTime64，可选参数，如果没有指定版本，则选择最后一条，指定ver，则选择ver值最大的一条。测试语句：

#没有指定版本 
CREATE TABLE rep_merge_tree_table1
(
    `id` UInt64,
    `name` String
)
ENGINE = ReplacingMergeTree
ORDER BY id;




#指定版本
CREATE TABLE rep_merge_tree_table2
(
    `id` UInt64,
    `name` String,
    `ver` UInt64
)
ENGINE = ReplacingMergeTree(ver)
ORDER BY id;

SummingMergeTree引擎

继承自MergeTree引擎。不同的是，它可以基于ORDER BY指定的列做合并，具体的合并方式是对SummingMergeTree(columns)指定的columns做sum的聚合操作。SummingMergeTree的参数：* columns, columns可以定义成一个元组(用()将多列括起来)，但必须是数值类型，并且不能包含order by对应的key。（如果columns中有不包含的列，则会随机选择一条进行合并） * 如果不指定，则默认所有的列都参与sum的聚合创建表语法：

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 sum_merge_tree_table1
(
    `id` UInt64,
    `view` UInt64,
    `click` UInt64
)
ENGINE = SummingMergeTree
ORDER BY id;
#指定列的聚合
 CREATE TABLE sum_merge_tree_table2
(
    `id` UInt64,
    `view` UInt64,
    `click` UInt64,
    `create_time` DateTime
)
ENGINE = SummingMergeTree((view, click))
ORDER BY id;

AggregatingMergeTree引擎

继承自MergeTree引擎，ClickHouse会基于ORDER BY 指定的key，按指定的aggregation函数进行聚合。可以定义一个AggregatingMergeTree引擎的表，或者一个聚合的物化视图。创建表语法：

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

测试语句：* 基于表：

CREATE TABLE agg_table1
(
    d Date,
    totalPrice AggregateFunction(sum, UInt64),
    uniqUid AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree()
ORDER BY d;
#使用：
#将数据导入到agg_table1表：
insert into agg_table1 select d,sumState(price) as totalPrice,uniqState(uid) as uniqUid from my_order1 group by d;
##查询：
select d,sumMerge(totalPrice),uniqMerge(uniqUid) from agg_table1 group by d;

* 基于物化视图：

CREATE MATERIALIZED VIEW agg_view1
ENGINE = AggregatingMergeTree() ORDER BY d
AS SELECT
    d,
    sumState(price)    AS totalPrice,
    uniqState(uid) AS uniqUid
FROM my_order1
GROUP BY d;
##使用：
##将以前的数据导入到物化视图：
INSERT INTO agg_view1 SELECT
     d,
     sumState(price) AS totalPrice,
     uniqState(uid) AS uniqUid
FROM my_order1
GROUP BY d;
#查询：
SELECT d,sumMerge(totalPrice),uniqMerge(uniqUid) FROM agg_view1 GROUP BY d;

CollapsingMergeTree引擎

继承自MergeTree引擎，并支持按列进行合并。CollapsingMergeTree 会异步的删除这些除了特定列 Sign 有 1 和 -1 的值以外，其余所有字段的值都相等的成对的行。没有成对的行会被保留。创建表语法：

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

CollapsingMergeTree表的参数：* sign - 使用1 表示state 行, -1表示 cancel行。取值为1和 -1。* sign的参数对应的列必须为Int8。测试语句：

CREATE TABLE collapsing_tab1
(
    `uid` UInt64,
    `click` UInt64,
    `view` UInt64,
    `sign` Int8
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY uid；

VersionedCollapsingMergeTree引擎

继承自MergeTree引擎，与CollapsingMergeTree一样，都可以进行数据合并，但CollapsingMergeTree严格依赖插入数据的顺序，VersionedCollapsingMergeTree可指定version列，对多线程环境使用支持更好。创建语句：

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

VersionedCollapsingMergeTree参数：* sign - 使用1 表示state 行, -1表示 cancel行。取值为1和 -1。* version - 指定带有对象状态的版本列标识，只有版本相同sign相反的两列才能合并，该字段的类型须为 UInt*. 测试语句：

CREATE TABLE collapsing_table2
(
    `uid` UInt64,
    `click` UInt64,
    `view` UInt64,
    `sign` Int8,
    `version` UInt64
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY uid;

数据库 clickhouse

文章转载自ItTalk，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

ClickHouse表引擎使用总结

表引擎总结

Integration相关的系列引擎

MySQL引擎

Mongo引擎

Postgres引擎

Log家族相关的引擎

Log引擎

StripeLog引擎

TinyLog引擎

MergeTree系列引擎

MergeTree引擎

ReplacingMergeTree引擎

SummingMergeTree引擎

AggregatingMergeTree引擎

CollapsingMergeTree引擎

VersionedCollapsingMergeTree引擎

评论