Referência de sintaxe CREATE MATERIALIZED VIEW - AnalyticDB: Central de Documentação do Alibaba Cloud

Este tópico descreve a instrução CREATE MATERIALIZED VIEW. Use esta instrução para criar uma materialized view com política de atualização completa ou rápida e definir seu cronograma de atualização.

Sintaxe

CREATE [OR REPLACE] MATERIALIZED VIEW mv_name
[mv_definition]
[mv_properties]
[COMMENT 'view_comment']
[REFRESH {COMPLETE|FAST}]
[ON {DEMAND|OVERWRITE}]
[START WITH date] [NEXT date]
[{DISABLE|ENABLE} QUERY REWRITE]
AS 
query_body

mv_definition:
  ({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'column_comment']
  | table_constraints}
  [, ... ])
  [table_attribute]
  [partition_options]
  [index_all]
  [storage_policy]
  [block_size]
  [engine]
  [table_properties]

Parâmetros

OR REPLACE	Opcional		Alterável após a criação: Não
Clusters com versão de kernel 3.1.4.7 ou posterior suportam este parâmetro. Se não existir uma materialized view com o mesmo nome, o sistema cria uma nova. Caso já exista uma materialized view com o mesmo nome, o sistema gera uma visualização temporária, preenche-a com os novos dados e substitui a materialized view original.
mv_definition	Opcional		Alterável após a criação: Não
Define o schema da materialized view. Ao omitir esta cláusula, o sistema infere o schema a partir do query_body. Por padrão, o sistema define uma chave primária, cria índices em todas as colunas, configura a política de armazenamento como hot storage e utiliza o mecanismo XUANWU. Para definir manualmente o schema de uma materialized view, incluindo chave de distribuição, chave de partição, chave primária, índices e política de armazenamento de dados quentes/frios, use a mesma sintaxe da instrução CREATE TABLE. Por exemplo, caso não deseje indexar todas as colunas, use a palavra-chave INDEX para especificar quais colunas indexar. Para reduzir custos de armazenamento, também é possível definir uma política mista de armazenamento quente/frio ou até mesmo reter apenas os dados do último ano. Regras de chave primária Atualização completa: Se nenhuma chave primária for definida explicitamente, o sistema gera automaticamente a coluna `__adb_auto_id__` como chave primária da materialized view. Para definir uma chave primária explicitamente, use qualquer coluna da saída do query_body como chave primária da materialized view. `fast refresh`: A chave primária, seja definida explicitamente ou gerada automaticamente, deve seguir estas regras: Em consultas de agregação agrupada (consultas de agregação com cláusula `GROUP BY`), a chave primária deve corresponder às colunas do `GROUP BY`. Por exemplo, ao usar `GROUP BY a,b`, a chave primária deve ser a e b. Para consultas de agregação não agrupadas (sem cláusula `GROUP BY`), a chave primária deve ser uma constante. Nas consultas sem agregação, a chave primária deve ser idêntica à da tabela base. Por exemplo, se a chave primária da tabela base for `PRIMARY KEY(sale_id,sale_date)`, a chave primária da materialized view também deverá ser `PRIMARY KEY(sale_id,sale_date)`. Recomendações Para obter desempenho ideal nas consultas, recomenda-se definir uma chave primária, chave de distribuição e chave de partição ao criar uma materialized view.
mv_properties	Opcional		Alterável após a criação: Sim (usando ALTER MATERIALIZED VIEW)
Clusters das edições Enterprise, Basic e Data Lakehouse com versão de kernel 3.1.9.3 ou posterior suportam este parâmetro. Define a política de recursos para a materialized view. Isso inclui `mv_resource_group` para alocação de recursos e `mv_refresh_hints` para configuração da tarefa de atualização. O valor deve estar no formato JSON. Exemplo: `MV_PROPERTIES='{ "mv_resource_group":"<resource_group_name>", "mv_refresh_hints":{"<hint_name>":"<hint_value>"} }'` mv_resource_group Especifica o grupo de recursos usado para criar e atualizar a materialized view. Se não especificado, o sistema utiliza o grupo de recursos padrão `user_default`. O valor deste parâmetro pode ser um grupo de recursos Interactive ou Job do mecanismo XIHE. Grupos de recursos Job provisionam recursos sob demanda, o que geralmente introduz latência de segundos ou minutos. Caso tenha alta tolerância à latência de atualização, especifique um grupo de recursos Job. Uma materialized view que utiliza um grupo de recursos Job também é conhecida como materialized view elástica. Para aumentar a velocidade de atualização de uma materialized view elástica, configure o parâmetro elastic_job_max_acu em `mv_refresh_hints` para modificar a quantidade máxima de recursos que a materialized view pode utilizar. Para detalhes de uso, consulte a seção Exemplo de Materialized View Elástica abaixo. Visualize os grupos de recursos disponíveis para seu cluster na página Resource Groups no console ou chamando a API DescribeDBResourceGroup. A operação falhará se o grupo de recursos especificado não existir. mv_refresh_hints Especifica parâmetros de configuração para a materialized view. Para obter uma lista dos parâmetros suportados e seus usos, consulte Common hints.
REFRESH [COMPLETE \| FAST]	Opcional	Valor padrão: COMPLETE	Alterável após a criação: Não
Define a política de atualização da materialized view. Para informações sobre as diferenças entre as políticas de atualização e seus casos de uso, consulte Select a refresh policy. COMPLETE Uma atualização completa executa o SQL da consulta original para verificar os dados de todas as partições alvo na tabela base e substitui totalmente os dados antigos pelos recém-calculados. A atualização completa suporta os mecanismos de acionamento `ON DEMAND [START WITH date] [NEXT date]` e `ON OVERWRITE`, permitindo realizar atualizações manuais sob demanda, agendar atualizações automáticas ou atualizar automaticamente quando a tabela base for sobrescrita. FAST As versões 3.1.9.0 e posteriores suportam este parâmetro. A versão 3.1.9.0 suporta atualização rápida apenas para materialized views de tabela única. As versões 3.2.0.0 e posteriores suportam atualização rápida tanto para materialized views de tabela única quanto de múltiplas tabelas. Executa uma atualização rápida. O sistema reescreve a consulta da view (`query_body`) para verificar apenas os dados alterados (provenientes de operações `INSERT`, `DELETE` e `UPDATE`) na tabela base e aplica essas alterações à materialized view. Isso evita a verificação de toda a tabela base em cada ciclo e reduz o custo computacional de cada atualização. Antes de criar uma materialized view que utiliza atualização rápida, ative o recurso de binary logging tanto para o cluster quanto para as tabelas base. Caso contrário, a criação falhará. Para instruções, consulte Enable the binary logging feature. Para uma materialized view com atualização rápida, o mecanismo de acionamento deve ser uma atualização automática agendada. Defina o horário da próxima atualização usando `ON DEMAND {NEXT date}`. A atualização rápida possui algumas limitações. Se o `query_body` não suportar atualização rápida, ocorrerá um erro ao tentar criar a materialized view.
ON [DEMAND \| OVERWRITE]	Opcional	Valor padrão: DEMAND	Alterável após a criação: Não
Define o mecanismo de acionamento da atualização da materialized view. Para informações sobre as diferenças entre os mecanismos de acionamento e seus casos de uso, consulte Select a refresh trigger mechanism. DEMAND Atualização sob demanda. Permite atualizar manualmente a materialized view quando necessário ou usar `NEXT` para especificar uma atualização automática agendada. Materialized views com atualização rápida suportam apenas `ON DEMAND`. OVERWRITE A materialized view é atualizada automaticamente após os dados em sua tabela base serem sobrescritos por uma instrução `INSERT OVERWRITE`. Quando o mecanismo de acionamento da atualização é `ON OVERWRITE`, não é possível definir `START WITH` ou `NEXT`.
[START WITH date] [NEXT date]	Opcional		Alterável após a criação: Não
Quando o mecanismo de acionamento da atualização de uma materialized view é `ON DEMAND`, defina seu cronograma de atualização. Se nenhum cronograma for definido, a view não será atualizada periodicamente. START WITH Horário da primeira atualização. Se omitido, a view será atualizada imediatamente após a criação. NEXT Horário da próxima atualização agendada. Para materialized views com atualização rápida, especifique `NEXT`. O intervalo de atualização automática deve estar entre 5 segundos (s) e 5 minutos (min). Para materialized views com atualização completa, `NEXT` é opcional. Se especificado, o intervalo mínimo de atualização automática é de 60 segundos (s). *date* Funções de tempo são suportadas. O tempo tem precisão de segundos e os milissegundos são truncados.
[DISABLE \| ENABLE] QUERY REWRITE	Opcional	Valor padrão: DISABLE	Alterável após a criação: Sim (usando ALTER MATERIALIZED VIEW)
As versões 3.1.4 e posteriores suportam este parâmetro. Ativa ou desativa a reescrita automática de consultas para esta materialized view. Para mais informações, consulte Query rewrite for materialized views. DISABLE Desativa o recurso de reescrita de consultas para a materialized view atual. ENABLE Ativa o recurso de reescrita de consultas para a materialized view atual. Após ativar este recurso, o otimizador pode reescrever total ou parcialmente uma consulta com base em seu padrão SQL e roteá-la para a materialized view. Isso evita a execução dos cálculos originais na tabela base e melhora o desempenho da consulta.
query_body	Obrigatório		Alterável após a criação: Não
Define a consulta nas tabelas base para a materialized view. Para uma materialized view com atualização completa, a tabela base pode ser uma tabela interna do AnalyticDB for MySQL, uma tabela externa, uma materialized view existente ou uma view. Não há restrições para a consulta. Para mais informações sobre a sintaxe da consulta, consulte SELECT. Para uma materialized view com atualização rápida, a tabela base deve ser uma tabela interna do AnalyticDB for MySQL. A consulta deve seguir estas regras: Regras de colunas SELECT As colunas na lista SELECT devem seguir estas regras: Com GROUP BY e funções de agregação: Inclua todas as colunas do GROUP BY na lista SELECT. Ver exemplos Exemplo correto (todas as colunas do GROUP BY incluídas): `CREATE MATERIALIZED VIEW demo_mv1 REFRESH FAST ON DEMAND NEXT now() + INTERVAL 3 minute AS SELECT sale_id, -- GROUP BY column sale_date, -- GROUP BY column max(quantity) AS max, -- expression columns require aliases sum(price) AS sum FROM sales GROUP BY sale_id, sale_date;` Incorreto (coluna do GROUP BY `sale_date` ausente): `CREATE MATERIALIZED VIEW false_mv1 REFRESH FAST ON DEMAND NEXT now() + INTERVAL 3 minute AS SELECT sale_id, max(quantity) AS max, sum(price) AS sum FROM sales GROUP BY sale_id, sale_date; -- sale_date is in GROUP BY but not in SELECT` Com funções de agregação e sem GROUP BY: A lista SELECT pode conter apenas colunas de agregação, ou apenas colunas constantes e de agregação. Ver exemplos `-- Aggregate columns only CREATE MATERIALIZED VIEW demo_mv2 REFRESH FAST ON DEMAND NEXT now() + INTERVAL 3 minute AS SELECT max(quantity) AS max, sum(price) AS sum FROM sales; -- Constant plus aggregate columns (the constant becomes the primary key) CREATE MATERIALIZED VIEW demo_mv3 REFRESH FAST ON DEMAND NEXT now() + INTERVAL 3 minute AS SELECT 1 AS pk, max(quantity) AS max, sum(price) AS sum FROM sales;` Sem agregação: Inclua todas as colunas de chave primária da tabela base. Ver exemplos `-- Single primary key CREATE MATERIALIZED VIEW demo_mv4 REFRESH FAST ON DEMAND NEXT now() + INTERVAL 3 minute AS SELECT sale_id, -- primary key column of base table sales quantity FROM sales; -- Composite primary key: PRIMARY KEY(sale_id, sale_date) CREATE MATERIALIZED VIEW demo_mv5 REFRESH FAST ON DEMAND NEXT now() + INTERVAL 3 minute AS SELECT sale_id, -- first primary key column sale_date, -- second primary key column quantity FROM sales1;` Consultas UNION ALL: Cada ramificação deve gerar uma coluna chamada `union_all_marker` com um valor constante distinto por ramificação. Inclua todas as colunas de chave primária da tabela base. A chave primária da materialized view deve incluir tanto as colunas de chave primária da tabela base quanto `union_all_marker`. `CREATE MATERIALIZED VIEW demo_union_all_mv (PRIMARY KEY(id, union_all_marker)) REFRESH FAST NEXT now() + INTERVAL 5 minute AS SELECT customer_id AS id, "customer" AS union_all_marker FROM customer UNION ALL SELECT sale_id AS id, "sales" AS union_all_marker FROM sales;` Colunas de expressão: Todas as colunas de expressão na lista SELECT devem ter alias, por exemplo `SUM(price) AS total_price`. Outras limitações Expressões não determinísticas, como `NOW()` e `RAND()`, não são suportadas. A cláusula `ORDER BY` não é suportada. A cláusula `HAVING` não é suportada. Funções de janela não são suportadas. Operações de conjunto como `UNION`, `EXCEPT` e `INTERSECT` não são suportadas. `UNION ALL` é suportado nas versões 3.2.5.0 e posteriores. Apenas `INNER JOIN` é suportado. As chaves de junção devem ser colunas originais das tabelas, ter o mesmo tipo de dados e ser indexadas. É possível unir no máximo cinco tabelas. Para unir mais tabelas, entre em contato com o suporte técnico. Apenas as seguintes funções de agregação são suportadas: `COUNT`, `SUM`, `MAX`, `MIN`, `AVG`, `APPROX_DISTINCT` e `COUNT(DISTINCT)`. `AVG` não suporta o tipo de dados `DECIMAL`. `COUNT(DISTINCT)` suporta apenas o tipo de dados `INTEGER`.

