04-ClickHouse 实践案例

1、枚举

CREATE  database testdb;
​
CREATE TABLE testdb.t_enum
(
x Enum8('hello' = 1, 'world' = 2)
)
ENGINE = TinyLog;

1） 这个 x 列只能存储类型定义中列出的值： ：'hello' 或'world'

正常：

INSERT INTO testdb.t_enum VALUES ('hello'), ('world'), ('hello');

异常：

insert into testdb.t_enum values('a');

2）看到对应行的数值，则必须将 Enum 值转换为整数类型

SELECT CAST(x, 'Int8') FROM testdb.t_enum;

使用场景：对一些状态、类型的字段算是一种空间优化，也算是一种数据约束。但是实 际使用中往往因为一些数据内容的变化增加一定的维护成本，甚至是数据丢失问题。所以谨 慎使用。

2、MergeTree

1） 建表语句

create table testdb.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);

2） 插入数据

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

3） 数据写入与分区合并

optimize table testdb.t_order_mt final;

2.1、二级索引

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

1） 老版本 使用二级索引前需要增加设置 是否允许使用实验性的二级索引（v20.1.2.4 开始，这个参数已被删除，默认开启） set allow_experimental_data_skipping_indices=1;

2） 创建 测试表

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);
​
其中 GRANULARITY N 是设定二级索引对于一级索引粒度的粒度。

3） 插入数据

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

4) 测试

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

2.2、数据TTL

TTL 即 Time To Live，MergeTree 提供了可以管理数据表或者列的生命周期的功能。

1 ）列级别 TTL

（1）创建测试表

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

（2）插入数据（注意：根据实际时间改变）

​
insert into t_order_mt3 values
(106,'sku_001',1000.00,'2020-06-12 22:52:30'),
(107,'sku_002',2000.00,'2020-06-12 22:52:30'),
(110,'sku_003',600.00,'2020-06-13 12:00:00');
​

(3）手动合并，查看效果 到期后，指定的字段数据归 0

2）表级 TTL

下面的这条语句是数据会在 create_time 之后 10 秒丢失

alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;

3、ReplacingMergeTree

存储特性完全继承 MergeTree，只是 多了一个去重的功能。尽管 MergeTree 可以设置主键，但是 primary key 其实没有唯一约束 的功能。如果你想处理掉重复的数据，可以借助这个 ReplacingMergeTree。

1 ） 案例演示

（1）创建表

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);
​
ReplacingMergeTree() 填入的参数为版本字段，重复数据保留版本字段值最大的。
如果不填版本字段，默认按照插入顺序保留最后一条。

（2）向表中插入数据

​
insert into t_order_rmt values
(101,'sku_001',1000.00,'2020-06-01 12: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',2000.00,'2020-06-01 11:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');
​

（3）执行第一次查询

 select * from t_order_rmt;

（4）手动合并

OPTIMIZE TABLE t_order_rmt FINAL;

（5）再执行一次查询

select * from t_order_rmt;

2） 结论

➢ 实际上是使用 order by 字段作为唯一键

➢ 去重不能跨分区

➢ 只有同一批插入（新版本）或合并分区时才会进行去重

➢ 认定重复的数据保留，版本字段值最大的

➢ 如果版本字段相同则按插入顺序保留最后一笔

4、SummingMergeTree

以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree 的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大。 ClickHouse 为了这种场景，提供了一种能够“预聚合”的引擎 SummingMergeTree。

1） 案例演示

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

（4）手动合并

OPTIMIZE TABLE t_order_smt FINAL;

（5）再执行一次查询

 select * from t_order_smt;

2 ） 结论

➢ 以 SummingMergeTree（）中指定的列作为汇总数据列

➢ 可以填写多列，必须数字列，如果不填，以所有非维度列且为数字列的字段为汇总数 据列

➢ 以 order by 的列为准，作为维度列

➢ 其他的列按插入顺序保留第一行 ➢ 不在一个分区的数据不会被聚合

➢ 只有在同一批次插入(新版本)或分片合并时才会进行聚合

3 ） 建议

设计聚合表的话，唯一键值、流水号可以去掉，所有字段全部是维度、度量或者时间戳

5、DML

（1）删除操作

alter table t_order_smt delete where sku_id ='sku_001';

（2）修改操作

alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id
=102;

（3）查询操作

ClickHouse 基本上与标准 SQL 差别不大

➢ 支持子查询

➢ 支持 CTE(Common Table Expression 公用表表达式 with 子句)

