clickhouse使用MySQL表引擎 clickhouse join表引擎

文章目录

• 前言
• 总览
• MergeTree Engine Family

• MergeTree
• ReplacingMergeTree
• CollapsingMergeTree
• VersionedCollapsingMergeTree
• AggregatingMergeTree
• 组合

• MySQL引擎
• JDBC引擎
• Buffer引擎
• 待更新

前言

在研究表引擎如此多天，终于要开始写也是ClickHouse最为核心重要的表引擎了，ClickHouse之所以被誉为手动挡的赛车，原因之一就是丰富多样的表引擎决定着ClickHouse使用的效率，所以在使用ClickHouse一定要研究掌握ClickHouse的所有表引擎，其中最为核心重点关注的当属MergeTree表引擎家族了。

总览

再一一简绍ClickHouse的表引擎先我们先总体来看看ClickHouse的表引擎

看到这么多表引擎是不是瞬间想放弃(其中MergeTree家族的表引擎在上面是没有完成列出的)，引擎越多意味着我们的选择越多，也就越证明了ClickHouse的强大，下面的讲为大家介绍生产或应用场景相对广泛的表引擎。

MergeTree Engine Family

MergeTree Family 是ClickHouse最为健壮的表引擎，生产大多会选用这个家族里面的某个表引擎其中最为常用的是Replicated MergeTree。

MergeTree是整个家族中最基础的表引擎，提供了主键索引、数据分区、数据副本和数据采样等基本能力。而其他引擎与之相组合又会提供新的能力，组合关系如下图

MergeTree

由于MergeTree是整个家族最为基础的表引擎，所以我们这里会重点对MergeTree进行分析
首选我们来看看MergeTree创建的语法

create table if not exists test_merge_tree
(
    name text,
    age Int8
) engine = MergeTree()
partition by name 
order by name
primary key name
sample by name
settings index_granularity = 8192;

• PARTITION BY [选填]：分区键，用于指定表数据以何种标准进行分区。分区键既可以是单个列字段，也可以通过元组的形式使用多个列字段，同时它也支持使用列表达式。如果不声明分区键，则ClickHouse会生成一个名为all的分区。合理使用数据分区，可以有效减少查询时数据文件的扫描范围
• [必填]：排序键，用于指定在一个数据片段内，数据以何种标准排序。默认情况下主键（PRIMARY KEY）与排序键相同。排序键既可以是单个列字段，例如ORDER BY name ，也可以通过元组的形式使用多个列字段，例如ORDER BY（name , age ）。当使用多个列字段排序时，以ORDER BY（name , age ）为例，在单个数据片段内，数据首先会以name 排序，相同age 的数据再按EventDate排序
• PRIMARY KEY [选填]：主键，顾名思义，声明后会依照主键字段生成一级索引，用于加速表查询。默认情况下，主键与排序键(ORDER BY)相同，所以通常直接使用ORDER BY代为指定主键，无须刻意通过PRIMARY KEY声明。所以在一般情况下，在单个数据片段内，数据与一级索引以相同的规则升序排列。与其他数据库不同，MergeTree主键允许存在重复数据（ReplacingMergeTree可以去重）
• SAMPLE BY [选填]：抽样表达式，用于声明数据以何种标准进行采样。如果使用了此配置项，那么在主键的配置中也需要声明同样的表达式，例如：

省略...
)ENGINE = MergeTree()
ORDER BY (name, age, intHash32(name))
SAMPLE BY intHash32(name)

抽样表达式需要配合SAMPLE子查询使用(SAMPLE暂时未用到，不做深入展开感兴趣可以自己研究)

• SETTINGS: index_granularity [选填]:index_granularity对于MergeTree而言是一项非常重要的参数，它表示索引的粒度，默认值为8192。也就是说，MergeTree的索引在默认情况下，每间隔8192行数据才生成一条索引。其索引为稀疏索引(因篇幅原因索引原理不在这里做具体展开)。

因为 MergeTree主键不具备去重能力，所以就有了ReplacingMergeTree

ReplacingMergeTree

ReplacingMergeTree可以看出是对MergeTree的组合，所以也就继承了MergeTree的所有功能，并同时拥有了主键去重的能力，但是值得注意的是ReplacingMergeTree的去重有如下特点

