物化视图(Materialized View)本质是一张特殊的数据表,主要用于预先计算并保存耗时较多的操作的结果,以便在查询时直接复用,从而避免这些耗时的操作,最终达到加速查询的目的。本文为您介绍物化视图相关命令、语法格式并提供使用示例,及物化视图在 CDC 同步场景中的数据处理过程。
创建物化视图
语法
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ON CLUSTER default_cluster]ENGINE = engine_name()ORDER BY expr[POPULATE]AS SELECT ...
参数说明
IF NOT EXISTS
:可选,表示如果已经存在一个同名的表,则会忽略后续的创建过程。db.
:可选,指定数据库的名称,默认为当前选择的数据库。table_name
:必填,物化视图表名称。TO[db.]name
:可选,将物化视图的数据写入到新表中。说明
如果需要将物化视图的数据写入新表,不能使用
POPULATE
关键字。ON CLUSTER default_cluster
:可选,增加 ON CLUSTER default_cluster
可在 default_cluster
集群的所有服务器上创建物化视图。ENGINE = engine_name()
:必填,表引擎类型。ORDER BY
:必填,指定排序键。POPULATE
:可选,POPULATE
关键字。如果创建物化视图时指定了 POPULATE
关键字,则在创建时将 SELECT
子句所指定的源表数据插入到物化视图中。不指定 POPULATE
关键字时,物化视图只会包含在物化视图创建后新写入源表的数据。说明
请谨慎选择指定
POPULATE
关键字。如果创建物化视图过程中源表有插入新数据,则新插入的数据不会插入到物化视图中,导致源表与物化视图数据不一致。SELECT ...
:必填,SELECT
子句。当数据写入物化视图中 SELECT
子句所指定的源表时,插入的数据会通过 SELECT
子句查询进行转换并将最终结果插入到物化视图中。说明
SELECT
语句不能包含 PREWHERE
、SAMPLE
、FINAL
。当物化视图的表涉及多表
JOIN
时,只有向最左边的表插入数据时才会更新。
当物化视图 SELECT
使用类似 FROM A JOIN B
的多表 JOIN
时,向 B 表插入数据时不会触发更新,只有向 A 表插入数据时才会触发物化视图更新,此时物化视图更新才会去 JOIN
B 表中的数据。使用示例
1. 连接 LibraSQL 分析引擎后在 TDSQL-H LibraDB 中创建源表。
CREATE TABLE view.basic_test ON CLUSTER default_cluster(`id` UInt64,`type` String,`cnt` UInt32,`version` Int8)ENGINE = ReplacingMergeTree(version)ORDER BY id;
2. 在源表中插入数据。
INSERT INTO view.basic_test VALUES(1, 'click', 10, 0),(2, 'click', 20, 0),(3, 'modify', 11,0),(4,'modify', 20, 0);
3. 创建物化视图,并指定
POPULATE
关键字。CREATE MATERIALIZED VIEW view.basic_modify_view ON CLUSTER default_clusterENGINE = ReplacingMergeTree(version)ORDER BY id POPULATE ASSELECTid,type,cnt,versionFROM view.basic_testWHERE type = 'modify';
4. 查询物化视图。
SELECT * FROM view.basic_modify_view;# 返回结果SELECT *FROM view.basic_modify_view┌─id─┬─type───┬─cnt─┬─version─┐│ 3 │ modify │ 11 │ 0 ││ 4 │ modify │ 20 │ 0 │└────┴────────┴─────┴─────────┘2 rows in set. Elapsed: 0.005 sec.
可查询到源表
basic_test
符合指定 POPULATE
关键字的数据已插入到物化视图 basic_modify_view
中。说明
如果没有指定
POPULATE
关键字,查询结果将为空。因为不指定 POPULATE
关键字时,物化视图只会包含在物化视图创建后新写入源表的数据。5. 在源表继续插入数据。
INSERT INTO view.basic_test VALUES(5, 'click', 15, 0),(6, 'click', 25, 0),(7, 'modify', 16,0),(8,'modify', 29, 0);
6. 再次查询物化视图。
SELECT * FROM view.basic_modify_view;# 返回结果SELECT *FROM view.basic_modify_view┌─id─┬─type───┬─cnt─┬─version─┐│ 3 │ modify │ 11 │ 0 ││ 4 │ modify │ 20 │ 0 │└────┴────────┴─────┴─────────┘┌─id─┬─type───┬─cnt─┬─version─┐│ 7 │ modify │ 16 │ 0 ││ 8 │ modify │ 29 │ 0 │└────┴────────┴─────┴─────────┘4 rows in set. Elapsed: 0.005 sec.
可查询到源表
basic_test
符合指定 POPULATE
关键字的数据已插入到物化视图 basic_modify_view
中。删除物化视图
语法
DROP TABLE [IF EXISTS] [db.]table_name [ON CLUSTER default_cluster]
参数说明
IF EXISTS
:可选。如果没有指定 IF EXISTS
且物化视图不存在会返回报错。db.
:可选,指定数据库的名称,默认为当前选择的数据库。table_name
:必填,物化视图表名称。ON CLUSTER default_cluster
:可选,如果创建物化视图时增加 ON CLUSTER default_cluster
,删除时也需要增加。使用示例
DROP TABLE view.basic_test_view ON CLUSTER default_cluster;
CDC 场景物化视图使用说明
原生 ClickHouse 实时 update 和 delete 性能较弱,TDSQL-H LibraDB 对 CDC 同步的数据表,使用 insert 代替 update 和 delete,额外为每个表新增字段
_sign
和 _version
,无需 FINAL 即可保证目的端的数据与源端完全一致。详细说明请参见 对 update 和 delete 优化。在 CDC 同步场景中,TDSQL-H LibraDB 并未针对物化视图表进行处理。针对数据的插入、更新和删除操作,TDSQL-H LibraDB 会使用
_sign
和 _version
这两个字段的不同组合展示数据处理过程。以下提供示例介绍在 CDC 同步场景中,源端数据更新时,TDSQL-H LibraDB 侧物化视图如何处理数据。
使用示例
1. 在待创建 CDC 任务的源端创建源表。
CREATE TABLE view.basic_test (`id` int NOT NULL,`type` varchar(50) DEFAULT '',`cnt` int DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2. 在源表中插入数据。
INSERT INTO view.basic_test VALUES(1, "click", 100),(2, "insert", 101),(3, "click", 50),(4, "insert", 40);
3. 创建 CDC 任务,具体操作请参见 添加 CDC。
其中,CDC 任务配置如下:
同步类型为初始结构 + 初始全量数据 + 增量数据。
同步对象为指定对象,源端库表为
view.basic_test
,同步到目的端名称保持不变,仍为 view.basic_test
。其它保持默认。
4. CDC 任务创建成功后,连接 LibraSQL 分析引擎后在 TDSQL-H LibraDB 中创建物化视图(包括本地表和分布式表)。
# 创建本地表,其中 b0b4b924-d555-431f-8228-51c9e4767413 为 uuid,请根据实际替换,且 SELECT 子句必须增加 _sign、_version字段。CREATE MATERIALIZED VIEW view.basic_test_view_local ON CLUSTER default_clusterENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/b0b4b924-d555-431f-8228-51c9e4767413/{shard}', '{replica}', _version)ORDER BY id POPULATE ASSELECTid,type,cnt,_sign,_versionFROM view.basic_test_local;# 创建分布式表CREATE TABLE view.basic_test_view ON CLUSTER default_cluster(`id` Int32,`type` Nullable(String),`cnt` Nullable(Int32),`_sign` Int8,`_version` UInt64)ENGINE = Distributed('default_cluster', 'view', 'basic_test_view_local', cityHash64(toString(tuple(id))));
5. 查询物化视图。
SELECT * FROM view.basic_test_view;# 返回结果SELECT *FROM view.basic_test_view┌─id─┬─type───┬─cnt─┬─_sign─┬─_version─┐│ 1 │ click │ 100 │ 1 │ 1 ││ 2 │ insert │ 101 │ 1 │ 1 ││ 3 │ click │ 50 │ 1 │ 1 ││ 4 │ insert │ 40 │ 1 │ 1 │└────┴────────┴─────┴───────┴──────────┘4 rows in set. Elapsed: 0.007 sec.
可查询到 CDC 任务源端库表
view.basic_test
数据已插入到物化视图 view.basic_test_view
中。6. 在 CDC 任务源端继续插入数据、更新数据、删除数据,观察 CDC 任务目的端物化视图的分布式表数据变化。
插入数据
在 CDC 任务源端插入数据。
INSERT INTO view.basic_test VALUES(5, "new_insert", 150);
在 CDC 任务目的端物化视图的分布式表查询数据。
SELECT * FROM view.basic_test_view;# 返回结果SELECT *FROM view.basic_test_view┌─id─┬─type───────┬─cnt─┬─_sign─┬─_version─┐│ 5 │ new_insert │ 150 │ 1 │ 65412 │└────┴────────────┴─────┴───────┴──────────┘┌─id─┬─type───┬─cnt─┬─_sign─┬─_version─┐│ 1 │ click │ 100 │ 1 │ 1 ││ 2 │ insert │ 101 │ 1 │ 1 ││ 3 │ click │ 50 │ 1 │ 1 ││ 4 │ insert │ 40 │ 1 │ 1 │└────┴────────┴─────┴───────┴──────────┘5 rows in set. Elapsed: 0.010 sec.
目的端数据变化:源端插入一条数据,目的端同步插入一条数据
_sign = 1
、_version = 65412
,即 insert 一条数据, _sign = 1
、_version
递增。删除数据
在 CDC 任务源端删除数据。
DELETE FROM view.basic_test WHERE id=5;
在 CDC 任务目的端物化视图的分布式表查询数据。
SELECT * FROM view.basic_test_view;# 返回结果SELECT *FROM view.basic_test_view┌─id─┬─type───────┬─cnt─┬─_sign─┬─_version─┐│ 5 │ new_insert │ 150 │ 1 │ 65412 │└────┴────────────┴─────┴───────┴──────────┘┌─id─┬─type───────┬─cnt─┬─_sign─┬─_version─┐│ 5 │ new_insert │ 150 │ -1 │ 66316 │└────┴────────────┴─────┴───────┴──────────┘┌─id─┬─type───┬─cnt─┬─_sign─┬─_version─┐│ 1 │ click │ 100 │ 1 │ 1 ││ 2 │ insert │ 101 │ 1 │ 1 ││ 3 │ click │ 50 │ 1 │ 1 ││ 4 │ insert │ 40 │ 1 │ 1 │└────┴────────┴─────┴───────┴──────────┘6 rows in set. Elapsed: 0.011 sec.
目的端数据变化:源端删除一条数据,目的端同步数据将 delete 转换为 insert 模式,插入一条数据
_sign = -1
、_version = 66316
,即 insert 一条数据,值为原值,_sign=-1
、_version
递增。更新数据
在 CDC 任务源端更新数据。
UPDATE view.basic_test SET cnt=300 WHERE id=1;
在 CDC 任务目的端物化视图的分布式表查询数据。
SELECT * FROM view.basic_test_view;# 返回结果SELECT *FROM view.basic_test_view┌─id─┬─type───────┬─cnt─┬─_sign─┬─_version─┐│ 5 │ new_insert │ 150 │ 1 │ 65412 │└────┴────────────┴─────┴───────┴──────────┘┌─id─┬─type───┬─cnt─┬─_sign─┬─_version─┐│ 1 │ click │ 100 │ 1 │ 1 ││ 2 │ insert │ 101 │ 1 │ 1 ││ 3 │ click │ 50 │ 1 │ 1 ││ 4 │ insert │ 40 │ 1 │ 1 │└────┴────────┴─────┴───────┴──────────┘┌─id─┬─type──┬─cnt─┬─_sign─┬─_version─┐│ 1 │ click │ 300 │ 1 │ 67021 │└────┴───────┴─────┴───────┴──────────┘┌─id─┬─type──┬─cnt─┬─_sign─┬─_version─┐│ 1 │ click │ 100 │ -1 │ 67021 │└────┴───────┴─────┴───────┴──────────┘┌─id─┬─type───────┬─cnt─┬─_sign─┬─_version─┐│ 5 │ new_insert │ 150 │ -1 │ 66316 │└────┴────────────┴─────┴───────┴──────────┘8 rows in set. Elapsed: 0.013 sec.
目的端数据变化:源端更新一条数据,目的端同步数据将 update 转换为 delete+insert 模式,具体说明如下:
6.1 插入一条数据
_sign = -1
、_version = 67021
,即 delete 一条数据,值为原值, sign=-1
、_version
递增。6.2 插入一条数据
_sign = 1
、_version = 67021
,即 insert 一条数据,值为新值, sign=1
、_version
和 delete 数据的 _version
值相同。物化视图数据处理
源端插入数据、删除数据、更新数据,目的端物化视图对数据处理过程总结如下:
插入数据
insert 一条数据,
_sign = 1
、_version
递增。删除数据
insert 一条数据,值为原值,
_sign=-1
、_version
递增。更新数据
delete 一条数据,值为原值,
sign=-1
、_version
递增。insert 一条数据,值为新值,
sign=1
、_version
和 delete 数据的 _version
值相同。