MaxCompute：CREATE MATERIALIZED VIEW

背景資訊

視圖是一種虛擬表，任何對視圖的查詢，都會轉換為視圖SQL語句的查詢。而物化視圖是一種特殊的物理表，物化視圖會儲存實際的資料，佔用儲存資源。更多物化視圖計費資訊，請參見計費規則。

物化視圖與傳統查詢的對比如下。

SELECT empid，deptname  
FROM emps JOIN depts 
ON emps.deptno=depts.deptno 
WHERE hire_date >= '2018-01-01';

CREATE MATERIALIZED VIEW mv 
AS SELECT empid，deptname, hire_date  
FROM emps JOIN depts 
ON emps.deptno=depts.deptno 
WHERE hire_date >= '2016-01-01';

SELECT empid, deptname FROM mv 
WHERE hire_date >= '2018-01-01';

SELECT empid，deptname 
FROM emps JOIN depts 
ON emps.deptno=depts.deptno 
WHERE hire_date >= '2018-01-01';
--相當於如下語句。
SELECT empid, deptname FROM mv 
WHERE hire_date >= '2018-01-01';

計費規則

物化視圖費用包含如下兩部分：

• 儲存費用

  物化視圖會佔用實體儲存體空間，會產生儲存費用，隨用隨付。更多計費資訊，請參見儲存費用（隨用隨付）。

• 計算費用

  建立、更新、查詢物化視圖及查詢改寫（物化視圖有效）過程中涉及到查詢資料，會消耗計算資源產生計算費用。

  • 當MaxCompute專案的規格類型為訂用帳戶時，不單獨收費。

  • 當MaxCompute專案的規格類型為隨用隨付時，按照SQL複雜度及輸入資料量計算費用。更多計費資訊，請參見SQL標準計費。您需要注意如下資訊：

    • 更新物化視圖執行的SQL與建立物化視圖執行的SQL相同，如果該物化視圖所在專案綁定的是預付費（訂用帳戶）計算資源群組，那麼會使用已經購買的預付費資源，不會有額外費用；如果綁定的是後付費資源群組，費用取決於執行SQL時輸入的資料量和複雜度。同時重新整理物化視圖後會按照實際儲存大小收取儲存費用。

    • 當物化視圖處於生效狀態時查詢改寫會從物化視圖中讀取資料，查詢語句的輸入資料量（從物化視圖讀取部分）與物化視圖相關，與物化視圖源表無關。當物化視圖處於失效狀態時不支援查詢改寫，查詢語句會直接查詢源表，查詢語句的輸入資料量與源表相關。更多查詢物化檢視狀態資訊，請參見查詢物化檢視狀態。

    • 由於多表關聯產生物化視圖會產生資料膨脹等原因，從物化視圖讀取的資料量不一定絕對小於源表，MaxCompute不能保證讀取物化視圖一定比讀取源表節省費用。

使用限制

物化視圖的使用限制如下：

• 不支援視窗函數。

• 不支援UDTF函數。

• 預設不支援非確定性函數（例如UDF、UDAF等）。當您的業務情境必須要使用非確定性函數時，請在Session層級設定屬性set odps.sql.materialized.view.support.nondeterministic.function=true;。

注意事項

• 當查詢語句執行失敗時，物化視圖也會建立失敗。

• 物化視圖分區列必須來源於某張源表，其順序和列數目必須和源表一樣，列名稱可以不一樣。

• 列注釋需要指定所有列，包含分區列。如果只指定部分列，會報錯。

• 可以同時指定分區和聚簇，此時每個分區中的資料都有指定的聚簇屬性。

• 當查詢語句中包含不支援的運算元時會報錯。物化視圖支援的運算元列表，請參見基於物化視圖執行查詢改寫操作。

• MaxCompute預設不支援使用非確定性函數（例如UDF、UDAF等）建立物化視圖。當您的業務情境必須要使用非確定性函數時，請在Session層級設定屬性set odps.sql.materialized.view.support.nondeterministic.function=true;。

• 物化視圖支援產生空分區，原始表分區為空白的時候，重新整理物化視圖，自動產生空分區。

命令格式

