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 esquema da materialized view. Ao omitir esta cláusula, o sistema infere o esquema 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 esquema 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 o melhor desempenho de consulta, 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 for 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. A diferença é que os grupos de recursos Job provisionam recursos sob demanda, o que geralmente introduz uma 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 operação 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 novos dados calculados. A atualização completa suporta os mecanismos de gatilho de atualização `ON DEMAND [START WITH date] [NEXT date]` e `ON OVERWRITE`, que permitem realizar atualizações manuais sob demanda, agendar atualizações automáticas ou atualizar automaticamente quando a tabela base é 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. A versão 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 (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 usa 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 que utiliza atualização rápida, o mecanismo de gatilho de atualização 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 gatilho de atualização da materialized view. Para informações sobre as diferenças entre os mecanismos de gatilho de atualização e seus casos de uso, consulte Select a refresh trigger mechanism. DEMAND Atualização sob demanda. Isso significa que você pode 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 gatilho de 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 gatilho de atualização de uma materialized view é `ON DEMAND`, é possível definir 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 uma materialized view que usa atualização rápida, especifique `NEXT`. O intervalo de atualização automática deve estar entre 5 segundos (s) e 5 minutos (min). Para uma materialized view que usa 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. 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

Permissões necessárias

Exemplos

Pré-requisitos

Os exemplos neste tópico utilizam 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 );

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 usa atualização rápida, ative o recurso de binary logging para o cluster e 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 fast_mv1 que usa atualização rápida sem operação de agregação e é 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 fast_mv2 que usa atualização rápida com agregação GROUP BY e é 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 uses 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                 -- Conditions can use any expression.
GROUP BY customer_id, sale_date;

Crie uma materialized view de tabela única fast_mv3 que usa atualização rápida sem agregação GROUP BY e é atualizada a cada minuto.

CREATE MATERIALIZED VIEW fast_mv3
REFRESH FAST NEXT now() + INTERVAL 1 minute
AS
SELECT count(*) AS cnt   -- The system generates a constant primary key to ensure that the materialized view contains only one record.
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 clustering, í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 deste 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: Aprenda a 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