Travailler avec des vues matérialisées

Une vue matérialisée est un ensemble de données précalculé dérivé d’une spécification de requête (SELECT dans la définition de la vue) et stocké pour une utilisation ultérieure. Étant donné que les données sont pré-calculées, l’interrogation d’une vue matérialisée est plus rapide que l’exécution d’une requête sur la table de base de la vue. Cette différence de performances peut être significative lorsqu’une requête est exécutée fréquemment ou est suffisamment complexe. Par conséquent, les vues matérialisées peuvent accélérer les opérations d’agrégation, de projection et de sélection coûteuses, en particulier celles qui s’exécutent fréquemment et qui s’exécutent sur de grands ensembles de données.

Note

Les vues matérialisées sont conçues pour améliorer les performances d’interrogation de charges de travail composées de modèles d’interrogation communs et répétés. Cependant, la matérialisation des résultats intermédiaires entraîne des coûts supplémentaires. En tant que tel, avant de créer des vues matérialisées, vous devez déterminer si les coûts sont compensés par les économies générées par la réutilisation assez fréquente de ces résultats.

Décider quand créer une vue matérialisée

Les vues matérialisées sont particulièrement utiles lorsque :

  • Les résultats de la requête contiennent un petit nombre de lignes et/ou de colonnes par rapport à la table de base (la table sur laquelle la vue est définie).

  • Les résultats de la requête contiennent des résultats qui nécessitent un traitement important, notamment :

    • Analyse de données semi-structurées.

    • Agrégats dont le calcul est long.

  • La requête se trouve sur une table externe (c’est-à-dire des ensembles de données stockés dans des fichiers dans une zone de préparation externe), ce qui peut avoir des performances plus lentes par rapport à l’interrogation de tables de base de données natives.

  • La table de base de la vue ne change pas fréquemment.

Avantages des vues matérialisées

La mise en œuvre de vues matérialisées par Snowflake offre un certain nombre de caractéristiques uniques :

  • Les vues matérialisées peuvent améliorer les performances des requêtes utilisant les mêmes résultats de sous-requête à plusieurs reprises.

  • Les vues matérialisées sont gérées de manière automatique et transparente par Snowflake. Un service en tâche de fond met à jour la vue matérialisée une fois que les modifications ont été apportées à la table de base. Cela est plus efficace et moins sujet aux erreurs que de conserver manuellement l’équivalent d’une vue matérialisée au niveau de l’application.

  • Les données accessibles via les vues matérialisées sont toujours actuelles, quelle que soit la quantité de DML effectuée sur la table de base. Si une requête est exécutée avant que la vue matérialisée ne soit mise à jour, Snowflake met à jour la vue matérialisée ou utilise les parties mises à jour de la vue matérialisée et récupère toutes les nouvelles données requises à partir de la table de base.

Important

La maintenance automatique des vues matérialisées consomme des crédits. Pour plus de détails, voir Coût des vues matérialisées (dans ce chapitre).

Décider quand créer une vue matérialisée ou une vue normale

En général, lorsque vous décidez de créer une vue matérialisée ou normale, utilisez les critères suivants :

  • Créez une vue matérialisée lorsque toutes les conditions suivantes sont réunies :

    • Les résultats de la requête de la vue ne changent pas souvent. Cela signifie presque toujours que la table sous-jacente/de base pour la vue ne change pas souvent, ou du moins que le sous-ensemble de lignes de la table de base utilisé dans la vue matérialisée ne change pas souvent.

    • Les résultats de la vue sont souvent utilisés (généralement plus souvent par rapport à la fréquence de changement des résultats).

    • La requête consomme beaucoup de ressources. En règle générale, cela signifie que la requête utilise beaucoup de temps de traitement ou de crédits, mais cela peut également signifier que la requête utilise beaucoup d’espace de stockage pour les résultats intermédiaires.

  • Créez une vue normale lorsque certaines des conditions suivantes sont réunies :

    • Les résultats de la vue changent souvent.

    • Les résultats ne sont pas utilisés souvent (par rapport à la vitesse à laquelle les résultats changent).

    • La requête n’utilise pas beaucoup de ressources, il n’est donc pas coûteux de la réexécuter.

Ces critères ne sont que des indications. Une vue matérialisée peut présenter des avantages même si elle n’est pas utilisée souvent - en particulier si les résultats changent moins souvent que l’utilisation de la vue.

De plus, vous devez prendre en compte d’autres facteurs pour décider d’utiliser une vue normale ou une vue matérialisée.

Par exemple, le coût de stockage de la vue matérialisée est un facteur ; si les résultats ne sont pas utilisés très souvent (même s’ils sont utilisés plus souvent par rapport à leur fréquence de changement), les coûts de stockage supplémentaires risquent de ne pas valoir le gain de performances.

Comparaison avec des tables, des vues standard et des résultats mis en cache

Les vues matérialisées ressemblent aux tables à certains égards et aux vues ordinaires (c’est-à-dire non matérialisées) à d’autres égards. En outre, les vues matérialisées présentent certaines similitudes avec les résultats mis en cache, notamment parce qu’elles permettent de stocker les résultats des requêtes pour une utilisation ultérieure.

Cette section décrit certaines des similitudes et des différences entre ces objets dans des domaines spécifiques, notamment :

  • Performances de la requête.

  • Sécurité de la requête.

  • Réduction de la complexité de la logique de requête.

  • Clustering de données (lié aux performances de la requête).

  • Frais de stockage et de maintenance.

Snowflake met en cache les résultats de la requête pendant une courte période après son exécution. Dans certaines situations, si la même requête est réexécutée, et si aucun élément n’a été modifié dans les tables auxquelles la requête accède, Snowflake peut simplement renvoyer les mêmes résultats sans réexécuter la requête. C’est la forme de réutilisation la plus rapide et la plus efficace, mais aussi la moins flexible. Pour plus de détails, voir Utilisation de résultats de requête persistants.

Les vues matérialisées et les résultats de requête en cache offrent des avantages en termes de performances de requête :

  • Les vues matérialisées sont plus souples, mais généralement plus lentes que les résultats mis en cache.

  • Les vues matérialisées sont plus rapides que les tables en raison de leur « cache » (c’est-à-dire les résultats de la requête pour la vue) ; en outre, si les données ont changé, elles peuvent utiliser leur « cache » pour les données qui n’ont pas changé et utiliser la table de base pour toutes les données qui ont été modifiées.

Les vues ordinaires ne mettent pas les données en cache et ne peuvent donc pas améliorer les performances grâce à la mise en cache. Cependant, dans certains cas, les vues aident Snowflake à générer un plan de requête plus efficace. Cependant, les vues matérialisées et les vues standard renforcent la sécurité des données en permettant à celles-ci d’être exposées ou masquées au niveau des lignes ou des colonnes.

Le tableau suivant indique les principales similitudes et différences entre les tables, les vues standard, les résultats de requête en cache et les vues matérialisées :

Avantages en termes de performances

Avantages en termes de sécurité

Simplifie la logique de requête

Prise en charge du clustering

Utilise le stockage

Utilise des crédits pour la maintenance

Remarques

Table ordinaire

Vue ordinaire

Résultat de la requête en cache

Utilisé uniquement si les données n’ont pas changé et si la requête utilise uniquement des fonctions déterministes (par exemple, pas CURRENT_DATE).

Vue matérialisée

Les exigences de stockage et de maintenance entraînent généralement une augmentation des coûts.

Table externe

Les données sont conservées en dehors de Snowflake et, par conséquent, n’entraînent aucun frais de stockage au sein de Snowflake.

Exemples de cas d’utilisation pour les vues matérialisées

