Clickhouse数据一致性和物化视图

一、数据一致性的保证

Clickhouse在数据一致性上没有很好的表现，其提供的ReplacingMergeTree只能够保证数据的最终一致性，因此在使用ReplacingMergeTree、 SummingMergeTree 这类表引擎的时候，会出现短暂
数据不一致的情况。

相关案例:
（1) 创建表

CREATE TABLE test_a(
user_id UInt64,
score String,
deleted UInt8 DEFAULT 0,
create_time DateTime DEFAULT toDateTime(0)
)ENGINE= Replac ingMergeTree(create_time)
ORDER BY user_id

user_id 是数据去重更新的标识 ;
create_time 是版本号字段，每组数据中 create_time 最大的一行表示最新的数据 ;
deleted 是自定的一个标记位，比如 0 代表未删除， 1 代表删除数据。

（2) 写入 1000万 测试数据

INSERT INTO TABLE test_a(user_id,score)
WITH(
SELECT ['A','B','C','D','E','F','G']
)AS dict
SELECT number AS user_id, dict[number%7+1] FROM numbers(10000000)

（3) 修改前 50万 行数据，修改内容包括 name 字段和 create_time 版本号字段

INSERT INTO TABLE test_a(user_id,score,create_time)
WITH(
SELECT ['AA','BB','CC','DD','EE' EE','FF','
)AS dict
SELECT number AS user_id, dict[number%7+1], now() AS create_time FROM
numbers(500000)

（4）统计总数

SELECT COUNT() FROM test_a;
10500000

由于ReplacingMergeTree还未触发分区合并，所以数据还未去重。
可以使用OPTIMIZE TABLE test_a FINAL命令强制表进行合并，但在数据合并期间CK将不能提供服务，这在生产环境是禁止出现的。

1.通过Group by对数据去重

（1）执行去重的查询

SELECT
	user_id ,
	argMax(score, create_time) AS score,
	argMax(deleted, create_time) AS deleted,
	max(create_time) AS ctime
FROM test_a
GROUP BY user_id
HAVING deleted = 0

◼ argMax(field1 field2):按照 field2 的最大值取 field1 的值。
当我们更新数据时，会写入一行新的数据，例如上面语句中， 通过查询最大的create_time 得到修改后的 score字段值 。

（2）创建视图，方便测试

CREATE VIEW view_test _a AS
SELECT
	user_id ,
	argMax(score, create_time) AS score,
	argMax(deleted, create_time) AS deleted,
	max(create_time) AS ctime
FROM test_a
GROUP BY user_id
HAVING deleted = 0

通过使用group by和筛选最大标记值的方法能够查询出最新的数据，使用视图对查询进行存储后能提高查询效率。

一些版本号小的数据并没有被真正的删除，而是被过滤掉了。在一些合适的场景下，可以结合
表级别的 TTL 最终将物理数据删除。

2.通过 FINAL 查询

在查询语句后增加FINAL修饰符 这样在查询的过程中将会执行 Merge的特殊逻辑 例如数据去重 预聚合等 。

但是这种方法在早期版本 基本没有人使用，因为在增加 FINAL 之后，我们的查询将会变
成一个单线程的执行过程，查询速度非常慢。

在v20.5.2.7-stable版本中， FINAL查询 支持多线程 执行，并且可以通过 max_final_threads 参数 控制单个查询的线程数。 但是 目前读取 part部分的动作依然是串行的 。

FINAL查询最终的性能和很多因素相关，列字段的大小、分区的数量等等都会影响到最
终的查询时间，所以还要 结合实际场景取舍 。

普通查询测试

explain pipeline select * from visits v 1 WHERE StartDate = '201 4 0 3 1 7
limit 100 settings max_threads = 2

(Expression)
ExpressionTransform × 2
	(SettingQuotaAndLimits)
		(Limit)
		Limit 2->2
			(ReadFromMergeTree)
			MergeTreeThread × 2 0->1

明显将由2个线程并行读取 part 查询。

FINAL查询测试

explain pipeline select * from visits v 1 final WHERE StartDate = '2014-03-17 limit 100 settings max_final_threads = 2;

(Expression)
Expre ssionTransform × 2
	(SettingQuotaAndLimits)
	(Limit)
	Limit 2->2
		(ReadFromMergeTree)
		ExpressionTransform × 2
			CollapsingSortedTra nsform × 2
				Copy 1->2
					AddingSelector
						ExpressionTransform
							MergeTree 0->1

从CollapsingSortedTransform这一步开始已经是多线程执行 但是 读取 part 部分的动作还是串行。

二、物化视图

ClickHouse的物化视图是一种查询结果的持久化，它确实是给我们带来了查询效率的提升。用户查起来跟表没有区别，它就是一张表，它也像是一张时刻在预计算的表，创建的过程它是用了一个特殊引擎，加上后来 as select 就是 create一个 table as select的写法。

“查询结果集”的范围很宽泛，可以是基础表中部分数据的一份简单拷贝，也可以是多表 join之后产生的结果或其子集，或者原始数据的聚合指标等等。所以，物化视图不会随着基础表的变化而变化，所以它也称为快照（ snapshot

1.物化视图与普通视图的区别

普通视图不保存数据，保存的仅仅是查询语句，查询的时候还是从原表读取数据，可以将普通视图理解为是个子查询。 物化视图则是把查询的结果根据相应的引擎存入到了磁盘或内存中 ，对数据重新进行了组织，你可以理解物化视图是完全的一张新表。

2.优缺点

优点：查询速度快 ，要是把物化视图这些规则全部写好，它比原数据查询快了很多，总的行数少了，因为都预计算好了。

缺点：它的本质是一个流式数据的使用场景，是累加式的技术，所以要用历史数据做去重、去核这样 的 分析，在物化视图里面是不太好用的。在某些场景的使用也是有限的。而且如果一张表加了好多物化视图，在写这张表的时候，就会消耗很多机器的资源， 比如 数据带宽占满、存储一下子增加了很多。

一张表如果是多张物理视图的数据来源，表数据更新后，多张物理视图在进行更改时会消耗大量资源。

3.基本语法

在ClickHouse中，创建物化视图的语法如下：也是create语法，会 创建一个隐藏的目标表来保存视图数据。 也可以 TO 表名，保存到一张显式的表。 没有加 TO表名，表名默认就是 .inner.物化视图名

CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name]
[ENGINE = engine] [POPULATE] AS SELECT