➢ 支持各种 JOIN， 但是 JOIN 操作无法使用缓存，所以即使是两次相同的 JOIN 语句， ClickHouse 也会视为两条新 SQL ➢ 窗口函数(官方正在测试中...)

➢ 不支持自定义函数

➢ GROUP BY 操作增加了 with rollup\with cube\with total 用来计算小计和总计。

（1）

 alter table t_order_mt delete where 1=1;
insert into t_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(101,'sku_002',2000.00,'2020-06-01 12:00:00'),
(103,'sku_004',2500.00,'2020-06-01 12:00:00'),
(104,'sku_002',2000.00,'2020-06-01 12:00:00'),
(105,'sku_003',600.00,'2020-06-02 12:00:00'),
(106,'sku_001',1000.00,'2020-06-04 12:00:00'),
(107,'sku_002',2000.00,'2020-06-04 12:00:00'),
(108,'sku_004',2500.00,'2020-06-04 12:00:00'),
(109,'sku_002',2000.00,'2020-06-04 12:00:00'),
(110,'sku_003',600.00,'2020-06-01 12:00:00');

（2）with rollup：

从右至左去掉维度进行小计

select id , sku_id,sum(total_amount) from t_order_mt group by
id,sku_id with rollup;

（3）with cube : 从右至左去掉维度进行小计，再从左至右去掉维度进行小计

 select id , sku_id,sum(total_amount) from t_order_mt group by
id,sku_id with cube;

（4）with totals: 只计算合计

select id , sku_id,sum(total_amount) from t_order_mt group by
id,sku_id with totals;

6、DDL

同 MySQL 的修改字段基本一致

1） 新增字段

alter table tableName add column newcolname String after col1;

2）修改字段类型

alter table tableName modify column newcolname String;

3） 删除字段

alter table tableName drop column newcolname;

4）导出数据

clickhouse-client --query "select * from t_order_mt where
create_time='2020-06-01 12:00:00'" --format CSVWithNames>
/opt/module/data/rs1.csv

7、王炸-MaterializeMySQL

需要先有mysql环境，快速安装方法可以参考本人另一篇博客：https://blog.csdn.net/q287573145/article/details/121651937

MySQL 的用户群体很大，为了能够增强数据的实时性，很多解决方案会利用 binlog 将 数据写入到 ClickHouse。为了能够监听 binlog 事件，我们需要用到类似 canal 这样的第三 方中间件，这无疑增加了系统的复杂度。

ClickHouse 20.8.2.3 版本新增加了 MaterializeMySQL 的 database 引擎，该 database 能 映 射 到 MySQL 中 的 某 个 database ， 并 自 动 在 ClickHouse 中 创 建 对 应 的 ReplacingMergeTree。ClickHouse 服务做为 MySQL 副本，读取 Binlog 并执行 DDL 和 DML 请 求，实现了基于 MySQL Binlog 机制的业务数据库实时同步功能。

7.1 特点

（1）MaterializeMySQL 同时支持 全量和 增量同步，在 database 创建之初会全量同步 MySQL 中的表和数据，之后则会通过 binlog 进行增量同步。

（2）MaterializeMySQL database 为其所创建的每张 ReplacingMergeTree 自动增加了 _sign 和 _version 字段。 其中， _version 用作 ReplacingMergeTree 的 ver 版本参数，每当监听到 insert、update 和 delete 事件时，在 databse 内全局自增。而 _sign 则用于标记是否被删除，取值 1 或 者 -1。

（3）目前 MaterializeMySQL 支持如下几种 binlog 事件:

➢ MYSQL_WRITE_ROWS_EVENT: sign = 1，version ++

➢ MYSQL_DELETE_ROWS_EVENT: sign = -1，version ++

➢ MYSQL_UPDATE_ROWS_EVENT: 新数据 _sign = 1

➢ MYSQL_QUERY_EVENT: 支持 CREATE TABLE 、DROP TABLE 、RENAME TABLE 等。

7.2 使用细则

（1）DDL 查询

MySQL DDL 查询被转换成相应的 ClickHouse DDL 查询（ALTER, CREATE, DROP, RENAME）。 如果 ClickHouse 不能解析某些 DDL 查询，该查询将被忽略。

（2）数据复制 MaterializeMySQL 不支持直接插入、删除和更新查询，而是将 DDL 语句进行相应转换：

MySQL INSERT 查询被转换为 INSERT with _sign=1。

