MaxCompute:CREATE MATERIALIZED VIEW

Les vues matérialisées conviennent aux scénarios suivants :

• Requêtes fréquemment exécutées suivant un modèle fixe.

• Requêtes impliquant des opérations chronophages, telles que les agrégations et les jointures.

• Requêtes n'accédant qu'à un petit sous-ensemble des données d'une table.

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [project_name.]<mv_name>
[LIFECYCLE <days>]    --Specifies the lifecycle.
[BUILD DEFERRED]    --Creates the schema without populating data.
[(<col_name> [COMMENT <col_comment>], ...)]    --Column comments.
[DISABLE REWRITE]    --Specifies whether the materialized view can be used for query rewrite.
[COMMENT 'table comment']    --Table comment.
[PARTITIONED BY (<col_name> [, <col_name>, ...])]    --Creates the materialized view as a partitioned table.
[CLUSTERED BY | RANGE CLUSTERED BY (<col_name> [, <col_name>, ...])
  [SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])]
    INTO <number_of_buckets> BUCKETS]    --Sets the shuffle and sort properties for a clustered table.
[REFRESH EVERY <num> {MINUTES | HOURS | DAYS}] 
[TBLPROPERTIES("compressionstrategy"="{normal|high|extreme}",    --Specifies the data storage compression strategy for the table.
                "enable_auto_substitute"="true",    --Specifies whether to enable query passthrough to the source table when a partition does not exist.
                "enable_auto_refresh"="true",    --Specifies whether to enable automatic refresh.
                "refresh_interval_minutes"="120",    --Specifies the refresh interval.
                "only_refresh_max_pt"="true"    --For partitioned materialized views, automatically refreshes only the latest partition from the source table.
                )]
AS <select_statement>;

Créer une vue matérialisée

1. Créez les tables nommées mf_t et mf_t1 et insérez-y des données.

   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. Créez une vue matérialisée.

   • Exemple 1 : Créez une vue matérialisée contenant une colonne de clé de partition nommée ds.

     CREATE MATERIALIZED VIEW mf_mv LIFECYCLE 7 
     (
       key comment 'unique id',
       value comment 'input value',
       ds comment 'partition'
       )
     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 the materialized view.
     SELECT * FROM mf_mv WHERE ds =1;
     +------------+------------+------------+
     | key        | value      | ds         |
     +------------+------------+------------+
     | 1          | 10         | 1          |
     +------------+------------+------------+

   • Exemple 2 : Créez une vue matérialisée non partitionnée et clusterisée.

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

   • Exemple 3 : Créez une vue matérialisée partitionnée et clusterisée.

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

Mettre en œuvre la réécriture de requête basée sur une vue matérialisée

1. Scénario

   Considérons une table de visites de pages nommée visit_records qui enregistre l'ID de page, l'ID utilisateur et l'heure de visite pour chaque consultation. Une tâche d'analyse fréquente consiste à compter le nombre de visites pour différentes pages.

   Dans cette situation, vous pouvez créer une vue matérialisée sur visit_records qui regroupe les données par ID de page et compte les visites pour chaque page. Vous pouvez ensuite exécuter des requêtes ultérieures contre cette vue matérialisée.

   La structure de visit_records est la suivante :

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

2. Créez une vue matérialisée.

   -- Create a materialized view for the visit_records table that groups by page ID and counts the visits for each page.
   
   CREATE MATERIALIZED VIEW count_mv AS SELECT page_id, count(*) FROM visit_records GROUP BY page_id;

3. Exécutez la requête suivante :

   SET odps.sql.materialized.view.enable.auto.rewriting=true; 
   SELECT page_id, count(*) FROM visit_records GROUP BY page_id;

   Lorsque cette instruction de requête est exécutée, MaxCompute fait automatiquement correspondre la vue matérialisée count_mv et lit les données pré-agrégées depuis count_mv.

4. Pour vérifier que la requête a été réécrite à l'aide de la vue matérialisée, exécutez la commande EXPLAIN suivante :

   EXPLAIN SELECT page_id, count(*) FROM visit_records GROUP BY page_id;

   Le résultat suivant est renvoyé :

   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

   La source de données Data source dans le résultat renvoyé indique que la table lue par la requête est la vue count_mv du projet doc_test_dev. Cela signifie que la vue matérialisée est effective et que la réécriture de requête a réussi.

Exemple 1 : Réécriture avec des conditions de filtrage

1. Créez une vue matérialisée.

   CREATE MATERIALIZED VIEW mv AS SELECT a,b,c FROM src WHERE a>5;

2. Le tableau suivant présente des exemples de réécriture pour la vue matérialisée.

   Requête d'origine	Requête réécrite
   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;	Échec de la réécriture car la vue matérialisée ne contient pas la colonne d.
   SELECT d, e FROM src WHERE a=10;	Échec de la réécriture car la vue matérialisée ne contient pas les colonnes d et e.
   SELECT a, b FROM src WHERE a=1;	Échec de la réécriture car la vue matérialisée ne contient pas de données où a=1.

Exemple 2 : Réécriture avec des fonctions d'agrégation

Exemple 3 : Réécriture avec une clause JOIN

Ces trois scénarios de réécriture JOIN peuvent être combinés.

L'objectif de la réécriture de requête via une vue matérialisée étant d'accélérer les requêtes, MaxCompute privilégie les règles de réécriture offrant les meilleures performances. Une règle n'est pas appliquée si elle introduit des opérations qui entraîneraient une faible accélération.

Exemple 4 : Réécriture avec une clause LEFT JOIN

1. Créez une vue matérialisée.

   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;

2. Le tableau suivant illustre la réécriture d'une requête basée sur la vue matérialisée.

   Requête d'origine	Requête réécrite
   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;

Exemple 5 : Réécriture avec une clause UNION ALL

1. Créez une vue matérialisée.

   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;

2. Le tableau suivant illustre la réécriture d'une requête basée sur la vue matérialisée.

   Requête d'origine	Requête réécrite
   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;

Pour activer la fonctionnalité de requête transparente pour une vue matérialisée, définissez le paramètre suivant :

Lors de la création de la vue matérialisée, ajoutez la configuration "enable_auto_substitute"="true" aux tblproperties.