Partage via

CRÉER UNE VUE DÉMATÉRIALISÉE

  • Article

S’applique à : coche marquée oui Databricks SQL

Important

Cette fonctionnalité est disponible en préversion publique.

Une vue matérialisée est une vue où les résultats précalculés sont disponibles pour la requête et peuvent être mis à jour pour refléter les modifications apportées à l’entrée. Chaque fois qu’une vue matérialisée est actualisée, les résultats de la requête sont recalculés pour refléter les modifications apportées aux jeux de données en amont. Toutes les vues matérialisées sont soutenues par un pipeline DLT. Vous pouvez actualiser les vues matérialisées manuellement, selon une planification ou en planifiant le pipeline DLT dans lequel elles sont contenues.

Syntaxe

CREATE MATERIALIZED VIEW [IF NOT EXISTS]
  view_name
  [ column_list ]
  [ view_clauses ]
  AS query

column_list
   ( { column_name [ MASK clause ] [...] } [, ...] )

view_clauses
  { PARTITIONED BY (col [, ...]) |
    COMMENT view_comment |
    TBLPROPERTIES clause |
    SCHEDULE [ REFRESH ] CRON cron_string [ AT TIME ZONE timezone_id ] |
    WITH { ROW FILTER clause } } [...]

Paramètres

  • IF NOT EXISTS

    Crée la vue si elle n’existe pas. Si une vue portant ce nom existe déjà, l’instruction CREATE VIEW est ignorée.

    Vous pouvez spécifier au maximum un seul des paramètres IF NOT EXISTS ou OR REFRESH.

  • view_name

    Nom de la vue nouvellement créée. Le nom complet de la vue doit être unique.

  • column_list

    Étiquette au choix les colonnes dans le résultat de la requête de la vue. Si vous fournissez une liste de colonnes, le nombre d’alias de colonnes doit correspondre au nombre d’expressions de la requête. Si aucune liste de colonnes n’est spécifiée, les alias sont dérivés du corps de la vue.

    • column_name

      Les noms de colonnes doivent être uniques et mappés aux colonnes de sortie de la requête.

    • Clause MASK

      Important

      Cette fonctionnalité est disponible en préversion publique.

      Permet d’ajouter une fonction de masque de colonne pour anonymiser les données sensibles. Toutes les requêtes futures de cette colonne recevront le résultat de l’évaluation de cette fonction sur la colonne à la place de la valeur d’origine de la colonne. Cela peut être utile à des fins de contrôle d’accès plus précis, dans lesquels la fonction peut inspecter l’identité et/ou l’appartenance à un groupe de l’utilisateur appelant afin de décider s’il convient de modifier la valeur.

  • view_clauses

    Vous pouvez également spécifier le partitionnement, les commentaires, les propriétés définies par l'utilisateur et un calendrier d'actualisation pour la nouvelle vue matérialisée. Chaque sous-clause ne peut être spécifiée qu’une seule fois.

    • PARTITIONNÉ PAR

      Liste facultative des colonnes de la table sur laquelle partitionner la table.

    • COMMENT view_comment

      Littéral STRING pour décrire la colonne.

    • TBLPROPERTIES

      (Facultatif) Définit une ou plusieurs propriétés définies par l’utilisateur.

    • SCHEDULE [ REFRESH ] CRON cron_string [ AT TIME ZONE timezone_id ]

      Le cas échéant, planifie la table de streaming ou la vue matérialisée pour actualiser ses données avec la planification cron quartz donnée. Seules les time_zone_values sont acceptées. La fonction AT TIME ZONE LOCAL n'est pas prise en charge. Si AT TIME ZONE est absent, le fuseau horaire de session est utilisé. Si AT TIME ZONE est absent et que le fuseau horaire de session n’est pas défini, une erreur est générée. SCHEDULE est équivalent sémantiquement à SCHEDULE REFRESH.

      Vous ne pouvez pas utiliser la syntaxe SCHEDULE dans une définition de pipeline Delta Live Tables.

    • AVEC Clause FILTRE DE LIGNE

      Important

      Cette fonctionnalité est disponible en préversion publique.

      Permet d’ajouter une fonction de filtre de ligne à la table. Toutes les requêtes futures de cette table recevront un sous-ensemble de ses lignes pour lesquelles la fonction prend la valeur booléenne TRUE. Cela peut être utile à des fins de contrôle d’accès plus précis, dans lesquels la fonction peut inspecter l’identité et/ou l’appartenance à un groupe de l’utilisateur appelant afin de décider s’il convient de filtrer certaines lignes.

  • COMMERequête

    Requête qui construit l’affichage à partir de tables de base ou d’autres affichages.

Autorisations requises

L’utilisateur qui crée une vue matérialisée est le propriétaire de la vue matérialisée et doit disposer des autorisations suivantes :

  • Privilège SELECT sur les tables de base référencées par la vue matérialisée.
  • Le privilège USE CATALOG sur le catalogue parent et le privilège USE SCHEMA sur le schéma parent.
  • Privilège CREATE sur le schéma pour la vue matérialisée.