创建物化视图的限制:
1.必须指定物化视图的 engine 用于数据存储
2.TO [db].[table]语法的时候，不得使用 POPULATE。
3.查询语句 (select）可以包含下面的子句 DISTINCT, GROUP BY, ORDER BY, LIMIT
4.物 化视图的 alter操作有些限制，操作起来不大方便。
5.若物化视图的定义使用了 TO [db.]name 子语句，则可以将目标表的视图 卸载
DETACH 再 装载 ATTACH

物化视图的数据更新:
（1 物化视图创建好之后，若源表被写入新数据则物化视图也会同步更新

（2 POPULATE 关键字决定了物化视图的更新策略：
◼ 若有 POPULATE 则在创建视图的过程会将源表已经存在的数据一并导入，类似于
create table … as
◼ 若无 POPULATE 则物化视图在创建之后没有数据，只会在创建只有同步之后写入
源表的数据
◼ clickhouse 官方并不推荐使用 POPULATE，因为在创建物化视图的过程中同时写入
的数据不能被插入物化视图。

（3 物化视图不支持同步删除，若源表的数据不存在（删除了）则物化视图的数据仍然保留
（4 物化视图是 一种 特殊的数据表，可以用 show tables 查看
（5 物化视图数据的删除：
（6 物化视图的删除：

对于一些确定的数据模型，可将统计指标通过物化视图的方式进行构建，这样可避免查
询时重复计算的过程，物化视图会在有新数据插入时进行更新。

三、MaterializeMySQL引擎

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

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

1.特点

（1) MaterializeMySQL 同时支持 全量 和 增量 同步 在 database 创建之初会全量同步MySQL 中的表和数据 之后则会通过 binlog 进行增量同步。
（2) MaterializeMySQL database 为其所创建的每张 ReplacingMergeTree 自动增加了_sign 和 _version 字段。

