MaxCompute:CREATE MATERIALIZED VIEW

Quando usar uma materialized view

Materialized views são adequadas para os seguintes cenários de consulta:

• Consultas executadas frequentemente e que seguem um padrão fixo.

• Consultas que envolvem operações demoradas, como JOINs ou agregações.

• Consultas que leem apenas uma pequena parte de uma tabela grande.

Se nenhuma dessas condições se aplicar — por exemplo, se o padrão de consulta mudar com frequência ou se o conjunto de dados for pequeno — uma view comum ou uma consulta direta pode ser mais apropriada.

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [project_name.]<mv_name>
[LIFECYCLE <days>]                          -- Lifecycle of the materialized view, in days.
[BUILD DEFERRED]                            -- Create schema only; do not populate data.
[(<col_name> [COMMENT <col_comment>], ...)] -- Column comments.
[DISABLE REWRITE]                           -- Disable query rewrite for this materialized view.
[COMMENT 'table_comment']                   -- Comment for the materialized view.
[PARTITIONED BY (<col_name> [, <col_name>, ...])]
[CLUSTERED BY | RANGE CLUSTERED BY (<col_name> [, <col_name>, ...])
    [SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])]
    INTO <number_of_buckets> BUCKETS]
[REFRESH EVERY <num> MINUTES | HOURS | DAYS]
[TBLPROPERTIES (
    "compressionstrategy" = "normal | high | extreme",
    "enable_auto_substitute" = "true",      -- Fall through to source table when partition is missing.
    "enable_auto_refresh" = "true",         -- Enable scheduled refresh.
    "refresh_interval_minutes" = "120",     -- Refresh interval in minutes.
    "only_refresh_max_pt" = "true"          -- Refresh only the latest partition from the source table.
)]
AS <select_statement>;

Criar uma materialized view

Os exemplos a seguir utilizam duas tabelas source particionadas: mf_t e mf_t1.

Etapa 1: Crie as tabelas source e insira dados.

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';
-- Result:
-- +------------+------------+------------+------------+
-- | 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';
-- Result:
-- +------------+------------+------------+------------+
-- | id         | value      | name       | ds         |
-- +------------+------------+------------+------------+
-- | 1          | 10         | kyle       | 1          |
-- | 3          | 20         | john       | 1          |
-- +------------+------------+------------+------------+

Etapa 2: Crie uma materialized view. Os exemplos a seguir mostram três configurações comuns.

Exemplo 1: Materialized view particionada

Cria uma materialized view com uma coluna de chave de partição ds, ciclo de vida de 7 dias e comentários nas colunas.

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;
-- Result:
-- +------------+------------+------------+
-- | key        | value      | ds         |
-- +------------+------------+------------+
-- | 1          | 10         | 1          |
-- +------------+------------+------------+

Exemplo 2: Materialized view não particionada com clustering

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: Materialized view particionada e com clustering

Combina particionamento e clustering. Os dados dentro de cada partição são distribuídos em buckets.

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

Usar reescrita de consulta para acelerar consultas

Este exemplo usa uma tabela de acesso a páginas visit_records que registra page_id, user_id e visit_time. Consultas de análise de tráfego que contam visitas por página são executadas frequentemente.

Etapa 1: Crie uma materialized view que pré-compute as contagens de visitas por página.

CREATE MATERIALIZED VIEW count_mv
AS SELECT page_id, count(*) FROM visit_records GROUP BY page_id;

Etapa 2: Ative a reescrita de consulta e execute a consulta original.

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

O MaxCompute corresponde automaticamente a consulta à count_mv e lê os resultados agregados dela, em vez de verificar visit_records.

Etapa 3: Verifique se a consulta foi reescrita usando EXPLAIN.

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

Saída esperada:

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 confirma que a consulta lê dados de count_mv no projeto doc_test_dev, e não da tabela original visit_records.

Configurar estratégias de atualização

Os três exemplos abaixo demonstram diferentes maneiras de manter uma materialized view atualizada.

Apenas atualização manual (padrão)

Nenhuma cláusula de atualização é especificada. Atualize a view explicitamente usando ALTER MATERIALIZED VIEW.