Permissões necessárias

O usuário que cria a materialized view deve ter todas as permissões a seguir:

Permissão CREATE no banco de dados onde a materialized view será criada.
Permissão SELECT nas colunas relevantes (ou na tabela inteira) para todas as tabelas base da materialized view.
Para criar uma materialized view com atualização automática, você também precisa das duas permissões seguintes:
- Permissão para conectar-se ao AnalyticDB for MySQL a partir de qualquer endereço IP (ou seja, '%').
- A permissão INSERT na materialized view ou em todas as tabelas do banco de dados onde a materialized view reside. Caso contrário, o sistema não conseguirá atualizar os dados na materialized view.

Exemplos

Pré-requisitos

Os exemplos neste tópico usam as tabelas base criadas nesta seção. Para executar os exemplos, primeiro crie as tabelas base usando as seguintes instruções SQL.

/+ RC_DDL_ENGINE_REWRITE_XUANWUV2=false / -- Set the table engine to XUANWU. CREATE TABLE customer ( customer_id INT PRIMARY KEY, customer_name VARCHAR(255), is_vip Boolean ); /+ RC_DDL_ENGINE_REWRITE_XUANWUV2=false / -- Set the table engine to XUANWU. CREATE TABLE sales ( sale_id INT PRIMARY KEY, product_id INT, customer_id INT, price DECIMAL(10, 2), quantity INT, sale_date TIMESTAMP );/+ RC_DDL_ENGINE_REWRITE_XUANWUV2=false / -- Specifies that the table uses the XUANWU engine. CREATE TABLE sales ( sale_id INT PRIMARY KEY, product_id INT, customer_id INT, price DECIMAL(10, 2), quantity INT, sale_date TIMESTAMP );

