Aller au contenu principal
Dernière mise à jour le

CREATE MATERIALIZED VIEW (pipelines)

Une * vue matérialisée * est une vue dont les résultats précalculés sont disponibles pour query et peuvent être mis à jour pour refléter les changements dans l'entrée. Les vues matérialisées sont gérées par un pipeline. Chaque fois qu'une vue matérialisée est mise à jour, les résultats de la query sont recalculés pour refléter les changements dans les datasets en amont. Vous pouvez mettre à jour les vues matérialisées manuellement ou selon un programme.

Pour en savoir plus sur la façon d'effectuer ou de planifier des mises à jour, consultez Exécuter une mise à jour de pipeline.

Syntaxe​

CREATE [OR REFRESH] [PRIVATE] MATERIALIZED VIEW
  view_name
  [ column_list ]
  [ view_clauses ]
  AS query

column_list
   ( { column_name column_type column_properties } [, ...]
    [ CONSTRAINT expectation_name EXPECT (expectation_expr)
      [ ON VIOLATION { FAIL UPDATE | DROP ROW } ] ] [, ...]
    [ , table_constraint ] [...] )

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

view_clauses
  { USING { DELTA | ICEBERG } |
    PARTITIONED BY (col [, ...]) |
    CLUSTER BY clause |
    LOCATION path |
    COMMENT view_comment |
    TBLPROPERTIES clause |
    REFRESH POLICY refresh_clause |
    WITH { ROW FILTER clause } } [...]

parameter​

  • REFRESH

    Si spécifié, crée la vue ou met à jour une vue existante et son contenu.

  • PRIVÉ

    Crée une vue matérialisée privée. Une vue matérialisée privée peut être utile comme table intermédiaire dans un pipeline que vous ne voulez pas publier dans le catalogue.

    • Ils ne sont pas ajoutés au catalogue et ne sont accessibles que dans le pipeline de définition.
    • Ils peuvent avoir le même nom qu'un objet existant dans le catalogue. Dans le pipeline, si une vue matérialisée privée et un objet du catalogue portent le même nom, les références à ce nom se résolvent en la vue matérialisée privée.
    • Les vues matérialisées privées ne sont conservées que pendant la durée de vie du pipeline, et pas seulement pour une seule mise à jour.

    Les vues matérialisées privées étaient auparavant créées avec le paramètre TEMPORARY.

  • view_name

    Le nom de la vue nouvellement créée. Le nom de vue entièrement qualifié doit être unique.

    Les vues matérialisées privées peuvent avoir le même nom qu'un objet publié dans le catalogue.

  • column_list

    Étiquette facultativement les colonnes dans le résultat de la query de la vue. Si vous fournissez une liste de colonnes, le nombre d'alias de colonne doit correspondre au nombre d'expressions dans la query. 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 correspondre aux colonnes de sortie de la query.

    • column_type

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

    • commentaire_colonne

      Un littéral STRING facultatif décrivant 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é.

    • contrainte de colonne

      Ajoute une contrainte de clé primaire informative ou de clé étrangère informative à la colonne dans une vue matérialisée.

    • Clause MASK

      Ajoute une fonction de masque de colonne pour anonymiser les données sensibles. Voir les filtres de lignes et masques de colonnes.

    • CONSTRAINT expectation_name EXPECT (expectation_expr) [ ON VIOLATION { FAIL UPDATE | DROP ROW } ]

      Ajoute des attentes en matière de qualité des données à la vue matérialisée. Ces attentes en matière de qualité des données peuvent être suivies dans le temps et consultées via le Log des événements de la vue matérialisée. Une attente FAIL UPDATE provoque l'échec du traitement lors de la création et de l'actualisation de la vue matérialisée. Une attente DROP ROW provoque la suppression de la ligne entière si l'attente n'est pas satisfaite. Voir Gérer la qualité des données avec les attentes du pipeline.

      expectation_expr peuvent être composées de littéraux, d'identifiants de colonne au sein de la vue matérialisée, et de fonctions ou opérateurs SQL intégrés et déterministes, à l'exception de :

      De plus, expr ne doit pas contenir de sous-requête.

      Une vue matérialisée dont la définition inclut des attentes est complètement actualisée à chaque mise à jour et ne prend pas en charge le refresh incrémentiel. Pour utiliser le refresh incrémental, supprimez les attentes ou appliquez-les en dehors de la définition de la vue matérialisée.

  • contrainte de table

    Lorsque vous spécifiez un schéma, vous pouvez définir des clés primaires et étrangères. Les contraintes sont informatives et ne sont pas appliquées. Consultez la clause CONSTRAINT dans la référence du langage SQL.

remarque

Pour définir des contraintes de table, votre pipeline doit être un pipeline compatible avec Unity Catalog.

  • view_clauses

    Spécifiez éventuellement le partitionnement, les commentaires et les propriétés définies par l'utilisateur pour la vue matérialisée. Chaque sous-clause ne peut être spécifiée qu'une seule fois.

    • UTILISATION DE DELTA

      Spécifie le format des données. La valeur par default est DELTA.

      Cette clause est facultative.

    • UTILISATION DE ICEBERG

      Crée une vue matérialisée compatible avec les lecteurs Iceberg externes. Après avoir créé la vue matérialisée, exécutez REPAIR TABLE <mv_name> SYNC METADATA. La vue matérialisée est en lecture seule pour les lecteurs Iceberg externes. Consultez Créer une vue matérialisée compatible avec les lecteurs Iceberg externes.

info

Aperçu

Les vues matérialisées Iceberg gérées sont en Aperçu public. Pour activer cette fonctionnalité, contactez votre équipe de compte Databricks.

  • PARTITIONNÉ PAR

    Une liste facultative d'une ou plusieurs colonnes à utiliser pour le partitionnement dans la table. Exclusif mutuellement avec CLUSTER BY.

    Le clustering liquide offre une solution flexible et optimisée pour le clustering. Envisagez d'utiliser CLUSTER BY au lieu de PARTITIONED BY pour les pipelines.

  • CLUSTER BY

    Activez le clustering liquide sur la table et définissez les colonnes à utiliser comme clés de clustering. Utilisez le clustering liquide automatique avec CLUSTER BY AUTO, et Databricks choisit intelligemment les clés de clustering pour optimiser les performances de la query. Mutuellement exclusif avec PARTITIONED BY.

    Consultez Utiliser le clustering liquide pour les tables.

  • Emplacement

    Un emplacement de stockage facultatif pour les données de table. S'il n'est pas défini, le système utilise par default l'emplacement de stockage du pipeline.

    Cette option est uniquement disponible lors de la publication dans Hive metastore. Dans Unity Catalog, l'emplacement est géré automatiquement.

  • Commentaire

    Une description facultative pour la table.

  • TBLPROPERTIES

    Une liste facultative de propriétés de table pour la table.

  • REFRESH POLICY

    (Bêta) En option, définit une politique de refresh pour la vue matérialisée.

    Voir clause REFRESH POLICY (pipelines).

  • AVEC FILTRE DE LIGNE

    Ajoute une fonction de filtre de ligne à la table. Les futures requêtes pour cette table reçoivent un sous-ensemble des lignes pour lesquelles la fonction évalue à VRAI. Ceci est utile pour le contrôle d'accès précis, car cela permet à la fonction d'inspecter l'identité et les appartenances aux groupes de l'utilisateur appelant afin de décider s'il faut filtrer certaines lignes.

    Voir ROW FILTER clause.

  • Saisir une requête

    Une query qui définit le dataset pour la table.

Autorisations requises​

L'utilisateur d'exécution pour un pipeline doit avoir les autorisations suivantes :

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

Pour qu'un utilisateur puisse mettre à jour le pipeline dans lequel la vue matérialisée est définie, il lui faut :

  • USE CATALOG privilège sur le catalogue parent et le privilège USE SCHEMA sur le schéma parent.
  • Propriété de la vue matérialisée ou privilège REFRESH sur la vue matérialisée.
  • Le propriétaire de la vue matérialisée doit disposer du 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 résultante, il lui faut :

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

Limitations​

  • Lorsqu'une vue matérialisée avec un agrégat sum sur une colonne NULL-able a la dernière valeur non-NULL supprimée de cette colonne – et que seules les valeurs NULL restent dans cette colonne –, la valeur agrégée résultante de la vue matérialisée renvoie zéro au lieu de NULL.

  • La référence de colonne ne requiert pas d'alias. Les expressions de référence non liées à une 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 doivent être spécifiées 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.

  • Le renommage de la table ou la modification du propriétaire n'est pas pris en charge.

  • Les colonnes générées, les colonnes d'identité et les colonnes default ne sont pas prises en charge.

Exemples​

SQL
-- Create a materialized view by reading from an external data source, using the default schema:
CREATE OR REFRESH MATERIALIZED VIEW taxi_raw
AS SELECT * FROM read_files("/databricks-datasets/nyctaxi/sample/json/")

-- Create a materialized view by reading from a dataset defined in a pipeline:
CREATE OR REFRESH MATERIALIZED VIEW filtered_data
AS SELECT
  ...
FROM taxi_raw

-- Specify a schema and clustering columns for a table:
CREATE OR REFRESH MATERIALIZED VIEW sales
(customer_id STRING,
  customer_name STRING,
  number_of_line_items STRING,
  order_datetime STRING,
  order_number LONG,
  order_day_of_week STRING GENERATED ALWAYS AS (dayofweek(order_datetime))
) CLUSTER BY (order_day_of_week, customer_id)
COMMENT "Raw data on sales"
AS SELECT * FROM ...

-- Use automatic liquid clustering to let Databricks choose the clustering columns:
CREATE OR REFRESH MATERIALIZED VIEW sample_trips
CLUSTER BY AUTO
AS SELECT pickup_zip, fare_amount FROM samples.nyctaxi.trips

-- Specify partition columns for a table:
CREATE OR REFRESH MATERIALIZED VIEW sales
(customer_id STRING,
  customer_name STRING,
  number_of_line_items STRING,
  order_datetime STRING,
  order_number LONG,
  order_day_of_week STRING GENERATED ALWAYS AS (dayofweek(order_datetime))
) PARTITIONED BY (order_day_of_week)
COMMENT "Raw data on sales"
AS SELECT * FROM ...

-- Specify a primary and foreign key constraint for a table:
CREATE OR REFRESH MATERIALIZED VIEW sales
(customer_id STRING NOT NULL PRIMARY KEY,
  customer_name STRING,
  number_of_line_items STRING,
  order_datetime STRING,
  order_number LONG,
  order_day_of_week STRING GENERATED ALWAYS AS (dayofweek(order_datetime)),
  CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES main.default.customers(customer_id)
)
COMMENT "Raw data on sales"
AS SELECT * FROM ...

-- Specify a row filter and mask clause for a table:
CREATE OR REFRESH MATERIALIZED VIEW sales (
  customer_id STRING MASK catalog.schema.customer_id_mask_fn,
  customer_name STRING,
  number_of_line_items STRING COMMENT 'Number of items in the order',
  order_datetime STRING,
  order_number LONG,
  order_day_of_week STRING GENERATED ALWAYS AS (dayofweek(order_datetime))
)
COMMENT "Raw data on sales"
WITH ROW FILTER catalog.schema.order_number_filter_fn ON (order_number)
AS SELECT * FROM sales_bronze
Sur cette page