Cette section décrit certains scénarios d’utilisation générale qui fournissent également un aperçu conceptuel des vues matérialisées :

  • Supposons que vous exécutiez chaque jour une requête Q comprenant une sous-requête S. Si S consomme beaucoup de ressources et interroge des données qui ne changent qu’une fois par semaine, vous pouvez améliorer les performances de la requête externe Q en exécutant S et en mettant en cache les résultats dans une table nommée CT :

    • Vous ne mettriez à jour la table qu’une fois par semaine.

    • Le reste du temps, lorsque vous exécutez Q, les résultats de la sous-requête de S sont référencés dans la table.

    Cela fonctionnerait bien tant que les résultats de la sous-requête S changeraient de manière prévisible (par exemple, à la même heure chaque semaine).

    Toutefois, si les résultats de S changent de manière imprévisible, la mise en cache des résultats dans une table est risquée. Parfois, votre requête principale Q renverra des résultats obsolètes si les résultats de la sous-requête S sont obsolètes (et donc les résultats de la table CT mise en cache ne sont plus à jour).

    Idéalement, vous auriez besoin d’un type de cache spécial pour des résultats qui changent rarement, mais pour lesquels le moment du changement est imprévisible. En l’envisageant sous un autre angle, vous souhaitez forcer la sous-requête S à être réexécutée (et votre table CT en cache à être mise à jour) si nécessaire.

    Une vue matérialisée implémente une approximation du meilleur des deux mondes. Vous définissez une requête pour votre vue matérialisée et les résultats de celle-ci sont mis en cache (comme s’ils étaient stockés dans une table interne), mais Snowflake met à jour le cache lorsque la table sur laquelle la vue matérialisée est définie est mise à jour. Ainsi, vos résultats de sous-requête sont facilement disponibles pour des performances rapides.

  • Comme exemple moins abstrait, supposons que vous dirigiez une petite succursale d’une grande pharmacie et que votre succursale stocke des centaines de médicaments sur un total de dizaines de milliers de médicaments approuvés par FDA.

    Supposons également que vous disposiez d’une liste complète de tous les médicaments que chacun de vos clients prend, et que presque tous ces clients ne commandent que des médicaments en stock (les commandes spéciales sont rares).

    Dans ce cas de figure, vous pouvez créer une vue matérialisée répertoriant uniquement les interactions entre les médicaments que vous avez en stock. Lorsqu’un client commande un médicament qu’il n’a jamais utilisé auparavant, si ce dernier et tous les autres médicaments qu’il prend sont couverts par votre vue matérialisée, vous n’avez pas besoin de consulter la totalité de la base de données FDA pour connaître les interactions médicamenteuses. Vous pouvez simplement vérifier la vue matérialisée afin que la recherche soit plus rapide.

  • Vous pouvez utiliser une vue matérialisée par elle-même ou dans une jointure.

    En reprenant l’exemple de la pharmacie, supposons que vous disposiez d’une table répertoriant tous les médicaments que chacun de vos clients prend ; vous pouvez associer cette table à la vue matérialisée des interactions médicamenteuses pour déterminer lequel des médicaments actuels du client pourrait interagir avec le nouveau médicament.

    Vous pourriez utiliser une jointure externe pour vous assurer de répertorier tous les médicaments du client, qu’ils soient ou non dans votre vue matérialisée. Si la jointure externe indique que l’un des médicaments actuels ne figure pas dans la vue matérialisée, vous pouvez réexécuter la requête sur la table complète des interactions médicamenteuses.

Comment l’optimiseur de requêtes utilise les vues matérialisées

Vous n’avez pas besoin de spécifier une vue matérialisée dans une instruction SQL pour que la vue soit utilisée. L’optimiseur de requêtes peut réécrire automatiquement les requêtes sur la table de base ou sur des vues ordinaires pour utiliser la vue matérialisée à la place.

Par exemple, supposons qu’une vue matérialisée contienne toutes les lignes et colonnes nécessaires à une requête sur une table de base. L’optimiseur peut décider de réécrire la requête pour utiliser la vue matérialisée, plutôt que la table de base. Cela peut considérablement accélérer une requête, en particulier si la table de base contient une grande quantité de données historiques.

Comme autre exemple, dans une jointure multi-table, l’optimiseur peut décider d’utiliser une vue matérialisée au lieu d’une table pour l’une des tables de la jointure.

Note

Même si une vue matérialisée peut remplacer la table de base dans une requête particulière, l’optimiseur peut ne pas utiliser la vue matérialisée. Par exemple, si la table de base est mise en cluster par un champ, l’optimiseur peut choisir d’analyser la table de base (plutôt que la vue matérialisée) car l’optimiseur peut éliminer efficacement les partitions et fournir des performances équivalentes à l’aide de la table de base.

Une vue matérialisée peut également être utilisée comme source de données pour une sous-requête.

Lorsque l’optimiseur choisit d’utiliser implicitement une vue matérialisée, la vue matérialisée est répertoriée dans le plan EXPLAIN ou le profil de requête au lieu de la table de base. Vous pouvez utiliser ces informations pour expérimenter et comprendre quelles requêtes peuvent bénéficier des vues matérialisées existantes.

À propos des vues matérialisées dans Snowflake

Les sections suivantes expliquent comment les vues matérialisées sont représentées dans Snowflake.

Commandes DDL pour les vues matérialisées

Les vues matérialisées sont des objets de base de données de première classe. Snowflake fournit les commandes DDL suivantes pour créer et gérer les vues matérialisées :

Opérations DML sur des vues matérialisées

Snowflake n’autorise pas le DML standard (par exemple INSERT, UPDATE, DELETE) sur les vues matérialisées. Snowflake ne permet pas aux utilisateurs de tronquer les vues matérialisées.

Pour plus de détails, voir Travailler avec des vues matérialisées : limites (dans ce chapitre).

Privilèges de contrôle d’accès

Il existe trois types de privilèges liés aux vues matérialisées :

  • Privilèges sur le schéma contenant la vue matérialisée.

  • Privilèges directs sur la vue matérialisée elle-même.

  • Privilèges sur les objets de base de données (par exemple, les tables) auxquels la vue matérialisée accède.

Vous pouvez utiliser les commandes standard pour octroyer et révoquer des privilèges sur des vues matérialisées :

Privilèges sur le schéma d’une vue matérialisée

Les vues matérialisées consomment de l’espace de stockage. Pour créer une vue matérialisée, vous avez besoin du privilège CREATE MATERIALIZED VIEW sur le schéma qui contiendra la vue matérialisée. Vous devrez exécuter une instruction similaire à celle-ci :

GRANT CREATE MATERIALIZED VIEW ON SCHEMA <schema_name> TO ROLE <role_name>;
Copy

Pour plus de détails sur l’instruction GRANT , voir GRANT <privilèges>.

Privilèges sur une vue matérialisée

Semblables à d’autres objets de base de données (tables, vues, UDFs, etc.), les vues matérialisées appartiennent à un rôle et disposent de privilèges pouvant être accordés à d’autres rôles.

Vous pouvez accorder les privilèges suivants sur une vue matérialisée :

  • SELECT

Comme pour les vues non matérialisées, une vue matérialisée n’hérite pas automatiquement des privilèges de sa table de base. Vous devez explicitement accorder des privilèges sur la vue matérialisée aux rôles qui doivent utiliser cette vue.

Note

L’exception à cette règle est lorsque l’optimiseur de requêtes réécrit une requête sur la table de base pour utiliser la vue matérialisée (comme expliqué dans Comment l’optimiseur de requêtes utilise les vues matérialisées). Dans ce cas, l’utilisateur n’a pas besoin de privilèges pour utiliser la vue matérialisée afin d’accéder aux résultats de la requête.

Privilèges sur les objets de base de données accessibles par la vue matérialisée

Comme pour les vues non matérialisées, un utilisateur qui souhaite accéder à une vue matérialisée a besoin de privilèges uniquement sur la vue, et non sur le ou les objets sous-jacents référencés par la vue.

Vues matérialisées sécurisées

Les vues matérialisées peuvent être des vues sécurisées.

La plupart des informations sur les vues sécurisées s’appliquent aux vues matérialisées sécurisées. Il existe quelques cas où les vues matérialisées sécurisées sont différentes des vues sécurisées non matérialisées. Les différences sont les suivantes :

  • La commande pour savoir si une vue est sécurisée.

    • Pour les vues non matérialisées, vérifiez la colonne IS_SECURE dans le résultat de la commande SHOW VIEWS.

    • Pour les vues matérialisées, consultez la colonne IS_SECURE dans le résultat de la commande SHOW MATERIALIZED VIEWS.

Pour plus d’informations sur les vues sécurisées, voir Utilisation de vues sécurisées.

La syntaxe permettant de créer des vues matérialisées sécurisées est documentée à l’adresse CREATE MATERIALIZED VIEW.

Créer des vues matérialisées et travailler avec

Cette section fournit des informations sur la création et l’utilisation de vues matérialisées.

Planification de la création d’une vue matérialisée

