Partager via

CREATE MATERIALIZED VIEW

S’applique à :coché Databricks SQL

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 s’appuient sur un pipeline ETL. Vous pouvez actualiser les vues matérialisées manuellement ou selon un calendrier.

Pour en savoir plus sur l’exécution d’une actualisation manuelle, consultez REFRESH (MATERIALIZED VIEW ou STREAMING TABLE).

Pour en savoir plus sur la planification d’une actualisation, consultez Exemples ou ALTER MATERIALIZED VIEW.

Les vues matérialisées peuvent uniquement être créées à l’aide d’un entrepôt SQL Pro ou Serverless, ou dans un pipeline.

Note

Les opérations de création et d’actualisation sur les vues matérialisées et les tables de flux sont alimentées par des pipelines déclaratifs Lakeflow Spark. Catalog Explorer vous permet d’afficher des détails sur les pipelines de stockage dans l’IU. Consultez Présentation de Catalog Explorer.

Syntaxe

{ CREATE OR REPLACE MATERIALIZED VIEW | CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] }
  view_name
  [ column_list ]
  [ view_clauses ]
  AS query

column_list
   ( { column_name column_type column_properties } [, ...]
      [ , table_constraint ] [...])

   column_properties
      { NOT NULL | COMMENT column_comment | column_constraint | MASK clause } [ ... ]

view_clauses
  { PARTITIONED BY (col [, ...]) |
    CLUSTER BY clause |
    COMMENT view_comment |
    DEFAULT COLLATION UTF8_BINARY |
    TBLPROPERTIES clause |
    SCHEDULE [ REFRESH ] schedule_clause |
    schedule |
    WITH { ROW FILTER clause } } [...]

schedule
  { SCHEDULE [ REFRESH ] schedule_clause |
    TRIGGER ON UPDATE [ AT MOST EVERY trigger_interval ] }

schedule_clause
  { EVERY number { HOUR | HOURS | DAY | DAYS | WEEK | WEEKS } |
    CRON cron_string [ AT TIME ZONE timezone_id ] }

