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. Les données étant précalculées, l’interrogation d’une vue matérialisée est plus rapide que l’exécution de la requête d’origine. Cette différence de performances peut être significative lorsqu’une requête est exécutée fréquemment ou est suffisamment complexe.

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.

Dans ce chapitre :

Quand utiliser les vues matérialisées

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 Frais de maintenance pour les vues matérialisées (dans ce chapitre).

Vues matérialisées par rapport aux vues normales

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.

Création et utilisation de vues matérialisées

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

Vue matérialisée DDL et DML

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 :

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.

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

Pour plus d’informations sur les privilèges sur les vues matérialisées, voir Privilèges de contrôle d’accès (dans cette rubrique).

Scénarios d’utilisation générale

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

Notes générales sur l’utilisation

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

  • Lors de la création initiale d’une vue matérialisée, Snowflake effectue l’équivalent d’une opération CTAS (CREATE TABLE … AS ….). Cela signifie que l’instruction CREATE MATERIALIZED VIEW peut prendre beaucoup de temps.

  • La maintenance des vues matérialisées est effectuée par un processus en arrière-plan et la chronologie n’est pas prévisible par l’utilisateur. Si la maintenance prend du retard, les requêtes risquent de s’exécuter plus lentement que si les vues étaient à jour. Cependant, les résultats seront toujours corrects. Si des micro-partitions de la vue matérialisée sont obsolètes, Snowflake ignore ces partitions et recherchera des données dans la table de base.

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

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

  • INFORMATION_SCHEMA.TABLES montre les 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.

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

  • Pour voir la dernière actualisation d’une vue matérialisée, consultez les colonnes REFRESHED_ON et BEHIND_BY dans le résultat de la commande SHOW MATERIALIZED VIEWS.

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.

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 :

    • UDFs.

    • De 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.

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

      Ce qui suit est emph:non autorisé :

      create materialized view mv2 as
          select
              y + 10
            from (
              select
                sum(x) as y
              from t
            );
      
    • 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, 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> ] )
      
    • 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);
      

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

    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;
    

    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.

  • La fonctionnalité « Time Travel » de Snowflake n’est pas prise en charge sur les vues matérialisées.

Limitations relatives à l’utilisation 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 à 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 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.

  • Time Travel n’est actuellement pas pris en charge sur les vues matérialisées.

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

Modifications apportées aux tables de base

Dans Snowflake, les modifications apportées à une table de base ne sont pas automatiquement propagées aux vues matérialisées basées sur cette table.

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

  • 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. Bien que vous puissiez la détruire, créez une nouvelle vue, puis exécutez les commandes GRANT et REVOKE pour recréer les privilèges sur la vue, le moyen le plus efficace de recréer la vue est généralement de la remplacer en une seule commande en exécutant CREATE OR REPLACE VIEW <nom_vue> ... COPY GRANTS ....

  • ALTER TABLE ... ADD <colonne> ne suspend pas une vue matérialisée créée sur cette 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 VIEW <nom_vue> ... COPY GRANTS ....

Vues matérialisées et clonage d’un schéma ou d’une base de données

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.

Cloner uniquement la vue matérialisée peut être utile si différents utilisateurs souhaitent « ajuster » la vue différemment. Par exemple, vous pouvez regrouper la vue clonée sur une ou plusieurs colonnes différentes de celles de la vue d’origine.

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;

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

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.

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

Pour plus de détails sur l’instruction GRANT, voir GRANT <privileges> … TO ROLE.

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.

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.

Frais de maintenance pour les 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.

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 l’interface Web ou de SQL :

Interface Web

En tant qu’administrateur de compte, cliquez sur Account Account tab » Billing & Usage.

Les coûts de crédit sont suivis dans un entrepôt virtuel géré par Snowflake nommé Snowflake logo in blue (no text) MATERIALIZED_VIEW_MAINTENANCE.

SQL

Interrogez l’un des éléments suivants :

Par exemple :

SELECT * FROM TABLE(INFORMATION_SCHEMA.MATERIALIZED_VIEW_REFRESH_HISTORY());

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 Snowflake logo in blue (no text) 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 d’informations sur le clustering, voir Fonctionnement des structures de table dans Snowflake et Clustering automatique.

Pour plus d’informations sur les coûts de clustering des vues matérialisées, voir Frais de maintenance pour les vues matérialisées et Meilleures pratiques pour les vues matérialisées.

Meilleures pratiques pour les 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 Modifications apportées aux tables de base (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');

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

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

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;

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

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

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

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

SELECT * FROM profits ORDER BY product_ID;

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.

Reprise :

ALTER MATERIALIZED VIEW mv1 RESUME;

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

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

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;

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

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
       ;

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

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;

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;

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 Partage des données sécurisé dans 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.

Cause

Dans de nombreux cas, cela est dû à une modification de la table sous-jacente sur laquelle la vue matérialisée est basée. Par exemple, cette erreur est renvoyée si la table est détruite ou si la vue matérialisée fait référence à une colonne de table mais que la colonne a été détruite.

Solution

Si la table a été détruite et ne sera pas recréée, vous devriez probablement détruire la vue.

Si la table a été modifiée, mais qu’elle existe toujours, vous pourrez peut-être détruire et recréer la vue matérialisée, en utilisant les colonnes restantes.

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

Cause

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

Si l’instruction SELECT échoue lors de l’actualisation, celle-ci échouera. Toutefois, comme l’actualisation est effectuée en arrière-plan, l’utilisateur ne verra pas de message d’erreur au moment où l’actualisation est tentée.

Si certaines ou toutes les données de la vue matérialisée sont obsolètes, Snowflake récupérera les données à jour de la table de base. Snowflake n’émettra pas de message d’erreur indiquant que la vue matérialisée n’a pas été actualisée.

Par conséquent, ni l’actualisation ni les requêtes suivantes n’affichent nécessairement l’échec de la commande SELECT dans la vue. Pour détecter si les actualisations échouent, utilisez la commande SHOW MATERIALIZED VIEWS et recherchez la colonne nommée refreshed_on. Si les données ne sont pas actualisées, il se peut que SELECT échoue.

Solution

Assurez-vous que la table sous-jacente existe. Si vous détruisez la table sur laquelle la vue est définie, mais que vous ne détruisez pas la vue, celle-ci continuera d’exister.

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