ClickHouse表引擎使用总结

发布于 2019年08月07日

概述

本文档对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;
上一篇 使用kubeasz搭建HA K8S集群
下一篇 配置使用cert-manager

评论