物化視圖管理 - MaxCompute

物化視圖（Materialized View）是一種預先計算的方式，通過儲存某些耗時操作（例如JOIN、AGGREGATE）的結果，以便在查詢時直接複用，從而避免重複執行這些耗時操作，最終實現加速查詢的目的。本文將介紹物化視圖相關的命令、文法格式，並提供使用樣本。

背景資訊

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

物化視圖適用於如下情境：

模式固定、且執行頻次高的查詢。
查詢包含非常耗時的操作，比如彙總、串連操作等。
查詢僅涉及表中的很小部分資料。

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

對比項

傳統查詢方式

物化視圖查詢方式

查詢語句

直接使用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';

如果物化視圖開啟了查詢改寫功能，使用如下SQL語句查詢資料時會直接從物化視圖中查詢資料：

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';

查詢特點

查詢涉及讀表、JOIN、過濾（where）操作。當源表資料量很大時，查詢速度會很慢。操作複雜度較高，運行效率低。

查詢涉及讀表、過濾操作。不涉及JOIN操作。MaxCompute會自動匹配到最優物化視圖，並直接從物化視圖中讀取資料，從而大大提高查詢效率。

物化視圖相關操作命令如下。

類型	功能	角色	操作入口
建立物化視圖支援分區和聚簇	基於查詢語句建立物化視圖。	具備專案建立表許可權（CreateTable）的使用者。	本文中的命令您可以在如下工具平台執行： MaxCompute用戶端使用DataWorks串連 MaxCompute Studio
更新物化視圖	更新已建立的物化視圖。	具備修改表許可權（Alter）的使用者。
修改物化視圖的生命週期	修改已建立的物化視圖的生命週期。	具備修改表許可權（Alter）的使用者。
開啟或禁用物化視圖的生命週期	開啟或禁用已建立的物化視圖的生命週期。	具備修改表許可權（Alter）的使用者。
查詢物化視圖資訊	查詢物化視圖的基本資料。	具備讀取表元資訊許可權（Describe）的使用者。
查詢物化檢視狀態	查看物化視圖有效或無效。	具備讀取表元資訊許可權（Describe）的使用者。
刪除物化視圖	刪除已建立的物化視圖。	具備刪除表許可權（Drop）的使用者。
刪除物化視圖分區	刪除已建立的物化視圖的分區。	具備刪除表許可權（Drop）的使用者。
物化視圖支援查詢穿透	當查詢的分區資料不存在時，需要自動實現到原始分區表去查詢資料。	具備專案寫入權限（Write）及建立表許可權（CreateTable）的使用者。
物化視圖定時更新	定時更新已建立物化視圖的資料。	具備修改表許可權（Alter）的使用者。

使用限制

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

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

建立物化視圖支援分區和聚簇

基於滿足物化視圖情境的資料建立物化視圖，支援分區和聚簇情境。

