ApsaraDB for SelectDB:Vue matérialisée synchrone

Syntaxe

CREATE MATERIALIZED VIEW <mv_name> AS <query>
[PROPERTIES ("key" = "value")]

Format de query :

SELECT select_expr [, select_expr ...]
FROM <base_table_name>
[GROUP BY column_name [, column_name ...]]
[ORDER BY column_name [, column_name ...]]

Paramètres

Contraintes de l'instruction SELECT :

• Table unique uniquement : aucune sous-requête autorisée.

• Les colonnes ne doivent inclure ni colonnes auto-incrémentées, ni constantes, expressions dupliquées ou fonctions de fenêtrage.

• Si le SELECT inclut des colonnes de clé de partition ou de bucketing, ces colonnes doivent être des colonnes Key dans la vue matérialisée.

• Clauses autorisées : WHERE, GROUP BY, ORDER BY.

• Clauses interdites : JOIN, HAVING, LIMIT, LATERAL VIEW.

Contraintes des fonctions d'agrégation :

Fonctions d'agrégation prises en charge :

Règles d'ajout automatique pour ORDER BY (si ORDER BY est omis) :

• Vue de type agrégation : toutes les colonnes de regroupement deviennent des colonnes de tri.

• Vue hors agrégation : les 36 premiers octets des colonnes deviennent des colonnes de tri.

• Si moins de trois colonnes sont ajoutées automatiquement, les trois premières colonnes sont utilisées.

• Si GROUP BY est spécifié, ORDER BY doit correspondre aux colonnes de regroupement.

Principes de conception

• Abstraire les modèles partagés. Définissez la vue matérialisée autour de modèles d'agrégation communs à plusieurs requêtes. Une vue correspondant à une seule requête consomme de l'espace de stockage pour un bénéfice minime.

• Cibler uniquement les dimensions courantes. Toutes les combinaisons de dimensions ne nécessitent pas de vue matérialisée. Concentrez-vous sur les combinaisons les plus fréquentes dans les requêtes de production.

Exemples

Les exemples suivants utilisent cette table de base :

CREATE TABLE duplicate_table (
    k1 INT NULL,
    k2 INT NULL,
    k3 BIGINT NULL,
    k4 BIGINT NULL
)
DUPLICATE KEY (k1, k2, k3, k4)
DISTRIBUTED BY HASH(k4) BUCKETS 3;

Exemple 1 : Sous-ensemble de colonnes avec préfixe réordonné

CREATE MATERIALIZED VIEW k1_k2 AS
SELECT k2, k1 FROM duplicate_table;

Résultat : une vue contenant uniquement k2 et k1, avec k2 comme colonne de tri principale. Les requêtes filtrant sur k2 peuvent utiliser l'index de préfixe de cette vue au lieu de celui de la table de base.

Exemple 2 : Ordre de tri explicite

CREATE MATERIALIZED VIEW k2_order AS
SELECT k2, k1 FROM duplicate_table ORDER BY k2;

Exemple 3 : Agrégation

CREATE MATERIALIZED VIEW k1_k2_sumk3 AS
SELECT k1, k2, sum(k3)
FROM duplicate_table
GROUP BY k1, k2;

Résultat : une vue avec k1 et k2 comme colonnes Key, et sum(k3) comme valeur agrégée. SelectDB ajoute automatiquement k1, k2 comme colonnes de tri, car GROUP BY est utilisé et ORDER BY est omis.

SHOW ALTER TABLE MATERIALIZED VIEW FROM <database>;

SHOW ALTER TABLE MATERIALIZED VIEW FROM test_db;

+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+
| JobId  | TableName     | CreateTime          | FinishTime          | BaseIndexName | RollupIndexName | RollupId | TransactionId | State    | Msg  | Progress | Timeout |
+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+
| 494349 | sales_records | 2020-07-30 20:04:56 | 2020-07-30 20:04:57 | sales_records | store_amt       | 494350   | 133107        | FINISHED |      | NULL     | 2592000 |
+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+

DESC <table_name> ALL;

DESC duplicate_table ALL;

+-----------------+---------------+---------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+
| IndexName       | IndexKeysType | Field         | Type   | InternalType | Null | Key   | Default | Extra | Visible | DefineExpr | WhereClause |
+-----------------+---------------+---------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+
| duplicate_table | DUP_KEYS      | k1            | INT    | INT          | Yes  | true  | NULL    |       | true    |            |             |
|                 |               | k2            | INT    | INT          | Yes  | true  | NULL    |       | true    |            |             |
|                 |               | k3            | BIGINT | BIGINT       | Yes  | true  | NULL    |       | true    |            |             |
|                 |               | k4            | BIGINT | BIGINT       | Yes  | true  | NULL    |       | true    |            |             |
|                 |               |               |        |              |      |       |         |       |         |            |             |
| k2_order        | DUP_KEYS      | mv_k2         | INT    | INT          | Yes  | true  | NULL    |       | true    | `k2`       |             |
|                 |               | mv_k1         | INT    | INT          | Yes  | false | NULL    | NONE  | true    | `k1`       |             |
|                 |               |               |        |              |      |       |         |       |         |            |             |
| k1_k2           | DUP_KEYS      | mv_k2         | INT    | INT          | Yes  | true  | NULL    |       | true    | `k2`       |             |
|                 |               | mv_k1         | INT    | INT          | Yes  | true  | NULL    |       | true    | `k1`       |             |
|                 |               |               |        |              |      |       |         |       |         |            |             |
| k1_k2_sumk3     | AGG_KEYS      | mv_k1         | INT    | INT          | Yes  | true  | NULL    |       | true    | `k1`       |             |
|                 |               | mv_k2         | INT    | INT          | Yes  | true  | NULL    |       | true    | `k2`       |             |
|                 |               | mva_SUM__`k3` | BIGINT | BIGINT       | Yes  | false | NULL    | SUM   | true    | `k3`       |             |
+-----------------+---------------+---------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+