Lorsque vous décidez de créer une vue matérialisée, envisagez de faire une analyse pour déterminer quelle est l’exigence d’une telle vue :

  1. Examinez les filtres, les projections et les agrégations de requêtes fréquentes ou coûteuses.

  2. Utilisez le profil de requête et la commande EXPLAIN pour voir si les vues matérialisées existantes sont déjà utilisées par la fonction de réécriture automatique des requêtes. Vous constaterez peut-être que vous n’avez pas besoin de créer de nouvelles vues matérialisées s’il existe des vues existantes qui correspondent bien aux requêtes.

  3. Avant d’ajouter des vues matérialisées, enregistrez les coûts et les performances actuels de la requête afin de pouvoir évaluer la différence après avoir créé la nouvelle vue matérialisée.

  4. Si vous trouvez des requêtes avec des filtres très sélectifs qui ne bénéficient pas du clustering de la table, une vue matérialisée contenant les mêmes filtres peut aider les requêtes à éviter d’analyser beaucoup de données.

    De même, si vous trouvez des requêtes qui utilisent l’agrégation, ou qui contiennent des expressions qui sont très coûteuses à évaluer (par exemple, des appels de fonction coûteux ou des opérations coûteuses sur des données semi-structurées), alors une vue matérialisée qui utilise la ou les mêmes expressions ou agrégation(s) peut offrir un avantage.

  5. Exécutez la commande EXPLAIN sur les requêtes d’origine, ou exécutez les requêtes et vérifiez le profil de requête pour voir si la nouvelle vue matérialisée est utilisée.

  6. Surveillez la requête combinée et les coûts de vue matérialisée et évaluez si les performances ou les avantages en termes de coûts justifient le coût de maintenance de la vue matérialisée.

    Examinez également les coûts de requête de la table de base. Dans les cas où l’optimiseur peut réécrire la requête pour utiliser une vue matérialisée, la compilation de requête peut consommer plus de temps et de ressources. (L’optimiseur a un plus grand nombre de possibilités à considérer.)

  7. N’oubliez pas que vous pouvez toujours référencer directement les vues matérialisées si cela simplifie vos requêtes ou si vous savez qu’une vue matérialisée vous donnera de meilleures performances. Cependant, dans la plupart des cas, vous pouvez simplement interroger la table de base et la fonction de réécriture automatique des requêtes le fera pour vous.

Création d’une vue matérialisée

Utilisez la commande CREATE MATERIALIZED VIEW pour créer une vue matérialisée. Pour voir un exemple, voir Exemple de base : Création d’une vue matérialisée (dans ce chapitre).

Note

L’instruction CREATE MATERIALIZED VIEW peut prendre beaucoup de temps.

Lors de la création initiale d’une vue matérialisée, Snowflake effectue l’équivalent d’une opération CTAS (CREATE TABLE … AS ….).

Lorsque vous créez une vue matérialisée, notez les points suivants :

Gestion des noms de colonnes non autorisés dans les vues matérialisées

Les noms de colonnes suivants ne sont pas autorisés dans une vue matérialisée :

  • Les noms qui commencent par SYSTEM$ ou METADATA$

  • Les noms qui contiennent $SYS_FACADE$

  • Le nom de colonne SYS_MV_SOURCE_PARTITION

Si vous définissez une vue matérialisée qui sélectionne une colonne portant l’un de ces noms, vous pouvez définir un alias pour cette colonne. Par exemple :

CREATE OR REPLACE MATERIALIZED VIEW mv AS
  SELECT SYSTEM$ALPHA AS col1, ...
Copy

Référence à la table de base

Dans la mesure du possible, utilisez le nom complet de la table de base référencé dans une vue matérialisée. Ceci isole la vue des modifications susceptibles d’invalider la vue, telles que le déplacement de la table de base vers un schéma différent de la vue (ou inversement).

Si le nom de la table de base n’est pas qualifié et que la table ou la vue est déplacée vers un autre schéma, la référence devient non valide.

De plus, si vous faites référence à la table de base plus d’une fois dans la définition de la vue, utilisez le même qualificatif dans toutes les références à la table de base. Par exemple, si vous choisissez d’utiliser le nom complet, assurez-vous que toutes les références à la table de base utilisent le nom complet.

Spécification de filtres pour l’optimisation des requêtes

Si vous spécifiez un filtre lors de la création d’une vue matérialisée (par exemple WHERE column_1 BETWEEN Y and Z), l’optimiseur peut utiliser la vue matérialisée pour les requêtes sur la table de base qui ont le même filtre ou un filtre plus restrictif. Voici quelques exemples :

  • Voici un exemple simple de subsomption de plage.

    Dans cet exemple, le filtre de la requête ne correspond pas au filtre de la vue matérialisée. Cependant, le filtre de la requête sélectionne uniquement les lignes qui se trouvent dans la vue matérialisée, de sorte que l’optimiseur puisse choisir d’analyser la vue matérialisée plutôt que la table entière.

    -- Example of a materialized view with a range filter
    create materialized view v1 as
      select * from table1 where column_1 between 100 and 400;
    
    Copy
    -- Example of a query that might be rewritten to use the materialized view
    select * from table1 where column_1 between 200 and 300;
    
    Copy
  • Cet exemple montre la subsomption OR. La vue matérialisée contient toutes les lignes dont la requête suivante a besoin.

    Définissez une vue matérialisée contenant toutes les lignes ayant la valeur X ou la valeur Y :

    create materialized view mv1 as
      select * from tab1 where column_1 = X or column_1 = Y;
    
    Copy

    Définissez une requête qui recherche uniquement la valeur Y (qui est incluse dans la vue matérialisée) :

    select * from tab1 where column_1 = Y;
    
    Copy

    La requête ci-dessus peut être réécrite en interne comme suit :

    select * from mv1 where column_1 = Y;
    
    Copy
  • Cet exemple est un autre exemple de subsomption OR . Il n’y a pas de OR explicite dans la définition de vue matérialisée. Cependant, une clause IN équivaut à une série d’expressions OR , de sorte que l’optimiseur puisse réécrire cette requête de la même manière qu’il a réécrit l’exemple de subsomption OR ci-dessus :

    create materialized view mv1 as
      select * from tab1 where column_1 in (X, Y);
    
    Copy

Définissez une requête qui recherche uniquement la valeur Y (qui est incluse dans la vue matérialisée) :

select * from tab1 where column_1 = Y;
Copy

La requête ci-dessus peut être réécrite en interne comme suit :

select * from mv1 where column_1 = Y;
Copy
  • Cet exemple utilise la subsomption AND :

    Créez une vue matérialisée contenant toutes les lignes où column_1 = X.

    create materialized view mv2 as
      select * from table1 where column_1 = X;
    
    Copy

    Créez une requête :

    select column_1, column_2 from table1 where column_1 = X AND column_2 = Y;
    
    Copy

    La requête peut être réécrite ainsi :

    select * from mv2 where column_2 = Y;
    
    Copy

    La requête réécrite n’a même pas besoin d’inclure l’expression column_1 = X car la définition de la vue matérialisée exige déjà que toutes les lignes correspondent à column_1 = X.

  • L’exemple suivant montre la subsomption agrégée :

    La vue matérialisée est définie ci-dessous :

    create materialized view mv4 as
      select column_1, column_2, sum(column_3) from table1 group by column_1, column_2;
    
    Copy

    La requête suivante peut utiliser la vue matérialisée définie ci-dessus :

    select column_1, sum(column_3) from table1 group by column_1;
    
    Copy

    La requête peut être réécrite ainsi :

    select column_1, sum(column_3) from mv4 group by column_1;
    
    Copy

    La requête réécrite ne tire pas parti du regroupement supplémentaire par colonne_2, mais la requête réécrite n’est pas non plus bloquée par ce regroupement supplémentaire.

Limitations relatives à la création de vues matérialisées

Note

Ce sont des limitations actuelles ; certaines d’entre elles pourraient être supprimées ou modifiées dans les versions futures.

