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';
-- The preceding statement is equivalent to the following statement: 
SELECT empid, deptname FROM mv 
WHERE hire_date >= '2018-01-01';

課金ルール

マテリアライズド・ビューを使用すると、次の項目に対して課金されます。

• ストレージ

  マテリアライズドビューは物理ストレージスペースを占有します。 マテリアライズドビューによって占有されている物理ストレージスペースに対して課金されます。 ストレージ料金の詳細については、「ストレージ料金 (従量課金) 」をご参照ください。

• コンピューティング

  マテリアライズドビューの作成、更新、クエリ、およびクエリの書き換え (マテリアライズドビューが有効な場合) に、データがクエリされます。 これらの操作はコンピューティングリソースを消費し、コンピューティングコストを発生します。

  • MaxComputeプロジェクトがサブスクリプション課金方法を使用している場合

    、余分なコンピューティングコストは発生しません。

  • MaxComputeプロジェクトで従量課金を使用している場合

    、料金はSQLの複雑さと入力データの量に基づいて計算されます。 課金の詳細については、「標準SQLジョブの課金」セクションを参照してください。 コンピューティング価格。 以下の事項にご注意ください。

    • マテリアライズド・ビューの更新に使用されるSQL文は、マテリアライズド・ビューの作成に使用されるSQL文と同じです。 マテリアライズドビューが属するプロジェクトがサブスクリプションリソースグループにバインドされている場合、購入したサブスクリプションリソースは使用され、追加料金は発生しません。 プロジェクトが従量課金制のリソースグループにバインドされている場合、料金は入力データの量とSQL文の複雑さによって異なります。 マテリアライズドビューを更新すると、使用されているストレージスペースに基づいてストレージ料金が課金されます。

    • マテリアライズド・ビューが有効である場合、クエリ書き換え操作が実行されると、マテリアライズド・ビューからデータが読み取られます。 クエリステートメントのマテリアライズドビューから読み取られる入力データの量は、マテリアライズドビューに関連しており、マテリアライズドビューのソーステーブルとは無関係である。 マテリアライズドビューが無効な場合、クエリの書き換え操作は実行できず、ソーステーブルからデータがクエリされます。 クエリステートメントの入力データの量は、ソーステーブルに関連しています。 マテリアライズドビューのステータスを照会する方法の詳細については、このトピックの「マテリアライズドビューのステータスの照会」をご参照ください。

    • 複数のテーブルの関連付けに基づいてマテリアライズドビューが生成されると、データ膨張が発生する可能性があります。 したがって、具体化されたビューによって読み取られるデータの量は、ソーステーブル内のデータの量よりも絶対に少なくない可能性があります。 MaxComputeでは、マテリアライズドビューからのデータ読み取りのコストがソーステーブルからのデータ読み取りよりも低くなるようにすることはできません。

制限事項

マテリアライズド・ビューを使用する前に、次の制限事項に注意してください。

• ウィンドウ関数はサポートされていません。

• ユーザー定義のテーブル値関数 (UDTF) はサポートされていません。

• デフォルトでは、ユーザー定義スカラー関数 (UDF) やユーザー定義集計関数 (UDAF) などの非決定論的関数はサポートされていません。 非決定性関数を使用する必要がある場合は、セッションレベルでset odps.sql.materialized.view.support.nondeterministic.function=true; コマンドを実行します。

注意事項

• マテリアライズドビューの作成に基づくクエリ文の実行に失敗した場合、マテリアライズドビューは作成できません。

• マテリアライズドビューのパーティションキー列は、ソーステーブルから派生する必要があります。 マテリアライズドビューの列のシーケンスと数は、ソーステーブルの列のシーケンスと数と同じである必要があります。 列名は異なる場合があります。

• パーティションキー列を含むすべての列にコメントを指定する必要があります。 一部の列にのみコメントを指定すると、エラーが返されます。

• マテリアライズド・ビューのパーティショニング属性とクラスタリング属性の両方を指定できます。 この場合、各パーティションのデータには、指定されたクラスタリング属性があります。

• マテリアライズド・ビューの作成に基づくクエリ文に、マテリアライズド・ビューでサポートされていない演算子が含まれている場合は、エラーが返されます。 マテリアライズドビューでサポートされている演算子の詳細については、「マテリアライズドビューに基づいてクエリの書き換え操作を実行する」をご参照ください。