MySQL DELETE 查询被转换为 INSERT with _sign=-1。

MySQL UPDATE 查询被转换成 INSERT with _sign=1 和 INSERT with _sign=-1。

（3 ）SELECT 查询 如果在 SELECT 查询中没有指定version，则使用 FINAL 修饰符，返回version 的最大值 对应的数据，即最新版本的数据。 如果在 SELECT 查询中没有指定sign，则默认使用 WHERE _sign=1，即返回未删除状态 (sign=1)的数据。

（4） 索引转换 ClickHouse 数据库表会自动将 MySQL 主键和索引子句转换为 ORDER BY 元组。

7.3 案例演示

7.3.1 MySQL 开启 binlog 和 和 GTID 模式

（1） 确保 MySQL 开启了 binlog 功能，且格式为 ROW 打开/etc/my.cnf,

在[mysqld]下添加：

server-id=1
log-bin=mysql-bin
binlog_format=ROW

（2）开启 GTID 模式

如果如果 clickhouse 使用的是 20.8 prestable 之后发布的版本，那么 MySQL 还需要配置 开启 GTID 模式, 这种方式在 mysql 主从模式下可以确保数据同步的一致性(主从切换时)。

# 设置为主从强一致性

gtid-mode=on
enforce-gtid-consistency=1

#记录日志GTID 是 MySQL 复制增强版

log-slave-updates=1 

从 MySQL 5.6 版本开始支持，目前已经是 MySQL 主流 复制模式。它为每个 event 分配一个全局唯一 ID 和序号，我们可以不用关心 MySQL 集群 主从拓扑结构，直接告知 MySQL 这个 GTID 即可。

（3）重启 MySQL

sudo systemctl restart mysqld

（4） 在 MySQL 中创建数据表并写入数据

CREATE DATABASE testck;
CREATE TABLE `testck`.`t_organization` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` int NOT NULL,
`name` text DEFAULT NULL,
`updatetime` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY (`code`)
) ENGINE=InnoDB;
INSERT INTO testck.t_organization (code, name,updatetime)
VALUES(1000,'Realinsight',NOW());
INSERT INTO testck.t_organization (code, name,updatetime)
VALUES(1001, 'Realindex',NOW());
INSERT INTO testck.t_organization (code, name,updatetime)
VALUES(1002,'EDT',NOW());

CREATE TABLE `testck`.`t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` int,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO testck.t_user (code) VALUES(1);

7.4 开启 ClickHouse 物化引擎

clickhouse-client  --multiline --password  你的密码

set allow_experimental_database_materialize_mysql=1;

7.5 创建复制管道

（1）ClickHouse 中创建 MaterializeMySQL 数据库

CREATE DATABASE test_binlog ENGINE = MaterializeMySQL('10.206.231.143:3306','testck','root','Sf@12345678');

其中 4 个参数分别是 MySQL 地址、databse、username 和 password。

（2）查看 ClickHouse 的数据
use test_binlog;
show tables;
select * from t_organization;
select * from t_user;

7.5 数据测试

7.5.1 修改数据

（1）在 MySQL 中修改数据:

update t_organization set name = CONCAT(name,'-v1') where id = 1

（2）查看 clickhouse 日志可以看到 binlog 监听事件，

查询 clickhouse select * from t_organization;

7.5.2 删除数据

（1）MySQL 删除数据:

DELETE FROM t_organization where id = 2;

（2）ClicKHouse，日志有 DeleteRows 的 binlog 监听事件，

查看数据：
select * from t_organization;

（3）在刚才的查询中增加 sign 和 _version 虚拟字段

select *,_sign,_version from t_organization order by _sign
desc,_version desc;

在查询时，对于已经被删除的数据，_sign=-1，ClickHouse 会自动重写 SQL，将 _sign = -1 的数据过滤掉;

对于修改的数据，则自动重写 SQL，为其增加 FINAL 修饰符。 select * from t_organization 等同于 select * from t_organization final where _sign = 1

7.5.3 删除表

（1）在 mysql 执行删除表 drop table t_user;

（2）此时在 clickhouse 处会同步删除对应表，如果查询会报错

show tables;
select * from t_user;

DB::Exception: Table scene_mms.scene doesn't exist..

（3）mysql 新建表，clickhouse 可以查询到

CREATE TABLE `testck`.`t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` int,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO testck.t_user (code) VALUES(1);

#ClickHouse 查询

show tables;

select * from t_user;