ClickHouse 简单使用（四）

8 表的引擎

和 MySQL 有相似，ClickHouse 创建表也需要指定引擎。ClickHouse有丰富的表引擎，以适合不同的应用场景。这里介绍几种常用的。更多信息，请参见table-engines。

8.1 MergeTree

ClickHouse 中最强大的表引擎当属 MergeTree （合并树）引擎及该系列（*MergeTree）中的其他引擎。

MergeTree 系列的引擎被设计用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多。

主要特点：

• 存储的数据按主键排序。

这使得你能够创建一个小型的稀疏索引来加快数据检索。

• 支持数据分区，如果指定了 分区键 的话。

在相同数据集和相同结果集的情况下 ClickHouse 中某些带分区的操作会比普通操作更快。查询中指定了分区键时 ClickHouse 会自动截取分区数据。这也有效增加了查询性能。

• 支持数据副本。

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

) ENGINE = MergeTree()

ORDER BY expr

[PARTITION BY expr]

[PRIMARY KEY expr]

[SAMPLE BY expr]

[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]

[SETTINGS name=value, ...]

8.2 CollapsingMergeTree

该引擎继承于 MergeTree，并在数据块合并算法中添加了折叠行的逻辑。

CollapsingMergeTree 会异步的删除（折叠）这些除了特定列 Sign 有 1 和 -1 的值以外，其余所有字段的值都相等的成对的行。没有成对的行会被保留。更多的细节请看本文的折叠部分。

因此，该引擎可以显著的降低存储量并提高 SELECT 查询效率。

创建表的语法：

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 是“状态”行，-1 是“取消”行。

列数据类型 — Int8。

折叠

数据

考虑你需要为某个对象保存不断变化的数据的情景。似乎为一个对象保存一行记录并在其发生任何变化时更新记录是合乎逻辑的，但是更新操作对 DBMS 来说是昂贵且缓慢的，因为它需要重写存储中的数据。如果你需要快速的写入数据，则更新操作是不可接受的，但是你可以按下面的描述顺序地更新一个对象的变化。

在写入行的时候使用特定的列 Sign。如果 Sign = 1 则表示这一行是对象的状态，我们称之为«状态»行。如果 Sign = -1 则表示是对具有相同属性的状态行的取消，我们称之为«取消»行。

例如，我们想要计算用户在某个站点访问的页面页面数以及他们在那里停留的时间。在某个时候，我们将用户的活动状态写入下面这样的行。

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

│ 4324182021466249494 │         5 │      146 │    1 │

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

一段时间后，我们写入下面的两行来记录用户活动的变化。

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

│ 4324182021466249494 │         5 │      146 │   -1 │

│ 4324182021466249494 │         6 │      185 │    1 │

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

第一行取消了这个对象（用户）的状态。它需要复制被取消的状态行的所有除了 Sign 的属性。

第二行包含了当前的状态。

因为我们只需要用户活动的最后状态，这些行。

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

│ 4324182021466249494 │         5 │      146 │    1 │

│ 4324182021466249494 │         5 │      146 │   -1 │

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

可以在折叠对象的失效（老的）状态的时候被删除。CollapsingMergeTree 会在合并数据片段的时候做这件事。

为什么我们每次改变需要 2 行可以阅读算法段。

这种方法的特殊属性：

1. 写入的程序应该记住对象的状态从而可以取消它。«取消»字符串应该是«状态»字符串的复制，除了相反的 Sign。它增加了存储的初始数据的大小，但使得写入数据更快速。
2. 由于写入的负载，列中长的增长阵列会降低引擎的效率。数据越简单，效率越高。
3. SELECT 的结果很大程度取决于对象变更历史的一致性。在准备插入数据时要准确。在不一致的数据中会得到不可预料的结果，例如，像会话深度这种非负指标的负值。

算法

当 ClickHouse 合并数据片段时，每组具有相同主键的连续行被减少到不超过两行，一行 Sign = 1（«状态»行），另一行 Sign = -1 （«取消»行），换句话说，数据项被折叠了。