Les limitations suivantes s’appliquent à la création de vues matérialisées :

  • Une vue matérialisée ne peut interroger qu’une seule table.

  • Les jointures, y compris les jointures automatiques, ne sont pas prises en charge.

  • Une vue matérialisée ne peut pas interroger :

    • Une vue matérialisée.

    • Une vue non matérialisée.

    • Une fonction de table définie par l’utilisateur (UDTF).

  • Une vue matérialisée ne peut pas inclure :

    • des UDFs (cette limitation s’applique à tous les types de fonctions définies par l’utilisateur, y compris les fonctions externes).

    • des fonctions de fenêtrage.

    • Des clauses HAVING.

    • Des clauses ORDER BY.

    • Des clauses LIMIT.

    • De clés GROUP BY qui ne figurent pas dans la liste SELECT. Toutes les clés GROUP BY d’une vue matérialisée doivent faire partie de la liste SELECT.

    • GROUP BY GROUPING SETS.

    • GROUP BY ROLLUP.

    • GROUP BY CUBE.

    • Imbrication de sous-requêtes dans une vue matérialisée.

    • Les opérateurs d’ensemble MINUS, EXCEPT ou INTERSECT.

  • De nombreuses fonctions d’agrégation ne sont pas autorisées dans une définition de vue matérialisée.

    Note

    Les fonctions d’agrégation autorisées dans les vues matérialisées présentent encore certaines restrictions :

    • Les fonctions d’agrégation ne peuvent pas être imbriquées.

    • Les fonctions d’agrégation utilisées dans des expressions complexes (par exemple (sum(salary)/10)) ne peuvent être utilisées qu’au niveau le plus extérieur d’une requête, pas dans une sous-requête ou une vue en ligne.

      Par exemple, ce qui suit est autorisé :

      create materialized view mv1 as
          select
              sum(x) + 100
            from t;
      
      Copy

      Ce qui suit n’est pas autorisé :

      create materialized view mv2 as
          select
              y + 10
            from (
              select
                sum(x) as y
              from t
            );
      
      Copy
    • DISTINCT ne peut pas être combiné avec des fonctions d’agrégation.

    • Dans une vue matérialisée, les fonctions d’agrégation AVG, COUNT, COUNT_IF, MIN, MAX et SUM peuvent être utilisées en tant que fonctions d’agrégation, mais pas en tant que fonctions de fenêtre. Dans une vue matérialisée, ces fonctions ne peuvent pas être utilisées avec la clause OVER :

      OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> ] )
      
      Copy
    • Si une fonction d’agrégation est dans une sous-requête, la vue matérialisée ne peut pas créer d’expression au-dessus des colonnes agrégées de cette sous-requête. Par exemple, considérons la définition de vue matérialisée suivante :

      create or replace materialized view mv1 as
          select c1 + 10 as c1new, c2
              from (select sum(c1) as c1, c2 from t group by c2);
      
      Copy

      L’expression « c1 + 10 » est une expression placée au-dessus d’une fonction d’agrégation dans une sous-requête et provoque donc un message d’erreur.

      Notez que même un opérateur d’égalité compte comme une expression, ce qui signifie que les expressions CASE utilisant des colonnes représentant des fonctions d’agrégation dans une sous-requête sont également interdites.

      Pour contourner cette limitation, créez une vue matérialisée sans l’expression, puis créez une vue non matérialisée incluant l’expression, par exemple :

      create or replace materialized view mv1 as
          select c1, c2
              from (select sum(c1) as c1, c2 from t group by c2);
      
      create or replace view expr_v1 as
          select c1 + 10 as c1new, c2
              from (select c1, c2 from mv1);
      
      Copy
  • Les fonctions utilisées dans une vue matérialisée doivent être déterministes. Par exemple, l’utilisation de CURRENT_TIME ou CURRENT_TIMESTAMP n’est pas autorisée.

  • Une vue matérialisée ne doit pas être définie à l’aide d’une fonction qui produit des résultats différents pour différents réglages de paramètres, tels que le paramètre au niveau de la session TIMESTAMP_TYPE_MAPPING.

    Par exemple, supposons qu’une vue est définie comme suit :

    create materialized view bad_example (ts1) as
        select to_timestamp(n) from t1;
    
    Copy

    Le type de données de la valeur de retour de TO_TIMESTAMP(n) dépend du paramètre TIMESTAMP_TYPE_MAPPING, donc le contenu de la vue matérialisée dépend de la valeur de TIMESTAMP_TYPE_MAPPING au moment de la création de la vue.

    Lorsqu’une vue matérialisée est créée, l’expression définissant chacune de ses colonnes est évaluée et stockée. Si une définition de colonne dépend d’une variable de session particulière et que la variable de session change, l’expression n’est pas réévaluée et la vue matérialisée n’est pas mise à jour. Si la vue matérialisée dépend d’une valeur particulière d’une variable de session et si la valeur de la variable de session a changé, les requêtes sur la vue matérialisée échouent.

    Pour éviter ce problème, forcez l’expression à une valeur qui ne dépend d’aucune variable de session. L’exemple ci-dessous convertit la sortie en un type de données particulier, indépendamment du paramètre TIMESTAMP_TYPE_MAPPING :

    create materialized view good_example (ts1) as
        select to_timestamp(n)::TIMESTAMP_NTZ from t1;
    
    Copy

    Ce problème est spécifique aux vues matérialisées. Les vues non matérialisées génèrent leur sortie de manière dynamique en fonction des paramètres actuels, de sorte que les résultats ne puissent pas être périmés.

  • Dans la définition d’une vue matérialisée, la sélection de la colonne SEQ à partir de la sortie de la fonction FLATTEN n’est pas prise en charge.

    Il n’est pas garanti que les valeurs de la colonne SEQ soient ordonnées de quelque manière que ce soit lorsqu’elles sont sélectionnées à partir d’une vue matérialisée. Si vous sélectionnez cette colonne dans la définition de la vue matérialisée, la sortie peut être indéterminée.

  • Les vues matérialisées ne peuvent pas être créées en utilisant la fonction Time Travel.

Exemple de base : Création d’une vue matérialisée

Cette section contient un exemple de base de création et d’utilisation d’une vue matérialisée :

CREATE OR REPLACE MATERIALIZED VIEW mv1 AS
  SELECT My_ResourceIntensive_Function(binary_col) FROM table1;

SELECT * FROM mv1;
Copy

Des exemples plus détaillés sont fournis dans Exemples (dans cette rubrique).

Présentation du mode de maintenance des vues matérialisées

Une fois que vous avez créé une vue matérialisée, un processus en arrière-plan conserve automatiquement les données dans la vue matérialisée. Remarques :

  • La maintenance des vues matérialisées est effectuée par un processus en tâche de fond, et le calendrier est optimisé en fonction de la charge de travail sur la table de base et la vue matérialisée.

    • Ce processus met à jour la vue matérialisée avec les modifications apportées par les opérations DML à la table de base (insertions, mises à jour et suppressions).

      En outre, le clustering sur la table de base peut également entraîner les actualisations d’une vue matérialisée. Reportez-vous à Meilleures pratiques pour le clustering des vues matérialisées et de leurs tables de base.

    • Lorsque des lignes sont insérées dans la table de base, le processus effectue une opération d”« actualisation » pour insérer les nouvelles lignes dans la vue matérialisée.

    • Lorsque des lignes sont supprimées dans la table de base, le processus effectue une opération de « compactage » sur la vue matérialisée, en supprimant ces lignes de la vue matérialisée.

  • Pour connaître la dernière fois qu’une vue matérialisée a été actualisée, exécutez la commande SHOW MATERIALIZED VIEWS.

    Vérifiez les colonnes REFRESHED_ON et BEHIND_BY dans la sortie :

    • Les colonnes REFRESHED_ON et COMPACTED_ON indiquent l’horodatage de la dernière opération DML sur la table de base qui a été traitée par les opérations d’actualisation et de compactage, respectivement.

    • La colonne BEHIND_BY indique le temps de retard des mises à jour de la vue matérialisée par rapport aux mises à jour de la table de base.

  • Si la maintenance prend du retard, les requêtes risquent de s’exécuter plus lentement que si les vues étaient à jour, mais les résultats seront toujours à jour.

    Si des micro-partitions de la vue matérialisée sont obsolètes, Snowflake ignore ces partitions et recherche des données dans la table de base.

  • Si le processus d’arrière-plan rencontre certaines erreurs de l’utilisateur (par exemple, la requête pour la vue aboutit à une erreur de « division par zéro »), le processus invalide la vue matérialisée.

    La requête d’une vue matérialisée non valide entraîne une erreur. Le message d’erreur comprend la raison pour laquelle la vue matérialisée a été invalidée. Par exemple :

    Failure during expansion of view 'MY_MV':
      SQL compilation error: Materialized View MY_MV is invalid.
      Invalidation reason: Division by zero
    

    Si cela se produit, résolvez le problème décrit dans le message d’erreur (par exemple, supprimez les lignes qui introduisent l’erreur « division par zéro ») et reprenez la vue matérialisée à l’aide de la commande ALTER MATERIALIZED VIEW… RESUME.

Suspension et reprise de la maintenance d’une vue matérialisée

Si vous devez suspendre la maintenance et l’utilisation d’une vue matérialisée, exécutez la commande ALTER MATERIALIZED VIEW avec le paramètre SUSPEND :

