Clickhouse MaterializeMySQL 数据库引擎

在Clickhouse 20.8.2.3 版本中新增一个数据库引擎,将clickhouse模拟为MySQL的从库,可以通过mysql的binlog实时的接收来自mysql的数据并在clickhouse物化,极大提升了数仓的查询性能和数据同步的时效性。同时增加了获取mysql数据的方式,除了mysql协议和mysql函数和mysql表引擎,clickhouse可以作为mysql的从库。

ClickHouse can work as MySQL replica - it is implemented by MaterializeMySQL database engine. 

运行环境:

CentOS 7.6
Clickhouse 20.10.1.4608 
MySQL 8.0.20

 

 MaterializeMySQL database engine 支持的情况:

1.支持mysql 库级别的数据同步,暂不支持表级别的。
2.MySQL 库映射到clickhouse中自动创建为ReplacingMergeTree 引擎的表
3.支持全量和增量同步,首次创建数据库引擎时进行一次全量复制,之后通过监控binlog变化进行增量数据同步
4.支持的MySQL版本:5.6 5.7 8.0
5.支持的操作:insert,update,delete,alter,create,drop,truncate等大部分DDL操作
7.支持的MySQL复制为GTID复制

MySQL需要配置的部分:

 1.开启binlog并设置为row格式:

log-bin=mysqlbin.log
binlog_format=ROW
server-id=1 

查看验证:

 

mysql> show variables like '%log%bin%';
+----------------------------------+-------------------------------+
| Variable_name                    | Value                         |
+----------------------------------+-------------------------------+
| log_bin                          | ON                            |
| log_bin_basename                 | /var/lib/mysql/mysqlbin       |
| log_bin_index                    | /var/lib/mysql/mysqlbin.index |
.....

mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

MySQL的版本号:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.20    |
+-----------+
1 row in set (0.00 sec)



在MySQL中创建一个数据库表:

mysql> create table dataset.users(id bigint not null auto_increment primary key,usercode varchar(8) not null comment '用户编码',username varchar(16) comment '用户名称',state int comment '状态 1在线0离线',createtime datetime not null, lastmodifytime datetime(6) not null default current_timestamp(6) on update current_timestamp(6),key ix_usercode(usercode))ENGINE=InnoDB default charset=utf8mb4 comment '用户信息';  
Query OK, 0 rows affected (0.04 sec)

mysql> insert into dataset.users(usercode,username,state,createtime)values('A008001','xiaowu',1,now()),('A008002','xiaoming',1,now());
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from dataset.users;
+----+----------+----------+-------+---------------------+----------------------------+
| id | usercode | username | state | createtime          | lastmodifytime             |
+----+----------+----------+-------+---------------------+----------------------------+
|  1 | A008001  | xiaowu   |     1 | 2020-09-02 09:05:08 | 2020-09-02 09:05:08.073302 |
|  2 | A008002  | xiaoming |     1 | 2020-09-02 09:05:08 | 2020-09-02 09:05:08.073302 |
+----+----------+----------+-------+---------------------+----------------------------+
2 rows in set (0.00 sec)


mysql> show create table dataset.users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `usercode` varchar(8) NOT NULL COMMENT '用户编码',
  `username` varchar(16) DEFAULT NULL COMMENT '用户名称',
  `state` int DEFAULT NULL COMMENT '状态 1在线0离线',
  `createtime` datetime NOT NULL,
  `lastmodifytime` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `ix_usercode` (`usercode`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户信息'
1 row in set (0.00 sec)



注意:
1.主键ID不能为null 即需要设置为not null
2.索引列也需要设置为not null

在clickhouse中查看信息并设置:

Clickhouse> select * from system.settings where name ='allow_experimental_database_materialize_mysql';

SELECT *
FROM system.settings
WHERE name = 'allow_experimental_database_materialize_mysql'

┌─name──────────────────────────────────────────┬─value─┬─changed─┬─description─────────────────────────────────────────────────┬─min──┬─max──┬─readonly─┬─type─┐
│ allow_experimental_database_materialize_mysql │ 0     │       0 │ Allow to create database with Engine=MaterializeMySQL(...). │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool │
└───────────────────────────────────────────────┴───────┴─────────┴─────────────────────────────────────────────────────────────┴──────┴──────┴──────────┴──────┘

1 rows in set. Elapsed: 0.006 sec. 

Clickhouse> select version();

SELECT version()

┌─version()────┐
│ 20.10.1.4608 │
└──────────────┘

1 rows in set. Elapsed: 0.004 sec. 


Clickhouse> set allow_experimental_database_materialize_mysql=1;



Clickhouse> create database dataset_users engine=MaterializeMySQL('192.168.8.110:3306','dataset','root','oracle');

CREATE DATABASE dataset_users
ENGINE = MaterializeMySQL('192.168.8.110:3306', 'dataset', 'root', 'oracle')

Ok.

0 rows in set. Elapsed: 2.449 sec. 

root为用户名
oracle为root对应的密码
dataset即为mysql中的数据库名称


Clickhouse> use dataset_users;

USE dataset_users

Ok.

0 rows in set. Elapsed: 0.002 sec. 

Clickhouse> show tables;

SHOW TABLES

┌─name──┐
│ users │
└───────┘

1 rows in set. Elapsed: 0.005 sec. 

Clickhouse> select * from users;

SELECT *
FROM users


Received exception from server (version 20.10.1):
Code: 1002. DB::Exception: Received from localhost:9000. DB::Exception: The replication sender thread cannot start in AUTO_POSITION mode: this server has GTID_MODE = OFF instead of ON.. 

0 rows in set. Elapsed: 0.003 sec. 


需要在MySQL端开启GTID模式:

1.确保MySQL版本在5.6. 以上
2.在MySQL  5.7版本支持热部署,即不停止服务的情况下开启GTID模式

操作步骤:

 SET GLOBAL ENFORCE_GTID_CONSISTENCY = 'WARN';
 SET GLOBAL ENFORCE_GTID_CONSISTENCY = 'ON';
 SET GLOBAL GTID_MODE = 'OFF_PERMISSIVE';
 SET GLOBAL GTID_MODE = 'ON_PERMISSIVE';
 SET GLOBAL GTID_MODE = 'ON';

查看验证:
mysql> show variables like 'gtid_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'ENFORCE_GTID_CONSISTENCY';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON    |
+--------------------------+-------+
1 row in set (0.00 sec)

 

 开启之后继续查询:

在MySQL端需要开启GTID模式:
Clickhouse> select * from users;

SELECT *
FROM users


Received exception from server (version 20.10.1):
Code: 1002. DB::Exception: Received from localhost:9000. DB::Exception: The replication sender thread cannot start in AUTO_POSITION mode: this server has GTID_MODE = OFF instead of ON.. 

0 rows in set. Elapsed: 0.002 sec. 

开启之后仍然报错。

需要删除数据库,重新创建MaterializeMySQL数据库表:

CREATE DATABASE dataset_users
ENGINE = MaterializeMySQL('192.168.8.110:3306', 'dataset', 'root', 'oracle')
重新查询数据:
Clickhouse> select * from users FORMAT PrettyCompactMonoBlock;

SELECT *
FROM users
FORMAT PrettyCompactMonoBlock

┌─id─┬─usercode─┬─username─┬─state─┬──────────createtime─┬─────────────lastmodifytime─┐
│  1 │ A008001  │ xiaowu   │     1 │ 2020-09-02 09:05:08 │ 2020-09-02 09:05:08.073302 │
│  2 │ A008002  │ xiaoming │     1 │ 2020-09-02 09:05:08 │ 2020-09-02 09:05:08.073302 │
└────┴──────────┴──────────┴───────┴─────────────────────┴────────────────────────────┘

2 rows in set. Elapsed: 0.006 sec. 

在clickhouse端传表的定义:

Clickhouse> desc table users;

DESCRIBE TABLE users

┌─name───────────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id             │ Int64            │              │                    │         │                  │                │
│ usercode       │ String           │              │                    │         │                  │                │
│ username       │ Nullable(String) │              │                    │         │                  │                │
│ state          │ Nullable(Int32)  │              │                    │         │                  │                │
│ createtime     │ DateTime         │              │                    │         │                  │                │
│ lastmodifytime │ DateTime64(6)    │              │                    │         │                  │                │
│ _sign          │ Int8             │ MATERIALIZED │ 1                  │         │                  │                │
│ _version       │ UInt64           │ MATERIALIZED │ 1                  │         │                  │                │
└────────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

8 rows in set. Elapsed: 0.003 sec. 

Clickhouse> show create table users;

SHOW CREATE TABLE users


Received exception from server (version 20.10.1):
Code: 390. DB::Exception: Received from localhost:9000. DB::Exception: There is no SHOW CREATE TABLE query for DatabaseMaterializeMySQL. 

0 rows in set. Elapsed: 0.002 sec. 


在默认的数据库下,可以看到users表的定义:

/var/lib/clickhouse/metadata/dataset_users

# cat users.sql 
ATTACH TABLE users
(
    `id` Int64,
    `usercode` String,
    `username` Nullable(String),
    `state` Nullable(Int32),
    `createtime` DateTime,
    `lastmodifytime` DateTime64(6),
    `_sign` Int8 MATERIALIZED 1,
    `_version` UInt64 MATERIALIZED 1
)
ENGINE = ReplacingMergeTree(_version)
PARTITION BY intDiv(id, 18446744073709551)
ORDER BY (usercode, id)
SETTINGS index_granularity = 8192

 结论:通过MaterializeMySQL 可以全量的抽取数据。

增量数据验证:

MySQL端:
1.插入数据:
mysql> insert into dataset.users(usercode,username,state,createtime)values('A008003','xiaozhao',1,now()),('A008004','xiaowang',1,now());     

clickhouse端:

Clickhouse> select * from users FORMAT PrettyCompactMonoBlock;

SELECT *
FROM users
FORMAT PrettyCompactMonoBlock

┌─id─┬─usercode─┬─username─┬─state─┬──────────createtime─┬─────────────lastmodifytime─┐
│  2 │ A008002  │ xiaoming │     1 │ 2020-09-02 09:05:08 │ 2020-09-02 09:05:08.073302 │
│  4 │ A008004  │ xiaowang │     1 │ 2020-09-02 09:29:41 │ 2020-09-02 09:29:41.683995 │
│  1 │ A008001  │ xiaowu   │     1 │ 2020-09-02 09:05:08 │ 2020-09-02 09:05:08.073302 │
│  3 │ A008003  │ xiaozhao │     1 │ 2020-09-02 09:29:41 │ 2020-09-02 09:29:41.683995 │
└────┴──────────┴──────────┴───────┴─────────────────────┴────────────────────────────┘

4 rows in set. Elapsed: 0.007 sec. 


可能报错:
Received exception from server (version 20.10.1):
Code: 48. DB::Exception: Received from localhost:9000. DB::Exception: Unsupported data type from MySQL.. 

0 rows in set. Elapsed: 0.003 sec. 
2.更新数据:

mysql> update dataset.users set state=0 where usercode='A008001';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Clickhouse> select * from dataset_users.users where usercode='A008001';

┌─id─┬─usercode─┬─username─┬─state─┬──────────createtime─┬─────────────lastmodifytime─
│  1 │ A008001  │ xiaowu   │     0 │ 2020-09-02 09:05:08 │ 2020-09-02 09:45:20.177437 │
└────┴──────────┴──────────┴───────┴─────────────────────┴────────────────────────────┴
3.删除数据:
mysql> delete from dataset.users where usercode='A008002';
Query OK, 1 row affected (0.00 sec)

Clickhouse>  select * from dataset_users.users where usercode='A008002';

SELECT *
FROM dataset_users.users
WHERE usercode = 'A008002'

Ok.

0 rows in set. Elapsed: 0.006 sec. 

列字段操作:
1.增加一列:
mysql> alter table dataset.users add column sex varchar(6) comment '性别';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Clickhouse> desc table dataset_users.users;

DESCRIBE TABLE dataset_users.users

┌─name───────────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id             │ Int64            │              │                    │         │                  │                │
│ usercode       │ String           │              │                    │         │                  │                │
│ username       │ Nullable(String) │              │                    │         │                  │                │
│ state          │ Nullable(Int32)  │              │                    │         │                  │                │
│ createtime     │ DateTime         │              │                    │         │                  │                │
│ lastmodifytime │ DateTime64(6)    │              │                    │         │                  │                │
│ sex            │ Nullable(String) │              │                    │         │                  │                │
│ _sign          │ Int8             │ MATERIALIZED │ 1                  │         │                  │                │
│ _version       │ UInt64           │ MATERIALIZED │ 1                  │         │                  │                │
└────────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

9 rows in set. Elapsed: 0.006 sec. 


2.增加两列:
mysql> alter table dataset.users add column birthday date comment '生日',add column salary decimal(22,6) comment '基本薪资';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Clickhouse> desc table dataset_users.users;

DESCRIBE TABLE dataset_users.users

┌─name───────────┬─type─────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id             │ Int64                    │              │                    │         │                  │                │
│ usercode       │ String                   │              │                    │         │                  │                │
│ username       │ Nullable(String)         │              │                    │         │                  │                │
│ state          │ Nullable(Int32)          │              │                    │         │                  │                │
│ createtime     │ DateTime                 │              │                    │         │                  │                │
│ lastmodifytime │ DateTime64(6)            │              │                    │         │                  │                │
│ sex            │ Nullable(String)         │              │                    │         │                  │                │
│ birthday       │ Nullable(Date)           │              │                    │         │                  │                │
│ salary         │ Nullable(Decimal(22, 6)) │              │                    │         │                  │                │
│ _sign          │ Int8                     │ MATERIALIZED │ 1                  │         │                  │                │
│ _version       │ UInt64                   │ MATERIALIZED │ 1                  │         │                  │                │
└────────────────┴──────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

11 rows in set. Elapsed: 0.005 sec. 

3.删除列:
mysql> alter table dataset.users drop column salary;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Clickhouse> desc table dataset_users.users;

DESCRIBE TABLE dataset_users.users

┌─name───────────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id             │ Int64            │              │                    │         │                  │                │
│ usercode       │ String           │              │                    │         │                  │                │
│ username       │ Nullable(String) │              │                    │         │                  │                │
│ state          │ Nullable(Int32)  │              │                    │         │                  │                │
│ createtime     │ DateTime         │              │                    │         │                  │                │
│ lastmodifytime │ DateTime64(6)    │              │                    │         │                  │                │
│ sex            │ Nullable(String) │              │                    │         │                  │                │
│ birthday       │ Nullable(Date)   │              │                    │         │                  │                │
│ _sign          │ Int8             │ MATERIALIZED │ 1                  │         │                  │                │
│ _version       │ UInt64           │ MATERIALIZED │ 1                  │         │                  │                │
└────────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

10 rows in set. Elapsed: 0.005 sec. 

4.修改列字段类型:
mysql> alter table dataset.users change birthday birthday int;
语法等同于:
alter table dataset.users modify  birthday int;

Clickhouse> desc table dataset_users.users;

DESCRIBE TABLE dataset_users.users

┌─name───────────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id             │ Int64            │              │                    │         │                  │                │
│ usercode       │ String           │              │                    │         │                  │                │
│ username       │ Nullable(String) │              │                    │         │                  │                │
│ state          │ Nullable(Int32)  │              │                    │         │                  │                │
│ createtime     │ DateTime         │              │                    │         │                  │                │
│ lastmodifytime │ DateTime64(6)    │              │                    │         │                  │                │
│ sex            │ Nullable(String) │              │                    │         │                  │                │
│ birthday       │ Nullable(Int32)  │              │                    │         │                  │                │
│ _sign          │ Int8             │ MATERIALIZED │ 1                  │         │                  │                │
│ _version       │ UInt64           │ MATERIALIZED │ 1                  │         │                  │                │
└────────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

10 rows in set. Elapsed: 0.004 sec. 

5.修改列的名称:
mysql> alter table dataset.users rename column birthday to birth;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Clickhouse> desc table dataset_users.users;

DESCRIBE TABLE dataset_users.users

┌─name───────────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id             │ Int64            │              │                    │         │                  │                │
│ usercode       │ String           │              │                    │         │                  │                │
│ username       │ Nullable(String) │              │                    │         │                  │                │
│ state          │ Nullable(Int32)  │              │                    │         │                  │                │
│ createtime     │ DateTime         │              │                    │         │                  │                │
│ lastmodifytime │ DateTime64(6)    │              │                    │         │                  │                │
│ sex            │ Nullable(String) │              │                    │         │                  │                │
│ birth          │ Nullable(Int32)  │              │                    │         │                  │                │
│ _sign          │ Int8             │ MATERIALIZED │ 1                  │         │                  │                │
│ _version       │ UInt64           │ MATERIALIZED │ 1                  │         │                  │                │
└────────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

10 rows in set. Elapsed: 0.003 sec. 

6.同时修改列字段的名称和数据类型:
mysql> desc dataset.users;
+----------------+-------------+------+-----+----------------------+--------------------------------------------------+
| Field          | Type        | Null | Key | Default              | Extra                                            |
+----------------+-------------+------+-----+----------------------+--------------------------------------------------+
| id             | bigint      | NO   | PRI | NULL                 | auto_increment                                   |
| usercode       | varchar(8)  | NO   | MUL | NULL                 |                                                  |
| username       | varchar(16) | YES  |     | NULL                 |                                                  |
| state          | int         | YES  |     | NULL                 |                                                  |
| createtime     | datetime    | NO   |     | NULL                 |                                                  |
| lastmodifytime | datetime(6) | NO   |     | CURRENT_TIMESTAMP(6) | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(6) |
| sex            | varchar(6)  | YES  |     | NULL                 |                                                  |
| birth          | int         | YES  |     | NULL                 |                                                  |
+----------------+-------------+------+-----+----------------------+--------------------------------------------------+
8 rows in set (0.00 sec)

mysql> alter table dataset.users change sex gender varchar(16);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
在clickhouse则无法查看了修改的字段名了,此时需要重新定义MaterializeMySQL

Clickhouse> desc table dataset_users.users;

DESCRIBE TABLE dataset_users.users

┌─name───────────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id             │ Int64            │              │                    │         │                  │                │
│ usercode       │ String           │              │                    │         │                  │                │
│ username       │ Nullable(String) │              │                    │         │                  │                │
│ state          │ Nullable(Int32)  │              │                    │         │                  │                │
│ createtime     │ DateTime         │              │                    │         │                  │                │
│ lastmodifytime │ DateTime64(6)    │              │                    │         │                  │                │
│ sex            │ Nullable(String) │              │                    │         │                  │                │
│ birth          │ Nullable(Int32)  │              │                    │         │                  │                │
│ _sign          │ Int8             │ MATERIALIZED │ 1                  │         │                  │                │
│ _version       │ UInt64           │ MATERIALIZED │ 1                  │         │                  │                │
└────────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

10 rows in set. Elapsed: 0.004 sec

DROP DATABASE dataset_users;
CREATE DATABASE dataset_users
ENGINE = MaterializeMySQL('192.168.8.110:3306', 'dataset', 'root', 'oracle');
Clickhouse>  desc table dataset_users.users;

DESCRIBE TABLE dataset_users.users

┌─name───────────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id             │ Int64            │              │                    │         │                  │                │
│ usercode       │ String           │              │                    │         │                  │                │
│ username       │ Nullable(String) │              │                    │         │                  │                │
│ state          │ Nullable(Int32)  │              │                    │         │                  │                │
│ createtime     │ DateTime         │              │                    │         │                  │                │
│ lastmodifytime │ DateTime64(6)    │              │                    │         │                  │                │
│ gender         │ Nullable(String) │              │                    │         │                  │                │
│ birth          │ Nullable(Int32)  │              │                    │         │                  │                │
│ _sign          │ Int8             │ MATERIALIZED │ 1                  │         │                  │                │
│ _version       │ UInt64           │ MATERIALIZED │ 1                  │         │                  │                │
└────────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

10 rows in set. Elapsed: 0.004 sec. 


在MySQL的库dataset中新增表:
mysql> use dataset;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table city(id int not null auto_increment primary key,province varchar(32),city varchar(32));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into city(province,city)values('hubei','wuhan');
Query OK, 1 row affected (0.01 sec)

在Clickhouse中查看新增的表和数据:
Clickhouse> show tables;

SHOW TABLES

┌─name──┐
│ city  │
│ users │
└───────┘

2 rows in set. Elapsed: 0.006 sec. 

Clickhouse> select * from city;

SELECT *
FROM city

┌─id─┬─province─┬─city──┐
│  1 │ hubei    │ wuhan │
└────┴──────────┴───────┘

1 rows in set. Elapsed: 0.004 sec. 
表名重新命名:
mysql> alter table city rename to citys;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+-------------------+
| Tables_in_dataset |
+-------------------+
| citys             |
| users             |
+-------------------+
2 rows in set (0.00 sec)

Clickhouse> show tables;

SHOW TABLES

┌─name──┐
│ citys │
│ users │
└───────┘

2 rows in set. Elapsed: 0.004 sec. 

删除表:
mysql> drop table city;
Query OK, 0 rows affected (0.01 sec)
Clickhouse> show tables;

SHOW TABLES

┌─name──┐
│ users │
└───────┘

一次删除多个表的操作:
Clickhouse> show tables;

SHOW TABLES

┌─name──┐
│ a     │
│ b     │
│ citys │
│ users │
└───────┘

4 rows in set. Elapsed: 0.007 sec.

mysql> drop table a,b;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-------------------+
| Tables_in_dataset |
+-------------------+
| citys             |
| users             |
+-------------------+
2 rows in set (0.00 sec)

Clickhouse> show tables;

SHOW TABLES

┌─name──┐
│ a     │
│ b     │
│ citys │
│ users │
└───────┘

4 rows in set. Elapsed: 0.004 sec. 

Clickhouse> drop table a;

DROP TABLE a


Received exception from server (version 20.10.1):
Code: 48. DB::Exception: Received from localhost:9000. DB::Exception: MaterializeMySQL database not support drop table.. 

0 rows in set. Elapsed: 0.002 sec. 

结论:

 1.clickhouse作为MySQL的从库可以支持表级别的增加和删除操作,支持rename操作;不支持MySQL中一次删除多个表的操作。

2.clickhouse 支持新增字段,仅修改字段类型,仅列名重命名,删除字段,目前的版本不支持MYSQL的同时修改字段名和类型的

3.clickhouse的增加和删除动作是实时同步的,原因在于我们创建的MaterializeMySQL engine会默认为每一张表生成ReplacingMergeTree engine,当clickhouse遇到删除的binlog操作时,会将这条数据的_sign字段设为-1;
目前ReplacingMergeTree还只是标记性删除,并非物理上的实际删除,索引随着删除日志的增多,查询过滤会有一定的负担。

4.截止这个版本目前还不支持datetime(n)类型的数据实时同步,不支持MySQL 8.0版本中的快速新加字段语法。 

参考:

https://clickhouse.tech/docs/en/whats-new/changelog/

https://github.com/ClickHouse/ClickHouse/issues/4006

https://github.com/ClickHouse/ClickHouse/pull/10851

 https://github.com/ClickHouse/ClickHouse/issues/15354

https://github.com/ClickHouse/ClickHouse/issues/15355

https://www.jianshu.com/p/d0d4306411b3?hmsr=toutiao.io

©️2020 CSDN 皮肤主题: 技术工厂 设计师:CSDN官方博客 返回首页