MaxCompute:Réécriture de requête avec vue matérialisée

Exemple 1 : Réécriture avec 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 les données où a=1.

Exemple 2 : Réécriture avec 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 avec 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;

Exemple 6 : Cas d'utilisation

1. Scénario

   Prenons l'exemple d'une table de visites de pages nommée visit_records qui enregistre l'ID de la page, l'ID de l'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, créez une vue matérialisée sur visit_records qui regroupe les données par ID de page et compte les visites pour chaque page. Exécutez ensuite des requêtes ultérieures sur 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;

   Lors de l'exécution de cette instruction de requête, MaxCompute fait correspondre automatiquement 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 confirme que la vue matérialisée est efficace et que la réécriture de la requête a réussi.