ALTER MATERIALIZED VIEW <name> SUSPEND
Copy

Si vous suspendez la maintenance d’une vue, vous ne pouvez pas interroger celle-ci avant de la reprendre.

Pour reprendre la maintenance et l’utilisation d’une vue matérialisée, exécutez la commande ALTER MATERIALIZED VIEW avec le paramètre RESUME :

ALTER MATERIALIZED VIEW <name> RESUME
Copy

Pour un exemple, voir Suspension des mises à jour d’une vue matérialisée.

Affichage d’informations sur les vues matérialisées

La commande et la vue suivantes fournissent des informations sur les vues matérialisées :

  • La commande SHOW VIEWS renvoie des informations sur les vues matérialisées et ordinaires.

  • La vue INFORMATION_SCHEMA.TABLES présente des vues matérialisées. La colonne TABLE_TYPE indique « MATERIALIZED VIEW ». La colonne IS_INSERTABLE est toujours « NO », car vous ne pouvez pas effectuer d’insertion directement dans une vue matérialisée.

    Note

    La vue INFORMATION_SCHEMA.VIEWS n’affiche pas de vues matérialisées. Les vues matérialisées sont indiquées par INFORMATION_SCHEMA.TABLES.

Travailler avec des vues matérialisées : limites

Note

Ce sont des limitations actuelles ; certaines d’entre elles pourraient être supprimées ou modifiées dans les versions futures.

Les limites suivantes s’appliquent à l’utilisation de vues matérialisées :

  • Pour vous assurer que les vues matérialisées restent cohérentes avec la table de base sur laquelle elles sont définies, vous ne pouvez pas effectuer la plupart des opérations DML sur une vue matérialisée elle-même. Par exemple, vous ne pouvez pas insérer de lignes directement dans une vue matérialisée (bien que vous puissiez bien sûr insérer des lignes dans la table de base). Les opérations interdites DML incluent :

    • COPY

    • DELETE

    • INSERT

    • MERGE

    • UPDATE

    La troncation d’une vue matérialisée n’est pas prise en charge.

  • Vous ne pouvez pas cloner directement une vue matérialisée à l’aide de la commande CREATE MATERIALIZED VIEW ... CLONE.... Toutefois, si vous clonez un schéma ou une base de données contenant une vue matérialisée, la vue matérialisée sera clonée et incluse dans le nouveau schéma ou la nouvelle base de données.

  • Snowflake ne prend pas en charge l’utilisation de la fonction Time Travel pour interroger des vues matérialisées à un point dans le passé (par exemple, en utilisant la clause AT lors de l’interrogation d’une vue matérialisée).

    Cependant, vous pouvez utiliser Time Travel pour cloner une base de données ou un schéma contenant une vue matérialisée à un point dans le passé. Pour plus de détails, voir Vues matérialisées et Time Travel.

  • Les vues matérialisées ne sont pas surveillées par Snowflake Utilisation de moniteurs de ressources.

Effets des modifications apportées aux tables de base sur les vues matérialisées

Les sections suivantes expliquent comment les vues matérialisées sont affectées par les modifications apportées aux tables de base.

Ajout de colonnes à la table de base

Si des colonnes sont ajoutées à la table de base, ces nouvelles colonnes ne sont pas automatiquement propagées aux vues matérialisées.

Cela est vrai même si la vue matérialisée a été définie avec SELECT *, (par exemple CREATE MATERIALIZED VIEW AS SELECT * FROM table2 ...). Les colonnes de la vue matérialisée sont définies au moment où la vue matérialisée est définie. Le SELECT * n’est pas interprété de manière dynamique chaque fois que la vue matérialisée est interrogée.

Pour éviter toute confusion, Snowflake recommande de ne pas utiliser SELECT * dans la définition d’une vue matérialisée.

Note

L’ajout d’une colonne à la table de base ne suspend pas une vue matérialisée créée sur cette table de base.

Modification ou suppression de colonnes dans la table de base

Si une table de base est modifiée pour que des colonnes existantes soient modifiées ou supprimées, toutes les vues matérialisées de cette table de base sont suspendues. Les vues matérialisées ne peuvent être ni utilisées ni maintenues. (Cela est vrai même si la colonne modifiée ou supprimée ne faisait pas partie de la vue matérialisée.)

Vous ne pouvez pas RESUME cette vue matérialisée. Si vous souhaitez l’utiliser de nouveau, vous devez la recréer.

La façon la plus simple de recréer une vue matérialisée avec les mêmes privilèges sur la vue est d’exécuter la commande :

CREATE OR REPLACE MATERIALIZED VIEW <view_name> ... COPY GRANTS ...
Copy

Cette méthode est plus efficace que l’exécution de commandes distinctes pour :

  1. Détruire la vue matérialisée (DROP MATERIALIZED VIEW).

  2. Créer à nouveau la vue matérialisée (CREATE MATERIALIZED VIEW).

  3. Créer les mêmes privilèges sur la vue (GRANT et REVOKE).

Renommer ou remplacer la table de base

Le fait de renommer ou d’échanger la table de base (ou le schéma ou la base de données contenant la table de base) peut faire en sorte que la vue matérialisée pointe vers une table de base différente de celle qui a été utilisée pour créer la vue matérialisée. Voici quelques exemples de situations dans lesquelles cela peut se produire :

  • La table de base est renommée (par ALTER TABLE … RENAME) et une autre table est créée avec le nom original de la table de base.

  • La table de base d’une vue matérialisée est remplacée par une autre table (via ALTER TABLE … SWAP WITH).

  • Le schéma ou la base de données contenant la table de base de la vue matérialisée est déplacé(e) par DROP, SWAP ou RENAME.

Dans ce cas, la vue matérialisée est suspendue. Dans la plupart des cas, vous devez recréer la vue matérialisée pour pouvoir l’utiliser.

Suppression de la table de base

Si une table de base est détruite, la vue matérialisée est suspendue (mais pas automatiquement détruite).

Dans la plupart des cas, la vue matérialisée doit être détruite.

Si, pour une raison quelconque, vous recréez la table de base et souhaitez également recréer la vue matérialisée avec la même définition que précédemment, recréez d’abord la table de base, puis remplacez la vue à l’aide de CREATE OR REPLACE MATERIALIZED VIEW <nom_vue> ... COPY GRANTS ....

Vues matérialisées dans les schémas et bases de données clonés

Si vous clonez un schéma ou une base de données contenant une vue matérialisée, la vue matérialisée est clonée.

Si vous clonez simultanément la vue matérialisée et la table de base correspondante (dans la même opération CREATE SCHEMA ... CLONE ou CREATE DATABASE ... CLONE), la vue matérialisée clonée se réfère à la table de base clonée.

Si vous clonez la vue matérialisée sans cloner la table de base (par exemple, si la table se trouve dans Database1.Schema1 et la vue dans Database1.Schema2 et que vous clonez uniquement Schema2 plutôt que l’intégralité de Database1), la vue clonée fera référence à la table de base d’origine.

Coût des vues matérialisées

Les vues matérialisées ont uune incidence sur vos coûts de stockage et de ressources de calcul :

  • Stockage : chaque vue matérialisée stocke les résultats de la requête, ce qui s’ajoute à l’utilisation de stockage mensuelle de votre compte.

  • Ressources de calcul : afin d’éviter que les vues matérialisées ne deviennent obsolètes, Snowflake effectue automatiquement la maintenance en arrière-plan des vues matérialisées. Lorsqu’une table de base est modifiée, toutes les vues matérialisées définies sur la table sont mises à jour par un service d’arrière-plan utilisant les ressources de calcul fournies par Snowflake.

    Ces mises à jour peuvent consommer des ressources importantes, ce qui entraîne une utilisation accrue du crédit. Cependant, Snowflake garantit une utilisation efficace du crédit en ne facturant à votre compte que les ressources réellement utilisées. La facturation est calculée par incréments d’une seconde.

Pour savoir combien de crédits par heure de calcul sont consommés par des vues matérialisées, reportez-vous au « Tableau des crédits de fonctionnalité sans serveur » dans le tableau de consommation des services Snowflake.

Estimation et contrôle des coûts