CREATE MATERIALIZED VIEW sales_mv
AS SELECT region, sum(amount) AS total FROM sales GROUP BY region;

Atualização agendada usando REFRESH EVERY

Atualiza a view automaticamente a cada 2 horas.

CREATE MATERIALIZED VIEW sales_mv
REFRESH EVERY 2 HOURS
AS SELECT region, sum(amount) AS total FROM sales GROUP BY region;

Atualização agendada usando TBLPROPERTIES

Define um intervalo de atualização de 120 minutos e atualiza automaticamente apenas a partição mais recente da tabela source.

CREATE MATERIALIZED VIEW sales_mv LIFECYCLE 7
PARTITIONED BY (ds)
TBLPROPERTIES (
    "enable_auto_refresh"      = "true",
    "refresh_interval_minutes" = "120",
    "only_refresh_max_pt"      = "true"
)
AS SELECT region, ds, sum(amount) AS total FROM sales GROUP BY region, ds;

SET odps.sql.materialized.view.enable.auto.rewriting=true;

SET odps.sql.materialized.view.source.project.white.list=<project_name1>,<project_name2>,<project_name3>;

Operadores suportados para reescrita de consulta

Para que a reescrita de consulta tenha êxito, todas as colunas necessárias pela consulta — colunas de saída, colunas de filtro, colunas de função de agregação e colunas de JOIN — devem estar presentes na materialized view.

Reescrever consultas com condições de filtro

Todos os exemplos nesta seção usam a seguinte materialized view:

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

Reescrever consultas com funções de agregação

Quando as chaves de agregação correspondem à materialized view, a reescrita é suportada para todas as funções de agregação. Quando as chaves de agregação diferem, apenas SUM, MIN e MAX são suportadas.

Todos os exemplos usam:

CREATE MATERIALIZED VIEW mv AS
SELECT a, b, sum(c) AS sum, count(d) AS cnt FROM src GROUP BY a, b;

Quando DISTINCT é usado, a reescrita é suportada apenas quando as chaves de agregação correspondem exatamente à materialized view.

Todos os exemplos usam:

CREATE MATERIALIZED VIEW mv AS
SELECT a, b, sum(DISTINCT c) AS sum, count(DISTINCT d) AS cnt FROM src GROUP BY a, b;

Reescrever consultas com JOIN

Reescrever entradas de JOIN

Quando as entradas de JOIN de uma consulta correspondem à definição de uma materialized view, o MaxCompute substitui essas entradas pela materialized view.

Crie materialized views:

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;

JOIN com condições de filtro

Crie materialized views:

-- Non-partitioned
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;
-- Partitioned
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;

JOIN com tabelas adicionais

Crie a materialized view:

CREATE MATERIALIZED VIEW mv AS SELECT j1.a, j1.b FROM j1 JOIN j2 ON j1.a = j2.a;

Os três tipos de reescrita de JOIN podem ser combinados. Se uma consulta atender às condições para múltiplas regras de reescrita, o MaxCompute selecionará o plano de execução mais eficiente. Se o plano reescrito não superar o original, a reescrita não será aplicada.

Reescrever consultas com LEFT JOIN

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

Reescrever consultas com UNION ALL

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

-- Create source table.
CREATE TABLE src (id bigint, name string) PARTITIONED BY (dt string);

-- Insert data into two partitions.
INSERT INTO src PARTITION(dt='20210101') VALUES (1, 'Alex');
INSERT INTO src PARTITION(dt='20210102') VALUES (2, 'Flink');

-- Create a partitioned materialized view with penetration query enabled.
CREATE MATERIALIZED VIEW IF NOT EXISTS mv LIFECYCLE 7
PARTITIONED BY (dt)
TBLPROPERTIES ("enable_auto_substitute" = "true")
AS SELECT id, name, dt FROM src;

SELECT * FROM mv WHERE dt = '20210101';

SELECT * FROM mv WHERE dt = '20210102';
-- Equivalent to:
SELECT * FROM (SELECT id, name, dt FROM src WHERE dt = '20210102') t;

SELECT * FROM mv WHERE dt >= '20201230' AND dt <= '20210102' AND id = '5';
-- Equivalent to:
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';