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

S’applique à : 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 sont soutenues par un pipeline DLT. Vous pouvez actualiser les vues matérialisées manuellement ou selon une planification.

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.

Syntaxe

{ CREATE OR REPLACE MATERIALIZED VIEW | CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] }
  view_name
  [ column_list ]
  [ view_clauses ]
  [schedule_clause]
  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 [, ...]) |
    COMMENT view_comment |
    TBLPROPERTIES clause |
    SCHEDULE [ REFRESH ] schedule_clause |
    WITH { ROW FILTER clause } } [...]

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

Paramètres

• REPLACE

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

• IF NOT EXISTS

  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.

  • column_name

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

  • column_type

    Indique le type de données de la colonne. Tous les 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 le nom de 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

    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 ultérieures 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é.

• table_constraint

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

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

  • SCHEDULE [ REFRESH ] schedule_clause

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

      Important

      Cette fonctionnalité est disponible en préversion publique.

      Pour planifier une actualisation qui se produit régulièrement, utilisez EVERY la syntaxe. Si EVERY la syntaxe est spécifiée, la table de diffusion en continu ou la vue matérialisée est actualisée régulièrement à l’intervalle spécifié en fonction de la valeur fournie, telle que HOUR, , , HOURS, WEEKDAYDAYSou .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

      Remarque

      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 cron de quartz. Les time_zone_values valides 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.

  • 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 ultérieures de cette table reçoivent 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, où 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 MATERIALIZED VIEW 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

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 retournent 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 extrait des lignes. Toutes les requêtes futures impliquant cette colonne recevront le résultat de l’évaluation de la fonction sur la colonne, à la place de 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
• 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é ou 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 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;

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

Articles connexes

• ALTER MATERIALIZED VIEW

• clause de contrainte

• DROP VIEW

• SHOW CREATE TABLE

• Filtrage des données de table sensibles avec des filtres de lignes et des masques de colonne

• DESCRIBE TABLE

• REFRESH