CREATE MATERIALIZED VIEW
基于一个或多个 Amazon Redshift 表创建实体化视图。您还可以将实体化视图建立在使用 Spectrum 或联合查询创建的外部表的基础上。有关 Spectrum 的信息,请参阅Amazon Redshift Spectrum。有关联合查询的信息,请参阅 在 Amazon Redshift 中使用联合查询来查询数据。
语法
CREATE MATERIALIZED VIEW mv_name [ BACKUP { YES | NO } ] [ table_attributes ] [ AUTO REFRESH { YES | NO } ] AS query
参数
- BACKUP
-
一个子句,指定实体化视图是否应包含在自动和手动集群快照中。
对于没有包含关键数据的实体化视图,请指定 BACKUP NO 以节省在创建快照以及从快照还原时的处理时间,并减小在 Amazon Simple Storage Service 上占用的存储空间。BACKUP NO 设置不会影响数据自动复制到集群内的其他节点,因此当发生节点故障时,指定了 BACKUP NO 的实体化视图将被还原。默认值为 BACKUP YES。
- table_attributes
-
用于指定实体化视图中数据的分布方式的子句,包括以下内容:
- AS query
-
一个定义实体化视图及其内容的有效
SELECT
语句。来自查询的结果集定义了实体化视图的列和行。有关创建实体化视图时的限制的信息,请参阅限制。此外,查询中使用的具体 SQL 语言结构将决定实体化视图可进行增量刷新还是完全刷新。有关刷新方法的信息,请参阅 REFRESH MATERIALIZED VIEW。有关增量刷新的限制的信息,请参阅增量刷新限制。
如果查询包含的 SQL 命令不支持递增刷新,则 Amazon Redshift 会显示一条消息,指示实体化视图将使用完全刷新。该消息可能显示,也可能不显示,具体取决于 SQL 客户端应用程序。选中
state
的 STV_MV_INFO 列可查看实体化视图使用的刷新类型。 - AUTO REFRESH
-
一个子句,用于定义是否应使用其基表中的最新更改自动刷新实体化视图。默认值为
NO
。有关更多信息,请参阅 刷新实体化视图。
使用说明
要创建实体化视图,您必须具有以下权限:
-
针对架构的 CREATE 权限。
-
对基表具有表级或列级 SELECT 权限以创建实体化视图。如果您对特定列具有列级权限,则可以仅在这些列上创建实体化视图。
对数据共享中的实体化视图进行增量刷新
在共享基表时,Amazon Redshift 支持对消费者数据共享中的实体化视图进行自动和增量刷新。增量刷新是一项操作,其中 Amazon Redshift 可识别上次刷新后发生的一个或多个基表中的更改,并仅更新实体化视图中的相应记录。与完全刷新相比,这项操作的运行速度更快,并且可以提高工作负载性能。您不必为了利用增量刷新而更改实体化视图的定义。
在实体化视图中使用增量刷新时,有几个限制需要注意:
无论是本地数据库还是远程数据库,实体化视图只能引用一个数据库。
增量刷新仅适用于新的实体化视图。因此,您必须删除现有的实体化视图并重新创建它们,才能进行增量刷新。
有关在数据共享中创建实体化视图的更多信息,请参阅在 Amazon Redshift 数据共享中使用视图,其中包含多个查询示例。
对实体化视图或基表的 DDL 更新
在 Amazon Redshift 中使用实体化视图时,请遵循以下有关对实体化视图或基表进行的数据定义语言 (DDL) 更新的使用说明。
-
您可以向基表添加列,而不会影响引用该基表的任何实体化视图。
-
某些操作可能会使实体化视图处于根本无法刷新的状态。例如,重命名或删除列、更改列类型、更改架构名称等此类操作。可以查询此类实体化视图,但不能对其进行刷新。在此情况下,必须删除并重新创建实体化视图。
-
通常,无法更改实体化视图的定义(其 SQL 语句)。
-
无法重命名实体化视图。
限制
您无法定义一个引用或包括以下任何内容的实体化视图:
-
标准视图或系统表和视图。
-
临时表。
-
用户定义的函数。
-
ORDER BY、LIMIT 或 OFFSET 子句。
-
对基表的后期绑定引用。换句话说,在实体化视图的定义 SQL 查询中引用的任何基表或相关列必须存在且必须有效。
-
仅领导节点函数:CURRENT_SCHEMA、CURRENT_SCHEMAS、HAS_DATABASE_PRIVILEGE、HAS_SCHEMA_PRIVILEGE、HAS_TABLE_PRIVILEGE。
当实体化视图定义包含可变函数或外部 schema 时,不能使用 AUTO REFRESH YES 选项。在一个实体化视图上定义另一个实体化视图时,也不能使用它。
您不必在实体化视图上手动运行 ANALYZE。该分析目前只通过 AUTO ANALYZE 发生。有关更多信息,请参阅 分析表。
示例
以下示例从三个联接和聚合的基表创建实体化视图。每个行均代表一个类别以及已售出的票数。查询 tickets_mv 实体化视图时,直接在 tickets_mv 实体化视图中访问预计算的数据。
CREATE MATERIALIZED VIEW tickets_mv AS select catgroup, sum(qtysold) as sold from category c, event e, sales s where c.catid = e.catid and e.eventid = s.eventid group by catgroup;
以下示例创建一个类似于上一个示例的实体化视图,并使用聚合函数 MAX()。
CREATE MATERIALIZED VIEW tickets_mv_max AS select catgroup, max(qtysold) as sold from category c, event e, sales s where c.catid = e.catid and e.eventid = s.eventid group by catgroup; SELECT name, state FROM STV_MV_INFO;
以下示例使用 UNION ALL 子句联接 Amazon Redshift public_sales
表和 Redshift Spectrum spectrum.sales
表来创建实体化视图 mv_sales_vw
。有关适用于 Amazon Redshift Spectrum 的 CREATE EXTERNAL TABLE 命令的信息,请参阅CREATE EXTERNAL TABLE。Redshift Spectrum 外部表引用 Amazon S3 上的数据。
CREATE MATERIALIZED VIEW mv_sales_vw as select salesid, qtysold, pricepaid, commission, saletime from public.sales union all select salesid, qtysold, pricepaid, commission, saletime from spectrum.sales
以下示例根据联合查询外部表创建实体化视图 mv_fq
。有关联合查询的信息,请参阅 CREATE EXTERNAL SCHEMA。
CREATE MATERIALIZED VIEW mv_fq as select firstname, lastname from apg.mv_fq_example; select firstname, lastname from mv_fq; firstname | lastname -----------+---------- John | Day Jane | Doe (2 rows)
以下示例显示了实体化视图的定义。
SELECT pg_catalog.pg_get_viewdef('mv_sales_vw'::regclass::oid, true); pg_get_viewdef --------------------------------------------------- create materialized view mv_sales_vw as select a from t;
以下示例显示了如何在实体化视图定义中设置 AUTO REFRESH 以及如何指定 DISTSTYLE。首先,创建一个简单的基表。
CREATE TABLE baseball_table (ball int, bat int);
然后,创建实体化视图。
CREATE MATERIALIZED VIEW mv_baseball DISTSTYLE ALL AUTO REFRESH YES AS SELECT ball AS baseball FROM baseball_table;
现在,您就可以查询 mv_baseball 实体化视图了。要检查是否对实体化视图开启了 AUTO REFRESH,请参阅 STV_MV_INFO。
以下示例创建了一个实体化视图,该视图引用了另一个数据库中的源表。它假设包含源表的数据库 database_A 与您在 database_B 中创建的实体化视图位于同一个集群或工作组中。(您可以用自己的数据库替换示例中的数据库。) 首先,在 database_A 中创建一个名为 cities 的表,其中包含 cityname 列。将该列的数据类型设为 VARCHAR。创建源表后,在 database_B 中运行以下命令,以创建其源为 cities 表的实体化视图。确保在 FROM 子句中指定源表的数据库和架构:
CREATE MATERIALIZED VIEW cities_mv AS SELECT cityname FROM database_A.public.cities;
查询您创建的实体化视图。该查询检索原始源为 database_A 中的 cities 表的记录:
select * from cities_mv;
当您运行 SELECT 语句时,cities_mv 会返回记录。只有在运行 REFRESH 语句时,才会刷新源表中的记录。另请注意,您不能直接在实体化视图中更新记录。有关刷新实体化视图中的数据的信息,请参阅REFRESH MATERIALIZED VIEW。
有关实体化视图概述以及用于刷新和删除实体化视图的 SQL 命令的详细信息,请参阅以下主题: