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. Pour vous inscrire et obtenir un accès, remplissez ce formulaire.

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 ]
  [ table_constraints ]
  [ view_clauses ]
  AS query

column_list
   ( { column_alias [ COMMENT column_comment | column_constraint ] [...] } [, ...] [ , table_constraint ] [...])

table_constraints
   ( table_constraint [, ...] )

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

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_alias

      Les alias de colonnes doivent être uniques.

    • column_comment

      Littéral STRING facultatif qui décrit l’alias de la colonne.

    • column_constraint

      Important

      Cette fonctionnalité est disponible en préversion publique.

      Ajoute une clé primaire informative ou une contrainte de clé étrangère informative à la colonne dans une vue matérialisée. Les contraintes ne sont pas prises en charge pour les vues matérialisées du catalogue hive_metastore.

  • table_constraint

    Important

    Cette fonctionnalité est disponible en préversion publique.

    Ajoute une clé primaire informative ou des contraintes de clés étrangères informatives à une vue matérialisée. Les contraintes ne sont pas prises en charge pour les vues matérialisées du catalogue hive-metastore.

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

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

Seul le propriétaire peut REFRESH 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.

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.

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 column constraint
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies(
    member_id NOT NULL PRIMARY KEY,
    full_name,
    movie_title
  )
  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 table constraint
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies(
    member_id NOT NULL,
    full_name,
    movie_title,
    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 a materialized view without column list
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies(
    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;