1. 去重不是按主键去重，而是按ORDER BY字段
2. 只能相同分区才能去重，不同分区无法去重
3. 去重只有在合并分区才会触发
4. 去重策略有两种，如果没有定义版本号字段按版本号保留重复最后列，定义了则保留版本号字段值最大的一行
   使用语法

create table order_detail
(
id String,
version DateTime
)ENGINE = ReplacingMergeTree(version)
PARTITION BY toYYYYMM(version)
ORDER BY id

所以在查询的时候仍然无法保证取出重复数据

CollapsingMergeTree

为了解决ReplacingMergeTree重复数据的问题又有了CollapsingMergeTree，而CollapsingMergeTree也支持行级别的删除，实际在OLAP中删除的代价是非常昂贵的，所以ClickHouse使用了增加代替删除，我们来看看CollapsingMergeTree是如何做到的
CollapsingMergeTree 在定义数据添加了字段 sign标识位，-1表示无效要被删除的数据，1表示有效数据,实际在删除的时候也是数据合并将sign为1和sign为-1折叠合并，保留sign为1的数据
使用语法

create table order_detail
(
id String,
sign Int8,
create_time DateTime
)ENGINE = CollapsingMergeTree(sign)
PARTITION BY toYYYYMM(create_time)
order by id

CollapsingMergeTree 在折叠数据有如下规定

1. 如果sign=1比sign=-1的数据多一行，则保留最后一行sign=1的数据
2. 如果sign=-1比sign=1的数据多一行，则保留第一行sign=-1的数据
3. 如果sign=1和sign=-1的数据行一样多，并且最后一行是sign=1，则保留第一行sign=-1和最后一行sign=1的数据
4. 如果sign=1和sign=-1的数据行一样多，并且最后一行是sign=-1，则什么也不保留
5. 其余情况，ClickHouse会打印警告日志，但不会报错，在这种情形下，查询结果不可预知

注意事项：

1. 折叠数据不是实时触发的，和其他MergeTree变种表引擎一样，只有在合并分区才会触发，所以分区合并前还是会看到旧数据
2. 查询前手动执行optimize TABLE table_name FINAL强制合并分区效率极为低下，生产最好禁用
3. 虽然普通查询会看到旧数据，但我们可以通过改写我们的SQL来实现去重
   改写示例如下：

-- 原始SQL
SELECT
    UserID,
    sum(pay) ,
    sum(sales)
FROM order_detail
GROUP BY order_id
-- 改写SQL
SELECT
    UserID,
    sum(pay) ,
    sum(sales)
FROM order_detail
GROUP BY order_id
HAVING sum(Sign) > 0;

4. CollapsingMergeTree 折叠要求sign=1和sign=-1数据必须相邻，分区内的数据是基于order by排序的，所以要实现sign=1和sing=-1的数据相邻只能依靠严格的写入顺序，如果是单线程程序是很容易控制写入顺序。但如果是多线程就无法保证了

为了解决多线程写入的问题有引入了VersionedCollapsingMergeTree表引擎

VersionedCollapsingMergeTree

VersionedCollapsingMergeTree和CollapsingMergeTree完全相同，并且任意顺序的数据都能完成折叠
实现方式为多引入一个版本号字段，VersionedCollapsingMergeTree在排序时会默认加上版本号字段进行排序从而保证数据的顺序性

• 语法

create table order_detail
(
id String,
create_time DateTime,
sing Int8,
ver UInt8
)
ENGINE = VersionedCollapsingMergeTree(sign, ver)
PARTITION BY toYYYYMM(create_time)
ORDER BY id

AggregatingMergeTree

如果做过一些统计数据的都知道有时候会以一些底表为基础统计一些其他的表出来，从而减少数据量，采用空间换时间达到加速查询的目的，而AggregatingMergeTree就是如此
AggregatingMergeTree的定义语法

-- 首选创建底表
create table test_order
(
    id String,
    sales Int32,
    month Int32
)ENGINE = MergeTree()
primary key id
order by (id,month);

--  创建物化视图
create materialized view test_order_month
ENGINE = AggregatingMergeTree()
partition by id
order by (id,month)
as select id,month,sumState(sales) as sales from test_order group by id,month;