对每个结果的数据部分 ClickHouse 保存：

1. 第一个«取消»和最后一个«状态»行，如果«状态»和«取消»行的数量匹配，且最后一个行是«状态»行

2. 最后一个«状态»行，如果«状态»行比«取消»行多一个或一个以上。

3. 第一个«取消»行，如果«取消»行比«状态»行多一个或一个以上。

4.在其他所有情况下，没有行。

 

同样，当“状态”行比“取消”行多至少2个，或者“状态”行比“取消”行多至少2个“取消”行时，合并继续，但是ClickHouse将此情况视为逻辑错误并将其记录在服务器日志。

如果多次插入同一数据，则会发生此错误。因此，折叠不应该改变统计数据的结果。

变化逐渐地被折叠，因此最终几乎每个对象都只剩下了最后的状态。

Sign 是必须的因为合并算法不保证所有有相同主键的行都会在同一个结果数据片段中，甚至是在同一台物理服务器上。ClickHouse 用多线程来处理 SELECT 请求，所以它不能预测结果中行的顺序。如果要从 CollapsingMergeTree 表中获取完全«折叠»后的数据，则需要聚合。

要完成折叠，请使用 GROUP BY 子句和用于处理符号的聚合函数编写请求。例如，要计算数量，使用 sum(Sign) 而不是 count()。要计算某物的总和，使用 sum(Sign * x) 而不是 sum(x)，并添加 HAVING sum(Sign) > 0 子句。

聚合体 count,sum 和 avg 可以用这种方式计算。如果一个对象至少有一个未被折叠的状态，则可以计算 uniq 聚合。min 和 max 聚合无法计算，因为 CollaspingMergeTree 不会保存折叠状态的值的历史记录。

如果你需要在不进行聚合的情况下获取数据（例如，要检查是否存在最新值与特定条件匹配的行），你可以在 FROM 从句中使用 FINAL 修饰符。这种方法显然是更低效的。

 

使用示例

建表：

CREATE TABLE UAct

(

    UserID UInt64,

    PageViews UInt8,

    Duration UInt8,

    Sign Int8

)

ENGINE = CollapsingMergeTree(Sign)

ORDER BY UserID;

插入数据:

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

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

我们使用两次 INSERT 请求来创建两个不同的数据片段。如果我们使用一个请求插入数据，ClickHouse 只会创建一个数据片段且不会执行任何合并操作。

获取数据：

SELECT * FROM Uact;

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

│ 4324182021466249494 │         5 │      146 │   -1 │

│ 4324182021466249494 │         6 │      185 │    1 │

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

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

│ 4324182021466249494 │         5 │      146 │    1 │

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

我们看到了什么，哪里有折叠？

通过两个 INSERT 请求，我们创建了两个数据片段。SELECT 请求在两个线程中被执行，我们得到了随机顺序的行。没有发生折叠是因为还没有合并数据片段。ClickHouse 在一个我们无法预料的未知时刻合并数据片段。

因此我们需要聚合：

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  │

如果我们不需要聚合并想要强制进行折叠，我们可以在 FROM 从句中使用 FINAL 修饰语。

SELECT * FROM UAct FINAL

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

│ 4324182021466249494 │         6 │      185 │    1 │

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

这种查询数据的方法是非常低效的。不要在大表中使用它。

如果想将一对sign 的值分别等于 1 和 -1，其他字段的值完全相同的数据抵消掉。

optimize table UAct final;

8.3 VersionedCollapsingMergeTree 

这个引擎：

• 允许快速写入不断变化的对象状态。
• 删除后台中的旧对象状态。 这显着降低了存储体积。

引擎继承自 MergeTree 并将折叠行的逻辑添加到合并数据部分的算法中。 VersionedCollapsingMergeTree 用于相同的目的 折叠树 但使用不同的折叠算法，允许以多个线程的任何顺序插入数据。 特别是， Version 列有助于正确折叠行，即使它们以错误的顺序插入。 相比之下, CollapsingMergeTree 只允许严格连续插入。

