ClickHouse学习(三)表引擎

---

表引擎的作用

表引擎（即表的类型）决定了：

1. 决定表存储在哪里以及以何种方式存储
2. 支持哪些查询以及如何支持
3. 并发数据访问
4. 索引的使用
5. 是否可以执行多线程请求
6. 数据复制参数

引擎类型

MergeTree

适用于高负载任务的最通用和功能最强大的表引擎。这些引擎的共同特点是可以快速插入数据并进行后续的后台数据处理。 MergeTree系列引擎支持数据复制（使用Replicated* 的引擎版本），分区和一些其他引擎不支持的其他功能。
该类型的引擎：

• MergeTree
• ReplacingMergeTree
• SummingMergeTree
• AggregatingMergeTree
• CollapsingMergeTree
• VersionedCollapsingMergeTree
• GraphiteMergeTree

日志

具有最小功能的轻量级引擎。当您需要快速写入许多小表（最多约100万行）并在以后整体读取它们时，该类型的引擎是最有效的。
该类型的引擎：

• TinyLog
• StripeLog
• Log

集成引擎

用于与其他的数据存储与处理系统集成的引擎。
该类型的引擎：

• Kafka
• MySQL
• ODBC
• JDBC
• HDFS

这里挑几个常用的进行讲解,需要了解其他的官方文档讲的很详细https://clickhouse.tech/docs/zh/engines/table-engines/

MergeTree

Clickhouse 中最强大的表引擎当属 MergeTree （合并树）引擎及该系列（*MergeTree）中的其他引擎,支持索引和分区。

测试数据:
create table t_order_mt(
 id UInt32,
 sku_id String,
 total_amount Decimal(16,2),
 create_time Datetime
) engine =MergeTree
 partition by toYYYYMMDD(create_time)
 primary key (id)
 order by (id,sku_id);

insert into t_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');

partition by 分区(可选)

• 分区是在建表时通过PARTITION BY expr 子句指定的。分区键可以是表中列的任意表达式
• 分区的目的主要是降低扫描的范围，优化查询速度
• 分区数据所在的路径/var/lib/clickhouse/data/default/表名
• 若不指定分区只会产生一个分区

按日期产生两个分区

在分区路径下产生两个分区文件

再次插入新的数据信息,会产生新的分区,目前还没合并

任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入
后的某个时刻（大概 10-15 分钟后），ClickHouse 会自动执行合并操作

将分区进行合并optimize table t_order_mt partition '20200601' final;


第一个分区与第三个分区进行合并,框起来的代表合并的次数

primary key 主键(可选)

提供了数据的一级索引，但是却不是唯一约束。这就意味着是可以存在相同 primary key 的数据的。

其排序方式是利用了稀疏索引,根据条件通过对主键进行某种形式的二分查找避免了全表扫描。

order by（必选）

order by 是 MergeTree 中唯一一个必填项，甚至比 primary key 还重要，因为当用户不设置主键的情况，很多处理会依照 order by 的字段进行处理

主键必须是 order by 字段的前缀字段。

跳数(二级)索引

跳数索引是指数据片段按照粒度(建表时指定的index_granularity)分割成小块后，将上述SQL granularity_value数量的小块组合成一个大的块，对这些大块写入索引信息，这样有助于使用where筛选时跳过大量不必要的数据，减少SELECT需要读取的数据量。

语法: INDEX index_name expr TYPE type(...) GRANULARITY granularity_value
案例: INDEX a total_amount TYPE minmax GRANULARITY 5

• minmax

存储指定表达式的极值（如果表达式是 tuple ，则存储 tuple 中每个元素的极值），这些信息用于跳过数据块，类似主键。

生命周期

MergeTree 提供了可以管理数据表或者列的生命周期的功能
涉及判断的字段必须是 Date 或者 Datetime 类型，推荐使用分区的日期字段

其属性可为:

• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR

列的生命周期

给列的加上生命周期到时间后会自动清除数据

create table t_order_mt3(
 id UInt32,
 sku_id String,
 total_amount Decimal(16,2) TTL create_time+interval 10 SECOND,
 create_time Datetime 
) engine =MergeTree
partition by toYYYYMMDD(create_time)
 primary key (id)
 order by (id, sku_id);

insert into t_order_mt3 values
(106,'sku_001',1000.00,'2021-07-30 11:07:30'),
(107,'sku_002',2000.00,'2021-07-30 11:08:30'),
(110,'sku_003',600.00,'2021-07-30 11:08:40');

表的生命周期

数据会在 create_time 之后 10 秒丢失

alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;

ReplacingMergeTree

• 该引擎和 MergeTree 的不同之处在于它会删除排序键值相同的重复项。
• 数据的去重只会在数据合并期间进行。合并会在后台一个不确定的时间进行，适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。
• 去重只会在分区内部进行去重，不能执行跨分区的去重。

create table t_order_rmt(
 id UInt32,
 sku_id String,
 total_amount Decimal(16,2) ,
 create_time Datetime 
) engine =ReplacingMergeTree(create_time)
 partition by toYYYYMMDD(create_time)
 primary key (id)
 order by (id, sku_id);
 
insert into t_order_rmt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');

保存的数据为版本字段值最大的,如果版本字段相同则按插入顺序保留最后一笔。

SummingMergeTree

当合并 SummingMergeTree 表的数据片段时，ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值。

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

create table t_order_smt(
 id UInt32,
 sku_id String,
 total_amount Decimal(16,2) ,
 create_time Datetime 
) engine =SummingMergeTree(total_amount)
 partition by toYYYYMMDD(create_time)
 primary key (id)
 order by (id,sku_id );


insert into t_order_smt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');

结论:

• 不再一个分区里的数据不会聚合
• 可以填写多列必须数字列，如果不填，以所有非维度列且为数字列的字段为汇总数据列
• 其他的列按插入顺序保留第一行