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
-
一个子句,用于指定实体化视图是否包含在存储于 Simple Storage Service(Amazon S3)中的自动和手动集群快照中。
BACKUP
的默认值为YES
。您可以指定
BACKUP NO
来节省创建快照和从快照还原时的处理时间,并减少 Simple Storage Service(Amazon S3)中所需的存储量。注意
BACKUP NO
设置不会影响自动将数据复制到集群内的其他节点,因此当发生节点故障时,指定了BACKUP NO
的表将被还原。 - table_attributes
-
用于指定实体化视图中数据的分布方式的子句,包括以下内容:
- AS query
-
一个定义实体化视图及其内容的有效
SELECT
语句。来自查询的结果集定义了实体化视图的列和行。有关创建实体化视图时的限制的信息,请参阅限制。此外,查询中使用的具体 SQL 语言结构将决定实体化视图可进行增量刷新还是完全刷新。有关刷新方法的信息,请参阅 REFRESH MATERIALIZED VIEW。有关增量刷新的限制的信息,请参阅增量刷新限制。
如果查询包含的 SQL 命令不支持递增刷新,则 Amazon Redshift 会显示一条消息,指示实体化视图将使用完全刷新。该消息可能显示,也可能不显示,具体取决于 SQL 客户端应用程序。选中
state
的 STV_MV_INFO 列可查看实体化视图使用的刷新类型。 - AUTO REFRESH
-
一个子句,用于定义是否应使用其基表中的最新更改自动刷新实体化视图。默认值为
NO
。有关更多信息,请参阅刷新实体化视图。
使用说明
要创建实体化视图,您必须具有以下权限:
-
针对架构的 CREATE 权限。
-
对基表具有表级或列级 SELECT 权限以创建实体化视图。如果您对特定列具有列级权限,则可以仅在这些列上创建实体化视图。
对实体化视图或基表的 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 外部表引用 Simple Storage Service(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 以及如何指定 DISTYLE。首先,创建一个简单的基表。
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 命令的详细信息,请参阅以下主题: