マテリアライズドビューのクエリリライト - MaxCompute - Alibaba Cloud ドキュメントセンター

MaxCompute は、クエリにフィルター条件や特定のオペレータータイプが含まれている場合、元の SQL クエリをマテリアライズドビューを使用するようにリライトできます。

注意事項

マテリアライズドビューのクエリリライトの核心となる原則は、マテリアライズドビューがクエリに必要なすべてのデータを含んでいる必要があることです。これには、出力カラム、およびフィルター条件、集計関数、または JOIN 条件で使用されるカラムが含まれます。マテリアライズドビューに含まれていないカラムが必要な場合、またはサポートされていない集計関数を使用している場合、クエリをリライトすることはできません。
マテリアライズドビューのクエリリライトを有効にするには、クエリステートメントの前に次の設定を追加します。
```
SET odps.sql.materialized.view.enable.auto.rewriting=true;
```
マテリアライズドビューが無効な状態の場合、クエリリライトはサポートされません。この場合、クエリは高速化されることなく、ソーステーブルに対して直接実行されます。
クロスプロジェクトリライト

デフォルトでは、MaxCompute プロジェクトは、クエリリライトに独自のマテリアライズドビューのみを使用できます。他のプロジェクトのマテリアライズドビューを使用するには、クエリの前に次の設定を追加して、許可された MaxCompute プロジェクトのリストを指定する必要があります。
```
SET odps.sql.materialized.view.source.project.white.list = <project_name1>,<project_name2>,<project_name3>;
```
LEFT/RIGHT JOIN または UNION ALL で定義されたマテリアライズドビューによる書き換えを有効にするには、クエリ文の前に次の設定を追加します。
```
SET odps.sql.materialized.view.enable.substitute.rewriting=true;
```

サポートされているオペレータータイプ

次の表では、MaxCompute がサポートするクエリリライトのオペレータータイプを、他の製品のものと比較しています。

オペレータータイプ	分類	MaxCompute	BigQuery	Amazon Redshift	Hive
FILTER	完全一致	サポート	サポート	サポート	サポート
FILTER	部分一致	サポート	サポート	サポート	サポート
AGGREGATE	単一のAGGREGATE	サポート	サポート	サポート	サポート
AGGREGATE	複数のAGGREGATE	サポート外	サポート外	サポート外	サポート外
JOIN	JOINタイプ	INNER JOIN	サポート外	INNER JOIN	INNER JOIN
	単一のJOIN	サポート	サポート外	サポート	サポート
	複数のJOIN	サポート	サポート外	サポート	サポート
AGGREGATE+JOIN	-	サポート	サポート外	サポート	サポート

例

例1：フィルター条件を使用したリライト

マテリアライズドビューを作成します。

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;`	マテリアライズドビューにカラム `d` が含まれていないため、リライトは失敗します。
`SELECT d, e FROM src WHERE a=10;`	マテリアライズドビューにカラム `d` および `e` が含まれていないため、リライトは失敗します。
`SELECT a, b FROM src WHERE a=1;`	マテリアライズドビューに `a=1` のデータが含まれていないため、リライトは失敗します。

例2：集計関数を使用したリライト

マテリアライズドビューとクエリが同じ集計キーを共有している場合、すべての集計関数をリライトできます。集計キーが異なる場合、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 mv GROUP BY a;`	ビューではすでにカラム `a` と `b` が集計されているため、カラム `b` を再度集計することはできず、リライトは失敗します。
`SELECT a, count(c) FROM src GROUP BY a;`	`COUNT` 関数の再集計はサポートされていないため、リライトは失敗します。

集計関数に DISTINCT が含まれている場合、クエリの書き換えは、マテリアライズドビューと元のクエリの集計キーが同じ場合にのみ可能です。それ以外の場合、書き換えは不可能です。

マテリアライズドビューを作成します。

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` に対して別の集計が必要なため、リライトは失敗します。

例3：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) j1 
JOIN (SELECT a,b FROM j2 WHERE b > 10) j2 ON 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 BY (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;

これら3つの JOIN リライトシナリオは組み合わせることができます。

マテリアライズドビューのクエリリライトの目的はクエリの高速化であるため、MaxCompute は最もパフォーマンスの高いリライトルールを優先します。パフォーマンスの低下を招く操作が含まれる場合、そのルールは適用されません。

例4：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, t1.job;

次の表は、マテリアライズドビューに基づいてクエリがどのようにリライトされるかを示しています。

元のクエリ	リライトされたクエリ
`SELECT t1.user_id, 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 user_id, total_amount FROM mv;`

例5：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, tran_amount FROM mv;`

例6：ユースケース

シナリオ

visit_records という名前のページ訪問テーブルがあり、各訪問のページ ID、ユーザー ID、訪問時刻を記録しているとします。

この場合、visit_records に対してマテリアライズドビューを作成し、ページ 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 コマンドを実行します。
```
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 は、クエリが読み取ったテーブルが doc_test_dev プロジェクトの count_mv であることを示します。これは、マテリアライズドビューが有効であり、クエリリライトが成功したことを意味します。

MaxCompute:マテリアライズドビューのクエリリライト

注意事項

サポートされているオペレータータイプ

例

例1：フィルター条件を使用したリライト

例2：集計関数を使用したリライト

例3：JOIN句を使用したリライト

JOIN 入力のリライト

フィルター条件を使用した JOIN

JOIN の拡張

例4：LEFT JOIN句を使用したリライト

例5：UNION ALL句を使用したリライト

例6：ユースケース

関連ドキュメント