Materialized view com atualização completa

Crie a materialized view myview1, que é atualizada a cada 5 minutos.

CREATE MATERIALIZED VIEW myview1
REFRESH   -- Equivalent to REFRESH COMPLETE
 NEXT now() + INTERVAL 5 minute
AS
SELECT count(*) as cnt FROM customer;

Crie a materialized view myview2, que é atualizada às 02:00 todos os dias.

CREATE MATERIALIZED VIEW myview2
REFRESH COMPLETE
 START WITH DATE_FORMAT(now() + INTERVAL 1 day, '%Y-%m-%d 02:00:00')
 NEXT DATE_FORMAT(now() + INTERVAL 1 day, '%Y-%m-%d 02:00:00')
AS
SELECT count(*) as cnt FROM customer;

Crie uma materialized view myview3 que é atualizada toda segunda-feira às 02:00.

CREATE MATERIALIZED VIEW myview3
REFRESH COMPLETE ON DEMAND
 START WITH DATE_FORMAT(now() + INTERVAL 7 - weekday(now()) day, '%Y-%m-%d 02:00:00') 
 NEXT DATE_FORMAT(now() + INTERVAL 7 - weekday(now()) day, '%Y-%m-%d 02:00:00')
AS
SELECT count(*) as cnt FROM customer;

