CREATE MATERIALIZED VIEW - Amazon Redshift
语法参数使用说明对数据共享中的实体化视图进行增量刷新对实体化视图或基表的 DDL 更新限制示例

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

用于指定实体化视图中数据的分布方式的子句，包括以下内容：

  • 实体化视图的分配方式，格式为 DISTSTYLE { EVEN | ALL | KEY }。如果忽略此子句，则分配方式为 EVEN。有关更多信息，请参阅 分配方式。

  • 实体化视图的分配键，格式为 DISTKEY ( distkey_identifier )。有关更多信息，请参阅 指定分配方式。

  • 实体化视图的排序键，格式为 SORTKEY ( column_name [, ...] )。有关更多信息，请参阅 排序键。

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 命令的详细信息，请参阅以下主题：