ClickHouse的物化视图及MySQL表引擎

MySQL表引擎可以与MySQL数据库中的数据表简历映射，并通过SQL向其发起远程查询。

MySQL表引擎可以与物化视图结合，来同步更新MySQL数据库中的数据。

语法：
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']);
 
解释：
host:port MySQL服务器的地址和端口
database MySQL中的数据库名
table 表示需要映射的MySQL表名称
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.
 

示例：

-- 1. 在MySQL中创建表定义和加入数据：
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.20    |
+-----------+
1 row in set (0.01 sec)
 mysql> show create table t_org\G
*************************** 1. row ***************************
Table: t_org
Create Table: CREATE TABLE `t_org` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `org_code` varchar(8) DEFAULT NULL,
  `org_name` varchar(32) DEFAULT NULL,
  `lastmodifytime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
 
插入数据：
insert into t_org(org_code,org_name)values('A01','研发部'),('A02','产品部'),('A03','测试部'),('A04','数据部'),('A05','运维部'); 

2.在clickhouse中对MySQL表引擎建立映射：
Clickhouse>  create table t_org(ID UInt32,org_code String,org_name String,LASTMODIFYTIME Datetime)ENGINE=MySQL('192.168.8.110:3306','datasets','t_org','root','oracle');
 
CREATE TABLE t_org
(
    `ID` UInt32, 
    `org_code` String, 
    `org_name` String, 
    `LASTMODIFYTIME` Datetime
)
ENGINE = MySQL('192.168.8.110:3306', 'datasets', 't_org', 'root', 'oracle')
 
Ok.
 
0 rows in set. Elapsed: 0.002 sec. 
  
注意：
1.MySQL 中的Decimal(p,s) 类型在Clickhouse中的定义的差异
2.clickhouse的数据库引擎 ENGINE = MySQL 这里的MySQL必须要是MySQL
否则报错信息：
Received exception from server (version 20.4.5):
Code: 56. DB::Exception: Received from localhost:9000. DB::Exception: Unknown table engine Mysql. Maybe you meant: ['MySQL']. 
 
查看表的定义： 
Clickhouse> desc t_org;
 
DESCRIBE TABLE t_org
 
┌─name───────────┬─type─────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ ID             │ UInt32   │              │                    │         │                  │                │
│ org_code       │ String   │              │                    │         │                  │                │
│ org_name       │ String   │              │                    │         │                  │                │
│ LASTMODIFYTIME │ DateTime │              │                    │         │                  │                │
└────────────────┴──────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
 
4 rows in set. Elapsed: 0.002 sec. 
-- 查询clickhouse的定义：
Clickhouse> select * from t_org;
 
SELECT *
FROM t_org
 
┌─ID─┬─org_code─┬─org_name─┬──────LASTMODIFYTIME─┐
│  1 │ A01      │ 研发部   │ 2020-07-06 15:13:46 │
│  2 │ A02      │ 产品部   │ 2020-07-06 15:13:46 │
│  3 │ A03      │ 测试部   │ 2020-07-06 15:13:46 │
│  4 │ A04      │ 数据部   │ 2020-07-06 15:13:46 │
│  5 │ A05      │ 运维部   │ 2020-07-06 15:13:46 │
└────┴──────────┴──────────┴─────────────────────┘
 
5 rows in set. Elapsed: 0.007 sec. 
在MySQL 中插入新的数据：
insert into t_org(org_code,org_name)values('A06','销售部'),('A07','人力部');
 
在clickhouse中查询：
Clickhouse> select * from t_org;
 
SELECT *
FROM t_org
 
┌─ID─┬─org_code─┬─org_name─┬──────LASTMODIFYTIME─┐
│  1 │ A01      │ 研发部   │ 2020-07-06 15:13:46 │
│  2 │ A02      │ 产品部   │ 2020-07-06 15:13:46 │
│  3 │ A03      │ 测试部   │ 2020-07-06 15:13:46 │
│  4 │ A04      │ 数据部   │ 2020-07-06 15:13:46 │
│  5 │ A05      │ 运维部   │ 2020-07-06 15:13:46 │
│  6 │ A06      │ 销售部   │ 2020-07-06 15:22:28 │
│  7 │ A07      │ 人力部   │ 2020-07-06 15:22:28 │
└────┴──────────┴──────────┴─────────────────────┘
 
7 rows in set. Elapsed: 0.005 sec. 
 
数据已经和MySQL中的数据一样。
 
在MySQL中更新数据：
mysql> update  t_org set org_name='财务部' where org_code='A07';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from t_org;
+----+----------+-----------+---------------------+
| ID | org_code | org_name  | lastmodifytime      |
+----+----------+-----------+---------------------+
|  1 | A01      | 研发部    | 2020-07-06 15:13:46 |
|  2 | A02      | 产品部    | 2020-07-06 15:13:46 |
|  3 | A03      | 测试部    | 2020-07-06 15:13:46 |
|  4 | A04      | 数据部    | 2020-07-06 15:13:46 |
|  5 | A05      | 运维部    | 2020-07-06 15:13:46 |
|  6 | A06      | 销售部    | 2020-07-06 15:22:28 |
|  7 | A07      | 财务部    | 2020-07-06 15:24:10 |
+----+----------+-----------+---------------------+
7 rows in set (0.00 sec)
在Clickhouse中查询：
Clickhouse> select * from t_org;
 
SELECT *
FROM t_org
 
┌─ID─┬─org_code─┬─org_name─┬──────LASTMODIFYTIME─┐
│  1 │ A01      │ 研发部   │ 2020-07-06 15:13:46 │
│  2 │ A02      │ 产品部   │ 2020-07-06 15:13:46 │
│  3 │ A03      │ 测试部   │ 2020-07-06 15:13:46 │
│  4 │ A04      │ 数据部   │ 2020-07-06 15:13:46 │
│  5 │ A05      │ 运维部   │ 2020-07-06 15:13:46 │
│  6 │ A06      │ 销售部   │ 2020-07-06 15:22:28 │
│  7 │ A07      │ 财务部   │ 2020-07-06 15:24:10 │
└────┴──────────┴──────────┴─────────────────────┘
 
7 rows in set. Elapsed: 0.003 sec. 
通过clickhouse查询远程的数据 可以看到数据一样发生了变化。
 
---delete 操作：
mysql> delete from t_org where org_code='A07';
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from t_org where org_code='A07';
Empty set (0.00 sec)
 
 
Clickhouse> select * from t_org;
 
SELECT *
FROM t_org
 
┌─ID─┬─org_code─┬─org_name─┬──────LASTMODIFYTIME─┐
│  1 │ A01      │ 研发部   │ 2020-07-06 15:13:46 │
│  2 │ A02      │ 产品部   │ 2020-07-06 15:13:46 │
│  3 │ A03      │ 测试部   │ 2020-07-06 15:13:46 │
│  4 │ A04      │ 数据部   │ 2020-07-06 15:13:46 │
│  5 │ A05      │ 运维部   │ 2020-07-06 15:13:46 │
│  6 │ A06      │ 销售部   │ 2020-07-06 15:22:28 │
└────┴──────────┴──────────┴─────────────────────┘
 
6 rows in set. Elapsed: 0.004 sec. 
 
 
--- 使用雾化视图：
 
Clickhouse> create materialized view mv_t_org engine=MergeTree() order by ID as select * from t_org;
 
CREATE MATERIALIZED VIEW mv_t_org
ENGINE = MergeTree()
ORDER BY ID AS
SELECT *
FROM t_org
 
Ok.
 
0 rows in set. Elapsed: 0.004 sec. 

结论：ClickHouse暂时不支持通过ClickHouse 的MySQL引擎表更新和删除操作，他们之间的关系如下：