MaxCompute:CREATE MATERIALIZED VIEW

As materialized views são indicadas para os seguintes cenários:

• Consultas executadas frequentemente com um padrão fixo.

• Consultas que envolvem operações demoradas, como agregações e joins.

• Consultas que acessam apenas um pequeno subconjunto de dados de uma tabela.

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

Criar uma materialized view

1. Crie tabelas chamadas mf_t e mf_t1 e insira dados nessas tabelas.

   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. Crie uma materialized view.

   • Exemplo 1: Crie uma materialized view contendo uma coluna de chave de partição chamada 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          |
     +------------+------------+------------+

   • Exemplo 2: Crie uma materialized view não particionada e clusterizada.

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

   • Exemplo 3: Crie uma materialized view particionada e clusterizada.

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

Implementar reescrita de consulta baseada em materialized view

1. Cenário

   Considere uma tabela de visitas a páginas chamada visit_records que registra o ID da página, o ID do usuário e o horário de cada visita. Uma tarefa frequente de análise consiste em contar o número de visitas para diferentes páginas.

   Nessa situação, crie uma materialized view sobre visit_records que agrupe por ID da página e conte as visitas de cada página. Em seguida, execute consultas subsequentes nessa materialized view.

   A estrutura de visit_records é a seguinte:

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

2. Crie uma materialized view.

   -- 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. Execute a seguinte consulta:

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

   Ao executar essa instrução de consulta, o MaxCompute associa automaticamente a materialized view count_mv e lê os dados pré-agregados de count_mv.

4. Para verificar se a consulta foi reescrita usando a materialized view, execute o seguinte comando EXPLAIN:

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

   O resultado retornado é:

   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

   O campo Data source no resultado retornado mostra que a tabela lida pela consulta é a count_mv do projeto doc_test_dev. Isso indica que a materialized view está ativa e a reescrita de consulta foi bem-sucedida.

Exemplo 1: Reescrita com condições de filtro

1. Crie uma materialized view.

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

2. A tabela a seguir apresenta exemplos de reescrita para a materialized view.

   Consulta original	Consulta reescrita
   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;	A reescrita falha porque a materialized view não contém a coluna d.
   SELECT d, e FROM src WHERE a=10;	A reescrita falha porque a materialized view não contém as colunas d e e.
   SELECT a, b FROM src WHERE a=1;	A reescrita falha porque a materialized view não contém dados onde a=1.

Exemplo 2: Reescrita com funções de agregação

Exemplo 3: Reescrita com cláusula JOIN

Esses três cenários de reescrita de JOIN podem ser combinados.

Como o objetivo da reescrita de consulta em materialized view é acelerar as consultas, o MaxCompute prioriza regras de reescrita que oferecem o melhor desempenho. Uma regra não será aplicada se introduzir operações que resultem em baixa aceleração.

Exemplo 4: Reescrita com cláusula LEFT JOIN

1. Crie uma materialized view.

   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. A tabela a seguir mostra como uma consulta é reescrita com base na materialized view.

   Consulta original	Consulta reescrita
   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;

Exemplo 5: Reescrita com cláusula UNION ALL

1. Crie uma materialized view.

   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. A tabela a seguir mostra como uma consulta é reescrita com base na materialized view.

   Consulta original	Consulta reescrita
   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;

Para ativar o recurso de consulta transparente em uma materialized view, defina o seguinte parâmetro:

Ao criar a materialized view, adicione a configuração "enable_auto_substitute"="true" em tblproperties.