• デフォルトでは、MaxComputeでは、UDFやUDAFなどの非決定性関数を使用してマテリアライズドビューを作成することはできません。 ビジネス要件に基づいて非決定性関数を使用する必要がある場合は、セッションレベルでset odps.sql.materialized.view.support.nondeterministic.function=true; コマンドを実行します。

• マテリアライズドビューのソーステーブルに空のパーティションが含まれている場合は、マテリアライズドビューを更新して、マテリアライズドビューに空のパーティションを生成できます。

構文

CREATE MATERIALIZED VIEW [IF NOT EXISTS][project_name.]<mv_name>
[LIFECYCLE <days>]    -- The lifecycle of the materialized view.
[BUILD DEFERRED]    -- Specifies that only the schema is created and no data is updated when you create the materialized view.
[(<col_name> [COMMENT <col_comment>],...)]    -- The column comments.
[DISABLE REWRITE] -- Specifies whether to disable the query rewrite operation that is performed based on the materialized view.
[COMMENT 'table comment']    -- The materialized view comments.
[PARTITIONED BY (<col_name> [, <col_name>, ...])    -- The partitions in the materialized view. This parameter is required when you create a partitioned materialized view.
[CLUSTERED BY|RANGE CLUSTERED BY (<col_name> [, <col_name>, ...])
     [SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])]
                 INTO <number_of_buckets> BUCKETS]    -- The shuffle and sort attributes of the materialized view. This parameter is required when you create a clustered materialized view.
[REFRESH EVERY <num> MINUTES/HOURS/DAYS]
[TBLPROPERTIES("compressionstrategy"="normal/high/extreme",    -- The compression policy for data storage of the materialized view.
                "enable_auto_substitute"="true",    -- Specifies whether to query data from the source partitioned table if the partition data that you want to query is not contained in the materialized view.
                "enable_auto_refresh"="true",    -- Specifies whether to enable the scheduled update feature.
                "refresh_interval_minutes"="120",    -- The update interval.
                "only_refresh_max_pt"="true"    -- Specifies whether to automatically update the latest partition data in the source table to the materialized view.
                )]
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'; 
   -- The following result is returned: 
   +------------+------------+------------+------------+
   | 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';
   -- The following result is returned: 
   +------------+------------+------------+------------+
   | 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';
     -- Query data from the created materialized view.
     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. 侵入クエリをサポートするパーティション分割マテリアライズドビューを作成します。

   -- Create a table named src. 
   CREATE TABLE src(id bigint,name string) PARTITIONED BY (dt string);
   -- Insert data into the table. 
   INSERT INTO src PARTITION(dt='20210101') VALUES(1,'Alex');
   INSERT INTO src PARTITION(dt='20210102') VALUES(2,'Flink');
   
   -- Create a partitioned materialized view that supports penetration query. 
   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. mvマテリアライズドビューからsrcテーブル内の20210101パーティションのデータを照会します。

   SELECT * FROM mv WHERE dt='20210101';

3. mvマテリアライズドビューからsrcテーブル内の20210102パーティションのデータを照会します。 mv具体化ビューにはパーティションデータが含まれていません。 したがって、srcテーブルからパーティションデータを照会するために、侵入照会が実行される。

   SELECT * FROM mv WHERE dt = '20210102';
   -- The preceding statement is equivalent to the following statement because the mv materialized view does not contain the data of the 20210102 partition and the partition data needs to be queried from the src table.
   SELECT * FROM (SELECT id, name, dt FROM src WHERE dt='20210102') t;

4. mvマテリアライズドビューから20210102 20201230のパーティションのデータを照会します。 mvマテリアライズドビューにすべてのパーティションデータが含まれるわけではありません。 したがって、mv具体化ビューに含まれていないデータを取得するために侵入クエリが実行され、取得されたデータおよびmv具体化ビューからクエリされたデータに対してUNION操作が実行され、最終結果が返される。

   SELECT * FROM mv WHERE dt >= '20201230' AND dt<='20210102' AND id='5'; 
   -- The mv materialized view does not contain the data of the 20210102 partition and a penetration query is performed to query the data of the 20210102 partition from the source table. The preceding statement is equivalent to the following statement:
   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: 既存のマテリアライズドビューを削除します。