如何使用CREATEMATERIALIZEDVIEW建立物化視圖 - MaxCompute

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

背景資訊

視圖是一種虛擬表，任何對視圖的查詢，都會轉換為視圖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會自動匹配到最優物化視圖，並直接從物化視圖中讀取資料，從而大大提高查詢效率。

計費規則

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

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

使用限制

物化視圖的使用限制如下。
- 不支援視窗函數。
- 不支援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;。

命令格式

set odps.sql.materialized.view.initial.partition={"分區列": "分區值"};
--可選項，指定初始分區，避免產生過多無用分區。
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]   
[tblproperties("compressionstrategy"="normal/high/extreme","enable_auto_substitute"="true")]
as <select_statement>;

參數說明

if not exists：可選。如果沒有指定if not exists且物化視圖已經存在會返回報錯。
project_name：可選。物化視圖所屬目標MaxCompute專案名稱。不填寫時表示當前所在MaxCompute專案。您可以登入MaxCompute控制台，在左上方切換地區後，即可在工作區 > 專案管理頁面查看到具體的MaxCompute專案名稱。
mv_name：必填。建立物化視圖的名稱。
days：可選。指定物化視圖的生命週期，單位為天。
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指定當分區不存在時是否自動穿透到原始分區表去查詢資料，詳細資料請參見物化視圖支援查詢穿透。
select_statement：必填。查詢語句，詳細格式請參見SELECT文法。

使用樣本

樣本一：建立以ds為分區列的物化視圖。命令樣本如下。

create materialized view mv lifecycle 7
(
  key comment 'unique id',
  ds comment 'date'
) 
partitioned on (ds) 
as select t1.id, t1.value, t1.ds as ds from t1 join t2 on t1.id = t2.id;

樣本二：建立帶有聚簇屬性的非分區物化視圖。命令樣本如下。

create materialized view mv lifecycle 7 
clustered by id sorted by value into 1024 buckets  
as select t1.id, t1.value, t1.ds as ds from t1 join t2 on t1.id = t2.id;

樣本三：建立帶有聚簇屬性的分區物化視圖。命令樣本如下。

create materialized view mv lifecycle 7 
partitioned on (ds) 
clustered by id sorted by value into 1024 buckets 
as select t1.id, t1.value, t1.ds as ds from t1 join t2 on t1.id = t2.id;

樣本四：假設有一張頁面訪問表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，說明物化視圖有效，查詢改寫成功。

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

物化視圖最重要的作用就是對查詢語句進行查詢改寫，如果期望查詢語句能利用物化視圖進行查詢改寫，則需要在查詢語句前添加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專案列表。

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

MaxCompute：CREATE MATERIALIZED VIEW

背景資訊

計費規則

使用限制

注意事項

命令格式

參數說明

使用樣本

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

物化視圖支援查詢穿透

相關命令

原始查詢語句	改寫後的查詢語句
`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;`