Freigeben über

CREATE MATERIALIZED VIEW

Gilt für:Häkchen für „Ja“ Databricks SQL

Eine materialisierte Sicht ist eine Sicht, in der vorberechnete Ergebnisse für Abfragen verfügbar sind und aktualisiert werden können, um Änderungen in der Eingabe widerzuspiegeln. Jedes Mal, wenn eine materialisierte Sicht aktualisiert wird, werden die Abfrageergebnisse neu berechnet, um Änderungen in Upstream-Datasets widerzuspiegeln. Alle materialisierten Ansichten werden von einer ETL-Datenpipeline unterstützt. Sie können materialisierte Sichten manuell oder nach einem Zeitplan aktualisieren.

Weitere Informationen zum Ausführen einer manuellen Aktualisierung finden Sie unter REFRESH (MATERIALIZED VIEW oder STREAMING TABLE).

Weitere Informationen zum Planen einer Aktualisierung finden Sie unter Beispiele oder ALTER MATERIALIZED VIEW.

Materialisierte Ansichten können nur mit einem Pro- oder Serverless SQL Warehouse oder innerhalb einer Pipeline erstellt werden.

Hinweis

Erstellen und Aktualisieren von Vorgängen für materialisierte Ansichten und Streamingtabellen werden von serverlosen Lakeflow Spark Declarative Pipelines unterstützt. Sie können mit dem Katalog-Explorer Details zu den unterstützenden Pipelines auf der Benutzeroberfläche anzeigen. Siehe Was ist der Katalog-Explorer?.

Syntax

{ 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 ] }

