マテリアライズドビュークエリの書き換え操作 - MaxCompute

MaxComputeでは、フィルター条件や特定の演算子タイプなど、元のSQLクエリステートメントをマテリアライズドビューに書き換えて、データ取得効率を向上させることができます。このトピックでは、SQL書き換えプロセスの包括的なガイドを提供します。

制限事項

クエリの書き換えにマテリアライズドビューを活用するには、クエリステートメントの前に構成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>; を追加する必要があります。 left/right joinまたはunion allを含むマテリアライズドビューの場合、set odps.sql.materialized.view.enable.substitute.rewriting=true; の設定を有効にします。
マテリアライズドビューのクエリの書き換えを機能させるには、出力列、フィルター条件列、集計関数列、JOIN条件列など、必要なデータがマテリアライズドビューに存在する必要があります。必要な列または集計関数がマテリアライズドビューでサポートされていない場合、書き換えは実行できません。

Rewrite examples

次の表は、MaxComputeのマテリアライズドビューのクエリ書き換えでサポートされる演算子の種類と、他の製品との対応関係を示しています。

演算子タイプ	分類	MaxCompute	BigQuery	Amazon RedShift	ハイブ
フィルター	フルマッチ式	対応	対応	対応	対応
フィルター	式の部分一致	対応	対応	対応	対応
AGGREGATE	シングルAGGREGATE	対応	対応	対応	対応
AGGREGATE	複数のAGGREGATE	非対応	非対応	非対応	非対応
JOIN	JOINタイプ	INNER JOIN	非対応	INNER JOIN	INNER JOIN
	シングルJOIN	対応	非対応	対応	対応
	複数の参加	対応	非対応	対応	対応
AGGREGATE + 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;`	マテリアライズドビューに列dがないため、書き換えに失敗しました。
`SELECT d, e FROM src WHERE a=10;`	マテリアライズドビューに列dとeがないため、書き換えに失敗しました。
`SELECT a, b FROM src WHERE a=1;`	具体化されたビューにa=1のデータがないため、書き換えに失敗しました。

集計関数を使用したクエリステートメントの書き換え

マテリアライズド・ビューのSQL文とクエリ文が同じ集計キーを持つ場合、書き換えがサポートされます。集約キーが異なる場合、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が含まれている場合、マテリアライズドビューのSQLステートメントとクエリステートメントが同じ集計キーを持つ場合、書き換えがサポートされます。それ以外の場合、書き換えはサポートされません。

次のステートメントでマテリアライズド・ビューを作成します。

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;`	の追加の集計が必要なため、書き換えに失敗しました。

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;

フィルター条件に参加する

次のステートメントでマテリアライズド・ビューを作成します。

--Create a non-partitioned materialized view.
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 a partitioned materialized view.
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がないため、書き換えに失敗しました。

追加のテーブルに参加する

次のステートメントでマテリアライズド・ビューを作成します。
```
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つのタイプのステートメントを組み合わせることができます。クエリ文が書き換えの条件を満たしている場合は、それに応じて書き換えることができます。
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_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;`

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;

使用シナリオ

各ユーザーのページID、ユーザーID、およびアクセス時間を記録するvisit_recordsという名前のページアクセステーブルがあります。ユーザーは頻繁に異なるページのトラフィックを分析します。 visit_recordsの構造は次のとおりです。

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

分析を容易にするには、ページIDでグループ化し、ページごとの訪問数をカウントするvisit_recordsテーブルの具体化ビューを作成します。次に、マテリアライズドビューに基づいてクエリ操作を実行します。

次のステートメントでマテリアライズド・ビューを作成します。

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を自動的に照合し、そこから集計データを取得します。
次のコマンドを使用して、クエリ文がマテリアライズドビューと一致するかどうかを確認します。
```
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
```
結果のデータソースは、クエリがdoc_test_devプロジェクトのテーブルcount_mvから読み取り、マテリアライズドビューの有効性とクエリの書き換えが成功したことを確認することを示しています。

元のクエリ文	書き換えられたクエリ文
`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;`