CREATE MATERIALIZED VIEW [IF NOT EXISTS][project_name.]<mv_name>
[LIFECYCLE <days>]    --指定生命週期
[BUILD DEFERRED]    -- 指定是在建立時只產生表結構，不產生資料
[(<col_name> [COMMENT <col_comment>],...)]    --列注釋
[DISABLE REWRITE]    --指定是否用於改寫
[COMMENT 'table comment']    --表注釋
[PARTITIONED BY (<col_name> [, <col_name>, ...])    --建立物化視圖表為分區表
[CLUSTERED BY|RANGE CLUSTERED BY (<col_name> [, <col_name>, ...])
[SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])]
    INTO <number_of_buckets> BUCKETS]    --用於建立聚簇表時設定表的Shuffle和Sort屬性
[REFRESH EVERY <num> MINUTES/HOURS/DAYS] 
[TBLPROPERTIES("compressionstrategy"="normal/high/extreme",    --指定表資料存放區壓縮策略
                "enable_auto_substitute"="true",    --指定當分區不存在時是否轉化視圖來查詢
                "enable_auto_refresh"="true",    --指定是否開啟自動重新整理
                "refresh_interval_minutes"="120",    --指定重新整理時間間隔
                "only_refresh_max_pt"="true"    --針對分區物化視圖，只自動重新整理源表最新分區
                )]
AS <select_statement>;

參數說明

使用樣本

建立物化視圖

1. 建立mf_t和mf_t1兩張表並插入資料。

   CREATE TABLE IF NOT EXISTS mf_t( 
        id     bigint, 
        value   bigint, 
        name   string) 
   PARTITIONED BY (ds STRING); 
   
   ALTER TABLE mf_t ADD PARTITION (ds='1'); 
   INSERT INTO mf_t PARTITION (ds='1') VALUES (1,10,'kyle'),(2,20,'xia'); 
   SELECT * FROM mf_t WHERE ds ='1'; 
   -- 返回結果如下。
   +------------+------------+------------+------------+
   | id         | value      | name       | ds         |
   +------------+------------+------------+------------+
   | 1          | 10         | kyle       | 1          |
   | 2          | 20         | xia        | 1          |
   +------------+------------+------------+------------+
   
   CREATE TABLE IF NOT EXISTS mf_t1( 
        id     bigint, 
        value   bigint, 
        name   string) 
   PARTITIONED BY (ds STRING); 
   
   ALTER TABLE mf_t1 ADD PARTITION (ds='1'); 
   INSERT INTO mf_t1 PARTITION (ds='1') VALUES (1,10,'kyle'),(3,20,'john'); 
   SELECT * FROM mf_t1 WHERE ds ='1';
   -- 返回結果如下。
   +------------+------------+------------+------------+
   | id         | value      | name       | ds         |
   +------------+------------+------------+------------+
   | 1          | 10         | kyle       | 1          |
   | 3          | 20         | john       | 1          |
   +------------+------------+------------+------------+

2. 建立物化視圖。

   • 樣本1：建立以ds為分區列的物化視圖。

     CREATE MATERIALIZED VIEW mf_mv LIFECYCLE 7 
     (
       key comment 'unique id',
       value comment 'input value',
       ds comment 'partitiion'
       )
     PARTITIONED BY (ds)
     AS SELECT t1.id AS key, t1.value AS value, t1.ds AS ds
          FROM mf_t AS t1 JOIN mf_t1 AS t2
            ON t1.id = t2.id AND t1.ds=t2.ds AND t1.ds='1';
     --查詢物化視圖
     SELECT * FROM mf_mv WHERE ds =1;
     +------------+------------+------------+
     | key        | value      | ds         |
     +------------+------------+------------+
     | 1          | 10         | 1          |
     +------------+------------+------------+

   • 樣本2：建立帶有聚簇屬性的非分區物化視圖。

     CREATE MATERIALIZED VIEW mf_mv2 LIFECYCLE 7 
     CLUSTERED BY (key) SORTED BY (value) INTO 1024 buckets 
     AS  SELECT t1.id AS key, t1.value AS value, t1.ds AS ds 
           FROM mf_t AS t1 JOIN mf_t1 AS t2 
             ON t1.id = t2.id AND t1.ds=t2.ds AND t1.ds='1';

   • 樣本3：建立帶有聚簇屬性的分區物化視圖。

     CREATE MATERIALIZED VIEW mf_mv3 LIFECYCLE 7 
     PARTITIONED BY (ds) 
     CLUSTERED BY (key) SORTED BY (value) INTO 1024 buckets 
     AS  SELECT t1.id AS key, t1.value AS value, t1.ds AS ds 
           FROM mf_t AS t1 JOIN mf_t1 AS t2 
             ON t1.id = t2.id AND t1.ds=t2.ds AND t1.ds='1';