Parameter

  • REPLACE

    Wenn dies angegeben wird, werden die Sicht und deren Inhalt ersetzt (sofern bereits vorhanden).

  • WENN NICHT EXISTIERT

    Erstellt die Sicht nur, wenn sie nicht vorhanden ist. Wenn bereits eine Sicht mit diesem Namen vorhanden ist, wird die CREATE MATERIALIZED VIEW-Anweisung ignoriert.

    Sie können nur IF NOT EXISTS oder OR REPLACE angeben.

  • view_name

    Der Name der neu erstellten Sicht. Der vollqualifizierte Anzeigename muss eindeutig sein.

  • column_list

    Bezeichnet optional die Spalten im Abfrageergebnis der Sicht. Wenn Sie eine Spaltenliste bereitstellen, muss die Anzahl der Spaltenaliase mit der Anzahl der Ausdrücke in der Abfrage übereinstimmen. Falls keine Spaltenliste angegeben ist, werden Aliase vom Text der Sicht abgeleitet.

    • Spaltenname

      Die Spaltennamen müssen eindeutig sein und mit den Ausgabespalten der Abfrage übereinstimmen.

    • Spaltentyp

      Gibt den Datentyp der Spalte an. Nicht alle von Azure Databricks unterstützten Datentypen werden von materialisierten Sichten unterstützt.

    • column_comment

      Ein optionales STRING-Literal, das die Spalte beschreibt. Diese Option muss zusammen mit column_type angegeben werden. Wenn der Spaltentyp nicht angegeben ist, wird der Spaltenkommentar übersprungen.

    • column_constraint

      Fügt der Spalte in einer materialisierten Sicht einen informativen Primärschlüssel oder eine informative Fremdschlüsseleinschränkung hinzu. Wenn der Spaltentyp nicht angegeben ist, wird die Spalteneinschränkung übersprungen.

    • MASK-Klausel

      Fügt eine Spaltenmaskierungsfunktion hinzu, um vertrauliche Daten zu anonymisieren. Alle nachfolgenden Abfragen dieser Spalte erhalten das Ergebnis der Auswertung dieser Funktion anstelle des ursprünglichen Werts der Spalte. Dies kann für eine präzise Zugriffssteuerung nützlich sein, bei der die Funktion die Identität oder Gruppenmitgliedschaft der aufrufenden Benutzenden überprüfen kann, um zu bestimmen, ob der Wert zurückgezogen werden soll. Wenn der Spaltentyp nicht angegeben ist, wird die Spaltenmaske übersprungen.

  • Tabellenbeschränkung

    Fügt der Tabelle in einer materialisierten Sicht einen informativen Primärschlüssel oder eine informative Fremdschlüsseleinschränkung hinzu. Wenn der Spaltentyp nicht angegeben ist, wird die Tabelleneinschränkung übersprungen.

  • view_clauses

    Geben Sie optional die Partitionierung, Kommentare, benutzerdefinierte Eigenschaften und einen Aktualisierungszeitplan für die neue materialisierte Sicht an. Jede Unterklausel kann nur einmal angegeben werden.

    • PARTITIONIERT VON

      Eine optionale Liste der Spalten der Tabelle, nach denen die Tabelle partitioniert werden soll.

      Hinweis

      Flüssigkeitsclustering bietet eine flexible, optimierte Lösung für Clustering. Erwägen Sie die Verwendung von CLUSTER BY anstelle von PARTITIONED BY für materialisierte Ansichten.

    • CLUSTER BY

      Eine optionale Klausel zum Gruppieren nach einer Teilmenge von Spalten. Verwenden Sie automatische Flüssigclustering mit CLUSTER BY AUTO, und Databricks wählt intelligent Clustering-Schlüssel aus, um die Abfrageleistung zu optimieren. Siehe Verwenden von Flüssigclustering für Tabellen.

      Flüssigkeitsclustering kann nicht mit PARTITIONED BYkombiniert werden.

    • KOMMENTAR anzeigen

      Ein STRING-Literal zum Beschreiben der Tabelle.

    • STANDARD-KOLLATIONIERUNG UTF8_BINARY

      Gilt für:check marked yes Databricks SQL check marked yes Databricks Runtime 17.1 and above

      Erzwingt die Standardsortierung der materialisierten Ansicht auf UTF8_BINARY. Diese Klausel ist obligatorisch, wenn das Schema, in dem die Ansicht erstellt wird, eine andere Standardkollation als UTF8_BINARY aufweist. Die Standardsortierung der materialisierten Ansicht wird als Standardsortierung im Körper der Ansicht verwendet.

    • TBLPROPERTIES

      Legt optional eine oder mehrere benutzerdefinierte Eigenschaften fest.

      Verwenden Sie diese Einstellung, um den Laufzeitkanal lakeflow Spark Declarative Pipelines anzugeben, der zum Ausführen dieser Anweisung verwendet wird. Legen Sie den Wert der pipelines.channel-Eigenschaft auf "PREVIEW" oder "CURRENT" fest. Standardwert: "CURRENT". Weitere Informationen zu Lakeflow Spark Declarative Pipelines-Kanälen finden Sie unter Lakeflow Spark Declarative Pipelines-Laufzeitkanäle.

    • Zeitplan

      Der Zeitplan kann entweder eine SCHEDULE Anweisung oder eine TRIGGER Anweisung sein.

      • ZEITPLAN [ REFRESH ] Planungsklausel

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

          Verwenden Sie die EVERY-Syntax, um eine Aktualisierung zu planen, die in regelmäßigen Abständen auftritt. Wenn die EVERY-Syntax angegeben ist, wird die Streamingtabelle oder materialisierte Sicht basierend auf dem angegebenen Wert (z. B. HOUR, HOURS, DAY, DAYS, WEEK oder WEEKS) regelmäßig im angegebenen Intervall aktualisiert. In der folgenden Tabelle finden Sie zulässige ganzzahlige Werte für number.

          Zeiteinheit Ganzzahliger Wert
          HOUR or HOURS 1 <= H <= 72
          DAY or DAYS 1 <= D <= 31
          WEEK or WEEKS 1 <= W <= 8

          Hinweis

          Die Singular- und Pluralformen der enthaltenen Zeiteinheit sind semantisch gleichwertig.

        • CRON cron_string [ AT TIME ZONE timezone_id ]

          Zum Planen einer Aktualisierung mithilfe eines quartz cron-Werts. Gültige time_zone_values-Werte werden akzeptiert. AT TIME ZONE LOCAL wird nicht unterstützt.

          Fehlt AT TIME ZONE, wird die Sitzungszeitzone verwendet. Wenn AT TIME ZONE fehlt und die Sitzungszeitzone nicht festgelegt ist, wird ein Fehler ausgelöst. SCHEDULE ist semantisch äquivalent mit SCHEDULE REFRESH.

      • AUSLÖSEN AUF UPDATE [ HÖCHSTENS JEDES trigger_interval ]

        Wichtig

        Das TRIGGER ON UPDATE Feature befindet sich in der Betaversion.

        Legen Sie optional fest, dass die Tabelle aktualisiert wird, wenn eine upstream-Datenquelle mindestens einmal pro Minute aktualisiert wird. Legen Sie einen Wert fest, für AT MOST EVERY den mindestens eine Mindestzeit zwischen Aktualisierungen erforderlich ist.

        Die upstream-Datenquellen müssen externe oder verwaltete Delta-Tabellen (einschließlich materialisierter Ansichten oder Streamingtabellen) oder verwaltete Ansichten sein, deren Abhängigkeiten auf unterstützte Tabellentypen beschränkt sind.

        Durch das Aktivieren von Dateiereignissen können Trigger leistungsleistungsfähiger werden und einige der Grenzwerte für Triggerupdates erhöht werden.

        Dies trigger_interval ist eine INTERVAL-Anweisung , die mindestens 1 Minute beträgt.

        TRIGGER ON UPDATE hat die folgenden Einschränkungen:

        • Nicht mehr als 10 upstream-Datenquellen pro materialisierte Ansicht bei Verwendung von TRIGGER ON UPDATE.
        • Maximal 1000 Streamingtabellen oder materialisierte Ansichten können mit TRIGGER ON UPDATEangegeben werden.
        • Die AT MOST EVERY Klausel ist standardmäßig auf 1 Minute festgelegt und darf nicht kleiner als 1 Minute sein.

    • WITH ROW FILTER-Klausel

      Fügt der Tabelle eine Zeilenfilterfunktion hinzu. Alle nachfolgenden Abfragen über diese Tabelle erhalten eine Teilmenge der Zeilen, für die die Funktion als boolescher TRUE-Wert ausgewertet wird. Dies kann für eine präzise Zugriffssteuerung nützlich sein, bei der die Funktion die Identität oder Gruppenmitgliedschaft der aufrufenden Benutzenden überprüfen kann, um zu bestimmen, ob bestimmte Zeilen gefiltert werden sollen.

  • AS-Abfrage

    Eine Abfrage, die die Sicht aus Basistabellen oder anderen Sichten erstellt.

