clickhouse5:表引擎-Megree

       clickhouse在建表时必须显示指定该表使用的引擎，以及引擎使用的相关参数。引擎的名称大小写敏感。表引擎决定了如何存储表的数据，包括：

• 数据的存储方式和位置，写到哪里以及从哪里读取数据
• 支持哪些查询以及如何支持
• 并发数据访问
• 索引的使用(如果存在)
• 是否可以执行多线程请求
• 数据复制参数

1.MergeTree

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

- MergeTree
- ReplacingMergeTree
- SummingMergeTree
- AggregatingMergeTree
- CollapsingMergeTree
- VersionedCollapsingMergeTree
- GraphiteMergeTree

1.1MergeTree

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

主要特点：

• 存储的数据按主键排序。使用户能够创建一个小型的稀疏索引来加快数据检索

• 若指定分区键的话，可以使用分区。在相同数据集和相同结果集的情况下clickhouse中某些带分区的操作比普通操作更快，查询中指定了分区键时clickhouse会自动截取分区数据，增加了查询性能。

• 支持数据副本

• 支持数据采样

建表语句

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');

1.1.2partition by   分区(可选)

1.  作用：降低扫描的范围，优化查询速度
2. 若不填：只会使用一个分区
3. 分区目录：MergeTree以列文件+索引文件+表定义文件组成的，但如果设定了分区这些文件都会保存到不同的分区目录中
4. 并行：分区后，设计跨分区的查询统计，clickhouse会以分区为单位并行处理
5. 数据写入与分区合并：任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后的某个时刻，clickhouse会自动执行合并操作(也可以手动通过optimizi执行)，把临时分区的数据，合并到已有分区中。

optimize table table_name final;

手动合并分区示例：

再次执行插入操作

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');

查询后结果如下，并没有纳入任何分区

手动合并

optimize table t_order_mt final;

再次查询

 

 1.1.2primary key 主键(可选)

       clickhouse中的主键，和其他数据库不太一样，它只提供了数据的一级索引，却不是唯一约束，这意味着是可以存在相同的primary key的数据的。主键的设定主要依据是查询语句中的where条件，根据条件通过对主键进行某种形式的二分查找，能够定位到对应的index granularity，避免了全表扫描。

      primary key若没有显示指定主键，clickhouse会使用排序键作为主键。

      index gramularity：索引粒度，指在稀疏索引中2个相邻索引对应数据的间隔。clickhouse中的mergetree默认是8192。官方不建议修改这个值，除非该列存在大量重复值，如在一个分区几万行才有一个不同数据。

      稀疏索引：

       稀疏索引的好处是可以用很少的索引数据，定位更多的数据，代价就是只能定位到索引粒度的第一行，然后再进行一点扫描。 

1.1.3 order by 排序(必选)

      order by设定了分区内的数据按照哪些字段顺序进行有序保存。

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

      主键必选是order by字段的前缀字段。如order by 字段是(id,sku_id),主键必须是id，或(id, sku_id)。

1.1.5二级索引

       目前在clickhouse的官网上二级索引的功能在v20.1.2.4之前被标注为实验性的，在整个版本之后默认是开启的。

建表

create table t_order_mt2(
   id UInt32,
sku_id String,
total_amount Decimal(16,2), create_time Datetime,
INDEX a total_amount TYPE minmax GRANULARITY 5 
) engine =MergeTree
  partition by toYYYYMMDD(create_time)
  primary key (id)
  order by (id, sku_id);

插入数据

insert into t_order_mt2 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');

查询---查看二级索引对非主键索引查询时起的作用

clickhouse-client --database=learnck --send_logs_level=trace<<<'select * from t_order_mt2 where total_amount > toDecimal32(900., 2)';

 1.16数据TTL

        TTL即Time To Live,MergeTree提供了可以管理数据表或列的生命周期的功能。TTL表示数据的存活时间，时间到达时，若列字段级别的TTL则会删除这一列的数据；若表级别的TTL则会删除整张表的数据；若同时设置了列级别和表级别的TTL则以先到期的为准。

       无论列级别还是表级别的TTL，都需要依托某个Datetime或Date类型的字段，通过对这个时间字段的INTERAL操作来表述TTL的过期时间

1）列级别TTL

 第一步：建表

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-08-13 17:56:10'), (107,'sku_002',2000.00,'2021-08-13 17:56:20'),
(110,'sku_003',600.00,'2021-08-13 17:56:30');

第三步：手动合并分区

optimize table t_order_mt3 final;

第四步：查询--到期后total_amount字段值为0

 select * from t_order_mt3;

2）表级TTL

      使用下述语句，整张表的数据会在create_time之后10秒消失

alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;

INTERVAL后能够使用的时间周期有：

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

2.ReplaceingMergeTree

       replaceingMergeTree是MergeTree的一个变种，它存储特性完全继承MergeTree,只是多了一个去重的功能。MergeTree可以设置主键，但是primary key并没有唯一约束的功能。如果处理重复的数据，可以使用ReplaceingMergeTree。

 1）去重时机

        数据的去重只会在合并的时候发生。合并会在位置的时间在后台进行，用户无法预先做出计划。尽管可以通过optimize语句手动合并，但尽量少用，因为optimize语句会引起对数据的大量读写。

2）去重范围

     如果表经过了分区，去重只会在分区内部进行，不能跨区进行去重。所以replaceingMergeTree能力有限，适用于在后台清楚重复的数据以节省空间，但是不能保证没有重复的数据出现。

3）示例

建表

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');

查询

select *from t_order_rmt;

 结论：

• 去重使用order by字段作为唯一键
• 去重不能跨分区
• 只有同一批插入或合并分区时才会进行去重
• 认定重复的数据保留版本字段值最大的
• 如果版本字段相同则按插入顺序保留最后一笔
• ReplaceingMergeTree()填入的参数为版本字段，重复数据保留版本字段值最大的。如果不填版本字段，默认按照插入顺序保留最后一条。

3.SummingMergeTree

      对于不查询明细，只关心维度进行汇总聚合结果的场景，若使用普通的MergeTree,无论是存储空间的开销，还是查询时临时聚合的开销都很大。clickhouse提供了一种能够”预聚合“的引擎

示例如下：

1）建表

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 );

2）插入数据

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');

3）查询

select * from t_order_smt;

结论：

• 以SummingMergeTree()中指定的列作为汇总数据列
• 可以填写多列，必须数字列，如果不填，以所有非维度列且未数字列的字段为汇总数据列
• 以order by的列为准，作为维度列
• 其他的列按插入顺序保留第一行
• 不在一个分区的数据不会被聚合
• 只有在同一批次插入或分片合并时才会进行聚合
• 设计聚合时，唯一键值、流水号可以去掉，所有字段全部是维度、度量或者时间戳