改寫物化視圖

+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| page_id         | string     |       |                                             |
| user_id         | string     |       |                                             |
| visit_time      | string     |       |                                             |
+------------------------------------------------------------------------------------+

基於物化視圖執行查詢改寫操作

物化視圖最重要的作用就是對查詢語句進行查詢改寫，如果期望查詢語句能利用物化視圖進行查詢改寫，則需要在查詢語句前添加set odps.sql.materialized.view.enable.auto.rewriting=true;配置。當物化視圖處於失效狀態時無法用於查詢改寫，查詢語句會直接查詢源表而無法獲得加速作用。

使用物化視圖查詢改寫的原則是查詢語句中需要的資料必須從物化視圖中得到，包括輸出資料行、篩選條件中需要的列、彙總函式需要的列、JOIN條件需要的列。如果查詢語句中需要的列不包含在物化視圖中或彙總函式不支援，則無法基於物化視圖進行查詢改寫。

物化視圖查詢穿透

對於分區物化視圖，不一定所有分區都有資料，可能只重新整理了最新的一些分區資料。但使用者查詢資料時，實際並不知道查詢的所有分區資料是否都存在，當查詢的分區資料不存在時，需要自動實現到原始分區表去查詢資料，流程如下圖所示。

物化視圖支援穿透查詢樣本如下。

1. 建立物化視圖支援分區並且支援查詢穿透。

   -- 建立src表。
   CREATE TABLE src(id bigint,name string) PARTITIONED BY (dt string);
   -- 插入資料。
   INSERT INTO src PARTITION(dt='20210101') VALUES(1,'Alex');
   INSERT INTO src PARTITION(dt='20210102') VALUES(2,'Flink');
   
   --建立分區物化視圖支援穿透。
   CREATE MATERIALIZED VIEW IF NOT EXISTS mv LIFECYCLE 7 
   PARTITIONED BY (dt) 
   tblproperties("enable_auto_substitute"="true") 
   AS SELECT id, name, dt FROM src;

2. 查詢表src中的分區為20210101的資料。

   SELECT * FROM mv WHERE dt='20210101';

3. 查詢物化視圖mv中的分區為20210102的資料，自動穿透到源表查詢資料。

   SELECT * FROM mv WHERE dt = '20210102';
   --因為20210102的資料沒有物化，則需要把查詢轉化到對應的源表，等價於
   SELECT * FROM (SELECT id, name, dt FROM src WHERE dt='20210102') t;

4. 查詢物化視圖mv中分區為20201230~20210102的資料，自動穿透到源表查詢的資料與物化視圖的資料執行UNION操作後再返回結果。

   SELECT * FROM mv WHERE dt >= '20201230' AND dt<='20210102' AND id='5'; 
   --因為20210102的資料沒有物化，則需要把查詢轉化到對應的源表。等價於：
   SELECT * FROM
   (SELECT id, name, dt FROM src WHERE dt='20211231' OR dt='20210102'
    UNION ALL  
    SELECT * FROM mv WHERE dt='20210101'
   ) t WHERE id = '5';

相關命令

• ALTER MATERIALIZED VIEW：更新物化視圖、修改物化視圖的生命週期、開啟或禁用物化視圖的生命週期和刪除物化視圖分區。

• DESC TABLE/VIEW：查看MaxCompute物化視圖的資訊。

• SELECT MATERIALIZED VIEW：查詢物化檢視狀態。

• DROP MATERIALIZED VIEW：刪除已建立的物化視圖。