Crie a materialized view myview4, que é atualizada às 02:00 no primeiro dia de cada mês.

CREATE MATERIALIZED VIEW myview4
REFRESH   -- Equivalent to REFRESH COMPLETE
 NEXT DATE_FORMAT(last_day(now()) + INTERVAL 1 day, '%Y-%m-%d 02:00:00')
AS
SELECT count(*) as cnt FROM customer;

Crie a materialized view myview5 e atualize-a apenas uma vez.

CREATE MATERIALIZED VIEW myview5
REFRESH   -- Equivalent to REFRESH COMPLETE
 START WITH now() + INTERVAL 1 day
AS 
SELECT count(*) as cnt FROM customer;

Crie a materialized view myview6 que não é atualizada automaticamente e depende inteiramente de atualizações manuais.

CREATE MATERIALIZED VIEW myview6 (
  PRIMARY KEY (customer_id)
) DISTRIBUTED BY HASH (customer_id)
AS
SELECT customer_id FROM customer;

Atualize manualmente a materialized view:

REFRESH MATERIALIZED VIEW myview6;

Crie a materialized view myview7. Não é necessário definir manualmente um horário de atualização, pois a materialized view é atualizada automaticamente após a tabela base ser sobrescrita por uma operação INSERT OVERWRITE.
```
CREATE MATERIALIZED VIEW myview7
REFRESH COMPLETE ON OVERWRITE
AS
SELECT count(*) as cnt FROM customer;
```