创建表：

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, version)

• sign — 指定行类型的列名: 1 是一个 “state” 行, -1 是一个 “cancel” 划列数据类型应为 Int8.
• version — 指定对象状态版本的列名。列数据类型应为 UInt*。

这个引擎和CollapsingMergeTree差不多，只是对CollapsingMergeTree引擎加了一个字段 version。

8.4 GraphiteMergeTree 

该引擎用来对 Graphite数据进行瘦身及汇总。对于想使用CH来存储Graphite数据的开发者来说可能有用。Graphite是一个开源的时序性数据库。

如果不需要对Graphite数据做汇总，那么可以使用任意的CH表引擎；但若需要，那就采用 GraphiteMergeTree 引擎。它能减少存储空间，同时能提高Graphite数据的查询效率。

该引擎继承自 MergeTree。

创建表：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]

(

    Path String,

    Time DateTime,

    Value <Numeric_type>,

    Version <Numeric_type>

    ...

) ENGINE = GraphiteMergeTree(config_section)

[PARTITION BY expr]

[ORDER BY expr]

[SAMPLE BY expr]

[SETTINGS name=value, ...]

 

含有Graphite数据集的表应该包含以下的数据列：

 - 指标名称(Graphite sensor)，数据类型：String

- 指标的时间度量，数据类型： DateTime

- 指标的值，数据类型：任意数值类型

- 指标的版本号，数据类型： 任意数值类型

CH以最大的版本号保存行记录，若版本号相同，保留最后写入的数据。

以上列必须设置在汇总参数配置中。

GraphiteMergeTree 参数
- config_section - 配置文件中标识汇总规则的节点名称

8.5 ReplacingMergeTree 

该引擎和 MergeTree 的不同之处在于它会删除排序键值相同的重复项。

数据的去重只会在数据合并期间进行。合并会在后台一个不确定的时间进行，因此你无法预先作出计划。有一些数据可能仍未被处理。尽管你可以调用 OPTIMIZE 语句发起计划外的合并，但请不要依靠它，因为 OPTIMIZE 语句会引发对数据的大量读写。

因此，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]

[SAMPLE BY expr]

[SETTINGS name=value, ...]

 

ReplacingMergeTree 的参数：

• ver — 版本列。类型为 UInt*，Date 或 DateTime。可选参数。

在数据合并的时候，ReplacingMergeTree 从所有具有相同排序键的行中选择一行留下：

• 如果 ver 列未指定，保留最后一条。
• 如果 ver 列已指定，保留 ver 值最大的版本。

使用示例

建表：

CREATE TABLE tb_rmt

(

    id UInt8,

    name String,

    version UInt8

)

ENGINE=ReplacingMergeTree(version)  ORDER BY (id,name);

插入数据：

insert into tb_rmt values (1,'Jason',1);

insert into tb_rmt values (1,'Jason',1);

insert into tb_rmt values (1,'Jason',2);

insert into tb_rmt values (2,'Tom',1);

insert into tb_rmt values (2,'Tom',1);

insert into tb_rmt values (2,'Tom',2);

 

获取数据：

select * from tb_rmt order by id,name,version;

┌─────id────┬────name───┬──version──┐

│      1     │    Jason   │       2    │

│      2     │     Tom    │       2    │

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

8.6 AggregatingMergeTree

该引擎继承自 MergeTree，并改变了数据片段的合并逻辑。 ClickHouse 会将一个数据片段内所有具有相同主键（准确的说是 排序键）的行替换成一行，这一行会存储一系列聚合函数的状态。

可以使用 AggregatingMergeTree 表来做增量数据的聚合统计，包括物化视图的数据聚合。

引擎使用以下类型来处理所有列：

• AggregateFunction
• SimpleAggregateFunction

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

使用方法：

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

以下通过示例进行介绍。

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

-- 建立明细表

CREATE TABLE visits