-- 插入数据
insert into test_order values ('1',200, 201901);
insert into test_order values ('1',300, 201901);
insert into test_order values ('2',100, 201901);
insert into test_order values ('2',200, 201901);

-- 查询
select id,sumMerge(sales) from test_order_month group by id;

需要注意的

1. 如果 test_order 在之前有数据，然后再创建物化视图test_order_month，test_order_month是不会包含之前的数据的，必须是创建了物化视图然后插入的数据才能被同步
3. 直接查询物化司视图的数据会是乱码的，需要调用相应的*Merge函数,因为AggregateFunction类型的字段使用二进制存储



4. 数据合并也只是合并分区的时候才会触发聚合计算的逻辑，测试可手动强制merge查看效果
   语法：

optimize table test_order_month;

4. 在聚合数据时，同一分区内，相同聚合Key的多行数据会合并成一行。对于那些非主键、非AggregateFunction类型字段，则会使用第一行数据的取值

组合

实际在生产上相关的MergeTree引擎都会和Replicated组合，因为只有组合了Replicated才具有副本能力。比如我们要使用
VersionedCollapsingMergeTree，实际可以使用组合的ReplicatedVersionedCollapsingMergeTree
开始就给出了MergeTree家族的组合关系，这里就不一一介绍了

MySQL引擎

MySQL表引擎可以与远程MySQL数据库中的数据表建立映射，并通过SQL向其发起远程查询，包括SELECT和INSERT
语法：

CREATE TABLE order_detail
(
id UInt32,
name String
)ENGINE = MySQL('host:port','database','table','user','password','replace_query','on_duplicate_clause')

参数说明：

• replace_query默认为0，对应MySQL的REPLACE INTO语法。如果将它设置为1，则会用REPLACE INTO代替INSERT INTO
• on_duplicate_clause默认为0，对应MySQL的ON DUPLICATE KEY语法。如果需要使用该设置，则必须将replace_query设置成0

JDBC引擎

再我们需要映射连接其他远程数据的时候可以使用JDBC引擎，因为我们的数据库不一定都是MySql，也可能是PostgreSql
，不过JDBC引擎需要我们单独运行一个服务jdbc-bridge,才能使用。
具体配置信息可自行官网查看，用法和MySql引擎类似

Buffer引擎

Buffer表引擎完全使用内存装载数据，不支持文件的持久化存储，所以当服务重启之后，表内的数据会被清空。Buffer表引擎不是为了面向查询场景而设计的，它的作用是充当缓冲区的角色。假设有这样一种场景，我们需要将数据写入目标MergeTree表A，由于写入的并发数很高，这可能会导致MergeTree表A的合并速度慢于写入速度（因为每一次INSERT都会生成一个新的分区目录）。此时，可以引入Buffer表来缓解这类问题，将Buffer表作为数据写入的缓冲区。数据首先被写入Buffer表，当满足预设条件时，Buffer表会自动将数据刷新到目标表。所以Buffer就是为了解决频繁写入导致频繁Merge带来的性能问题
语法：

ENGINE = Buffer(database, table, num_layers,  min_time, max_time, min_rows, max_rows, min_bytes, max_bytes)

参数解释:

• database：目标表的数据库。
• table：目标表的名称，Buffer表内的数据会自动刷新到目标表
• num_layers：可以理解成线程数，Buffer表会按照num_layers的数量开启线程，以并行的方式将数据刷新到目标表，官方建议设为16。
• min_time和max_time：时间条件的最小和最大值，单位为秒，从第一次向表内写入数据的时候开始计算
• min_rows和max_rows：数据行条件的最小和最大值；
• min_bytes和max_bytes：数据体量条件的最小和最大值，单位为字节。
  Buffer表并不是实时刷新数据的，只有在阈值条件满足时它才会刷新。阈值条件由三组最小和最大值组成。接下来说明三组极值条件参数的具体含义：

1. 如果三组条件中所有的最小阈值都已满足，则触发刷新动作；
2. 如果三组条件中至少有一个最大阈值条件满足，则触发刷新动作；
3. 如果写入的一批数据的数据行大于max_rows，或者数据体量大于max_bytes，则数据直接被写入目标表

待更新