物化视图

最近更新时间：2022-09-01 11:15:04
我的收藏
本页目录：

物化视图（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_cluster
ENGINE = ReplacingMergeTree(version)
ORDER BY id POPULATE AS
SELECT
  id,
  type,
  cnt,
  version
FROM view.basic_test
WHERE 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_cluster
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/b0b4b924-d555-431f-8228-51c9e4767413/{shard}', '{replica}', _version)
ORDER BY id POPULATE AS
SELECT
 id,
 type,
 cnt,
 _sign,
 _version
FROM 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 值相同。
参考文档
创建物化视图的更多信息，请参见 Create Materialized View。
上一篇: 使用 JDBC 连接 TDSQL-H LibraDB 下一篇: 修改键列数据类型