TDSQL-H LibraDB 物化视图

一、语法

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 子句查询进行转换并将最终结果插入到物化视图中。

注意：说明：

三、使用示例

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 ASSELECT id, type, cnt, versionFROM view.basic_testWHERE type = 'modify';

4. 查询物化视图。

可查询到源表 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 ASSELECT id, 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 任务目的端物化视图的分布式表查询数据。

目的端数据变化：源端插入一条数据，目的端同步插入一条数据 _sign = 1 、_version = 65412，即 insert 一条数据， _sign = 1、_version 递增。

·删除数据
在 CDC 任务源端删除数据。

DELETE FROM view.basic_test WHERE id=5;

在 CDC 任务目的端物化视图的分布式表查询数据。

目的端数据变化：源端删除一条数据，目的端同步数据将 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 值相同。

十、参考文档

创建物化视图的更多信息，请参见 Create Materialized View。

TDSQL-H LibraDB 物化视图

一、语法

二、参数说明

三、使用示例

四、语法

五、参数说明

六、使用示例

七、CDC 场景物化视图使用说明

八、使用示例

九、物化视图数据处理

十、参考文档

相关内容：