Pour qu’un utilisateur puisse actualiser la vue matérialisée, il a besoin des éléments suivants :

  • Le privilège USE CATALOG sur le catalogue parent et le privilège USE SCHEMA sur le schéma parent.
  • Propriété de la vue matérialisée ou du privilège REFRESH sur la vue matérialisée.
  • Le propriétaire de la vue matérialisée doit avoir le privilège SELECT sur les tables de base référencées par la vue matérialisée.

Pour qu’un utilisateur puisse interroger la vue matérialisée, il a besoin des éléments suivants :

  • Le privilège USE CATALOG sur le catalogue parent et le privilège USE SCHEMA sur le schéma parent.
  • Privilège SELECT sur la vue matérialisée.

Filtres de lignes et masques de colonne

Important

Cette fonctionnalité est disponible en préversion publique.

Les filtres de lignes vous permettent de spécifier une fonction qui s’applique en tant que filtre chaque fois qu’une analyse de table récupère des lignes. Ces filtres garantissent que les requêtes suivantes retournent uniquement les lignes pour lesquelles le prédicat de filtre est évalué comme vrai.

Les masques de colonne vous permettent de masquer les valeurs d’une colonne chaque fois qu’une analyse de table récupère des lignes. Toutes les requêtes futures impliquant cette colonne recevront le résultat de l’évaluation de la fonction sur la colonne, en remplaçant la valeur d’origine de la colonne.

Pour plus d’informations sur l’utilisation des filtres de lignes et des masques de colonne, consultez Filtrer les données de table sensibles à l’aide de filtres de lignes et de masques de colonne.

Gestion des filtres de lignes et des masques de colonne

Les filtres de lignes et les masques de colonne sur les vues matérialisées doivent être ajoutés via l’instruction CREATE.

Comportement

  • Actualiser en tant que définisseur : lorsque l’instruction REFRESH MATERIALIZED VIEW actualise une vue matérialisée, les fonctions de filtre de lignes s’exécutent avec les droits du définisseur (en tant que propriétaire de la table). Cela signifie que l’actualisation de la table utilise le contexte de sécurité de l’utilisateur qui a créé la vue matérialisée.
  • Requête : bien que la plupart des filtres s’exécutent avec les droits du définisseur, les fonctions qui vérifient le contexte utilisateur (par exemple CURRENT_USER et IS_MEMBER) sont des exceptions. Ces fonctions s’exécutent en tant qu’appelant. Cette approche applique la sécurité des données et les contrôles d’accès spécifiques à l’utilisateur en fonction du contexte de l’utilisateur actuel.
  • Lors de la création de vues matérialisées sur des tables sources qui contiennent des filtres de lignes et des masques de colonne, l’actualisation de la vue matérialisée est toujours complète. Une actualisation complète retraite toutes les données disponibles dans la source avec les dernières définitions. Cela garantit que les stratégies de sécurité sur les tables sources sont évaluées et appliquées avec les données et les définitions les plus à jour.

Observabilité

Utilisez DESCRIBE EXTENDED, INFORMATION_SCHEMA ou l’Explorateur de catalogues pour examiner les filtres de lignes et les masques de colonne existants qui s’appliquent à une vue matérialisée donnée. Cette fonctionnalité permet aux utilisateurs d’auditer et d’examiner les mesures d’accès aux données et de protection sur les vues matérialisées.

Limites

  • Lorsqu’une vue matérialisée avec un agrégat sum sur une colonne acceptant la valeur NULL a la dernière valeur non NULL supprimée de cette colonne (et donc que seules les valeurs NULL restent dans cette colonne ), la valeur d’agrégation résultante de la vue matérialisée retourne zéro au lieu de NULL.
  • La référence de colonne ne nécessite pas d’alias. Les expressions de référence autres que les colonnes nécessitent un alias, comme dans l’exemple suivant :
    • Autorisé : SELECT col1, SUM(col2) AS sum_col2 FROM t GROUP BY col1
    • Non autorisé : SELECT col1, SUM(col2) FROM t GROUP BY col1
  • Les vues matérialisées ne prennent pas en charge les colonnes d’identité ou les clés de substitution.
  • Les vues matérialisées ne prennent pas en charge les commandes ad hoc OPTIMIZE et VACUUM. La maintenance s’effectue automatiquement.
  • Les vues matérialisées ne prennent pas en charge les contraintes de table telles que PRIMARY KEY et FOREIGN KEY.
  • Les vues matérialisées ne permettent pas de définir des contraintes de qualité des données.

Exemples

-- Create a materialized view if it doesn't exist
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies
  AS SELECT mo.member_id, mb.full_name, mo.movie_title
       FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;

-- Create and schedule a materialized view to be refreshed daily at midnight.
-- Note: All columns in a GROUP BY need to be explicitly aliased
> CREATE MATERIALIZED VIEW daily_sales
  COMMENT 'Daily sales numbers'
  SCHEDULE CRON '0 0 0 * * ? *'
  AS SELECT date AS date, sum(sales) AS sumOfSales
       FROM table1
       GROUP BY date;

-- Create a materialized view with a row filter and a column mask
> CREATE MATERIALIZED VIEW masked_view (
    id,
    name,
    region,
    ssn MASK catalog.schema.ssn_mask_fn
  )
  WITH ROW FILTER catalog.schema.us_filter_fn ON (region)
  AS SELECT id, name, region, ssn
       FROM employees;