AnalyticDB:Create a materialized view

/*+ 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
);

CREATE MATERIALIZED VIEW join_mv
REFRESH COMPLETE ON DEMAND
AS
SELECT
  sale_id,
  SUM(price * quantity) AS price
FROM customer
INNER JOIN (SELECT sale_id, customer_id, price, quantity FROM sales) sales
  ON customer.customer_id = sales.customer_id
GROUP BY customer.customer_id;

REFRESH MATERIALIZED VIEW join_mv;

Ativar binary logging

A atualização incremental depende do binary logging para rastrear alterações nas tabelas base. Ative-o no nível do cluster e para cada tabela base:

SET ADB_CONFIG BINLOG_ENABLE=true;
ALTER TABLE customer binlog=true;
ALTER TABLE sales binlog=true;

Se a ativação do binary logging falhar, consulte Não é possível criar materialized view FAST.

Criar uma materialized view rápida de tabela única

O exemplo abaixo cria uma materialized view rápida chamada sales_mv_incre na tabela sales, com intervalo de atualização automática de 3 minutos:

CREATE MATERIALIZED VIEW sales_mv_incre
REFRESH FAST NEXT now() + INTERVAL 3 minute
AS
SELECT
  sale_id,
  SUM(price * quantity) AS price
FROM sales
GROUP BY sale_id;

Criar uma materialized view rápida de várias tabelas

Em clusters com versão V3.2.1.0 ou superior, materialized views rápidas podem unir múltiplas tabelas. O exemplo a seguir une customer e sales:

CREATE MATERIALIZED VIEW join_mv_incre
REFRESH FAST NEXT now() + INTERVAL 3 minute
AS
SELECT
  customer.customer_id,
  SUM(sales.price) AS price
FROM customer
INNER JOIN (SELECT customer_id, price FROM sales) sales
  ON customer.customer_id = sales.customer_id
GROUP BY customer.customer_id;

Para a sintaxe completa de CREATE MATERIALIZED VIEW, consulte CREATE MATERIALIZED VIEW.

SHOW PROCESSLIST WHERE info LIKE '%CREATE MATERIALIZED VIEW%';

CREATE MATERIALIZED VIEW job_mv
MV_PROPERTIES='{
  "mv_resource_group": "my_job_rg",
  "mv_refresh_hints": {"query_priority": "HIGH"}
}'
REFRESH COMPLETE ON DEMAND
START WITH now()
NEXT now() + INTERVAL 1 DAY
AS
SELECT * FROM customer;

Regras para colunas SELECT

As colunas na lista SELECT devem obedecer às seguintes regras:

• Com GROUP BY e funções de agregação: Inclua todas as colunas do GROUP BY na lista SELECT.

  Visualize exemplos

  Exemplo correto (todas as colunas 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 GROUP BY ausente: sale_date):

  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.

  Visualize 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.

  Visualize 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 aliases, por exemplo SUM(price) AS total_price.

Limites gerais

Estes limites aplicam-se a todas as materialized views.

• Não é possível executar INSERT, DELETE ou UPDATE em uma materialized view.

• Não é possível excluir ou renomear uma tabela base ou suas colunas enquanto uma materialized view as referencia. Exclua primeiro a materialized view e depois modifique a tabela base.

• Limite máximo de materialized views por cluster:

  • V3.1.4.7 ou posterior: 64.

  • Anterior à V3.1.4.7: 8.

Para aumentar a cota, entre em contato com o suporte técnico.

Limites para materialized views de atualização completa

Ao adicionar ou remover nós reservados, os jobs assíncronos são desativados. Como a atualização completa é um job assíncrono, ela não pode ser executada durante o dimensionamento de nós. A atualização rápida não é afetada.

Limites para materialized views rápidas (incrementais)

Consulte Restrições de consulta para MV incremental para obter a lista completa de restrições SQL.

Gatilho de atualização: Apenas a atualização automática agendada é compatível. O intervalo deve ser entre 5 segundos e 5 minutos.

Como manter apenas o ano mais recente de dados em uma materialized view?

Use uma coluna de data como chave de partição (PARTITION BY) e defina um valor de LIFECYCLE para limitar quantas partições são retidas. Para uma view particionada por dia, defina LIFECYCLE 365 para manter as 365 partições mais recentes (um ano).

Exemplo: a tabela sales recebe novos registros diariamente, particionados por sale_date:

CREATE MATERIALIZED VIEW sales_mv_lifecycle
PARTITION BY VALUE(DATE_FORMAT(sale_date, '%Y%m%d')) LIFECYCLE 365
REFRESH FAST NEXT now() + INTERVAL 100 second
AS
SELECT
  sale_date,
  SUM(price * quantity) AS price
FROM sales
GROUP BY sale_date;

Erro de execução de consulta: Can not create FAST materialized view, because demotable doesn't support getting incremental data

O binary logging não está habilitado para a tabela base demotable. Habilite-o com:

ALTER TABLE demotable binlog=true;

Se você vir XUANWU_V2 engine not support ALTER_BINLOG_ENABLE now, a tabela base usa o mecanismo XUANWU_V2 e o cluster executa uma versão de kernel anterior à V3.2.6.0. O XUANWU_V2 aceita binary logging a partir da versão de kernel V3.2.6.0. Atualize o cluster para V3.2.6.0 ou posterior e execute ALTER TABLE demotable binlog=true; novamente para habilitar o binary logging na tabela base.

Erro de execução de consulta: PRIMARY KEY id must output to MV

Sua materialized view rápida usa uma consulta não agregada sem GROUP BY. Nesse caso, a lista SELECT deve incluir todas as colunas de chave primária da tabela base.

Incorreto (ausência de sale_id, que é a chave primária de sales):

CREATE MATERIALIZED VIEW wrong_example1
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT product_id, price
FROM sales;

Adicione a coluna de chave primária:

CREATE MATERIALIZED VIEW correct_example1
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT sale_id, product_id, price
FROM sales;

Erro de execução de consulta: MV PRIMARY KEY must be equal to base table PRIMARY KEY

Sua materialized view rápida usa uma consulta não agregada sem GROUP BY, e a definição da chave primária da materialized view inclui colunas que não fazem parte da chave primária da tabela base.

Incorreto (product_id não é uma coluna de chave primária de sales):

CREATE MATERIALIZED VIEW wrong_example2
(PRIMARY KEY(sale_id, product_id))
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT sale_id, product_id, price
FROM sales;

Remova colunas que não são chave primária da definição da chave primária:

CREATE MATERIALIZED VIEW correct_example2
(PRIMARY KEY(sale_id))
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT sale_id, product_id, price
FROM sales;

Erro de execução de consulta: FAST materialized view must define PRIMARY KEY

Este erro tem duas causas possíveis:

• Nenhuma chave primária válida definida. Atualize a definição da materialized view para atender a estas regras:

  • Consulta agregada agrupada (com GROUP BY): a chave primária deve corresponder às colunas do GROUP BY (por exemplo, se GROUP BY a, b, defina PRIMARY KEY(a, b)).

  • Consulta agregada não agrupada (sem GROUP BY): a chave primária deve ser uma constante.

  • Consulta não agregada: a chave primária deve corresponder exatamente à chave primária da tabela base (por exemplo, se a tabela base tiver PRIMARY KEY(sale_id, sale_date), a view também deve ter PRIMARY KEY(sale_id, sale_date)).

• Uma função foi aplicada a uma coluna de chave primária. Remova a função da coluna de chave primária na consulta.

Erro de execução de consulta: The join graph is not supported

As colunas de junção têm tipos de dados incompatíveis. Por exemplo, se customer.id e sales.id tiverem tipos diferentes, a junção falhará. Alinhe os tipos com:

ALTER TABLE tablename MODIFY COLUMN columnname newtype;

Para mais informações, consulte Alterar o tipo de dados de uma coluna.

Erro de execução de consulta: Unable to use index join to refresh this fast MV

As colunas de junção não possuem índices. Adicione um índice em cada coluna de junção:

ALTER TABLE tablename ADD KEY idx_name(columnname);

Para mais informações, consulte Criar um índice.

Erro de execução de consulta: Query exceeded reserved memory limit

A consulta excedeu o limite de memória por nó. Use o recurso de Diagnóstico SQL para identificar estágios e operadores com alto consumo de memória (Agregação, TopN, Janela e Junção são os causadores mais comuns) e otimize esses operadores. Consulte também Métricas de memória e Usar detalhes de estágio e tarefa para analisar consultas.