Parameters

  • REPLACE

    Si elle est spécifiée, remplace la vue et son contenu si elle existe déjà.

  • SI N'EXISTE PAS

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

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

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

    • nom_de_colonne

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

    • column_type

      Spécifie le type de données de la colonne. Certains types de données pris en charge par Azure Databricks ne sont pas pris en charge par les vues matérialisées.

    • column_comment

      Littéral STRING facultatif qui décrit la colonne. Cette option doit être spécifiée avec column_type. Si le type de colonne n’est pas spécifié, le commentaire de colonne est ignoré.

    • column_constraint

      Ajoute une clé primaire informative ou une contrainte de clé étrangère informative à la colonne dans une vue matérialisée. Si le type de colonne n’est pas spécifié, la contrainte de colonne est ignorée.

    • Clause MASK

      Ajoute une fonction de masque de colonne pour anonymiser les données sensibles. Toutes les requêtes suivantes de cette colonne reçoivent 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, où la fonction peut inspecter l’identité ou l’appartenance à un groupe de l’utilisateur appelant afin de décider s’il convient de modifier la valeur. Si le type de colonne n’est pas spécifié, le masque de colonne est ignoré.

  • contrainte_de_table

    Ajoute une clé primaire informative ou une contrainte de clé étrangère informative à la table dans une vue matérialisée. Si le type de colonne n’est pas spécifié, la contrainte de table est ignorée.

  • 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 selon laquelle partitionner la table.

      Note

      Le clustering liquide offre une solution flexible et optimisée pour le regroupement. Envisagez d’utiliser CLUSTER BY plutôt que PARTITIONED BY pour les vues matérialisées.

    • CLUSTER BY

      Clause facultative pour regrouper par un sous-ensemble de colonnes. Utilisez le clustering liquide automatique avec CLUSTER BY AUTO, et Databricks choisit intelligemment les clés de clustering pour optimiser les performances des requêtes. Consultez Utilisation de Liquid Clustering pour les tables.

      Le clustering liquide ne peut pas être combiné avec PARTITIONED BY.

    • COMMENTAIRE view_comment

      Littéral STRING permettant de décrire la table.

    • COLLATION PAR DÉFAUT UTF8_BINARY

      S’applique à :check marqué oui Databricks SQL vérifié marqué oui Databricks Runtime 17.1 et versions ultérieures

      Force la définition du classement par défaut de la vue matérialisée sur UTF8_BINARY. Cette clause est obligatoire si le schéma dans lequel la vue est créée a un classement par défaut autre que UTF8_BINARY. Le classement par défaut de la vue matérialisée sert de classement par défaut dans le corps de la vue.

    • TBLPROPERTIES

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

      Utilisez ce paramètre pour spécifier le canal d’exécution de pipelines déclaratifs Spark Lakeflow utilisé pour exécuter cette instruction. Définissez la valeur de la propriété pipelines.channel sur "PREVIEW" ou "CURRENT". La valeur par défaut est "CURRENT". Pour plus d'informations sur les canaux des pipelines déclaratifs Spark Lakeflow, consultez les canaux d'exécution des pipelines déclaratifs Spark Lakeflow.

    • horaire

      La planification peut être une SCHEDULE instruction ou une TRIGGER instruction.

      • PROGRAMME [ REFRESH ] clause_du_programme

        • EVERY number { HOUR | HOURS | DAY | DAYS | WEEK | WEEKS }

          Planifiez une actualisation qui se produit régulièrement à l’aide de la syntaxe EVERY. Si la syntaxe EVERY est spécifiée, la table de streaming ou la vue matérialisée est actualisée régulièrement à l’intervalle spécifié en fonction de la valeur fournie, par exemple HOUR, HOURS, DAY, DAYS, WEEK ou WEEKS. Le tableau suivant répertorie les valeurs entières acceptées pour number :

          Unité de temps Valeur entière
          HOUR or HOURS 1 <= H <= 72
          DAY or DAYS 1 <= D <= 31
          WEEK or WEEKS 1 <= W <= 8

          Note

          Les formes singulières et plurielles de l’unité de temps incluse sont sémantiquement équivalentes.

        • CRON cron_string [ AT TIME ZONE timezone_id ]

          Pour planifier une actualisation à l’aide d’une valeur quartz cron. Les valeurs time_zone_values valides sont acceptées. AT TIME ZONE LOCAL n’est pas pris 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 l’équivalent sémantique de SCHEDULE REFRESH.

      • DÉCLENCHEMENT ON UPDATE [ AU PLUS TOUS LES trigger_interval ]

        Importante

        La TRIGGER ON UPDATE fonctionnalité est en version bêta.

        Si vous le souhaitez, définissez la table à actualiser lorsqu’une source de données en amont est mise à jour, au plus une fois par minute. Définissez une valeur pour AT MOST EVERY exiger au moins une durée minimale entre les actualisations.

        Les sources de données en amont doivent être des tables Delta externes ou gérées (y compris des vues matérialisées ou des tables de diffusion en continu) ou des vues managées dont les dépendances sont limitées aux types de tables pris en charge. Les tables partagées via Delta Sharing ne sont pas prises en charge en tant que sources en amont.

        L’activation des événements de fichier peut rendre les déclencheurs plus performants et augmenter certaines des limites des mises à jour du déclencheur.

        Il trigger_interval s’agit d’une instruction INTERVAL qui est d’au moins 1 minute.

        TRIGGER ON UPDATE présente les limitations suivantes

        • Pas plus de 10 sources de données en amont par vue matérialisée avec TRIGGER ON UPDATE.
        • Un maximum de 1 000 tables de diffusion en continu ou de vues matérialisées peut être spécifié avec TRIGGER ON UPDATE.
        • La AT MOST EVERY clause est par défaut de 1 minute et ne peut pas être inférieure à 1 minute.

    • CLAUSE WITHROW FILTER

      Ajoute une fonction de filtre de ligne à la table. Toutes les requêtes ultérieures de cette table reçoivent un sous-ensemble des 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, où la fonction peut inspecter l’identité ou l’appartenance à un groupe de l’utilisateur appelant afin de décider s’il convient de filtrer certaines lignes.

  • AS Requê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.
  • Privilège USE CATALOG sur le catalogue parent et privilège USE SCHEMA sur le schéma parent.
  • Privilège CREATE MATERIALIZED VIEW sur le schéma pour la vue matérialisée.

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

  • Privilège USE CATALOG sur le catalogue parent et 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.

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

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