Il n’existe aucun outil permettant d’estimer les coûts de maintenance des vues matérialisées. En général, les coûts sont proportionnels aux facteurs suivants :

  • Le nombre de vues matérialisées créées sur chaque table de base et la quantité de données modifiées dans chacune des vues matérialisées lorsque la table de base change. Toute modification des micro-partitions dans la table de base nécessite une éventuelle maintenance de la vue matérialisée, que ces modifications soient dues à un reclustering ou à des instructions DML exécutées sur la table de base.

  • Nombre de ces vues matérialisées qui sont mises en cluster. Le maintien du clustering (d’une table ou d’une vue matérialisée) augmente les coûts.

    Si une vue matérialisée est mise en cluster différemment de la table de base, le nombre de micropartitions modifiées dans la vue matérialisée peut être sensiblement plus grand que le nombre de micropartitions modifiées dans la table de base.

    Par exemple, considérons le cas où la table de base est modifiée en grande partie par l’insertion (ajout) de données et n’est pas mise en cluster, la table de base est alors en grande partie dans l’ordre dans lequel les lignes ont été insérées dans la table. Imaginez que la vue matérialisée est mise en cluster par une colonne indépendante, par exemple, le code postal. Si 100 nouvelles lignes sont ajoutées à la table de base, celles-ci peuvent entrer dans une ou deux nouvelles micropartitions, sans toucher aux autres micropartitions de la table de base. Mais ces 100 lignes peuvent nécessiter une réécriture de 100 micropartitions dans la vue matérialisée en cluster.

    Comme autre exemple, considérez les suppressions. La suppression des lignes les plus anciennes dans une table de base non clusterisée peut supprimer uniquement les micropartitions les plus anciennes, mais peut nécessiter des modifications d’un nombre beaucoup plus élevé de micropartitions dans une vue matérialisée qui n’est pas mise en cluster par âge.

    (Pour plus d’informations sur le clustering des vues matérialisées, voir : Vues matérialisées et clustering.)

Vous pouvez contrôler le coût de maintenance des vues matérialisées en choisissant avec soin le nombre de vues à créer, les tables sur lesquelles les créer, et la définition de chaque vue (y compris le nombre de lignes et de colonnes dans cette vue).

Vous pouvez également contrôler les coûts en suspendant ou en reprenant la vue matérialisée. Cependant, suspendre la maintenance ne fait généralement que différer les coûts au lieu de les réduire. Plus la maintenance est différée, plus il y a de maintenance à faire.

Voir aussi Meilleures pratiques pour la gestion des vues matérialisées.

Astuce

Si vous êtes préoccupé par les coûts associés à la gestion de vues matérialisées, Snowflake recommande de commencer lentement avec cette fonctionnalité (c.-à-d. de ne créer que quelques vues matérialisées sur des tables sélectionnées) et de surveiller les coûts au fil du temps.

Affichage des coûts

Vous pouvez afficher les coûts de facturation pour la gestion des vues matérialisées à l’aide de Snowsight, de l”Classic Console, ou de SQL :

Snowsight:

En tant qu’utilisateur disposant des privilèges adéquats, sélectionnez Admin » Usage.

Classic Console:

En tant qu’administrateur de compte, sélectionnez Account Onglet Compte » Billing & Usage.

Les coûts de crédit sont suivis dans un entrepôt virtuel géré par Snowflake nommé Logo Snowflake en bleu (sans texte) MATERIALIZED_VIEW_MAINTENANCE.

SQL:

Interrogez l’un des éléments suivants :

  • la fonction de table MATERIALIZED_VIEW_REFRESH_HISTORY (dans Schéma d’information de Snowflake).

    Par exemple :

    SELECT * FROM TABLE(INFORMATION_SCHEMA.MATERIALIZED_VIEW_REFRESH_HISTORY());
    
    Copy
  • la vue Vue MATERIALIZED_VIEW_REFRESH_HISTORY (dans Account Usage).

    Les requêtes suivantes peuvent être exécutées sur la vue MATERIALIZED_VIEW_REFRESH_HISTORY :

    Requête : historique des coûts en vues matérialisées (par jour, par objet).

    Cette requête fournit une liste complète des vues matérialisées et le volume de crédits consommés via le service au cours des 30 derniers jours, ventilés par jour. Toute irrégularité dans la consommation de crédit ou une consommation constamment élevée sont des signaux d’alerte qui entraînent la nécessité d’une investigation plus profonde.

    SELECT TO_DATE(start_time) AS date,
      database_name,
      schema_name,
      table_name,
      SUM(credits_used) AS credits_used
    FROM snowflake.account_usage.materialized_view_refresh_history
    WHERE start_time >= DATEADD(month,-1,CURRENT_TIMESTAMP())
    GROUP BY 1,2,3,4
    ORDER BY 5 DESC;
    
    Copy

    Requête : historique des vues matérialisées et moyenne sur un mois

    Cette requête montre les crédits quotidiens moyens consommés par les vues matérialisées regroupées par semaine au cours de la dernière année. Elle peut aider à identifier les anomalies dans les moyennes quotidiennes sur l’année, ce qui vous permet d’enquêter sur les pics ou les changements inattendus dans la consommation.

    WITH credits_by_day AS (
      SELECT TO_DATE(start_time) AS date,
        SUM(credits_used) AS credits_used
      FROM snowflake.account_usage.materialized_view_refresh_history
      WHERE start_time >= DATEADD(year,-1,CURRENT_TIMESTAMP())
      GROUP BY 1
      ORDER BY 2 DESC
    )
    
    SELECT DATE_TRUNC('week',date),
      AVG(credits_used) AS avg_daily_credits
    FROM credits_by_day
    GROUP BY 1
    ORDER BY 1;
    
    Copy

Note

Les moniteurs de ressources permettent de contrôler l’utilisation du crédit d’entrepôt virtuel. Cependant, vous ne pouvez pas les utiliser pour contrôler l’utilisation du crédit pour les entrepôts fournis par Snowflake, y compris l’entrepôt Logo Snowflake en bleu (sans texte) MATERIALIZED_VIEW_MAINTENANCE.

Vues matérialisées et clustering

La définition d’une clé de clustering sur une vue matérialisée est prise en charge et peut améliorer les performances dans de nombreuses situations. Cependant, cela ajoute également des coûts.

Si vous regroupez en cluster les vues matérialisées et la table de base sur laquelle sont définies les vues matérialisées, vous pouvez regrouper les vues matérialisées en cluster sur différentes colonnes à partir des colonnes utilisées pour regrouper en cluster la table de base.

Dans la plupart des cas, le clustering d’un sous-ensemble de vues matérialisées sur une table a tendance à être plus rentable que le clustering de la table elle-même. Si l’accès aux données de la table de base se fait (presque) exclusivement via les vues matérialisées et (presque) jamais directement via la table de base, le clustering de la table de base ajoute des coûts sans ajouter d’avantage.

Si vous envisagez de regrouper à la fois la table de base et les vues matérialisées, Snowflake recommande de commencer par regrouper uniquement les vues matérialisées et de surveiller les performances et les coûts avant et après l’ajout du clustering à la table de base.

Si vous envisagez de créer une table, de la charger et de créer une ou plusieurs vues matérialisées en cluster sur la table, Snowflake vous recommande de créer les vues matérialisées en dernier (après avoir chargé le plus de données possible). Cela peut permettre de réaliser des économies sur le chargement de données initial, car cela évite des efforts supplémentaires pour maintenir la mise en cluster de la vue matérialisée lors de son premier chargement.

Pour plus de détails sur le clustering, reportez-vous à :

Pour plus d’informations sur les coûts de clustering des vues matérialisées, voir :

Vues matérialisées et Time Travel

Actuellement, vous ne pouvez pas utiliser Time Travel pour interroger des données historiques pour les vues matérialisées.

Cependant, notez ce qui suit :

Meilleures pratiques pour les vues matérialisées

Les sections suivantes résument les meilleures pratiques pour travailler avec des vues matérialisées :