Materialized view de tabela única com atualização rápida

Antes de criar uma materialized view que utiliza atualização rápida, ative o recurso de binary logging para o cluster e para as tabelas base.

SET ADB_CONFIG BINLOG_ENABLE=true;

ALTER TABLE customer binlog=true;

ALTER TABLE sales binlog=true;

Crie uma materialized view de tabela única chamada fast_mv1 com atualização rápida e sem agregação. Ela é atualizada a cada 10 segundos.

CREATE MATERIALIZED VIEW fast_mv1
REFRESH FAST NEXT now() + INTERVAL 10 second
AS
SELECT sale_id, sale_date, price
FROM sales
WHERE price > 10;

Crie uma materialized view de tabela única chamada fast_mv2 com atualização rápida e agregação agrupada. Ela é atualizada a cada 5 segundos.

CREATE MATERIALIZED VIEW fast_mv2
REFRESH FAST NEXT now() + INTERVAL 5 second
AS
SELECT
   customer_id, sale_date,                 -- The system automatically includes the GROUP BY columns as the primary key of the materialized view.
   COUNT(sale_id) AS cnt_sale_id,          -- Aggregate output column.
   SUM(price * quantity) AS total_revenue, -- Aggregate output column.
   customer_id / 100 AS new_customer_id    -- Non-aggregate output columns can use any expression.
FROM sales
WHERE ifnull(price, 1) > 0                 -- Any expression can be used in the condition.
GROUP BY customer_id, sale_date;

Crie uma materialized view de tabela única chamada fast_mv3 com atualização rápida e agregação não agrupada. Ela é atualizada a cada minuto.

CREATE MATERIALIZED VIEW fast_mv3
REFRESH FAST NEXT now() + INTERVAL 1 minute
AS
SELECT count(*) AS cnt   -- The system automatically generates a constant primary key to ensure only one record exists in the materialized view.
FROM sales;

Materialized view de múltiplas tabelas com atualização rápida

Crie uma materialized view de múltiplas tabelas chamada fast_mv4 com atualização rápida e sem agregação. Ela é atualizada a cada 5 segundos.

CREATE MATERIALIZED VIEW fast_mv4
REFRESH FAST NEXT now() + INTERVAL 5 second
AS
SELECT 
    c.customer_id,
    c.customer_name,
    s.sale_id,
    (s.price * s.quantity) AS revenue
FROM 
    sales s
JOIN 
    customer c ON s.customer_id = c.customer_id;

Crie uma materialized view de múltiplas tabelas chamada fast_mv5 com atualização rápida e agregação agrupada. Ela é atualizada a cada 10 segundos.