Filtres de lignes et masques de colonnes

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 extrait des lignes. Ces filtres permettent de vérifier que les requêtes suivantes renvoient uniquement les lignes pour lesquelles le prédicat de filtre a la valeur true.

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 Filtres de lignes et masques de colonne.

Gestion des filtres de lignes et des masques de colonnes

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

Comportement

  • Actualiser en tant que definer : 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 definer (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 definer, les fonctions qui vérifient le contexte utilisateur (par CURRENT_USER exemple 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 comportant des filtres de lignes et des masques de colonnes, 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 définitions les plus récentes. Ainsi, les stratégies de sécurité sur les tables source sont évaluées et appliquées avec les données et définitions les plus à jour.

Observabilité

Examinez les filtres de lignes et les masques de colonnes existants qui s’appliquent à une vue matérialisée donnée à l’aide de DESCRIBE EXTENDED, INFORMATION_SCHEMA ou de Catalog Explorer. 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.

Limitations

  • Lorsqu’une vue matérialisée avec un sum agrégat sur une colonne pouvant contenir des valeurs NULL a la dernière valeur non NULL supprimée de cette colonne et qu'il ne reste que des valeurs NULL dans cette colonne, la valeur d’agrégation résultante de la vue matérialisée retourne zéro dans ce cas au lieu de NULL.
  • La référence de colonne ne nécessite pas d’alias. Les expressions de référence autre que de colonne 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
  • NOT NULL doit être spécifié manuellement avec PRIMARY KEY afin d’être une instruction valide.
  • Les vues matérialisées ne prennent pas en charge les colonnes d’identité ni les clés de substitution.
  • Les vues matérialisées ne prennent pas en charge les commandes OPTIMIZE et VACUUM. La maintenance s’effectue automatiquement.
  • 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 whenever the
-- upstream data is updated
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies
  TRIGGER ON UPDATE
  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 every day.
-- Note: All columns in a GROUP BY need to be explicitly aliased
> CREATE MATERIALIZED VIEW daily_sales
  COMMENT 'Daily sales numbers'
  SCHEDULE EVERY 1 DAY
  AS SELECT date AS date, sum(sales) AS sumOfSales
       FROM table1
       GROUP BY date;

-- Sets the runtime channel to "PREVIEW"
> CREATE MATERIALIZED VIEW mv_preview
  TBLPROPERTIES(pipelines.channel = "PREVIEW")
  AS SELECT * FROM RANGE(10)

-- Create a materialized view with a table constraint
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies(
    member_id int NOT NULL,
    full_name string,
    movie_title string,
    CONSTRAINT movie_pk PRIMARY KEY(member_id)
  )
  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 or replace the materialized view to remove the table constraint and add a partition
> CREATE OR REPLACE MATERIALIZED VIEW subscribed_movies
  PARTITIONED BY (member_id)
  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 a materialized view with a row filter and a column mask
> CREATE MATERIALIZED VIEW masked_view (
    id int,
    name string,
    region string,
    ssn string MASK catalog.schema.ssn_mask_fn
  )
  WITH ROW FILTER catalog.schema.us_filter_fn ON (region)
  AS SELECT id, name, region, ssn
       FROM employees;
  • Last updated on