使用限制
- 物化視圖的名稱不允許和當前已有表、視圖、物化視圖名稱重複。您可以通過show tables;命令查看專案中的全部表及物化視圖名稱。
- 不支援基於存在的物化視圖建立新的物化視圖。
- 不支援基於外部表格建立物化視圖。
注意事項
- 當查詢語句執行失敗時，物化視圖也會建立失敗。
- 物化視圖分區列必須來源於某張源表，其順序和列數目必須和源表一樣，列名稱可以不一樣。
- 列注釋需要指定所有列，包含分區列。如果只指定部分列，會報錯。
- 可以同時指定分區和聚簇，此時每個分區中的資料都有指定的聚簇屬性
- 當查詢語句中包含不支援的運算元時會報錯。物化視圖支援的運算元列表，請參見基於物化視圖執行查詢改寫操作。
- 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 ON/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屬性
[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>;

參數說明
- if not exists：可選。如果沒有指定if not exists且物化視圖已經存在會返回報錯。
- project_name：可選。物化視圖所屬目標MaxCompute專案名稱。不填寫時表示當前所在MaxCompute專案。您可以登入
  MaxCompute控制台，左上方切換地區後，即可在專案管理頁簽查看到具體的MaxCompute專案名稱。
- mv_name：必填。建立物化視圖的名稱。
- days：可選。指定物化視圖的生命週期，單位為天。
- BUILD DEFERRED：可選，如果加上這個關鍵字代表建立物化視圖時，只產生表結構，不重新整理資料。
- col_name：可選。指定物化視圖的列名稱。
- col_comment：可選。指定物化視圖的列的注釋。
- DISABLE REWRITE：可選。設定禁止通過物化視圖執行查詢改寫操作。不指定時表示允許通過物化視圖執行查詢改寫操作，您可以執行alter materialized view [project_name.]<mv_name> disable rewrite;命令禁止通過物化視圖執行查詢改寫操作。同理您可以執行alter materialized view [project_name.]<mv_name> enable rewrite;命令允許通過物化視圖執行查詢改寫操作。
- partitioned on：可選。指定物化視圖分區欄位，表示建立的物化視圖表為分區表。
- clustered by|range clustered by：可選。用於建立聚簇表時設定表的Shuffle屬性。
- sorted by：可選。用於建立聚簇表時設定表的Sort屬性。
- number_of_buckets：可選。用於建立聚簇表時設定表分桶數。
- tblproperties：可選。compressionstrategy指定表資料的儲存壓縮策略，可以選normal、high或extreme。enable_auto_substitute指定當分區不存在時是否自動穿透到原始分區表去查詢資料，詳細資料請參見物化視圖支援查詢穿透。enable_auto_refresh：可選，當需要自動重新整理資料時需要設定為true。refresh_interval_minutes：條件可選，當enable_auto_refresh為true時，需要配置重新整理間隔時間，單位為分鐘。only_refresh_max_pt：可選，只適用於帶分區的物化視圖，但設定為true時，只會重新整理源表最新分區。
- select_statement：必填。查詢語句，詳細格式請參見SELECT文法。

使用樣本

樣本一：建立物化視圖。命令樣本如下。

--樣本table1
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          |
+------------+------------+------------+------------+

--樣本table2
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          |
+------------+------------+------------+------------+


--樣本1：建立以ds為分區列的物化視圖
create materialized view mf_mv lifecycle 7
(
  key comment 'unique id',
  value comment 'input value',
  ds comment 'partitiion'
  )
partitioned on (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 on (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';

樣本二：原始表分區為空白的時候，重新整理物化視圖，自動產生空分區。

create table mf_blank_pts(id bigint ,name string) partitioned by (ds bigint);
alter table mf_blank_pts add partition (ds = 1);
alter table mf_blank_pts add partition (ds = 2);
insert into table mf_blank_pts partition(ds=1) values(1,"aba"),(2,"cbd");

-- 初始化分區資料
set odps.sql.materialized.view.initial.partition={"ds": 1};
create materialized view if not exists mf_mv_blank_pts partitioned on (ds)
as select id,name,ds from mf_blank_pts;

alter materialized view mf_mv_blank_pts rebuild partition (ds>0);

show partitions mf_mv_blank_pts;
--原表分區ds=2是沒有值，當重新整理ds>0分區時，物化視圖裡有了空分區
ds=1
ds=2
select * from mf_mv_blank_pts where ds>0;
--返回只有分區1有值
+------------+------------+------------+
| id         | name       | ds         |
+------------+------------+------------+
| 1          | aba        | 1          |
| 2          | cbd        | 1          |
+------------+------------+------------+

更新物化視圖

當物化視圖的資料對應的表或分區產生插入、覆寫、更新、刪除等操作時，物化視圖會自動失效，無法用於查詢改寫。您可以先查看物化檢視狀態，當物化視圖失效時，執行更新操作。查看物化檢視狀態操作，請參見查詢物化檢視狀態。

注意事項
- 物化視圖的更新操作只會更新源表有資料變化的表或者分區。
- 您可以藉助於DataWorks調度能力實現定時更新。DataWorks的調度操作資訊，請參見調度配置。

命令格式

alter materialized view [<project_name>.]<mv_name>
      rebuild [partition(<ds>=max_pt(<table_name>),<expression1>...)];

參數說明
- project_name：可選。物化視圖所屬目標MaxCompute專案名稱。不填寫時表示當前所在MaxCompute專案。您可以登入
  MaxCompute控制台，左上方切換地區後，即可在專案管理頁簽查看到具體的MaxCompute專案名稱。
- mv_name：必填。待更新物化視圖的名稱。
- ds：可選，物化視圖分區欄位名稱。
- max_pt：可選，取指定表或者物化視圖（table_name）的最大分區值。
- expression：可選。當更新分區物化視圖時，需要指定待更新分區資訊，支援運算式。

使用樣本

樣本一：更新非分區物化視圖。命令樣本如下。
```
alter materialized view count_mv rebuild;
```
樣本二：更新分區物化視圖的某個分區。命令樣本如下。
```
alter materialized view mv rebuild partition (ds='20210101');
```
樣本三：更新分區物化視圖的滿足指定條件的分區。命令樣本如下。
```
alter materialized view mv rebuild partition(ds>='20210101', ds<='20210105');
```

樣本四：更新分區物化視圖的最新分區資料。命令樣本如下。

-- 建立樣本分區表
create table if not exists sale_detail_jt
(shop_name STRING ,
customer_id STRING ,
total_price DOUBLE )
PARTITIONED BY (sale_date STRING ,region STRING );

alter TABLE  sale_detail_jt ADD PARTITION (sale_date='2013',region='china');

INSERT INTO  sale_detail_jt PARTITION (sale_date='2013',region='china') VALUES 
    ('s1','c1',100.1),
    ('s2','c2',100.2),
    ('s3','c3',100.3);

alter TABLE  sale_detail_jt ADD PARTITION (sale_date='2013',region='en');
INSERT INTO  sale_detail_jt PARTITION (sale_date='2013',region='en') VALUES 
    ('t1','c5',200.0),
    ('t2','c6',300.0);
    
--查看分區資料
select * from sale_detail_jt where sale_date='2013' and region='china';
+-----------+-------------+-------------+-----------+--------+
| shop_name | customer_id | total_price | sale_date | region |
+-----------+-------------+-------------+-----------+--------+
| s1        | c1          | 100.1       | 2013      | china  |
| s2        | c2          | 100.2       | 2013      | china  |
| s5        | c2          | 100.2       | 2013      | china  |
+-----------+-------------+-------------+-----------+--------+
--查看分區資料
select * from sale_detail_jt where sale_date='2013' and region='en';
+-----------+-------------+-------------+-----------+--------+
| shop_name | customer_id | total_price | sale_date | region |
+-----------+-------------+-------------+-----------+--------+
| t1        | c5          | 200.0       | 2013      | en     |
| t2        | c6          | 300.0       | 2013      | en     |
+-----------+-------------+-------------+-----------+--------+

--建立物化視圖
CREATE MATERIALIZED VIEW mv_deferred
BUILD DEFERRED
AS select * from sale_detail_jt;

--查詢物化視圖 mv_deferred;
select * from mv_deferred;
--返回
+-----------+-------------+-------------+-----------+--------+
| shop_name | customer_id | total_price | sale_date | region |
+-----------+-------------+-------------+-----------+--------+
+-----------+-------------+-------------+-----------+--------+

--建立分區表
create table mf_part (id bigint,name string) partitioned by (dt string);
--插入資料
insert into mf_part partition(dt='2013') values(1,'name1'),(2,'name2');
--查詢資料
select * from mf_part where dt='2013';
--返回：
+------------+------+----+
| id         | name | dt |
+------------+------+----+
| 1          | name1 | 2013 |
| 2          | name2 | 2013 |
+------------+------+----+
--建立帶分區的物化視圖
create materialized view mv_rebuild
BUILD DEFERRED
partitioned on (dt) as
select * from mf_part;

--查詢物化視圖資料
select * from mv_rebuild where dt='2013';
--返回
+------------+------+----+
| id         | name | dt |
+------------+------+----+
+------------+------+----+
--重新整理最新分區資料
alter materialized view mv_rebuild
      rebuild partition(dt=max_pt('mf_part'));

--查詢物化視圖資料
select * from mv_rebuild where dt='2013';
--返回
+------------+------+----+
| id         | name | dt |
+------------+------+----+
| 1          | name1 | 2013 |
| 2          | name2 | 2013 |
+------------+------+----+

修改物化視圖的生命週期

修改已建立的物化視圖的生命週期。

命令格式

alter materialized view [<project_name>.]<mv_name> set lifecycle <days>;

參數說明
- project_name：可選。物化視圖所屬目標MaxCompute專案名稱。不填寫時表示當前所在MaxCompute專案。您可以登入
  MaxCompute控制台，左上方切換地區後，即可在專案管理頁簽查看到具體的MaxCompute專案名稱。
- mv_name：必填。待更新物化視圖的名稱。
- days：必填。設定物化視圖的新生命週期。單位為天。

使用樣本

--修改物化視圖的生命週期為10天。
alter materialized view mv set lifecycle 10;

開啟或禁用物化視圖的生命週期

開啟或禁用已建立的物化視圖的生命週期。

命令格式

alter materialized view [<project_name>.]<mv_name> [<pt_spec>] enable|disable lifecycle;

參數說明
- project_name：可選。物化視圖所屬目標MaxCompute專案名稱。不填寫時表示當前所在MaxCompute專案。您可以登入
  MaxCompute控制台，左上方切換地區後，即可在專案管理頁簽查看到具體的MaxCompute專案名稱。
- mv_name：必填。待開啟或禁用生命週期的物化視圖的名稱。
- pt_spec：可選。待開啟或禁用生命週期的物化視圖的分區。格式為(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)。partition_col是分區欄位，partition_col_value是分區值。
- enable|disable：必填。enable代表開啟，disable代表禁用，禁用後該分區或表就不涉及生命週期管理。
使用樣本
- 樣本一：開啟物化視圖的生命週期管理。命令樣本如下。
```
alter materialized view mv partition (ds='20210101') enable lifecycle;
```
- 樣本二：禁用物化視圖的生命週期管理。命令樣本如下。
```
alter materialized view mv partition (ds='20210101') disable lifecycle;
```

查詢物化視圖資訊

查看物化視圖的結構、修改時間等資訊。

命令格式

DESC EXTENDED [<project_name>.]<mv_name>;

參數說明
- project_name：可選。物化視圖所屬目標MaxCompute專案名稱。不填寫時表示當前所在MaxCompute專案。您可以登入
  MaxCompute控制台，左上方切換地區後，即可在專案管理頁簽查看到具體的MaxCompute專案名稱。
- mv_name：必填。待查詢物化視圖的名稱。

使用樣本

DESC EXTENDED mv;

返回結果如下：

說明

如下結果樣本需要MaxCompute用戶端升級至v0.43及以上版本，詳情請參見使用本地用戶端（odpscmd）串連。

+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$$****@***.aliyunid.com                       |
| Project:                  m****                                                |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2023-05-30 13:16:07                                      |
| LastDDLTime:              2023-05-30 13:16:07                                      |
| LastModifiedTime:         2023-05-30 13:16:07                                      |
+------------------------------------------------------------------------------------+
| MaterializedView: YES                                                              |
| ViewText: select id,name from mf_refresh                                           |
| Rewrite Enabled: true                                                              |
| AutoRefresh Enabled: true                                                          |
| Refresh Interval Minutes: 10                                                       |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
+------------------------------------------------------------------------------------+
| id       | bigint |       |               | true     | NULL         |              |
| name     | string |       |               | true     | NULL         |              |
+------------------------------------------------------------------------------------+
| Extended Info:                                                                     |
+------------------------------------------------------------------------------------+
| IsOutdated:               false                                                    |
| TableID:                  569ec712873e44b3868e79b7a8beabab                         |
| IsArchived:               false                                                    |
| PhysicalSize:             1875                                                     |
| FileNum:                  2                                                        |
| StoredAs:                 CFile                                                    |
| CompressionStrategy:      normal                                                   |
| odps.timemachine.retention.days: 1                                                        |
| ColdStorageStatus:        N/A                                                      |
| encryption_enable:        false                                                    |
+------------------------------------------------------------------------------------+
| AutoRefresh History:                                                               |
+------------------------------------------------------------------------------------+
| InstanceId                | Status     | StartTime           | EndTime             |
+------------------------------------------------------------------------------------+
| 20230619070546735ghwl1****** | TERMINATED | 2023-06-19 15:05:46 | 2023-06-19 15:05:47 |
| 20230619065545586gwllc****** | TERMINATED | 2023-06-19 14:55:45 | 2023-06-19 14:55:46 |
| 20230619064544463gcjgom****** | TERMINATED | 2023-06-19 14:45:44 | 2023-06-19 14:45:45 |
| 20230619063543334gzxs2d****** | TERMINATED | 2023-06-19 14:35:43 | 2023-06-19 14:35:44 |
| 2023061906254257gi21w2****** | TERMINATED | 2023-06-19 14:25:42 | 2023-06-19 14:25:43 |
| 20230619061540813giacg8****** | TERMINATED | 2023-06-19 14:15:41 | 2023-06-19 14:15:41 |
| 20230619060539674gswjq9****** | TERMINATED | 2023-06-19 14:05:39 | 2023-06-19 14:05:40 |
| 20230619055538578gvdjk****** | TERMINATED | 2023-06-19 13:55:38 | 2023-06-19 13:55:40 |
| 20230619054537356glqdne****** | TERMINATED | 2023-06-19 13:45:37 | 2023-06-19 13:45:38 |
| 2023061905353687gcc5pl****** | TERMINATED | 2023-06-19 13:35:36 | 2023-06-19 13:35:37 |
+------------------------------------------------------------------------------------+

查詢物化檢視狀態

查詢物化檢視狀態，以便及時知曉源表變更，確保物化視圖有效。物化檢視狀態分為如下兩種：

物化視圖有效
執行查詢語句時，MaxCompute會從物化視圖中直接查詢資料，不會從來源資料中查詢資料。
物化視圖無效
執行查詢語句時，MaxCompute無法從物化視圖中直接查詢資料，會從來源資料中查詢資料，無法實現查詢加速。

使用者可以通過下面的函數查看物化視圖的資料是否有效。

函式宣告。

Boolean materialized_view_is_valid(<mv_name>,<partition_value>);

使用樣本。
檢查mf_mv_refresh4的資料是否與原表最新的資料一致，如果一致返回true，否則返回false。
- ```
select materialized_view_is_valid("mf_mv_refresh4");
```
- ```
select materialized_view_is_valid("mf_mv_refresh_pts","20220810");
```

刪除物化視圖

刪除已建立的物化視圖。

命令格式

drop materialized view [if exists] [<project_name>.]<mv_name> [purge];

參數說明
- if exists：可選。如果沒有指定if exists且物化視圖不存在會返回報錯。
- project_name：可選。物化視圖所屬目標MaxCompute專案名稱。不填寫時表示當前所在MaxCompute專案。您可以登入
  MaxCompute控制台，左上方切換地區後，即可在專案管理頁簽查看到具體的MaxCompute專案名稱。
- mv_name：必填。待刪除物化視圖的名稱。
- purge：可選。指定purge，在刪除物化視圖時，直接刪除資料。
使用樣本
- 刪除物化視圖mv。
```
drop materialized view mv;
```
- 刪除物化視圖mv的同時也刪除資料。
```
drop materialized view mv purge;
```

刪除物化視圖分區

刪除已建立的物化視圖的單個或多個分區。

命令格式

alter materialized view [<project_name>.]<mv_name> drop [if exists] partition <pt_spec> [partition <pt_spec>, partition <pt_spec>....];

參數說明
- project_name：可選。物化視圖所屬目標MaxCompute專案名稱。不填寫時表示當前所在MaxCompute專案。您可以登入
  MaxCompute控制台，左上方切換地區後，即可在專案管理頁簽查看到具體的MaxCompute專案名稱。
- mv_name：必填。待刪除分區的分區物化視圖的名稱。
- if exists：可選。如果沒有指定if exists且物化視圖不存在會返回報錯。
- pt_spec：至少要指定一個分區。待刪除的分區。格式為(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)。partition_col是分區欄位，partition_col_value是分區值。
使用樣本
- 樣本一：刪除分區物化視圖的某個分區。命令樣本如下。
```
alter materialized view mv drop partition (ds='20210101');
```
- 樣本二：刪除分區物化視圖的滿足指定條件的分區。命令樣本如下。
```
alter materialized view mv drop partition (ds>='20210101', ds<='20210105');
```

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

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

說明

預設每個MaxCompute專案只能利用自身的物化視圖進行查詢改寫，如果需要利用其他專案中的物化視圖進行改寫，您需要在查詢語句前添加set odps.sql.materialized.view.source.project.white.list=<project_name1>,<project_name2>,<project_name3>;配置指定其他MaxCompute專案列表。如果物化視圖中帶有left/right join和union all關鍵字，需要添加：set odps.sql.materialized.view.enable.substitute.rewriting=true;Flag。

MaxCompute中物化視圖的查詢改寫支援的運算元類型及與其他產品的對照關係如下。

運算元類型	分類	MaxCompute	BigQuery	Amazon RedShift	Hive
FILTER	運算式完全符合	支援	支援	支援	支援
FILTER	運算式部分匹配	支援	支援	支援	支援
AGGREGATE	單個AGGREGATE	支援	支援	支援	支援
AGGREGATE	多個AGGREGATE	不支援	不支援	不支援	不支援
JOIN	JOIN類型	INNER JOIN	不支援	INNER JOIN	INNER JOIN
	單個JOIN	支援	不支援	支援	支援
	多個JOIN	支援	不支援	支援	支援
AGGREGATE+JOIN	-	支援	不支援	支援	支援

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

改寫帶過濾條件的查詢語句。假設建立的物化視圖如下。

create materialized view mv as select a,b,c from src where a>5;

基於建立的物化視圖執行查詢語句，查詢改寫對照如下。

原始查詢語句	改寫後的查詢語句
`select a,b from src where a>5;`	`select a,b from mv;`
`select a, b from src where a=10;`	`select a,b from mv where a=10;`
`select a, b from src where a=10 and b=3;`	`select a,b from mv where a=10 and b=3;`
`select a, b from src where a>3;`	`(select a,b from src where a>3 and a<=5) union (select a,b from mv);`
`select a, b from src where a=10 and d=4;`	改寫不成功，因為mv中沒有d列。
`select d, e from src where a=10;`	改寫不成功，因為mv中沒有d、e列。
`select a, b from src where a=1;`	改寫不成功，因為mv中沒有a=1的資料。

改寫帶彙總函式的查詢語句

如果物化視圖的SQL語句和查詢語句的彙總Key相同，那麼所有彙總函式都可以改寫，如果彙總Key不相同，只支援SUM、MIN和MAX。

假設建立的物化視圖如下。

create materialized view mv as 
select a, b, sum(c) as sum, count(d) as cnt from src group by a, b;

基於建立的物化視圖執行查詢語句，查詢改寫對照如下。

原始查詢語句	改寫後的查詢語句
`select a, sum(c) from src group by a;`	`select a, sum(sum) from mv group by a;`
`select a, count(d) from src group by a, b;`	`select a, cnt from mv;`
`select a, count(b) from (select a, b from src group by a, b) group by a;`	`select a,count(b) from mv group by a;`
`select a, count(b) from src group by a;`	改寫不成功，視圖對a、b列進行過彙總，不能再對b進行彙總。
`select a, count(c) from src group by a;`	改寫不成功，對於COUNT函數不支援重新彙總。

如果彙總函式中有distinct，當物化視圖語句和查詢語句彙總Key相同，可以改寫，否則不可以改寫。

假設建立的物化視圖如下。

create materialized view mv as 
select a, b, sum(distinct c) as sum, count(distinct d) as cnt from src group by a, b;

基於建立的物化視圖執行查詢語句，查詢改寫對照如下。

原始查詢語句	改寫後的查詢語句
`select a, count(distinct d) from src group by a, b;`	`select a, cnt from mv;`
`select a, count(c) from src group by a, b;`	改寫不成功，對於COUNT函數不支援重新彙總。
`select a, count(distinct c) from src group by a;`	改寫不成功，因為需要對a再進行彙總。

改寫帶join的查詢語句

改寫join輸入

假設建立的物化視圖如下。

create materialized view mv1 as select a, b from j1 where b > 10;
create materialized view mv2 as select a, b from j2 where b > 10;

基於建立的物化視圖執行查詢語句，查詢改寫對照如下。

原始查詢語句	改寫後的查詢語句
`select j1.a,j1.b,j2.a from (select a,b from j1 where b > 10) j1 join j2 on j1.a=j2.a;`	`select mv1.a, mv1.b, j2.a from mv1 join j2 on mv1.a=j2.a;`
`select j1.a,j1.b,j2.a from(select a,b from j1 where b > 10) j1join(select a,b from j2 where b > 10) j2on j1.a=j2.a;`	`select mv1.a,mv1.b,mv2.a from mv1 join mv2 on mv1.a=mv2.a;`

join帶過濾條件

假設建立的物化視圖如下。

--建立非分區物化視圖。
create materialized view mv1 as select j1.a, j1.b from j1 join j2 on j1.a=j2.a;
create materialized view mv2 as select j1.a, j1.b from j1 join j2 on j1.a=j2.a where j1.a > 10;
--建立分區物化視圖。
create materialized view mv lifecycle 7 partitioned on (ds) as select t1.id, t1.ds as ds from t1 join t2 on t1.id = t2.id;

基於建立的物化視圖執行查詢語句，查詢改寫對照如下。

原始查詢語句	改寫後的查詢語句
`select j1.a,j1.b from j1 join j2 on j1.a=j2.a where j1.a=4;`	`select a, b from mv1 where a=4;`
`select j1.a,j1.b from j1 join j2 on j1.a=j2.a where j1.a > 20;`	`select a,b from mv2 where a>20;`
`select j1.a,j1.b from j1 join j2 on j1.a=j2.a where j1.a > 5;`	`(select j1.a,j1.b from j1 join j2 on j1.a=j2.a where j1.a > 5 and j1.a <= 10) union select * from mv2;`
`select key from t1 join t2 on t1.id= t2.id where t1.ds='20210306';`	`select key from mv where ds='20210306';`
`select key from t1 join t2 on t1.id= t2.id where t1.ds>='20210306';`	`select key from mv where ds>='20210306';`
`select j1.a,j1.b from j1 join j2 on j1.a=j2.a where j2.a=4;`	改寫不成功，因為物化視圖沒有j2.a列。

join增加表

假設建立的物化視圖如下。

create materialized view mv as select j1.a, j1.b from j1 join j2 on j1.a=j2.a;

基於建立的物化視圖執行查詢語句，查詢改寫對照如下。

原始查詢語句	改寫後的查詢語句
`select j1.a, j1.b from j1 join j2 join j3 on j1.a=j2.a and j1.a=j3.a;`	`select mv.a, mv.b from mv join j3 on mv.a=j3.a;`
`select j1.a, j1.b from j1 join j2 join j3 on j1.a=j2.a and j2.a=j3.a;`	`select mv.a,mv.b from mv join j3 on mv.a=j3.a;`

說明

以上三種語句可以相互結合，如果查詢語句符合改寫條件，則可以改寫。
MaxCompute會選擇最佳的改寫規則運行，如果改寫後增加了一些操作，不是最優運行計劃，最終也不會被選中。

改寫帶left join的查詢語句

假設建立的物化視圖如下。

create materialized view mv lifecycle 7(
        user_id,
        job,
        total_amount
) as select t1.user_id, t1.job, sum(t2.order_amount) as total_amount 
      from user_info as t1 left join sale_order as t2 on t1.user_id=t2.user_id group by t1.user_id;

基於建立的物化視圖執行查詢語句，查詢改寫對照如下。

原始查詢語句	改寫後的查詢語句
`select t1.user_id, sum(t2.order_amout) as total_amount from user_info as t1 left join sale_order as t2 on t1.user_id=t2.user_id group by t1.user_id;`	`select user_id, total_amount from mv;`

改寫帶union all的查詢語句
假設建立的物化視圖如下。
```
create materialized view mv lifecycle 7(
        user_id,
        tran_amount,
        tran_date
) as select user_id, tran_amount, tran_date from alipay_tran union all
 select user_id, tran_amount, tran_date from unionpay_tran;
```
基於建立的物化視圖執行查詢語句，查詢改寫對照如下。
原始查詢語句
改寫後的查詢語句
select user_id, tran_amount from alipay_tran union all select user_id, tran_amount from unionpay_tran;
select user_id, total_amount from mv;

物化視圖支援查詢穿透

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

查詢透穿圖示

如果需要物化視圖支援穿透查詢能力，您需要設定如下參數：

在Session層級設定set odps.optimizer.cbo.rule.filter.black=re;，分區列會被摺疊成常量。
建立物化視圖時，在tblproperties屬性中添加"enable_auto_substitute"="true"配置。

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

建立物化視圖支援分區並且支援查詢穿透。命令樣本如下。

--將分區列摺疊為常量，實現分區動態可變。
set odps.optimizer.cbo.rule.filter.black=re;
--初始化20210101分區資料。
set odps.sql.materialized.view.initial.partition={"dt": "20210101"};
--建立分區物化視圖支援穿透。
create materialized view if not exists mv lifecycle 7 
tblproperties("enable_auto_substitute"="true") 
partitioned on (dt)
as select id, name, dt from src;

查詢表src中的分區為20210101的資料。命令樣本如下。
```
select * from mv where dt='20210101';
```

查詢物化視圖mv中的分區為20210102的資料，自動穿透到源表查詢資料。命令樣本如下。

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

查詢物化視圖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';

物化視圖定時更新

使用說明。
- 物化視圖定時更新本質上是系統後台提交了一個任務，完成時間和當前叢集資源、任務大小有關。
- 當前定時更新是全量重新整理模式，即：
  - 當分區物化視圖中分區列對應的分區基表的某個分區資料發生變化時，只會重新整理物化視圖中相應的分區，其他分區不受影響；當分區物化視圖中被關聯的其它表發生變化時，會重新整理整個物化視圖。
  - 對於非分區物化視圖，會自動檢查本次更新是否有必要，即假如涉及的源表沒有發生變化，則會跳過此次更新。
- 如果某次重新整理所用的時間超過自動重新整理指定的間隔，會自動跳過超過的時間點，等到下次最近的時間點再重新整理。
  例如，定時重新整理時間間隔設定為20分鐘，第一次重新整理時間是10:00:00時，那麼按照重新整理時間間隔，理論上第二次重新整理時間是10:20:00，第三次重新整理時間是10:40:00。
  如果10:00:00開始第一次重新整理後，一直到10:30:00重新整理才結束，那麼10:20:00的重新整理會被跳過，直到10:40:00再開始重新整理。
- 當前MaxCompute無法即時感知到設定了定時更新的物化視圖，有一定延時（30分鐘以內）。

開啟物化視圖定時更新功能。

MaxCompute提供了三種設定物化視圖定時重新整理資料的方式。

方式一：通過every 參數設定。

建立物化視圖時指定更新頻率，每N分鐘/小時/天更新一次。下一次更新時間點為上次更新資料時間點加N分鐘/小時/天。

文法命令。

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [project_name.]mv_name
[LIFECYCLE days] -- 指定生命週期
[(col_name [COMMENT col_comment], ...)] -- 列注釋
[DISABLE REWRITE] -- 指定是否用於改寫
[PARTITIONED ON (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 xx MINUTES/HOURS/DAYS] -- 設定物化視圖定時更新間隔
TBLPROPERTIES("enable_auto_refresh"="true") -- 開啟自動更新

AS <select_statement>;

使用樣本。

建立一個每10分鐘更新一次的物化視圖。

--create table
create table mf_refresh (id bigint, name string);
--insert values
insert into table mf_refresh values(1,'kyle'),(2,'tom');
--create mv
create materialized view if not exists mf_mv_refresh
refresh every 10 minutes
tblproperties("enable_auto_refresh"="true")
as select id,name from mf_refresh;
--select data from mv
select * from mf_mv_refresh;
+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
+------------+------------+

--after created mv,insert value to table
insert into table mf_refresh values(3,'jen'),(4,'cathy');

--after 10m, select the data from mv
select * from mf_mv_refresh;
+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 3          | jen        |
| 4          | cathy      |
+------------+------------+

方式二：通過tblproperties屬性的refresh_interval_minutes參數設定。

建立物化視圖時指定更新頻率，每N分鐘更新一次。下一次更新時間點為上次更新資料時間點加N分鐘。

文法命令。

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [project_name.]mv_name
[LIFECYCLE days] -- 指定生命週期
[(col_name [COMMENT col_comment], ...)] -- 列注釋
[DISABLE REWRITE] -- 指定是否用於改寫
[PARTITIONED ON (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屬性
TBLPROPERTIES("enable_auto_refresh"="true", "refresh_interval_minutes"="xx")
-- 開啟定時更新
AS <select_statement>;

使用樣本。

建立一個每20分鐘更新一次的物化視圖。

--create mv with fresh data by20 min
create materialized view if not exists mf_mv_refresh2
tblproperties("enable_auto_refresh"="true"，"refresh_interval_minutes"="20")
as select id,name from mf_refresh;

--select data from mv
select * from mf_mv_refresh2;
+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 3          | jen        |
| 4          | cathy      |
+------------+------------+
--after mv,insert value to table
insert into table mf_refresh values(5,'roger'),(6,'david');

--after 30m，select data from mv
--傳回值
+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 3          | jen        |
| 4          | cathy      |
| 5          | roger      |
| 6          | david      |
+------------+------------+

方式三：通過tblproperties屬性的refresh_cron參數設定。

根據cron定時器，可以配置定時間隔更新或者定點更新，間隔定點更新等。

文法命令。

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [project_name.]mv_name
[LIFECYCLE days] -- 指定生命週期
[(col_name [COMMENT col_comment], ...)] -- 列注釋
[DISABLE REWRITE] -- 指定是否用於改寫
[PARTITIONED ON (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屬性

TBLPROPERTIES("enable_auto_refresh"="true", "refresh_cron"="xx")
-- 開啟定時更新
AS <select_statement>;

其中refresh_cron對應的參數是QUARTZ Cron格式的字串，使用詳情請參見Cron expression examples。

使用樣本。

建立一個每5分鐘更新一次的物化視圖。

--cron base create mv
create materialized view if not exists mf_mv_refresh3
tblproperties("enable_auto_refresh"="true"，"refresh_cron"="* */5 * * * ? *")
as select id,name from mf_refresh;
--select data from mv
select * from mf_mv_refresh3;
--傳回值
+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 3          | jen        |
| 4          | cathy      |
| 5          | roger      |
| 6          | david      |
+------------+------------+
--insert value to table
insert into table mf_refresh values(7,'ethan'),(8,'cal');
--after 5min,select data from mv
select * from mf_mv_refresh3;
--傳回值
+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 7          | ethan      |
| 8          | cal        |
| 3          | jen        |
| 4          | cathy      |
| 5          | roger      |
| 6          | david      |
+------------+------------+

建立一個每天晚上8點更新一次的物化視圖。

--cron base create mv
create materialized view if not exists mf_mv_refresh4
tblproperties("enable_auto_refresh"="true"，"refresh_cron"="0 0 20 * * ? *")
as select id,name from mf_refresh;
--select data from mv
select * from mf_mv_refresh4;
--傳回值
+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 7          | ethan      |
| 8          | cal        |
| 3          | jen        |
| 4          | cathy      |
| 5          | roger      |
| 6          | david      |
+------------+------------+
--insert value to table
insert into table mf_refresh values(9,'annie'),(10,'zoe');
--when 20:00,select data from mv
select * from mf_mv_refresh4;
--傳回值
+------------+------------+
| id         | name       |
+------------+------------+
| 1          | kyle       |
| 2          | tom        |
| 9          | annie      |
| 10         | zoe        |
| 7          | ethan      |
| 8          | cal        |
| 3          | jen        |
| 4          | cathy      |
| 5          | roger      |
| 6          | david      |
+------------+------------+

更新物化視圖重新整理時間。

可以使用如下命令對已存在的物化視圖設定定時更新。

ALTER MATERIALIZED VIEW mv SET TBLPROPERTIES("enable_auto_refresh"="true", "refresh_interval_minutes"="xx");
ALTER MATERIALIZED VIEW mv SET TBLPROPERTIES("enable_auto_refresh"="true", "refresh_cron"="xx");

關閉定時重新整理功能。
可以使用如下命令關閉物化視圖定時更新功能。
```
ALTER MATERIALIZED VIEW mv SET TBLPROPERTIES("enable_auto_refresh"="false");
```
查看物化視圖更新歷史。
可以使用如下命令查看物化視圖更新歷史。
```
DESC EXTENDED mv;
```
返回結果，請參見查詢物化視圖資訊。

使用樣本

情境：假設有一張頁面訪問表visit_records，記錄了各個使用者訪問的頁面ID、使用者ID、訪問時間。使用者經常要對不同頁面的訪問量進行查詢分析。visit_records的結構如下。

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

此時，可以給visit_records表建立一個以頁面ID分組，統計各個頁面訪問次數的物化視圖，並基於物化視圖執行後續查詢操作：

執行如下語句建立物化視圖。

create materialized view count_mv as select page_id, count(*) from visit_records group by page_id;

執行查詢語句如下。
```
set odps.sql.materialized.view.enable.auto.rewriting=true; 
select page_id, count(*) from visit_records group by page_id;
```
執行該查詢語句時，MaxCompute能自動匹配到物化視圖count_mv，從count_mv中讀取彙總好的資料。

執行如下命令檢驗查詢語句是否匹配到物化視圖。

explain select page_id, count(*) from visit_records group by page_id;

返回結果如下。

job0 is root job

In Job job0:
root Tasks: M1

In Task M1:
    Data source: doc_test_dev.count_mv
    TS: doc_test_dev.count_mv
        FS: output: Screen
            schema:
              page_id (string)
              _c1 (bigint)


OK

從返回結果中的Data source可查看到當前查詢讀取的表是count_mv，說明物化視圖有效，查詢改寫成功。

計費規則

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

儲存費用
物化視圖會佔用實體儲存體空間，會產生儲存費用，隨用隨付。更多計費資訊，請參見儲存費用（隨用隨付）。
計算費用
建立、更新、查詢物化視圖及查詢改寫（物化視圖有效）過程中涉及到查詢資料，會消耗計算資源產生計算費用。
- 當MaxCompute專案的規格類型為訂用帳戶
  時，不單獨收費。
- 當MaxCompute專案的規格類型為隨用隨付
  時，按照SQL複雜度及輸入資料量計算費用。更多計費資訊，請參見SQL標準計費。您需要注意如下資訊：
  - 更新物化視圖執行的SQL與建立物化視圖執行的SQL相同，如果該物化視圖所在專案綁定的是預付費（訂用帳戶）計算資源群組，那麼會使用已經購買的預付費資源，不會有額外費用；如果綁定的是後付費資源群組，費用取決於執行SQL時輸入的資料量和複雜度。同時重新整理物化視圖後會按照實際儲存大小收取儲存費用。
  - 當物化視圖處於生效狀態時查詢改寫會從物化視圖中讀取資料，查詢語句的輸入資料量（從物化視圖讀取部分）與物化視圖相關，與物化視圖源表無關。當物化視圖處於失效狀態時不支援查詢改寫，查詢語句會直接查詢源表，查詢語句的輸入資料量與源表相關。更多查詢物化檢視狀態資訊，請參見查詢物化檢視狀態。
  - 由於多表關聯產生物化視圖會產生資料膨脹等原因，從物化視圖讀取的資料量不一定絕對小於源表，MaxCompute不能保證讀取物化視圖一定比讀取源表節省費用。

原始查詢語句	改寫後的查詢語句
`select user_id, tran_amount from alipay_tran union all select user_id, tran_amount from unionpay_tran;`	`select user_id, total_amount from mv;`