Erforderliche Berechtigungen

Die Benutzenden, die eine materialisierte Sicht (Materialized View, MV) erstellen, sind die MV-Besitzenden und benötigen die folgenden Berechtigungen:

  • SELECT-Berechtigung für die Basistabellen, auf die von der materialisierten Sicht verwiesen wird.
  • Berechtigung USE CATALOG für den übergeordneten Katalog und Berechtigung USE SCHEMA für das übergeordnete Schema.
  • CREATE MATERIALIZED VIEW-Berechtigung für das Schema für die materialisierte Sicht.

Damit Benutzende die materialisierte Sicht aktualisieren können, ist Folgendes erforderlich:

  • Berechtigung USE CATALOG für den übergeordneten Katalog und Berechtigung USE SCHEMA für das übergeordnete Schema.
  • Besitz der materialisierten Sicht oder REFRESH-Berechtigung für die materialisierte Sicht.
  • Die Besitzenden benötigen die SELECT-Berechtigung für die Basistabellen, auf die die materialisierte Sicht verweist.

Damit Benutzende die materialisierte Sicht abfragen können, ist Folgendes erforderlich:

  • Berechtigung USE CATALOG für den übergeordneten Katalog und Berechtigung USE SCHEMA für das übergeordnete Schema.
  • SELECT-Berechtigung für die materialisierte Sicht.

Zeilenfilter und Spaltenmasken

Mit Zeilenfiltern können Sie eine Funktion angeben, die als Filter gilt, wenn ein Tabellenscan Zeilen abruft. Mit diesen Filtern können Sie sicherstellen, dass nachfolgende Abfragen nur Zeilen zurückgeben, für die das Filterprädikat zu TRUE ausgewertet wird.

Mit Spaltenmasken können Sie die Werte einer Spalte maskieren, wann immer ein Tabellenscan Zeilen abruft. Alle zukünftigen Abfragen, die diese Spalte einbeziehen, erhalten das Ergebnis der Auswertung der Funktion über die Spalte, wobei der ursprüngliche Wert der Spalte ersetzt wird.

Weitere Informationen zur Verwendung von Zeilenfiltern und Spaltenformaten finden Sie unter Zeilenfilter und Spaltenformate.

Verwalten von Zeilenfiltern und Spaltenmasken

Zeilenfilter und Spaltenmasken für materialisierte Sichten sollten mithilfe der CREATE-Anweisung hinzugefügt werden.

Verhalten

  • Als Definer aktualisieren: Wenn die REFRESH MATERIALIZED VIEW Anweisung eine materialisierte Ansicht aktualisiert, werden Zeilenfilterfunktionen mit den Rechten des Definers (als Tabellenbesitzer) ausgeführt. Dies bedeutet, dass die Tabellenaktualisierung den Sicherheitskontext des Benutzers verwendet, der die materialisierte Sicht erstellt hat.
  • Abfrage: Während die meisten Filter mit den Rechten des Definers ausgeführt werden, sind Funktionen, die den Benutzerkontext (z CURRENT_USER . B. und IS_MEMBER) überprüfen, Ausnahmen. Diese Funktionen werden als Aufrufer ausgeführt. Dieser Ansatz erzwingt benutzerspezifische Datensicherheit und Zugriffssteuerungen basierend auf dem Kontext des aktuellen Benutzers.
  • Beim Erstellen materialisierter Sichten über Quelltabellen, die Zeilenfilter und Spaltenmasken enthalten, ist die Aktualisierung der materialisierten Sicht immer eine vollständige Aktualisierung. Bei einer vollständigen Aktualisierung werden alle in der Quelle verfügbaren Daten mit den neuesten Definitionen aktualisiert. Dadurch wird sichergestellt, dass Sicherheitsrichtlinien der Quelltabellen ausgewertet und mit den aktuellen Daten und Definitionen aktualisiert werden.

Einblick

Verwenden Sie DESCRIBE EXTENDED, INFORMATION_SCHEMA oder den Katalog-Explorer, um die vorhandenen Zeilenfilter und Spaltenmasken zu untersuchen, die für eine bestimmte materialisierte Sicht gelten. Mit dieser Funktionalität können Benutzende Datenzugriffs- und Schutzmaßnahmen für materialisierte Sichten überwachen und überprüfen.

Einschränkungen

  • Wenn aus einer materialisierten Ansicht mit einem sum-Aggregat über eine NULL-fähige Spalte der letzte Wert ungleich NULL aus dieser Spalte entfernt wird, sodass nur noch NULL-Werte verbleiben, gibt der resultierende Aggregatwert der materialisierten Ansicht null anstelle von NULL zurück.
  • Spaltenverweise erfordern keinen Alias. Ausdrücke für Nicht-Spaltenverweise erfordern einen Alias, wie im folgenden Beispiel angezeigt:
    • Zulässig: SELECT col1, SUM(col2) AS sum_col2 FROM t GROUP BY col1
    • Nicht zulässig: SELECT col1, SUM(col2) FROM t GROUP BY col1
  • NOT NULL muss zusammen mit PRIMARY KEY manuell angegeben werden, um eine gültige Anweisung darzustellen.
  • Materialisierte Sichten unterstützen keine Identitätsspalten oder Ersatzschlüssel.
  • Materialisierte Sichten unterstützen nicht die Befehle OPTIMIZE und VACUUM. Die Wartung erfolgt automatisch.
  • Materialisierte Sichten unterstützen keine Erwartungen zum Definieren von Datenqualitätseinschränkungen.

Beispiele

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