(

    UserID UInt64,

    CounterID UInt8,

    StartDate Date,

    Sign Int8

)

ENGINE = CollapsingMergeTree(Sign)

ORDER BY UserID;

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

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

CREATE MATERIALIZED VIEW test.basic

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

AS SELECT

    CounterID,

    StartDate,

    sumState(Sign)    AS Visits,

    uniqState(UserID) AS Users

FROM test.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使用。

-- 建立明细表

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;GROUP BY CounterID, StartDate;

8.7 SummingMergeTree

该引擎继承自 MergeTree。区别在于，当合并 SummingMergeTree 表的数据片段时，ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值。如果主键的组合方式使得单个键值对应于大量的行，则可以显著的减少存储空间并加快数据查询的速度。

我们推荐将该引擎和 MergeTree 一起使用。例如，在准备做报告的时候，将完整的数据存储在 MergeTree 表中，并且使用 SummingMergeTree 来存储聚合数据。这种方法可以使你避免因为使用不正确的主键组合方式而丢失有价值的数据。

建表：

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

。

SummingMergeTree 的参数

• columns - 包含了将要被汇总的列的列名的元组。可选参数。
  所选的列必须是数值类型，并且不可位于主键中。

如果没有指定 `columns`，ClickHouse 会把所有不在主键中的数值类型的列都进行汇总。

用法示例 

考虑如下的表：

CREATE TABLE summtt

(

    key UInt32,

    value UInt32

)

ENGINE = SummingMergeTree()

ORDER BY key

 

向其中插入数据：

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

ClickHouse可能不会完整的汇总所有行，因此我们在查询中使用了聚合函数 sum 和 GROUP BY 子句。

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

 

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

│   2 │          1 │

│   1 │          3 │

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

8.8 join引擎 

建表语句：

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 = Join(join_strictness, join_type, k1[, k2, ...])

 

引擎参数：

• join_strictness – JOIN 限制，有ANY，ALL，SEMI或ANTI.
• join_type – INNER, LEFT, RIGHT, FULL
• k1[, k2, ...] – 进行JOIN 操作时 USING语句用到的key列

使用join_strictness 和 join_type 参数时不需要用引号, 例如, Join(ANY, LEFT, col1). 这些参数必须和进行join操作的表相匹配。否则，CH不会报错，但是可能返回错误的数据。

表用法

示例：

|

创建左关联表:

CREATE TABLE id_val(`id` UInt32, `val` UInt32) ENGINE = TinyLog;

INSERT INTO id_val VALUES (1,11)(2,12)(3,13)

 

创建 Join 右边的表:

CREATE TABLE id_val_join(`id` UInt32, `val` UInt8) ENGINE = Join(ANY, LEFT, id);

INSERT INTO id_val_join VALUES (1,21)(1,22)(3,23)

 

表关联:

SELECT * FROM id_val ANY LEFT JOIN id_val_join USING (id) SETTINGS join_use_nulls = 1

┌─id─┬─val─┬─id_val_join.val─┐

│  1 │  11 │              21 │

│  2 │  12 │            ᴺᵁᴸᴸ │

│  3 │  13 │              23 │

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

8.9 Distributed

Distributed引擎本身不存储数据, 但可以在多个服务器上进行分布式查询。读是自动并行的。读取时，远程服务器表的索引（如果有的话）会被使用。

分布式引擎参数：

服务器配置文件中的集群名，

远程数据库名，

远程表名，

数据分片键（可选），

策略名称，它将用于存储临时文件以进行异步发送（可选）

例如：

Distributed(logs, default, hits[, sharding_key[, policy_name]])

将从集群logs中每个服务器上的default.hits表中读取集群中所有服务器上的数据。数据不仅在远程服务器上被读取，而且被部分处理（在可能的范围内）。例如，对于使用GROUP BY的查询，数据将在远程服务器上聚合，并且聚合函数的中间状态将发送到请求者服务器。然后将进一步汇总数据。

9.3 节有该引擎的使用案例。