SHOW CREATE MATERIALIZED VIEW <mv_name> ON <table_name>;

SHOW CREATE MATERIALIZED VIEW id_col1 ON table3;

+-----------+----------+----------------------------------------------------------------+
| TableName | ViewName | CreateStmt                                                     |
+-----------+----------+----------------------------------------------------------------+
| table3    | id_col1  | create materialized view id_col1 as select id,col1 from table3 |
+-----------+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

Annuler une création en cours

Si la tâche de création n'est pas encore terminée, annulez-la avec la commande suivante :

CANCEL ALTER TABLE MATERIALIZED VIEW FROM <database>.<table_name>;

Exemple :

CANCEL ALTER TABLE MATERIALIZED VIEW FROM test_db.duplicate_table;

Si la vue est déjà construite, cette commande n'a aucun effet ; utilisez plutôt DROP.

Supprimer une vue matérialisée terminée

DROP MATERIALIZED VIEW [IF EXISTS] <mv_name> ON <table_name>;

Exemple :

-- List materialized views before dropping
DESC duplicate_table ALL;

-- Drop the view named k1_k2
DROP MATERIALIZED VIEW k1_k2 ON duplicate_table;

-- Confirm the view is gone
DESC duplicate_table ALL;

EXPLAIN <your_query>;

Décompte distinct exact avec BITMAP_UNION

Scénario : Comptage d'utilisateurs uniques (ou de toute autre colonne entière à haute cardinalité) groupés selon plusieurs dimensions.

Requête typique :

SELECT advertiser, channel, COUNT(DISTINCT user_id)
FROM advertiser_view_record
GROUP BY advertiser, channel;

L'utilisation de COUNT(DISTINCT ...) sur de grandes tables est coûteuse. Une vue matérialisée avec BITMAP_UNION pré-déduplique les données afin que la requête lise des bitmaps agrégés plutôt que des lignes brutes.

Créer la vue :

CREATE MATERIALIZED VIEW advertiser_uv AS
SELECT advertiser, channel, bitmap_union(to_bitmap(user_id))
FROM advertiser_view_record
GROUP BY advertiser, channel;

user_id étant de type INT, enveloppez-le avec to_bitmap() avant d'appliquer bitmap_union . Cela convertit les entiers au format bitmap requis par la fonction.

Une fois la vue construite (State = FINISHED), la requête d'origine est automatiquement réécrite ainsi :

SELECT advertiser, channel, bitmap_union_count(to_bitmap(user_id))
FROM advertiser_uv
GROUP BY advertiser, channel;

Vérifier la correspondance :

EXPLAIN SELECT advertiser, channel, COUNT(DISTINCT user_id)
FROM advertiser_view_record
GROUP BY advertiser, channel;

Dans la sortie EXPLAIN, localisez OlapScanNode. Confirmez que la valeur rollup est bien advertiser_uv. Vérifiez également que count(distinct) a été réécrit en bitmap_union_count(to_bitmap).

Décompte distinct approximatif avec HLL_UNION

Scénario : Estimation de décomptes uniques lorsqu'une précision exacte n'est pas requise et que la vitesse de requête prime.

CREATE MATERIALIZED VIEW approx_uv AS
SELECT advertiser, channel, hll_union(hll_hash(user_id))
FROM advertiser_view_record
GROUP BY advertiser, channel;

Les requêtes utilisant approx_count_distinct, ndv, hll_union_agg ou hll_raw_agg sur user_id correspondent automatiquement à cette vue.

user_id ne peut pas être de type DECIMAL lors de l'utilisation du format HLL_UNION(HLL_HASH(col)) .

Réorganisation de l'index de préfixe

Scénario : Une requête fréquente filtre sur une colonne qui n'est pas la colonne principale de la clé de tri de la table de base.

Si la table de base utilise DUPLICATE KEY (k1, k2, k3, k4) mais que les requêtes filtrent souvent sur k2 :

CREATE MATERIALIZED VIEW k2_order AS
SELECT k2, k1 FROM duplicate_table ORDER BY k2;

Les requêtes avec WHERE k2 = ... peuvent désormais utiliser l'index de préfixe de cette vue, ce qui réduit considérablement la plage de balayage.