Meilleures pratiques pour la création de vues matérialisées

  • La plupart des vues matérialisées doivent produire l’un des résultats suivants :

    • Filtrer les données. Vous pouvez le faire de cette manière :

      • Filtrer les lignes, par exemple en définissant la vue matérialisée afin que seules les données très récentes soient incluses. Dans certaines applications, les meilleures données à stocker sont les données anormales. Par exemple, si vous surveillez la pression dans un pipeline pour estimer le risque de défaillance des canalisations, vous pouvez stocker toutes les données de pression dans la table de base et ne stocker que les mesures de pression anormalement élevées dans la vue matérialisée. De même, si vous surveillez le trafic réseau, votre table de base peut stocker toutes les informations de surveillance, tandis que votre vue matérialisée ne stocke que des informations inhabituelles et suspectes (par exemple, à partir d’adresses IP connues pour lancer des attaques par déni de service (DOS).

      • Filtrer des colonnes, par exemple en sélectionnant des colonnes spécifiques plutôt que « SELECT * … ». Utiliser SELECT * ... pour définir une vue matérialisée est généralement coûteux. Cela peut également conduire à des erreurs futures ; si des colonnes sont ajoutées ultérieurement à la table de base (par exemple : ALTER TABLE ... ADD COLUMN ...), la vue matérialisée n’intègre pas automatiquement la ou les nouvelles colonnes.

    • Effectuer des opérations exigeantes en ressources et stocker les résultats de sorte que les opérations exigeantes en ressources ne soient plus aussi fréquentes.

  • Vous pouvez créer plusieurs vues matérialisées pour la même table de base. Par exemple, vous pouvez créer une vue matérialisée contenant uniquement les données les plus récentes et une autre vue matérialisée stockant des données inhabituelles. Vous pouvez ensuite créer une vue non matérialisée qui joint les deux tables et affiche des données récentes qui correspondent à des données historiques inhabituelles afin de pouvoir détecter rapidement des situations inhabituelles telles qu’une attaque de type DOS (par déni de service) en cours de montée en puissance.

    Snowflake recommande les vues matérialisées pour des données inhabituelles uniquement lorsque :

    • La table de base n’est pas mise en cluster ou les colonnes contenant les données inhabituelles ne font pas déjà partie de la clé de clustering de la table de base.

    • Les données sont assez inhabituelles pour qu’elles soient faciles à isoler, mais pas si inhabituelles qu’elles sont rarement utilisées. (Si les données sont rarement utilisées, le coût de maintenance de la vue matérialisée l’emportera probablement sur les avantages en termes de performances et d’économies de coûts résultant de la possibilité d’y accéder rapidement lors de leur utilisation.)

Meilleures pratiques pour la gestion des vues matérialisées

  • Snowflake recommande le traitement par lots DML de la table de base :

    • DELETE : si les tables stockent des données pour la période la plus récente (par exemple, le jour, la semaine ou le mois le plus récent), lorsque vous ajustez votre table de base en supprimant les anciennes données, les modifications apportées à la table de base sont propagées à la vue matérialisée. Selon la manière dont les données sont distribuées sur les micro-partitions, cela pourrait vous amener à payer davantage pour les mises à jour en arrière-plan des vues matérialisées. Dans certains cas, vous pourrez peut-être réduire les coûts en effectuant des suppressions moins fréquemment (par exemple, tous les jours plutôt que toutes les heures, ou toutes les heures plutôt que toutes les 10 minutes).

      Si vous n’avez pas besoin de conserver une quantité spécifique de données anciennes, essayez de trouver le meilleur équilibre entre coût et fonctionnalité.

    • INSERT, UPDATE, et MERGE : le traitement par lots de ces types d’instructions DML sur la table de base peut réduire les coûts de maintenance des vues matérialisées.

Meilleures pratiques pour le clustering des vues matérialisées et de leurs tables de base

  • Si vous créez une vue matérialisée sur une table de base et si l’accès aux vues matérialisées est fréquent et si l’accès à la table de base est peu fréquent, il est généralement plus efficace d’éviter le clustering de la table de base.

    Si vous créez une vue matérialisée sur une table en cluster, envisagez de supprimer tout clustering sur la table de base, car toute modification du clustering de la table de base nécessitera éventuellement une actualisation de la vue matérialisée, ce qui s’ajoute aux coûts de maintenance de la vue matérialisée.

  • Le clustering des vues matérialisées, en particulier des vues matérialisées sur des tables de base qui changent fréquemment, augmente les coûts. Ne regroupez pas plus de vues matérialisées en cluster que nécessaire.

  • Presque toutes les informations sur les tables de clustering s’appliquent également au clustering des vues matérialisées. Pour plus d’informations sur le clustering des tables, voir Stratégies de sélection des clés de clustering.

Exemples

Cette section contient des exemples supplémentaires de création et d’utilisation de vues matérialisées. Pour un exemple d’introduction simple, voir Exemple de base : Création d’une vue matérialisée (dans cette rubrique).

Vue matérialisée simple

Ce premier exemple illustre une vue matérialisée simple et une requête simple sur la vue.

Créez la table et chargez les données, puis créez la vue :

CREATE TABLE inventory (product_ID INTEGER, wholesale_price FLOAT,
  description VARCHAR);
    
CREATE OR REPLACE MATERIALIZED VIEW mv1 AS
  SELECT product_ID, wholesale_price FROM inventory;

INSERT INTO inventory (product_ID, wholesale_price, description) VALUES 
    (1, 1.00, 'cog');
Copy

Sélectionnez des données à partir de la vue :

SELECT product_ID, wholesale_price FROM mv1;
+------------+-----------------+
| PRODUCT_ID | WHOLESALE_PRICE |
|------------+-----------------|
|          1 |               1 |
+------------+-----------------+
Copy

Association d’une vue matérialisée

Vous pouvez associer une vue matérialisée à une table ou à une autre vue. Cet exemple s’appuie sur l’exemple précédent en créant une table supplémentaire, puis une vue non matérialisée montrant les bénéfices en reliant la vue matérialisée à une table :

CREATE TABLE sales (product_ID INTEGER, quantity INTEGER, price FLOAT);

INSERT INTO sales (product_ID, quantity, price) VALUES 
   (1,  1, 1.99);

CREATE or replace VIEW profits AS
  SELECT m.product_ID, SUM(IFNULL(s.quantity, 0)) AS quantity,
      SUM(IFNULL(quantity * (s.price - m.wholesale_price), 0)) AS profit
    FROM mv1 AS m LEFT OUTER JOIN sales AS s ON s.product_ID = m.product_ID
    GROUP BY m.product_ID;
Copy

Sélectionnez des données à partir de la vue :

SELECT * FROM profits;
+------------+----------+--------+
| PRODUCT_ID | QUANTITY | PROFIT |
|------------+----------+--------|
|          1 |        1 |   0.99 |
+------------+----------+--------+
Copy

Suspension des mises à jour d’une vue matérialisée

L’exemple suivant suspend temporairement l’utilisation (et la maintenance) de la vue matérialisée mv1 et indique que les requêtes sur cette vue génèrent un message d’erreur lorsque la vue matérialisée est suspendue :

ALTER MATERIALIZED VIEW mv1 SUSPEND;
    
INSERT INTO inventory (product_ID, wholesale_price, description) VALUES 
    (2, 2.00, 'sprocket');

INSERT INTO sales (product_ID, quantity, price) VALUES 
   (2, 10, 2.99),
   (2,  1, 2.99);
Copy

Sélectionnez des données dans la vue matérialisée :

SELECT * FROM profits ORDER BY product_ID;
Copy

Sortie :

002037 (42601): SQL compilation error:
Failure during expansion of view 'PROFITS': SQL compilation error:
Failure during expansion of view 'MV1': SQL compilation error: Materialized View MV1 is invalid.
Copy

Reprise :

ALTER MATERIALIZED VIEW mv1 RESUME;
Copy

Sélectionnez des données dans la vue matérialisée :

SELECT * FROM profits ORDER BY product_ID;
+------------+----------+--------+
| PRODUCT_ID | QUANTITY | PROFIT |
|------------+----------+--------|
|          1 |        1 |   0.99 |
|          2 |       11 |  10.89 |
+------------+----------+--------+
Copy

Clustering d’une vue matérialisée

Cet exemple crée une vue matérialisée pour une mise en cluster plus tard :

Ces instructions créent deux tables qui suivent les informations sur les segments d’un pipeline (par exemple, pour le gaz naturel).

Les segments les plus susceptibles d’échouer dans un avenir proche sont souvent les segments les plus anciens, ou constitués de matériaux facilement corrodables, ou ayant connu des périodes de pression anormalement élevée. Dans cet exemple, nous suivons l’ancienneté de chaque tuyau, la pression et les matériaux (fer, cuivre, PVC plastique, etc.).

CREATE TABLE pipeline_segments (
    segment_ID BIGINT,
    material VARCHAR, -- e.g. copper, cast iron, PVC.
    installation_year DATE,  -- older pipes are more likely to be corroded.
    rated_pressure FLOAT  -- maximum recommended pressure at installation time.
    );
    
INSERT INTO pipeline_segments 
    (segment_ID, material, installation_year, rated_pressure)
  VALUES
    (1, 'PVC', '1994-01-01'::DATE, 60),
    (2, 'cast iron', '1950-01-01'::DATE, 120)
    ;

CREATE TABLE pipeline_pressures (
    segment_ID BIGINT,
    pressure_psi FLOAT,  -- pressure in Pounds per Square Inch
    measurement_timestamp TIMESTAMP
    );
INSERT INTO pipeline_pressures 
   (segment_ID, pressure_psi, measurement_timestamp) 
  VALUES
    (2, 10, '2018-09-01 00:01:00'),
    (2, 95, '2018-09-01 00:02:00')
    ;
Copy

Les segments de pipeline ne changent pas très souvent et les segments de pipeline les plus anciens sont ceux qui risquent le plus de tomber en panne. Créez donc une vue matérialisée des segments les plus anciens.

CREATE MATERIALIZED VIEW vulnerable_pipes 
  (segment_ID, installation_year, rated_pressure) 
  AS
    SELECT segment_ID, installation_year, rated_pressure
        FROM pipeline_segments 
        WHERE material = 'cast iron' AND installation_year < '1980'::DATE;
Copy

Vous pouvez ajouter un cluster ou modifier la clé de cluster. Par exemple, pour effectuer un cluster sur installation_year :

ALTER MATERIALIZED VIEW vulnerable_pipes CLUSTER BY (installation_year);
Copy

Les nouvelles mesures de pression arrivant fréquemment, peut-être toutes les 10 secondes, il serait donc coûteux de conserver une vue matérialisée sur les mesures de pression. Par conséquent, même si la performance (récupération rapide) des données de pression récentes est importante, la table pipeline_pressures démarre sans vue matérialisée.

Si les performances sont trop lentes, vous pouvez créer une vue matérialisée contenant uniquement les données de pression récentes ou uniquement des données relatives à des événements anormaux sous haute pression.

Créez une vue (non matérialisée) qui combine les informations de la vue matérialisée et de la table pipeline_pressures :

CREATE VIEW high_risk AS
    SELECT seg.segment_ID, installation_year, measurement_timestamp::DATE AS measurement_date, 
         DATEDIFF('YEAR', installation_year::DATE, measurement_timestamp::DATE) AS age, 
         rated_pressure - age AS safe_pressure, pressure_psi AS actual_pressure
       FROM vulnerable_pipes AS seg INNER JOIN pipeline_pressures AS psi 
           ON psi.segment_ID = seg.segment_ID
       WHERE pressure_psi > safe_pressure
       ;
Copy

Maintenant, listez les segments de pipeline à haut risque :

SELECT * FROM high_risk;
+------------+-------------------+------------------+-----+---------------+-----------------+
| SEGMENT_ID | INSTALLATION_YEAR | MEASUREMENT_DATE | AGE | SAFE_PRESSURE | ACTUAL_PRESSURE |
|------------+-------------------+------------------+-----+---------------+-----------------|
|          2 | 1950-01-01        | 2018-09-01       |  68 |            52 |              95 |
+------------+-------------------+------------------+-----+---------------+-----------------+
Copy

Cela montre que le segment de pipeline avec segment_id = 2, constitué d’un matériau corrodant, est ancien. Ce segment n’a jamais connu de pression supérieure à la pression nominale maximale au moment de son installation, mais en raison du potentiel de corrosion, sa « limite de sécurité » a diminué avec le temps et la pression la plus élevée qu’il a subie est supérieure à la pression recommandée pour un tuyau aussi ancien que celui-ci au moment de la mesure de la pression.

Création d’une vue matérialisée sur des données partagées

Vous pouvez créer une vue matérialisée sur des données partagées.

Compte1 :

create or replace table db1.schema1.table1(c1 int);
create or replace share sh1;
grant usage on database db1 to share sh1;
alter share sh1 add accounts = account2;
grant usage on schema db1.schema1 to share sh1;
grant select on table db1.schema1.table1 to share sh1;
Copy

Compte2 :

create or replace database dbshared from share account1.sh1;
create or replace materialized view mv1 as select * from dbshared.schema1.table1 where c1 >= 50;
Copy

Note

N’oubliez pas que la gestion de vues matérialisées consommera des crédits. Lorsque vous créez une vue matérialisée sur la table partagée d’un autre utilisateur, les modifications apportées à cette table partagée entraînent des frais pour vous à mesure que votre vue matérialisée est conservée.

Partage d’une vue matérialisée

Vous pouvez utiliser la fonctionnalité de partage de données de Snowflake pour partager une vue matérialisée.

Pour plus d’informations sur le partage de données, voir Aperçu du partage de données sur Snowflake.

Note

N’oubliez pas que la gestion de vues matérialisées consommera des crédits. Lorsqu’une autre personne crée une vue matérialisée sur vos données partagées, toute modification de vos données partagées peut entraîner des frais pour les personnes qui ont des vues matérialisées sur vos données partagées. Plus le nombre de vues matérialisées sur une table de base partagée est important, plus il est important de mettre à jour efficacement cette table de base afin de minimiser les coûts de maintenance des vues matérialisées.

Dépannage

Erreur de compilation : Failure during expansion of view '<nom>': SQL compilation error: Materialized View <nom> is invalid.

Causes possibles:
  • La vue matérialisée a été suspendue. Pour plus d’informations sur la suspension et la reprise des vues, voir ALTER MATERIALIZED VIEW.

  • Une modification de la table de base de la vue matérialisée a invalidé la vue matérialisée. Par exemple, cette erreur est renvoyée si :

    • La table de base est détruite.

    • Une colonne de la table de base a été supprimée.

  • Le processus d’arrière-plan a rencontré une erreur d’un type spécifique (par exemple, une erreur de « division par zéro ») et n’a pas réussi à actualiser la vue matérialisée.

Solutions possibles:
  • Si la vue a été suspendue :

    • Envisagez de reprendre la vue en exécutant ALTER MATERIALIZED VIEW RESUME.

    • Envisagez d’exécuter la requête sur la table de base. Cependant, cela risque de consommer plus de crédits et de prendre plus de temps que d’exécuter la requête sur la vue matérialisée.

  • Si la table de base a été modifiée ou détruite :

    • Si la table de base a été détruite, alors détruisez la vue matérialisée.

    • Si la table de base a été modifiée (par exemple, si une colonne référencée par la vue a été détruite), et si la vue matérialisée est encore utile avec la nouvelle version de la table, il faut envisager de détruire et de recréer la vue matérialisée en utilisant les colonnes qui restent dans la table de base.

    • Si aucune autre cause du message d’erreur n’est apparente, envisagez de détruire et de recréer la vue matérialisée.

    • Envisagez d’exécuter la requête sur la table de base. Cependant, cela risque de consommer plus de crédits et de prendre plus de temps que d’exécuter la requête sur la vue matérialisée.

  • Si le processus d’arrière-plan n’a pas réussi à actualiser la vue matérialisée en raison d’une erreur, le message d’erreur doit inclure des détails sur la raison pour laquelle la vue matérialisée a été invalidée. Par exemple :

    Failure during expansion of view 'MY_MV':
      SQL compilation error: Materialized View MY_MV is invalid.
      Invalidation reason: Division by zero
    

Si cela se produit, résolvez le problème décrit dans le message d’erreur et reprenez la vue matérialisée à l’aide de la commande ALTER MATERIALIZED VIEW… RESUME.

La commande SHOW MATERIALIZED VIEWS affiche les vues matérialisées non mises à jour

Cause possible:

Une cause possible est l’échec de l’actualisation, car l’instruction SELECT dans la définition de la vue a échoué.

L’actualisation étant effectuée en arrière-plan, vous ne verrez pas de message d’erreur au moment de la tentative d’actualisation. Au lieu de cela, vous verrez le message d’erreur lorsque vous interrogez la vue matérialisée ou lorsque vous exécutez SHOW MATERIALIZED VIEWS.

Solution possible:

Si la colonne invalid est true, vérifiez dans la colonne invalid_reason la raison pour laquelle la vue a été invalidée.

Dans certains cas, vous pourriez peut-être résoudre le problème en exécutant manuellement l’instruction SELECT dans la définition de la vue matérialisée ou en exécutant une instruction SELECT plus simple (moins coûteuse) sur la table référencée dans la définition de la vue matérialisée.

Si vous ne connaissez pas la définition exacte de la vue matérialisée, vous pouvez la retrouver dans le résultat de SHOW MATERIALIZED VIEWS ou en utilisant la fonction GET_DDL.