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;

Activer la journalisation binaire

L'actualisation incrémentielle repose sur la journalisation binaire pour suivre les modifications apportées aux tables de base. Activez-la au niveau du cluster et pour chaque table de base :

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

Si l'activation de la journalisation binaire échoue, consultez Impossible de créer une vue matérialisée FAST.

Créer une vue matérialisée rapide sur une seule table

L'exemple suivant crée une vue matérialisée rapide nommée sales_mv_incre sur la table sales avec un intervalle d'actualisation automatique de 3 minutes :

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;

Créer une vue matérialisée rapide multi-tables

Sur les clusters exécutant la version V3.2.1.0 ou ultérieure, les vues matérialisées rapides peuvent joindre plusieurs tables. L'exemple suivant joint les tables customer et 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;

Pour connaître la syntaxe complète de CREATE MATERIALIZED VIEW, consultez 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;

Règles relatives aux colonnes SELECT

Les colonnes de la liste SELECT doivent respecter les règles suivantes :

• Avec GROUP BY et des fonctions d'agrégation : Incluez toutes les colonnes GROUP BY dans la liste SELECT.

  Afficher des exemples

  Exemple correct (toutes les colonnes GROUP BY sont incluses) :

  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;

  Incorrect (colonne GROUP BY sale_date manquante) :

  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

• Avec des fonctions d'agrégation et sans GROUP BY : La liste SELECT ne peut contenir que des colonnes d'agrégation, ou uniquement des colonnes constantes et d'agrégation.

  Afficher des exemples

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

• Sans agrégation : Incluez toutes les colonnes de clé primaire de la table de base.

  Afficher des exemples

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

• Requêtes UNION ALL : Chaque branche doit produire une colonne nommée union_all_marker avec une valeur constante distincte par branche. Incluez toutes les colonnes de clé primaire des tables de base. La clé primaire de la vue matérialisée doit inclure à la fois les colonnes de clé primaire de la table de base et 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;

• Colonnes d'expression : Toutes les colonnes d'expression de la liste SELECT doivent avoir des alias, par exemple SUM(price) AS total_price.

Limites générales

Ces limites s'appliquent à toutes les vues matérialisées.

• Vous ne pouvez pas exécuter INSERT, DELETE ou UPDATE sur une vue matérialisée.

• Vous ne pouvez pas supprimer ni renommer une table de base ou ses colonnes tant qu'une vue matérialisée les référence. Supprimez d'abord la vue matérialisée, puis modifiez la table de base.

• Nombre maximal de vues matérialisées par cluster :

  • V3.1.4.7 ou ultérieure : 64

  • Antérieure à V3.1.4.7 : 8

Pour augmenter le quota, contactez le support technique.

Limites des vues matérialisées à actualisation complète

Lorsque vous ajoutez ou supprimez des nœuds réservés, les tâches asynchrones sont désactivées. Étant donné que l'actualisation complète est une tâche asynchrone, elle ne peut pas s'exécuter pendant la mise à l'échelle des nœuds. L'actualisation rapide n'est pas affectée.

Limites des vues matérialisées rapides (incrémentielles)

Consultez Contraintes des requêtes pour les vues matérialisées incrémentielles pour la liste complète des restrictions SQL.

Déclencheur d'actualisation : Seule l'actualisation automatique planifiée est prise en charge. L'intervalle doit être compris entre 5 secondes et 5 minutes.

Comment conserver uniquement les données de l'année la plus récente dans une vue matérialisée ?

Utilisez une colonne de date comme clé de partition (PARTITION BY) et définissez une valeur LIFECYCLE pour limiter le nombre de partitions conservées. Pour une vue partitionnée par jour, définissez LIFECYCLE 365 afin de conserver les 365 partitions les plus récentes (un an).

Exemple : la table sales reçoit de nouveaux enregistrements quotidiennement, partitionnés par 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;

Erreur d'exécution de requête : Impossible de créer une vue matérialisée FAST, car demotable ne prend pas en charge l'obtention de données incrémentielles

La journalisation binaire n'est pas activée pour la table de base demotable. Activez-la avec :

ALTER TABLE demotable binlog=true;

Si vous voyez XUANWU_V2 engine not support ALTER_BINLOG_ENABLE now, la table de base utilise le moteur XUANWU_V2 et le cluster exécute une version du noyau antérieure à V3.2.6.0. XUANWU_V2 prend en charge la journalisation binaire à partir de la version du noyau V3.2.6.0. Mettez à niveau le cluster vers la version V3.2.6.0 ou ultérieure, puis exécutez à nouveau ALTER TABLE demotable binlog=true; pour activer la journalisation binaire sur la table de base.

Erreur d'exécution de requête : La clé primaire id doit être envoyée vers la MV

Votre vue matérialisée rapide utilise une requête non agrégée sans GROUP BY. Dans ce cas, la liste SELECT doit inclure toutes les colonnes de clé primaire de la table de base.

Incorrect (sale_id manquante, qui est la clé primaire de sales) :

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

Ajoutez la colonne de clé primaire :

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

Erreur d'exécution de requête : La clé primaire de la MV doit être égale à la clé primaire de la table de base

Votre vue matérialisée rapide utilise une requête non agrégée sans GROUP BY, et la définition de la clé primaire de la vue matérialisée inclut des colonnes qui ne font pas partie de la clé primaire de la table de base.

Incorrect (product_id n'est pas une colonne de clé primaire 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;

Supprimez les colonnes non clés primaires de la définition de la clé primaire :

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;

Erreur d'exécution de requête : La vue matérialisée FAST doit définir une clé primaire

Cette erreur a deux causes possibles :

• Aucune clé primaire valide définie. Mettez à jour la définition de la vue matérialisée pour respecter ces règles :

  • Requête d'agrégation groupée (avec GROUP BY) : la clé primaire doit correspondre aux colonnes GROUP BY (par exemple, si GROUP BY a, b, définissez PRIMARY KEY(a, b)).

  • Requête d'agrégation non groupée (sans GROUP BY) : la clé primaire doit être une constante.

  • Requête non agrégée : la clé primaire doit correspondre exactement à la clé primaire de la table de base (par exemple, si la table de base a PRIMARY KEY(sale_id, sale_date), la vue doit également avoir PRIMARY KEY(sale_id, sale_date)).

• Une fonction est appliquée à une colonne de clé primaire. Supprimez la fonction de la colonne de clé primaire dans la requête.

Erreur d'exécution de requête : Le graphe de jointure n'est pas pris en charge

Les colonnes de jointure ont des types de données incompatibles. Par exemple, si customer.id et sales.id ont des types différents, la jointure échoue. Alignez les types avec :

ALTER TABLE tablename MODIFY COLUMN columnname newtype;

Pour plus d'informations, consultez Modifier le type de données d'une colonne.

Erreur d'exécution de requête : Impossible d'utiliser la jointure d'index pour actualiser cette MV rapide

Les colonnes de jointure manquent d'index. Ajoutez un index sur chaque colonne de jointure :

ALTER TABLE tablename ADD KEY idx_name(columnname);

Pour plus d'informations, consultez Créer un index.

Erreur d'exécution de requête : La requête a dépassé la limite de mémoire réservée

La requête a dépassé la limite de mémoire par nœud. Utilisez la fonctionnalité diagnostics SQL pour identifier les étapes et opérateurs gourmands en mémoire (Agrégation, TopN, Fenêtrage et Jointure sont les coupables courants), puis optimisez ces opérateurs. Consultez également Métriques de mémoire et Utiliser les détails des étapes et des tâches pour analyser les requêtes.