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

此子句指定是否將具體化視觀表包含在自動化和手動叢集快照中，這些快照會儲存在 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 用戶端應用程式而定。檢查 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。

  • 具體化視觀表定義包含可變函數或外部結構描述時，您無法使用 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 命令的詳細資訊，請參閱下列主題：