CREATE MATERIALIZED VIEW fast_mv5
REFRESH FAST NEXT now() + INTERVAL 10 second
AS
SELECT 
    s.sale_id,
    c.customer_name,
    COUNT(*) AS cnt,           
    SUM(s.price * s.quantity) AS revenue                
FROM 
    sales s
JOIN 
    (SELECT customer_id, customer_name FROM customer) c ON c.customer_id = s.customer_id
GROUP BY 
    s.sale_id, c.customer_name;

Materialized view particionada

Crie a materialized view myview8 e defina a chave de distribuição e a chave de partição.

CREATE MATERIALIZED VIEW myview8 (
  quantity INT,    -- The materialized view includes all columns from the query results even if they are not explicitly listed in the definition.
  price DECIMAL(10, 2),
  sale_date TIMESTAMP
) 
DISTRIBUTED BY HASH(sale_id)
PARTITION BY VALUE(date_format(sale_date, "%Y%m%d")) LIFECYCLE 30
AS 
SELECT * FROM sales;

Definir chaves e índices

Crie a materialized view myview9, criando um índice apenas na coluna especificada customer_name em vez de em todas as colunas.

CREATE MATERIALIZED VIEW myview9 (
  INDEX (sale_date),
  PRIMARY KEY (sale_id)
) DISTRIBUTED BY HASH (sale_id)
REFRESH
 NEXT now() + INTERVAL 1 DAY
AS
SELECT * FROM sales;

Crie uma materialized view chamada myview10 com chave primária, chave de distribuição, índice de cluster, índices em colunas especificadas e um comentário.

CREATE MATERIALIZED VIEW myview10 (
  quantity INT,    -- The materialized view includes all columns from the query results even if they are not explicitly listed in the definition.
  price DECIMAL(10, 2),
  KEY INDEX_ID(customer_id) COMMENT 'customer',
  CLUSTERED KEY INDEX(sale_id),
  PRIMARY KEY(sale_id,sale_date)
) 
DISTRIBUTED BY HASH(sale_id)
COMMENT 'MATERIALIZED VIEW c'
AS 
SELECT * FROM sales;

Materialized view elástica

Crie a materialized view elástica myview11, usando o grupo de recursos do tipo Job serverless para criá-la e atualizá-la, com frequência de atualização de uma vez por dia.

CREATE MATERIALIZED VIEW myview11
MV_PROPERTIES='{
  "mv_resource_group":"serverless"
}'
REFRESH COMPLETE ON DEMAND
 START WITH now()
 NEXT now() + INTERVAL 1 DAY
AS
SELECT * FROM sales;

Crie a materialized view elástica myview12, que usa o grupo de recursos do tipo Job serverless para criação e atualizações, podendo utilizar 12 ACU de recursos desse grupo.

CREATE MATERIALIZED VIEW myview12
MV_PROPERTIES='{
  "mv_resource_group":"serverless",
  "mv_refresh_hints":{"elastic_job_max_acu":"12"}
}'
REFRESH COMPLETE ON DEMAND
 START WITH now()
 NEXT now() + INTERVAL 1 DAY
AS
SELECT * FROM sales;

Tópicos relacionados

Materialized views: Conheça os casos de uso e as atualizações de recursos das materialized views.
Create a materialized view: Aprenda a criar uma materialized view e encontre soluções para erros comuns.
Refresh a materialized view: Saiba como realizar uma atualização completa ou rápida em uma materialized view.
Manage materialized views: Veja como visualizar a definição de uma materialized view, listar todas as materialized views e excluir uma materialized view.
Query rewrite for materialized views

AnalyticDB:CREATE MATERIALIZED VIEW

Sintaxe

Parâmetros

OR REPLACE

mv_definition

Regras de chave primária

Recomendações

mv_properties

mv_resource_group

mv_refresh_hints

REFRESH [COMPLETE | FAST]

COMPLETE

FAST

ON [DEMAND | OVERWRITE]

DEMAND

OVERWRITE

[START WITH date] [NEXT date]

START WITH

NEXT

date

[DISABLE | ENABLE] QUERY REWRITE

DISABLE

ENABLE

query_body

Regras de colunas SELECT

Outras limitações