其中_version 用作 ReplacingMergeTree 的 ver 版本参数 每当监听到 insert、 update 和 delete 事件时 在 databse 内全局自增。而 _sign 则用于标记是否被删除，取值 1 或者 -1 。

目前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等。

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元组。ClickHouse只有一个物理顺序，由 ORDER BY子句决定。如果需要创建新的物理顺序，请使用物化视图。

四、常见问题排除

分布式 DDL某数据节点的副本不执行

（1 问题： 使用分布式 ddl执行命令 create table on cluster xxxx 某个节点上没有创建
表，但是 client返回正常，查看日志有如下报错。
xxx xxx : Retrying createReplica(), because some other replicaswere created at the same time
（2 解决办法： 重启该不执行的节点。

CK的同一数据的副本之间能够自行同步数据。

数据副本表和数据不一致

（1 问题： 由于某个数据节点副本异常，导致两数据副本表不一致，某个数据副本缺少表，需要将两个数据副本调整一致。
（2 解决办法：
在缺少表的数据副本节点上创建缺少的表，创建为本地表，表结构可以在其他数据副本通过 show crete table xxxx获取。
表结构创建后， clickhouse会自动从其他副本同步该表数据，验证数据量是否一致即可。

副本节点全量恢复

（1）问题 某个数据副本异常无法启动，需要重新搭建副本。
（2）解决办法
清空异常副本节点的metadata和 data目录。从另一个正常副本将metadata目录拷贝过来（这一步之后可以启动数据库，但是只有表结构没有数据）。
执行sudo -u clickhouse touch /data/clickhouse/flags/force_restore_data
启动数据库。

数据副本启动缺少 zk表

（1）问题 某个数据副本表在 zk上丢失数据，或者不存在，但是 metadata元数据里
存在，导致启动异常，报错：

Can t get data for node /clickhouse/tables/0102/xxxxx/xxxxxxx/replicas/ xxx /metadata: node doesn t exist (No node):Cannot attach table xxxxxxx

（2）解决办法
metadata中移除该表的结构文件，如果多个表报错都移除mv metadata/xxxxxx/xxxxxxxx.sql /tmp/
启动数据库
手工创建缺少的表，表结构从其他节点show create table获取。
创建后会自动同步数据，验证数据是否一致。

ZK table replicas数据未删除，导致重建表报错

（1）问题 重建表过程中，先使用 drop table xxx on cluster xxx ,各节点在 clickhouse上table已物理删除，但是 zk里面针对某个 clickhouse节点的 table meta信息未被删除（低概率事件），因 zk里仍存在该表的 meta信息，导致再次创建该表 create table xxx on cluster, 该节点无法创建表 (其他节点创建表成功 )，报错

Replica /clickhouse/tables/01-03/xxxxxx/xxx/replicas/xxx already exists…

（2）解决办法从其他数据副本cp该 table的 metadata sql过来 .重启节点。

Clickhouse节点意外关闭

（1）问题 模拟其中一个节点意外宕机，在大量 insert数据的情况下，关闭某个节点。
（2 现象： 数据写入不受影响、数据查询不受影响、建表 DDL执行到异常节点会卡住，
报错：

Code: 159. DB:: Exception: Received from localhost:9000. DB::Exception:Watching task /clickhouse/task_queue/ddl/query 0000565925 is executinglonger than distributed_ddl_task_timeout (=180) seconds. There are 1unfin ished hosts (0 of them are currently active), they are going toexecute the query in background.

（3）解决办法 启动异常节点，期间其他副本写入数据会自动同步过来，其他副本的
建表 DDL也会同步。