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用戶端應用程式。檢查 STV_ MV_ INFO 的 state 資料欄，以查看具體化檢視所使用的重新整理類型。

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。

  • 當具體化視觀表定義包含可變函數或外部結構描述時，您就無法使用 AUTOREFRESHYES選項。當您在另一個具體化視觀表上定義具體化視觀表時，也無法使用此選項。

  • 您不需要在具體化視觀表上手動執行 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;

下列範例使用 UNIONALL子句來加入 Amazon Redshift public_sales資料表和 Redshift Spectrum spectrum.sales資料表，以建立材料檢視mv_sales_vw。如需有關 Amazon Redshift Spectrum CREATEEXTERNALTABLE命令的資訊，請參閱 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;

下列範例示範如何在具體化檢視定義AUTOREFRESH中設定 ，並指定 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 具體化視觀表。若要檢查具體化視觀表AUTOREFRESH是否已開啟，請參閱 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陳述式時，city_mv 會傳回記錄。只有在執行REFRESH陳述式時，才會從來源資料表重新整理記錄。此外，請注意，您無法直接在具體化視觀表中更新記錄。如需有關重新整理具體化視觀表中資料的資訊，請參閱 REFRESH MATERIALIZED VIEW。

如需具體化檢視概觀的詳細資訊，以及用於重新整理和捨棄具體化檢視的SQL命令，請參閱下列主題：