CREATE MATERIALIZED VIEW - Amazon Redshift
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

CREATE MATERIALIZED VIEW

基于可以使用 Spectrum 或联合查询创建的一个或多个 Amazon Redshift 表或外部表创建具体化视图。有关 Spectrum 的信息,请参阅使用 Amazon Redshift Spectrum 查询外部数据。有关联合查询的信息,请参阅在 Amazon Redshift 中使用联合查询来查询数据

语法

CREATE MATERIALIZED VIEW mv_name
[ BACKUP { YES | NO } ]
[ table_attributes ]   
AS query 

参数

BACKUP

一个子句,用于指定具体化视图是否包含在存储于 Amazon S3 中的自动和手动集群快照中。

BACKUP 的默认值为 YES

您可以指定 BACKUP NO 来节省创建快照和从快照还原时的处理时间,并减少 Amazon S3 中所需的存储量。

注意

BACKUP NO 设置不会影响自动将数据复制到集群内的其他节点,因此当发生节点故障时,指定了 BACKUP NO 的表将被还原。

table_attributes

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

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

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

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

AS query

一个定义具体化视图及其内容的有效 SELECT 语句。来自查询的结果集定义了具体化视图的列和行。有关创建具体化视图时的限制的信息,请参阅限制

此外,查询中使用的具体 SQL 语言结构将决定具体化视图可进行增量刷新还是完全刷新。有关刷新方法的信息,请参阅 REFRESH MATERIALIZED VIEW。有关增量刷新的限制的信息,请参阅增量刷新限制

如果查询包含的 SQL 命令不支持增量刷新,则 Amazon Redshift 会显示一条消息,指示具体化视图将使用完全刷新。该消息可能显示,也可能不显示,具体取决于 SQL 客户端应用程序。例如,psql 显示该消息,而 JDBC 客户端可能不显示该消息。选中 STV_MV_INFOstate 列可查看具体化视图使用的刷新类型。

使用说明

要创建具体化视图,您必须具有以下权限:

  • 针对架构的 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、AGE、CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME、NOW。

  • 日期函数:CURRENT_DATE、DATE, DATE_PART、DATE_TRUNC、DATE_CMP_TIMESTAMPTZ、SYSDATE、TIMEOFDAY、TO_TIMESTAMP。定义具体化视图时,请考虑以下采用特定输入参数类型的函数:DATE 对于时间戳是不可变的;DATE_PART 对于日期、时间、间隔和 time-tz 是不可变的;DATE_TRUNC 对于以下数据类型是不可变的:日期、时间戳和间隔。必须使用不可变的函数才能成功创建具体化视图。否则,Amazon Redshift 会阻止创建包含可变函数的具体化视图。有关函数的更多信息,请参阅函数稳定性类别

  • 数学函数:RANDOM。

  • 日期类型格式化函数:TO_CHAR WITH TIMESTAMPTZ。

示例

以下示例从三个联接和聚合的基表创建具体化视图。每个行均代表一个类别以及已售出的票数。查询 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()。您可以通过查询 STV_MV_INFO 表进行验证并查看“state”列是否为 0。

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 name | state -----------------+-------- tickets_mv | 1 tickets_mv_max | 0

以下示例使用 UNION ALLE 子句连接 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;

有关具体化视图概述以及用于刷新和删除具体化视图的 SQL 命令的详细信息,请参阅以下主题: