30.工具使用:ClickHouse从入门到放弃-引擎

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第3天，点击查看活动详情

28.工具使用:ClickHouse从入门到放弃-概述 - 掘金 (juejin.cn)

29.工具使用:ClickHouse从入门到放弃-环境搭建 - 掘金 (juejin.cn)

书接上回：

(1)mysql表引擎使用（postgre引擎类似）

1 应用

参考文档 ：

clickhouse.com/docs/zh/eng…

clickhouse.com/docs/zh/eng…

www.cnblogs.com/MrYang-11-G…

官网描述：MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许您对表进行insert和select查询，以方便您在ClickHouse与MySQL之间进行数据交换。 MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中，因此您可以执行诸如show tables或show create table之类的操作。

ClickHouse使用mysql引擎可以与mysql数据库中的数据表建⽴映射，并通过SQL向其发起远程查询或插入数据，这是一个异步的过程，相当于ck起了一个线程专门用于同步mysql的数据到ck，主要在于同步mysql配置表的信息，因为配置表常有修改的需求，而ck并不擅长修改记录，且配置表的记录往往在几百条，配置表的同步往往是实时的，目前针对小表数据使用，数据量大的表不建议使用。

2 语法规则

1）引擎定义：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
...
) ENGINE = MySQL('host:port', 'database', 'table', 'user',
'password'[, replace_query, 'on_duplicate_clause']);
2）参数含义：

host：port表示MySQL的地址和端⼝。database表示数据库的名称。table表示需要映射的表名称。user表示MySQL的⽤户名。password表示MySQL的密码。replace_query默认为0，对应MySQL的REPLACE INTO语法。如果将它设置为1，则会⽤REPLACE INTO代替INSERT INTO。on_duplicate_clause默认为0，对应MySQL的ON DUPLICATE KEY语法。如果需要使⽤该设置，则必须将replace_query设置成0。

3 示例

--创建一张mysql测试表：
CREATE TABLE `trade_store` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`store_id` bigint(20) NOT NULL COMMENT '店铺id',
`suppliers_id` bigint(20) NOT NULL COMMENT '商家id',
) ENGINE=InnoDB AUTO_INCREMENT=31301 DEFAULT CHARSET=utf8mb4 COMMENT='商家店铺表'

-- 创建clickhouse表，并指定引擎为mysql：
create table trade_store
(
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`store_id` bigint(20) NOT NULL COMMENT '店铺id',
`suppliers_id` bigint(20) NOT NULL COMMENT '商家id',
)
engine = MySQL('192.168.120.110:3306', 'test', 'trade_store', 'root', '123456');

4 总结

mysql引擎有点类似于 pg的FWD,会将请求转发给mysql，还是在mysql上执行，适合小表

FDW（Foreign Data Wrapper）是PostgreSQL（下文简称PG）中一项非常有意思的技术，通过它可以将PG变成一个通用的SQL引擎，使得用户可以通过SQL访问存储在PG之外的数据。

（2）SummingMergeTree引擎

1 应用

参考文档：

clickhouse.com/docs/zh/eng…

该引擎继承自 MergeTree。区别在于，当合并 SummingMergeTree 表的数据片段时，ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值（测试的时候批量导数据会很慢，可能就是这个原因） 。如果主键的组合方式使得单个键值对应于大量的行，则可以显著的减少存储空间并加快数据查询的速度。

我们推荐将该引擎和 MergeTree 一起使用。例如，在准备做报告的时候，将完整的数据存储在 MergeTree 表中，并且使用 SummingMergeTree 来存储聚合数据。这种方法可以使你避免因为使用不正确的主键组合方式而丢失有价值的数据。

2 语法规则

1）引擎定义：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
  name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
  name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
  ...
) ENGINE = SummingMergeTree([columns])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

3 示例

4 总结

查询统计快，初始化数据慢

（3）MaterializeMySQL引擎

1 应用

参考文档

clickhouse.com/docs/zh/eng…

clickhouse.com/docs/en/eng…

blog.csdn.net/zhangcongyi…

mp.weixin.qq.com/s?__biz=Mzg…

www.cnblogs.com/MrYang-11-G…

cdn.modb.pro/db/49058

clickhouse 20.8将新增 MaterializeMySQL引擎 ,可通过binlog日志实时物化mysql数据，极大提升了数仓的查询性能和数据同步的时效性；原有mysql中承担的数据分析工作 可交由clickhouse去做，这么做可显著降低线上mysql的负载，从此OLTP与OLAP业务实现完美融合。

1.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 等。

　　即支持mysql 5.6/5.7/8.0版本数据库，兼容insert，update，delete，alter，create，drop，truncate等大部分DDL操作。

1.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。

　　即使用MaterializedMySQL数据库引擎时，ReplacingMergeTree表与虚拟_sign和_version列一起使用。

• _version— 交易计数器。键入UInt64。
• _sign— 删除标记。键入Int8。可能的值：
• • 1— 未删除行，
  • -1— 行被删除。

（3）SELECT 查询

　　如果在 SELECT 查询中没有指定version，则使用 FINAL 修饰符，返回version 的最大值对应的数据，即最新版本的数据。

　　如果在 SELECT 查询中没有指定sign，则默认使用 WHERE _sign=1，即返回未删除状态（ sign=1)的数据。

（4）索引转换

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

　　ClickHouse 只有一个物理顺序，由 ORDER BY 子句决定。如果需要创建新的物理顺序，请使用物化视图。

• _sign=-1没有从表中物理删除的行。
• UPDATE/DELETE引擎不支持级联查询MaterializedMySQL，因为它们在 MySQL 二进制日志中不可见。
• 复制很容易被破坏。
• 禁止对数据库和表进行手动操作。
• MaterializedMySQL受optimize_on_insert 设置影响。MaterializedMySQL当 MySQL 服务器中的表发生变化时，数据会合并到数据库中的相应表中。

（5）类型转换

MySQL	ClickHouse
TINY	Int8
SHORT	Int16
INT24	Int32
LONG	UInt32
LONGLONG	UInt64
FLOAT	Float32
DOUBLE	Float64
DECIMAL, NEWDECIMAL	Decimal
DATE, NEWDATE	Date32
DATETIME, TIMESTAMP	DateTime
DATETIME2, TIMESTAMP2	DateTime64
YEAR	UInt16
TIME	Int64
ENUM	Enum
STRING	String
VARCHAR, VAR_STRING	String
BLOB	String
GEOMETRY	String
BINARY	FixedString
BIT	UInt64
SET	UInt64

2 语法规则

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializedMySQL('host:port', ['database' | database], 'user', 'password') [SETTINGS ...]
[TABLE OVERRIDE table1 (...), TABLE OVERRIDE table2 (...)]

引擎参数

• host:port — MySQL 服务地址.
• database — MySQL 数据库名称.
• user — MySQL 用户名.
• password — MySQL 用户密码.

引擎配置

• max_rows_in_buffer — 允许在内存中缓存数据的最大行数(对于单个表和无法查询的缓存数据)。当超过这个数字时，数据将被物化。默认值:65 505。
• max_bytes_in_buffer - 允许在内存中缓存数据的最大字节数(对于单个表和无法查询的缓存数据)。当超过这个数字时，数据将被物化。默认值: 1 048 576。
• max_rows_in_buffers - 允许在内存中缓存数据的最大行数(用于数据库和无法查询的缓存数据)。当超过这个数字时，数据将被物化。默认值: 65 505。
• max_bytes_in_buffers - 允许在内存中缓存数据的最大字节数(用于数据库和无法查询的缓存数据)。当超过这个数字时，数据将被物化。默认值: 1 048 576。
• max_flush_data_time- 允许数据在内存中缓存的最大毫秒数(对于数据库和无法查询的缓存数据)。当超过这个时间，数据将被物化。默认值: 1000。
• max_wait_time_when_mysql_unavailable - MySQL不可用时的重试间隔(毫秒)。负值禁用重试。默认值:1000。— allows_query_when_mysql_lost—允许在MySQL丢失时查询物化表。默认值:0(false)。
• allows_query_when_mysql_lost — ‎允许在MySQL丢失时查询实例化表. 默认值: 0 (false).
• materialized_mysql_tables_list 以逗号分隔的mysql数据库表列表，该列表将由MaterializedMySQL数据库引擎复制。默认值：空列表 — 表示将复制整个表‎.

3 示例

CREATE DATABASE mysql ENGINE = MaterializedMySQL('192.168.120.110:3306', 'test', 'user', '123456')
    SETTINGS
      allows_query_when_mysql_lost=true,
      max_wait_time_when_mysql_unavailable=10000,
      materialized_mysql_tables_list=trade_order,trade_store;
       
       
     

报错1：Enable allow_experimental_database_materialized_mysql to use it.. (UNKNOWN_DATABASE_ENGINE)  需要配置：allow_experimental_database_materialized_mysql

配置如下：\

[anchu@localhost ~]$ clickhouse-client --user=default --password=clickhouse -h 192.168.120.110 --port 9000 -m

localhost :) SET allow_experimental_database_materialized_mysql = 1;

0 rows in set. Elapsed: 0.002 sec.

localhost :)CREATE DATABASE mysql ENGINE = MaterializedMySQL('192.168.120.110:3306', 'test', 'user', '123456')
                SETTINGS
                    allows_query_when_mysql_lost=true,
                    max_wait_time_when_mysql_unavailable=10000,
                    materialized_mysql_tables_list='TEST';

报错2：MySQL SYNC USER ACCESS ERR: mysql sync user needs at least GLOBAL PRIVILEGES:'RELOAD, REPLICATION SLAVE, REPLICATION CLIENT' and SELECT PRIVILEGE on Database test.   需要当前用户有REPLICATION SLAVE权限

参考文档：cdn.modb.pro/db/49058\

• 创建用户
• 全局赋予 replication client,replication slave, reload 权限
• 对同步库 test 赋予 select 权限

-- mysql中创建用户,并赋予权限
[anchu@localhost ~]$ mysql -u root -P 3306 -h 192.168.120.110 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 98
Server version: 5.7.24-log MySQL Community Server (GPL)
mysql>  CREATE USER 'clickhouse'@'%' IDENTIFIED BY 'clickhouse';
Query OK, 0 rows affected (0.08 sec)

mysql> GRANT select ON test.* TO 'clickhouse'@'%';   -- 注意库名test
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT replication client,replication slave, reload on *.* to 'clickhouse'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql>


-- clickhouse 继续创建索引，切换为clickhouse用户

clickhouse-client --user=default --password=clickhouse -h 192.168.120.110 --port 9000 -m

localhost :) SET allow_experimental_database_materialized_mysql = 1; -- 会话层面，需要改在配置文件里

Query id: a7aeef05-f7c7-4fa7-b14c-b0a8eeb658db

Ok.

localhost :)  CREATE DATABASE mysql ENGINE = MaterializedMySQL('192.168.120.110:3306', 'test', 'clickhouse', 'clickhouse')
                              SETTINGS
                                allows_query_when_mysql_lost=true,
                                max_wait_time_when_mysql_unavailable=10000,
                                materialized_mysql_tables_list='test';

CREATE DATABASE mysql
ENGINE = MaterializedMySQL('192.168.120.110:3306', 'test', 'clickhouse', 'clickhouse')
SETTINGS allows_query_when_mysql_lost = 1, max_wait_time_when_mysql_unavailable = 10000, materialized_mysql_tables_list = 'test'

Query id: fbb7b97d-f992-454c-a4d2-9a855b23c501

Ok.

0 rows in set. Elapsed: 0.056 sec.


--查看数据库mysql
localhost :) show databases;

SHOW DATABASES

Query id: 9926a02d-1d4d-4a13-9aaa-68f320e5763a

┌─name───────────────┐
│ INFORMATION_SCHEMA │
│ default           │
│ information_schema │
│ mysql             │
│ system             │
└────────────────────┘

5 rows in set. Elapsed: 0.003 sec.

localhost :) use mysql;


localhost :) show tables;  


localhost :)

报错3：没有同步到表，原因是必须有主键；

-- clickhouse 删除之前创建的mysql库
localhost :) drop database mysql;
localhost :)  CREATE DATABASE mysql ENGINE = MaterializedMySQL('192.168.120.110:3306', 'test', 'clickhouse', 'clickhouse')
                              SETTINGS
                                allows_query_when_mysql_lost=true,
                                max_wait_time_when_mysql_unavailable=10000,
                                materialized_mysql_tables_list='trade_store,trade_order';

localhost :) use mysql;
localhost :) show tables;

SHOW TABLES

Query id: 4f33e86c-0e93-4c8a-913f-4dccd4bb179d

┌─name────────┐
│ trade_order │
│ trade_store │
└─────────────┘


localhost :) select * from trade_store limit 1;

SELECT *
FROM trade_store
LIMIT 1

Query id: e6e56f04-fea2-493b-aa7a-19c981eb7846

┌─id─┬─store_id─┬─suppliers_id─┬─network_num─┬─im_identifier─┬─store_name───┬─head_pic───────────────────────────────────────────────────────────────┬─sign_pic──────────────────────────────────────────────────────────────────┬─store_type─┬─mobile──────┬─landline─┬─coordinates─┬─latitude─┬─longitude─┬──────────psite_id─┬──────────csite_id─┬─site_id─┬─real_site_id─┬─state─┬─stars─┬─rooms─┬─beds─┬─seats─┬─features─┬─store_star─┬─service_star─┬─goods_star─┬─shipping_star─┬─────────create_time─┬─────────update_time─┬─project_code─┐
│  1 │        1 │            1 │             │               │ 测试 │ https://nb-img.hzanchu.com/acimg/3564e3565e8bd1bc3bb7418a8f24c24d.jpeg │ https://wsnbh-img.hzanchu.com/acimg/3e7d4729d601b160497836a0ee381a17.jpeg │          0 │ 17716256898 │ 0       │             │         │           │ 43657283580821504 │ 43657508412293120 │     112 │          112 │     0 │     0 │     0 │    0 │     0 │         │          5 │            5 │          5 │             5 │ 2019-11-13 11:31:07 │ 2022-04-25 18:51:30 │ 3300         │

1 rows in set. Elapsed: 0.012 sec. Processed 5.39 thousand rows, 2.16 MB (468.51 thousand rows/s., 187.57 MB/s.)