物化视图mysql,ClickHouse的物化视图及MySQL表引擎

-- . 在MySQL中创建表定义和加入数据：

mysql> selectversion();+-----------+

| version() |

+-----------+

| 8.0. |

+-----------+

row in set (0.01sec)

mysql>show create table t_org\G*************************** . row ***************************Table: t_org

Create Table: CREATE TABLE `t_org` (

`ID`intNOT NULL AUTO_INCREMENT,

`org_code` varchar() DEFAULT NULL,

`org_name` varchar() DEFAULT NULL,

`lastmodifytime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci row in set (0.00sec)

插入数据：

insert into t_org(org_code,org_name)values('A01','研发部'),('A02','产品部'),('A03','测试部'),('A04','数据部'),('A05','运维部');.在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. rows in set. Elapsed: 0.002sec.

注意：.MySQL 中的Decimal(p,s) 类型在Clickhouse中的定义的差异.clickhouse的数据库引擎 ENGINE =MySQL 这里的MySQL必须要是MySQL

否则报错信息：

Received exceptionfrom server (version 20.4.):

Code:. DB::Exception: Received from localhost:. 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 │ │ │ │ │ │

└────────────────┴──────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘ rows in set. Elapsed: 0.002sec.--查询clickhouse的定义：

Clickhouse> select * fromt_org;

SELECT*FROM t_org

┌─ID─┬─org_code─┬─org_name─┬──────LASTMODIFYTIME─┐

│ │ A01 │ 研发部 │ -- ::│

│ │ A02 │ 产品部 │ -- ::│

│ │ A03 │ 测试部 │ -- ::│

│ │ A04 │ 数据部 │ -- ::│

│ │ A05 │ 运维部 │ -- ::│

└────┴──────────┴──────────┴─────────────────────┘ rows in set. Elapsed: 0.007sec.

在MySQL 中插入新的数据：

insert into t_org(org_code,org_name)values('A06','销售部'),('A07','人力部');

在clickhouse中查询：

Clickhouse> select * fromt_org;

SELECT*FROM t_org

┌─ID─┬─org_code─┬─org_name─┬──────LASTMODIFYTIME─┐

│ │ A01 │ 研发部 │ -- ::│

│ │ A02 │ 产品部 │ -- ::│

│ │ A03 │ 测试部 │ -- ::│

│ │ A04 │ 数据部 │ -- ::│

│ │ A05 │ 运维部 │ -- ::│

│ │ A06 │ 销售部 │ -- ::│

│ │ A07 │ 人力部 │ -- ::│

└────┴──────────┴──────────┴─────────────────────┘ rows in set. Elapsed: 0.005sec.

数据已经和MySQL中的数据一样。

在MySQL中更新数据：

mysql> update t_org set org_name='财务部' where org_code='A07';

Query OK, row affected (0.00sec)

Rows matched: Changed: Warnings: mysql> select * fromt_org;+----+----------+-----------+---------------------+

| ID | org_code | org_name | lastmodifytime |

+----+----------+-----------+---------------------+

| | A01 | 研发部 | -- :: |

| | A02 | 产品部 | -- :: |

| | A03 | 测试部 | -- :: |

| | A04 | 数据部 | -- :: |

| | A05 | 运维部 | -- :: |

| | A06 | 销售部 | -- :: |

| | A07 | 财务部 | -- :: |

+----+----------+-----------+---------------------+

rows in set (0.00sec)

在Clickhouse中查询：

Clickhouse> select * fromt_org;

SELECT*FROM t_org

┌─ID─┬─org_code─┬─org_name─┬──────LASTMODIFYTIME─┐

│ │ A01 │ 研发部 │ -- ::│

│ │ A02 │ 产品部 │ -- ::│

│ │ A03 │ 测试部 │ -- ::│

│ │ A04 │ 数据部 │ -- ::│

│ │ A05 │ 运维部 │ -- ::│

│ │ A06 │ 销售部 │ -- ::│

│ │ A07 │ 财务部 │ -- ::│

└────┴──────────┴──────────┴─────────────────────┘ rows in set. Elapsed: 0.003sec.

通过clickhouse查询远程的数据 可以看到数据一样发生了变化。---delete 操作：

mysql> delete from t_org where org_code='A07';

Query OK, row affected (0.00sec)

mysql> select * from t_org where org_code='A07';

Emptyset (0.00sec)

Clickhouse> select * fromt_org;

SELECT*FROM t_org

┌─ID─┬─org_code─┬─org_name─┬──────LASTMODIFYTIME─┐

│ │ A01 │ 研发部 │ -- ::│

│ │ A02 │ 产品部 │ -- ::│

│ │ A03 │ 测试部 │ -- ::│

│ │ A04 │ 数据部 │ -- ::│

│ │ A05 │ 运维部 │ -- ::│

│ │ A06 │ 销售部 │ -- ::│

└────┴──────────┴──────────┴─────────────────────┘ rows in set. Elapsed: 0.004sec.---使用雾化视图：

Clickhouse> create materialized view mv_t_org engine=MergeTree() order by ID as select * fromt_org;

CREATE MATERIALIZED VIEW mv_t_org

ENGINE=MergeTree()

ORDER BY ID AS

SELECT*FROM t_org

Ok. rows in set. Elapsed: 0.004 sec.