Verwenden von materialisierten Ansichten

Eine materialisierte Ansicht ist ein vorberechnetes Dataset, das von einer Abfragespezifikation (die SELECT-Liste in der Ansichtsdefinition) abgeleitet und zur späteren Verwendung gespeichert wird. Da die Daten vorberechnet sind, ist das Abfragen einer materialisierten Ansicht schneller als das Ausführen einer Abfrage auf der Basistabelle der Ansicht. Dieser Leistungsunterschied kann signifikant sein, wenn eine Abfrage häufig ausgeführt wird oder hinreichend komplex ist. Infolgedessen können materialisierte Ansichten teure Aggregations-, Projektions- und Auswahloperationen beschleunigen, insbesondere solche, die häufig und/oder auf großen Datasets ausgeführt werden.

Bemerkung

Materialisierte Ansichten verbessern die Abfrageleistung von Workloads, die aus häufig verwendeten und/oder sich wiederholenden Abfragemustern bestehen. Eine Materialisierung von Zwischenergebnissen verursacht jedoch zusätzliche Kosten. Bevor Sie materialisierte Ansichten erstellen, sollten Sie also überlegen, ob die Kosten durch die Einsparungen bei häufiger Verwendung dieser Ergebnisse aufgewogen werden.

Unter diesem Thema:

Kriterien für das Erstellen materialisierter Ansichten

Materialisierte Ansichten sind besonders in folgenden Situationen nützlich:

  • Abfrageergebnisse enthalten im Verhältnis zur Basistabelle (Tabelle, für die die Ansicht definiert ist) eine relativ kleine Anzahl von Zeilen und/oder Spalten.

  • Abfrageergebnisse enthalten Ergebnisse, die eine erhebliche Verarbeitung erfordern, z. B.:

    • Analyse semistrukturierter Daten

    • Aggregate, deren Berechnung zeitaufwändig ist

  • Die Abfrage erfolgt auf einer externen Tabelle (d. h. Datasets, die in Dateien eines externen Stagingbereichs gespeichert sind), wodurch die Leistung im Vergleich zu Abfragen auf nativen Datenbanktabellen möglicherweise geringer ist.

  • Die Basistabelle der Ansicht ändert sich nicht regelmäßig.

Vorteile materialisierter Ansichten

Die Snowflake-Implementierung materialisierter Ansichten bietet eine Reihe besonderer Vorteile:

  • Materialisierte Ansichten können die Leistung von Abfragen verbessern, die wiederholt dieselben Unterabfrageergebnisse verwenden.

  • Materialisierte Ansichten werden von Snowflake automatisch und transparent verwaltet. Ein Hintergrundservice aktualisiert die materialisierte Ansicht, nachdem Änderungen an der Basistabelle vorgenommen wurden. Dies ist effizienter und weniger fehleranfällig als die manuelle Wartung des Äquivalents einer materialisierten Ansicht auf Anwendungsebene.

  • Daten, auf die über materialisierte Ansichten zugegriffen wird, sind immer aktuell, unabhängig vom Umfang der DML, die auf der Basistabelle ausgeführt wurde. Wenn eine Abfrage ausgeführt wird, bevor die materialisierte Ansicht auf dem neuesten Stand ist, aktualisiert Snowflake entweder die materialisierte Ansicht oder verwendet die aktuellen Teile der materialisierten Ansicht und ruft die erforderlichen neueren Daten aus der Basistabelle ab.

Wichtig

Die automatische Wartung materialisierter Ansichten verbraucht Credits. Weitere Details dazu finden Sie unter Wartungskosten für materialisierte Ansichten (unter diesem Thema).

Kriterien für das Erstellen materialisierter vs. Standardansichten

Wenn Sie entscheiden müssen, ob Sie eine materialisierte Ansicht oder eine Standardansicht erstellen, verwenden Sie im Allgemeinen die folgenden Kriterien:

  • Erstellen Sie eine materialisierte Ansicht, wenn alle der folgenden Bedingungen erfüllt sind:

    • Die Abfrageergebnisse aus der Ansicht ändern sich nicht oft. Dies bedeutet fast immer, dass sich die zugrunde liegende Basistabelle der Ansicht nicht oft ändert oder dass sich zumindest die Teilmenge der Basistabellenzeilen, die in der materialisierten Ansicht verwendet werden, nicht häufig ändert.

    • Die Ergebnisse der Ansicht werden häufig verwendet (normalerweise wesentlich häufiger als sich die Abfrageergebnisse ändern).

    • Die Abfrage verbraucht viele Ressourcen. In der Regel bedeutet dies, dass die Abfrage viel Verarbeitungszeit oder Credits verbraucht. Es kann jedoch auch bedeuten, dass die Abfrage viel Speicherplatz für Zwischenergebnisse benötigt.

  • Erstellen Sie eine reguläre Ansicht, wenn mindestens eine der folgenden Bedingungen erfüllt ist:

    • Die Ergebnisse der Ansicht ändern sich häufig.

    • Die Ergebnisse werden nicht oft verwendet (relativ zu der Änderungsrate der Ergebnisse).

    • Die Abfrage ist nicht ressourcenintensiv, daher ist es nicht kostspielig, sie erneut auszuführen.

Diese Kriterien stellen lediglich Richtlinien dar. Eine materialisierte Ansicht kann Vorteile bieten, auch wenn sie nicht oft verwendet wird – vor allem, wenn sich die Ergebnisse weniger häufig ändern als die Verwendung der Ansicht.

Bei der Entscheidung, ob eine reguläre Ansicht oder eine materialisierte Ansicht verwendet werden soll, sind weitere Faktoren zu berücksichtigen.

Beispielsweise sind die Kosten für das Speichern der materialisierten Ansicht ein Faktor. Wenn die Ergebnisse nicht sehr häufig verwendet werden (selbst wenn sie häufiger verwendet werden als sie sich ändern), sind die zusätzlichen Speicherkosten möglicherweise nicht die Leistungssteigerung wert.

Vergleich mit Tabellen, regulären Ansichten und zwischengespeicherten Ergebnissen

Materialisierte Ansichten sind in gewisser Hinsicht den Tabellen und in anderer Hinsicht regulären Ansichten (d. h. nicht materialisierten Ansichten) ähnlich. Darüber hinaus weisen materialisierte Ansichten einige Ähnlichkeiten mit zwischengespeicherten Ergebnissen auf, insbesondere weil beide das Speichern von Abfrageergebnissen für eine spätere Wiederverwendung ermöglichen.

In diesem Abschnitt werden einige Ähnlichkeiten und Unterschiede zwischen diesen Objekten in bestimmten Bereichen beschrieben, darunter:

  • Abfrageleistung

  • Abfragesicherheit

  • Reduzierte Komplexität der Abfragelogik

  • Daten-Clustering (bezogen auf die Abfrageleistung)

  • Speicher- und Wartungskosten

Snowflake speichert die Abfrageergebnisse nach dem Ausführen einer Abfrage für kurze Zeit im Cache. Wenn dieselbe Abfrage erneut ausgeführt wird und sich in den Tabellen, auf die die Abfrage zugreift, nichts geändert hat, kann Snowflake in einigen Fällen einfach dieselben Ergebnisse zurückgeben, ohne dass die Abfrage erneut ausgeführt werden muss. Dies ist die schnellste und effizienteste Form von Wiederverwendung, aber auch die am wenigsten flexible. Weitere Details dazu finden Sie unter Verwenden von persistent gespeicherten Abfrageergebnissen.

Sowohl materialisierte Ansichten als auch zwischengespeicherte Abfrageergebnisse bieten Vorteile für die Abfrageperformance:

  • Materialisierte Ansichten sind flexibler, aber normalerweise langsamer als zwischengespeicherte Ergebnisse.

  • Materialisierte Ansichten sind aufgrund ihres „Cache“ (d. h. für Abfrageergebnisse der Ansicht) schneller als Tabellen. Wenn sich Daten geändert haben, können sie außerdem ihren „Cache“ für Daten verwenden, die sich nicht geändert haben, und die Basistabelle für alle Daten nutzen, die sich geändert haben.

In regulären Ansichten werden keine Daten zwischengespeichert; daher kann die Leistung durch Zwischenspeichern nicht verbessert werden. In einigen Fällen helfen Ansichten Snowflake jedoch dabei, einen effizienteren Abfrageplan zu erstellen. Außerdem erlauben sowohl materialisierte Ansichten als auch reguläre Ansichten einen besseren Datenschutz, da sich Daten auf Zeilen- oder Spaltenebene ein- und ausblenden lassen.

Die folgende Tabelle veranschaulicht die wichtigsten Ähnlichkeiten und Unterschiede zwischen Tabellen, regulären Ansichten, zwischengespeicherten Abfrageergebnissen und materialisierten Ansichten:

Leistungsvorteile

Sicherheitsvorteile

Vereinfacht Abfragelogik

Unterstützt Clustering

Verbraucht Speicher

Verbraucht Credits für Wartung

Anmerkungen

Reguläre Tabelle

Reguläre Ansicht

Zwischengespeichertes Abfrageergebnis

Wird nur verwendet, wenn sich die Daten nicht geändert haben und die Abfrage nur deterministische Funktionen verwendet (z. B. nicht CURRENT_DATE).

Materialisierte Ansicht

Speicher- und Wartungsanforderungen führen in der Regel zu erhöhten Kosten.

Externe Tabelle

Die Daten werden außerhalb von Snowflake verwaltet, sodass keine Speichergebühren innerhalb von Snowflake anfallen.

Anwendungsfälle von materialisierten Ansichten

In diesem Abschnitt werden einige allgemeine Einsatzszenarien beschrieben, die gleichzeitig einen konzeptionellen Überblick über materialisierte Ansichten bieten:

  • Angenommen, Sie führen täglich eine Abfrage Q aus, die eine Unterabfrage S enthält. Wenn S ressourcenintensiv ist und Daten abfragt, die sich nur einmal pro Woche ändern, können Sie die Leistung der äußeren Abfrage Q verbessern, indem Sie S ausführen und die Ergebnisse in einer Tabelle namens CT zwischenspeichern:

    • Sie würden die Tabelle nur einmal pro Woche aktualisieren.

    • In der übrigen Zeit, wenn Sie Q ausführen, wird auf die Ergebnisse der Unterabfrage von S verwiesen, die in der Tabelle gespeichert wurden.

    Dies funktioniert gut, solange sich die Ergebnisse der Unterabfrage S vorhersehbar ändern (z. B. jede Woche zur gleichen Zeit).

    Wenn sich die Ergebnisse von S jedoch unvorhersehbar ändern, ist es riskant, die Ergebnisse in einer Tabelle zwischenzuspeichern; zum Teil wird Ihre Hauptabfrage Q veraltete Ergebnisse zurückgeben, wenn die Ergebnisse der Unterabfrage S veraltet sind (und daher die Ergebnisse der Cachetabelle CT veraltet sind).

    Im Idealfall verwenden Sie einen speziellen Cachetyp für Ergebnisse, die sich selten ändern, bei denen der Zeitpunkt der Änderung jedoch nicht vorhersagbar ist. Anders ausgedrückt: Sie möchten, dass Ihre Unterabfrage S ggf. erneut ausgeführt wird (und Ihre Cachetabelle CT aktualisiert wird).

    Materialisierte Ansicht bieten eine Annäherung an das Beste aus beiden Welten. Sie definieren eine Abfrage für Ihre materialisierte Ansicht, und die Ergebnisse der Abfrage werden zwischengespeichert (als wären sie in einer internen Tabelle gespeichert). Wenn die Tabelle, für die die materialisierte Ansicht definiert ist, aktualisiert wird, aktualisiert Snowflake aber den Cache. Somit stehen Ihre Unterabfrageergebnisse sofort für eine schnellere Leistung zur Verfügung.

  • Nehmen Sie als weniger abstraktes Beispiel an, dass Sie eine kleine Filiale einer großen Apotheke betreiben und Ihre Filiale hunderte Medikamente aus insgesamt zehntausenden von FDA-zugelassenen Medikamenten vorrätig hat.

    Angenommen, Sie haben eine vollständige Liste aller Medikamente, die jeder Ihrer Kunden einnimmt, wobei fast alle Kunden nur Medikamente bestellen, die auf Lager sind (d. h. Sonderbestellungen sind selten).

    In diesem Szenario könnten Sie eine materialisierte Ansicht erstellen, in der nur die Wechselwirkungen zwischen Medikamenten aufgelistet sind, die Sie auf Lager halten. Wenn ein Kunde ein Medikament bestellt, das er noch nie zuvor eingenommen hat, und dieses Medikament und alle anderen Medikamente, die er einnimmt, von Ihrer materialisierten Sicht erfasst werden, müssen Sie nicht die gesamte FDA-Datenbank auf Wechselwirkungen mit anderen Arzneimitteln überprüfen. Sie müssen lediglich die materialisierte Ansicht prüfen, was die Suche spürbar beschleunigt.

  • Sie können eine materialisierte Ansicht eigenständig oder in einer Verknüpfung (Join) verwenden können.

    Um mit dem Apothekenbeispiel fortzufahren: Nehmen Sie an, Sie haben eine Tabelle, in der alle Medikamente aufgelistet sind, die jeder Ihrer Kunden einnimmt. Sie können diese Tabelle mit der materialisierten Ansicht der Wechselwirkungen verbinden, um herauszufinden, welche der aktuellen Medikamente des Kunden Wechselwirkungen mit dem neuen Medikament aufweisen.

    Sie können eine äußere Verknüpfung (Outer Join) verwenden, um sicherzustellen, dass Sie alle Arzneimittel des Kunden auflisten, unabhängig davon, ob sie sich in Ihrer materialisierten Ansicht befinden oder nicht. Wenn die äußere Verknüpfung zeigt, dass sich eines der aktuellen Arzneimittel nicht in der materialisierten Ansicht befindet, können Sie die Abfrage auf der Tabelle mit den vollständigen Arzneimittelwechselwirkungen erneut ausführen.

Abfrageoptimierung und materialisierte Ansichten

Sie müssen in einer SQL-Anweisung keine materialisierte Ansicht angeben, damit die Ansicht verwendet werden kann. Die Abfrageoptimierung sorgt automatisch für das Umschreiben von Abfragen auf einer Basistabelle oder auf Standardansichten, um stattdessen die materialisierte Ansicht zu verwenden.

Angenommen, eine materialisierte Ansicht enthält alle Zeilen und Spalten, die von einer Abfrage für eine Basistabelle benötigt werden. Die Abfrageoptimierung kann entscheiden, ob die Abfrage umgeschrieben wird, um anstelle der Basistabelle die materialisierte Ansicht zu verwenden. Dies zu einer erheblichen Beschleunigung einer Abfrage führen, insbesondere wenn die Basistabelle eine große Menge historischer Daten enthält.

Als weiteres Beispiel kann die Abfrageoptimierung bei einem Join mit mehreren Tabellen entscheiden, dass für eine der Tabellen im Join eine materialisierte Ansicht anstelle einer Tabelle verwendet wird.

Bemerkung

Selbst wenn eine materialisierte Ansicht die Basistabelle in einer bestimmten Abfrage ersetzen kann, verwendet die Abfrageoptimierung die materialisierte Ansicht möglicherweise nicht. Wenn die Basistabelle beispielsweise über ein Feld gruppiert ist, scannt die Abfrageoptimierung möglicherweise die Basistabelle (anstelle der materialisierten Ansicht), da die Optimierung Partitionen effektiv auslassen und auch bei Verwendung der Basistabelle eine äquivalente Performance bereitstellen kann.

Eine materialisierte Ansicht kann auch als Datenquelle für eine Unterabfrage verwendet werden.

Wenn die Abfrageoptimierung implizit eine materialisierte Ansicht verwendet, wird die im EXPLAIN-Plan oder in Query Profile anstelle der Basistabelle die materialisierte Ansicht aufgeführt. Mithilfe dieser Informationen können Sie experimentieren und verstehen, welche Abfragen von vorhandenen materialisierten Ansichten profitieren.

Informationen zu materialisierten Ansichten in Snowflake

In den nächsten Abschnitten wird erläutert, wie materialisierte Ansichten in Snowflake repräsentiert werden.

DDL-Befehle für materialisierte Ansichten

Materialisierte Ansichten sind First-Class-Datenbankobjekte. Snowflake bietet folgende DDL-Befehle zum Erstellen und Verwalten von materialisierten Ansichten:

DML-Operationen auf materialisierten Ansichten

Snowflake erlaubt keine Standard-DML (z. B. INSERT, UPDATE, DELETE) auf materialisierten Ansichten. Mit Snowflake können Benutzer materialisierte Ansichten nicht abschneiden.

Weitere Details dazu finden Sie unter Einschränkungen bei der Verwendung von materialisierten Ansichten (unter diesem Thema).

Zugriffssteuerungsrechte

Es gibt zwei Arten von Berechtigungen, die sich auf materialisierte Ansichten beziehen:

  • Berechtigungen des Schemas, das die materialisierte Ansicht enthält.

  • Berechtigungen, die direkt für die materialisierte Ansicht selbst gelten.

  • Berechtigungen der Datenbankobjekte (z. B. Tabellen), auf die die materialisierte Ansicht zugreift.

Sie können die Standardbefehle zum Erteilen und Widerrufen von Berechtigungen für materialisierte Ansichten verwenden:

Berechtigungen für das Schema einer materialisierten Ansicht

Materialisierte Ansichten belegen Speicherplatz. Um eine materialisierte Ansicht zu erstellen, benötigen Sie die Berechtigung CREATE MATERIALIZED VIEW für das Schema, das die materialisierte Ansicht enthält. Sie müssen eine Anweisung ausführen, die der folgenden ähnelt:

GRANT CREATE MATERIALIZED VIEW ON SCHEMA <schema_name> TO ROLE <role_name>;

Weitere Informationen zur GRANT-Anweisung finden Sie unter GRANT <Berechtigungen> … TO ROLE.

Berechtigungen für eine materialisierte Ansicht

Ähnlich wie andere Datenbankobjekte (Tabellen, Ansichten, UDFs usw.) gehören materialisierte Ansichten einer Rolle und verfügen über Berechtigungen, die anderen Rollen erteilt werden können.

Sie können für eine materialisierte Ansicht folgende Berechtigungen erteilen:

  • SELECT

Wie bei nicht materialisierten Ansichten übernimmt eine materialisierte Ansicht nicht automatisch die Berechtigungen ihrer Basistabelle. Sie sollten den Rollen, die diese Ansicht verwenden sollen, explizit Berechtigungen für die materialisierte Ansicht erteilen.

Bemerkung

Die Ausnahme von dieser Regel besteht darin, dass die Abfrageoptimierung eine Abfrage auf der Basistabelle umschreibt, um die materialisierte Ansicht zu verwenden (wie unter Abfrageoptimierung und materialisierte Ansichten erläutert). In diesem Fall benötigt der Benutzer keine Berechtigungen, um die materialisierte Ansicht zu verwenden und auf die Ergebnisse der Abfrage zuzugreifen.

Berechtigungen für Datenbankobjekte, auf die materialisierte Ansichten zugreifen

Wie bei nicht materialisierten Ansichten benötigt ein Benutzer, der auf eine materialisierte Ansicht zugreifen möchte, nur Berechtigungen für die Ansicht, nicht aber für die zugrunde liegenden Objekte, auf die die Ansicht verweist.

Sichere materialisierte Ansichten

Materialisierte Ansichten können sichere Ansichten sein.

Die meisten Informationen über sichere Ansichten gelten für sichere materialisierte Ansichten. In einigen Fällen unterscheiden sich sichere materialisierte Ansichten von sicheren nicht materialisierten Ansichten. Zu den Unterschieden gehören:

  • Der Befehl, um herauszufinden, ob eine Ansicht sicher ist.

    • Überprüfen Sie bei nicht materialisierten Ansichten die Spalte IS_SECURE in der Ausgabe des Befehls SHOW VIEWS.

    • Überprüfen Sie bei materialisierten Ansichten die Spalte IS_SECURE in der Ausgabe des Befehls SHOW MATERIALIZED VIEWS.

Weitere Informationen zu sicheren Ansichten finden Sie unter Verwenden von sicheren Ansichten.

Die Syntax zum Erstellen sicherer materialisierter Ansichten ist unter CREATE MATERIALIZED VIEW dokumentiert.

Erstellen und Verwenden von materialisierten Ansichten

Dieser Abschnitt enthält Informationen zum Erstellen und Verwenden von materialisierten Ansichten.

Planen der Erstellung einer materialisierten Ansicht

Wenn Sie sich entscheiden, eine materialisierte Ansicht zu erstellen, sollten Sie eine Analyse durchführen, um die Notwendigkeit einer solchen Ansicht zu ermitteln:

  1. Untersuchen Sie die Filter, Projektionen und Aggregationen von Abfragen, die häufig oder teuer sind.

  2. Verwenden Sie Query Profile und den Befehl EXPLAIN, um festzustellen, ob vorhandene materialisierte Ansichten bereits von der Funktion zum automatischen Umschreiben von Abfragen (Abfrageoptimierung) verwendet werden. Möglicherweise müssen Sie keine neuen materialisierten Ansichten erstellen, wenn bereits Ansichten vorhanden sind, die gut zu den Abfragen passen.

  3. Notieren Sie vor dem Hinzufügen materialisierter Ansichten die aktuellen Abfragekosten und die aktuelle Performance, damit Sie den Unterschied nach dem Erstellen der neuen materialisierten Ansicht bewerten können.

  4. Wenn Sie Abfragen mit sehr selektiven Filtern finden, die nicht vom Clustering der Tabelle profitieren, kann eine materialisierte Ansicht mit denselben Filtern den Abfragen helfen, das Scannen vieler Daten zu vermeiden.

    Wenn Sie Abfragen finden, die eine Aggregation verwenden oder Ausdrücke enthalten, deren Auswertung sehr teuer ist (z. B. teure Funktionsaufrufe oder teure Operationen auf semistrukturierte Daten), kann eine materialisierte Ansicht, die dieselben Ausdrücke oder Aggregationen verwendet, einen Vorteil bieten.

  5. Führen Sie den Befehl EXPLAIN für die ursprünglichen Abfragen aus, oder führen Sie die Abfragen aus und überprüfen Sie das Query Profile, um festzustellen, ob die neue materialisierte Ansicht verwendet wird.

  6. Überwachen Sie die kombinierten Kosten für Abfrage und materialisierte Ansicht, und bewerten Sie, ob die Leistungs- oder Kostenvorteile die Kosten für die Wartung der materialisierten Ansicht rechtfertigen.

    Untersuchen Sie auch die Abfragekosten der Basistabelle. In Fällen, in denen die Abfrageoptimierung die Abfrage umschreibt, um eine materialisierte Ansicht zu verwenden, kann die Abfragekompilierung mehr Zeit und Ressourcen verbrauchen. (Die Abfrageoptimierung muss eine viel größere Anzahl von Möglichkeiten prüfen.)

  7. Denken Sie daran, dass Sie immer direkt auf materialisierte Ansichten verweisen können, wenn dies Ihre Abfragen vereinfacht oder Sie wissen, dass eine materialisierte Ansicht eine bessere Leistung bietet. In den meisten Fällen können Sie jedoch einfach die Basistabelle abfragen, und die Funktion zum automatischen Umschreiben von Abfragen führt dies für Sie aus.

Erstellen einer materialisierten Ansicht

Verwenden Sie den Befehl CREATE MATERIALIZED VIEW, um eine materialisierte Ansicht zu erstellen. Ein Beispiel dazu finden Sie unter Grundlegendes Beispiel: Erstellen einer materialisierten Ansicht (unter diesem Thema).

Beachten Sie Folgendes:

  • Verwenden Sie nach Möglichkeit den vollqualifizierten Namen für die Basistabelle, auf die in einer materialisierten Ansicht verwiesen wird. Dadurch wird die Ansicht vor Änderungen geschützt, durch die sie ungültig werden kann, z. B. durch Verschieben der Basistabelle in ein anderes Schema als in der Ansicht (oder umgekehrt).

    Wenn der Name der Basistabelle nicht qualifiziert ist und die Tabelle oder Ansicht in ein anderes Schema verschoben wird, wird die Referenz ungültig.

  • Wenn Sie sich in der Ansichtendefinition mehr als einmal auf die Basistabelle beziehen, verwenden Sie in allen Verweisen auf die Basistabelle denselben Qualifier. Wenn Sie sich z. B. für die Verwendung des vollqualifizierten Namens entscheiden, stellen Sie sicher, dass alle Verweise auf die Basistabelle den vollqualifizierten Namen verwenden.

  • Wenn Sie beim Erstellen einer materialisierten Ansicht einen Filter angeben (z. B. WHERE column_1 BETWEEN Y and Z), kann die Abfrageoptimierung die materialisierte Ansicht für Abfragen auf der Basistabelle verwenden, die denselben oder einen restriktiveren Filter haben. Hier sind einige Beispiele:

    • Hier ist ein einfaches Beispiel für die Subsumption von Bereichen. In diesem Beispiel stimmt der Filter in der Abfrage nicht mit dem Filter in der materialisierten Ansicht überein. Der Filter in der Abfrage wählt jedoch nur Zeilen aus, die sich in der materialisierten Ansicht befinden, sodass die Abfrageoptimierung nur die materialisierte Ansicht anstelle der gesamten Tabelle scannt.

      -- Example of a materialized view with a range filter
      create materialized view v1 as
          select * from table1 where column_1 between 100 and 400;
      
      -- Example of a query that might be rewritten to use the materialized view
      select * from table1 where column_1 between 200 and 300;
      
    • Dieses Beispiel zeigt die OR-Subsumption. Die materialisierte Ansicht enthält alle Zeilen, die von der nachfolgenden Abfrage benötigt werden.

      Definieren Sie eine materialisierte Ansicht, die alle Zeilen enthält, die entweder den Wert X oder den Wert Y haben:

      create materialized view mv1 as
          select * from tab1 where column_1 = X or column_1 = Y;
      

      Definieren Sie eine Abfrage, die nur nach dem Wert Y sucht (der in der materialisierten Ansicht enthalten ist):

      select * from tab1 where column_1 = Y;
      

      Die obige Abfrage kann intern wie folgt umgeschrieben werden:

      select * from mv1 where column_1 = Y;
      
    • Dieses Beispiel ist ein weiteres Beispiel für die OR-Subsumption. Die Definition der materialisierten Ansicht enthält kein explizites OR. Eine IN-Klausel entspricht jedoch einer Reihe von OR-Ausdrücken, sodass die Optimierung diese Abfrage genauso umschreiben kann wie im obige Beispiel die OR-Subsumption:

      create materialized view mv1 as
          select * from tab1 where column_1 in (X, Y);
      

      Definieren Sie eine Abfrage, die nur nach dem Wert Y sucht (der in der materialisierten Ansicht enthalten ist):

      select * from tab1 where column_1 = Y;
      

      Die obige Abfrage kann intern wie folgt umgeschrieben werden:

      select * from mv1 where column_1 = Y;
      
    • In diesem Beispiel wird die AND-Subsumption verwendet:

      Erstellen Sie eine materialisierte Ansicht, die alle Zeilen enthält, in denen column_1 = X.

      create materialized view mv2 as
          select * from table1 where column_1 = X;
      

      Erstellen Sie eine Abfrage:

      select column_1, column_2 from table1 where column_1 = X AND column_2 = Y;
      

      Die Abfrage kann wie folgt umgeschrieben werden:

      select * from mv2 where column_2 = Y;
      

      Die umgeschriebene Abfrage muss nicht einmal den Ausdruck column_1 = X enthalten, da die Definition der materialisierten Ansicht bereits erfordert, dass alle Zeilen die Anforderung column_1 = X erfüllen.

    • Das folgende Beispiel zeigt die aggregierte Subsumption:

      Die materialisierte Ansicht ist unten definiert:

      create materialized view mv4 as
          select column_1, column_2, sum(column_3) from table1 group by column_1, column_2;
      

      Die folgende Abfrage kann die oben definierte materialisierte Ansicht verwenden:

      select column_1, sum(column_3) from table1 group by column_1;
      

      Die Abfrage kann wie folgt umgeschrieben werden:

      select column_1, sum(column_3) from mv4 group by column_1;
      

      Die umgeschriebene Abfrage nutzt die zusätzliche Gruppierung nach „column_2“ nicht aus, aber die umgeschriebene Abfrage wird auch nicht durch diese zusätzliche Gruppierung blockiert.

  • Dies bedeutet, dass die Ausführung der CREATE MATERIALIZED VIEW-Anweisung möglicherweise einige Zeit in Anspruch nimmt.

    Wenn eine materialisierte Ansicht erstellt wird, führt Snowflake das Äquivalent einer CTAS-Operation (CREATE TABLE … AS …) aus.

Einschränkungen beim Erstellen materialisierter Ansichten

Bemerkung

Die hier genannten Einschränkungen gelten momentan. Einige davon werden in zukünftigen Versionen möglicherweise entfernt oder geändert.

Die folgenden Einschränkungen gelten für das Erstellen materialisierter Ansichten:

  • Eine materialisierte Ansicht kann nur eine einzelne Tabelle abfragen.

  • Verknüpfungen (Joins), einschließlich Selbstverknüpfungen (Self-Joins), werden nicht unterstützt.

  • Eine materialisierte Ansicht kann Folgendes nicht abfragen:

    • materialisierte Ansichten

    • nicht materialisierte Ansichten

    • UDTFs (benutzerdefinierte Tabellenfunktionen)

  • Eine materialisierte Ansicht darf Folgendes nicht enthalten:

    • UDFs (diese Einschränkung gilt für alle Typen von benutzerdefinierten Funktionen, einschließlich externer Funktionen).

    • Fensterfunktionen

    • HAVING-Klausel.

    • ORDER BY-Klausel.

    • LIMIT-Klausel.

    • GROUP BY-Schlüssel, die nicht in der SELECT-Liste enthalten sind. Alle GROUP BY-Schlüssel einer materialisierten Ansicht müssen Teil der SELECT-Liste sein.

    • GROUP BY GROUPING SETS.

    • GROUP BY ROLLUP.

    • GROUP BY CUBE.

    • Verschachtelung von Unterabfragen innerhalb einer materialisierten Ansicht

  • Viele Aggregatfunktionen sind in der Definition einer materialisierten Ansicht nicht zulässig.

    Bemerkung

    Aggregatfunktionen, die in materialisierten Ansichten zulässig sind, weisen dennoch einige Einschränkungen auf:

    • Aggregatfunktionen können nicht verschachtelt werden.

    • In komplexen Ausdrücken verwendete Aggregatfunktionen (z. B. (sum(salary)/10)) können nur auf der äußersten Ebene einer Abfrage verwendet werden, nicht in einer Unterabfrage oder einer Inline-Ansicht.

      Beispielsweise wird Folgendes unterstützt:

      create materialized view mv1 as
          select
              sum(x) + 100
            from t;
      

      Folgender Befehl ist beispielsweise nicht zulässig:

      create materialized view mv2 as
          select
              y + 10
            from (
              select
                sum(x) as y
              from t
            );
      
    • DISTINCT kann nicht mit Aggregatfunktionen kombiniert werden.

    • In einer materialisierten Ansicht können AVG, COUNT, MIN, MAX und SUM als Aggregatfunktionen, jedoch nicht als Fensterfunktionen verwendet werden. In einer materialisierten Ansicht können diese Funktionen nicht mit der OVER-Klausel verwendet werden:

      OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> ] )
      
    • Wenn sich eine Aggregatfunktion in einer Unterabfrage befindet, kann die materialisierte Ansicht keinen Ausdruck über den aggregierten Spalten dieser Unterabfrage erstellen. Betrachten Sie beispielsweise die folgende Definition einer materialisierten Ansicht:

      create or replace materialized view mv1 as
          select c1 + 10 as c1new, c2
              from (select sum(c1) as c1, c2 from t group by c2);
      

      Der Ausdruck „c1 + 10“ ist ein Ausdruck über einer Aggregatfunktion in einer Unterabfrage und verursacht daher eine Fehlermeldung.

      Beachten Sie, dass auch ein Gleichheitsoperator als Ausdruck zählt. Dies bedeutet, dass auch CASE-Ausdrücke mit Spalten, die Aggregatfunktionen in einer Unterabfrage darstellen, nicht zulässig sind.

      Erstellen Sie eine materialisierte Ansicht ohne den Ausdruck, und erstellen Sie dann eine nicht materialisierte Ansicht, die den Ausdruck enthält, beispielsweise um diese Beschränkung umzugehen:

      create or replace materialized view mv1 as
          select c1, c2
              from (select sum(c1) as c1, c2 from t group by c2);
      
      create or replace view expr_v1 as
          select c1 + 10 as c1new, c2
              from (select c1, c2 from mv1);
      
  • Funktionen, die in einer materialisierten Ansicht verwendet werden, müssen deterministisch sein. Eine Verwendung von CURRENT_TIME oder CURRENT_TIMESTAMP ist beispielsweise nicht zulässig.

  • Eine materialisierte Ansicht sollte nicht mit einer Funktion definiert werden, die unterschiedliche Ergebnisse für unterschiedliche Einstellungen von Parametern erzeugt, z. B. den Parameter TIMESTAMP_TYPE_MAPPING auf Sitzungsebene.

    Angenommen, eine Ansicht ist wie folgt definiert:

    create materialized view bad_example (ts1) as
        select to_timestamp(n) from t1;
    

    Der Datentyp des Rückgabewerts von TO_TIMESTAMP(n) hängt vom Parameter TIMESTAMP_TYPE_MAPPING ab, sodass der Inhalt der materialisierten Ansicht vom Wert für TIMESTAMP_TYPE_MAPPING zum Zeitpunkt der Erstellung der Ansicht abhängt.

    Wenn eine materialisierte Ansicht erstellt wird, wird der Ausdruck, der jede ihrer Spalten definiert, ausgewertet und gespeichert. Wenn eine Spaltendefinition von einer bestimmten Sitzungsvariablen abhängt und sich die Sitzungsvariable ändert, wird der Ausdruck nicht neu ausgewertet und die materialisierte Ansicht wird nicht aktualisiert. Wenn die materialisierte Ansicht von einem bestimmten Wert einer Sitzungsvariablen abhängt und sich der Wert der Sitzungsvariablen geändert hat, schlagen Abfragen in der materialisierten Ansicht fehl.

    Um dieses Problem zu vermeiden, erzwingen Sie einen Wert für den Ausdruck, der nicht von Sitzungsvariablen abhängt. Im folgenden Beispiel wird die Ausgabe unabhängig vom Parameter TIMESTAMP_TYPE_MAPPING in einen bestimmten Datentyp umgewandelt:

    create materialized view good_example (ts1) as
        select to_timestamp(n)::TIMESTAMP_NTZ from t1;
    

    Dieses Problem ist spezifisch für materialisierte Ansichten. Nicht materialisierte Ansichten generieren ihre Ausgabe dynamisch basierend auf den aktuellen Parametereinstellungen, sodass die Ergebnisse nicht veraltet sein können.

  • Die „Time Travel“-Funktion von Snowflake wird in materialisierten Ansichten nicht unterstützt.

Grundlegendes Beispiel: Erstellen einer materialisierten Ansicht

Dieser Abschnitt enthält ein grundlegendes Beispiel zum Erstellen und Verwenden einer materialisierten Ansicht:

CREATE OR REPLACE MATERIALIZED VIEW mv1 AS
  SELECT My_ResourceIntensive_Function(binary_col) FROM table1;

SELECT * FROM mv1;

Detailliertere Beispiele finden Sie unter Beispiele (unter diesem Thema).

Verwenden von materialisierten Ansichten

Nachdem Sie eine materialisierte Ansicht erstellt haben, verwaltet ein Hintergrundprozess automatisch die Daten in der materialisierten Ansicht. Beachten Sie Folgendes:

  • Die Wartung materialisierter Ansichten wird von einem Hintergrundprozess ausgeführt, wobei der Zeitpunkt vom Benutzer nicht vorhergesagt werden kann. Wenn die Wartung zurückbleibt, werden Abfragen möglicherweise langsamer ausgeführt als bei aktuellen Ansichten. Die Ergebnisse sind jedoch immer korrekt. Wenn Mikropartitionen der materialisierten Ansicht nicht mehr aktuell sind, überspringt Snowflake diese Partitionen und sucht die Daten in der Basistabelle.

  • Überprüfen Sie die Spalten REFRESHED_ON und BEHIND_BY in der Ausgabe des Befehls SHOW MATERIALIZED VIEWS, um zu ermitteln, wann eine materialisierte Ansicht zuletzt aktualisiert wurde.

  • Wenn Sie die Wartung einer Ansicht anhalten, sollten Sie die Ansicht erst wieder abfragen, nachdem die Wartung fortgesetzt wurde.

Der folgende Befehl und die folgende Ansicht stellen Informationen zu materialisierten Ansichten bereit:

  • Der Befehl SHOW VIEWS gibt Informationen über materialisierte und Standardansichten zurück.

  • INFORMATION_SCHEMA.TABLES zeigt materialisierte Ansichten an. In der Spalte TABLE_TYPE wird „MATERIALIZED VIEW“ angezeigt. Die Spalte IS_INSERTABLE lautet immer „NO“, da Sie keine direkten Einfügungen in eine materialisierte Ansicht vornehmen können.

    Bemerkung

    INFORMATION_SCHEMA.VIEWS zeigt keine materialisierte Ansichten an. Materialisierte Ansichten werden durch INFORMATION_SCHEMA.TABLES angezeigt.

Einschränkungen bei der Verwendung von materialisierten Ansichten

Bemerkung

Die hier genannten Einschränkungen gelten momentan. Einige davon werden in zukünftigen Versionen möglicherweise entfernt oder geändert.

Die folgenden Einschränkungen gelten für die Verwendung materialisierter Ansichten:

  • Um sicherzustellen, dass materialisierte Ansichten mit der Basistabelle, für die sie definiert sind, konsistent bleiben, können Sie die meisten DML-Operationen nicht auf der materialisierten Ansicht selbst ausführen. Sie können beispielsweise keine Zeilen direkt in eine materialisierte Ansicht einfügen (obwohl Sie natürlich Zeilen in die Basistabelle einfügen können). Folgende DML-Operationen sind nicht zulässig:

    • COPY

    • DELETE

    • INSERT

    • MERGE

    • UPDATE

    Das Abschneiden einer materialisierten Ansicht wird nicht unterstützt.

  • Sie können eine materialisierte Ansicht nicht direkt mit dem Befehl CREATE VIEW ... CLONE... klonen. Wenn Sie jedoch ein Schema oder eine Datenbank klonen, die eine materialisierte Ansicht enthält, wird die materialisierte Ansicht geklont und in das neue Schema oder die neue Datenbank aufgenommen.

  • Time Travel wird bei materialisierten Ansichten derzeit nicht unterstützt.

  • Materialisierte Ansichten werden von Snowflake Verwenden von Ressourcenmonitoren nicht überwacht.

Auswirkungen von Änderungen an Basistabellen auf materialisierte Ansichten

In Snowflake werden Änderungen an einer Basistabelle nicht automatisch in materialisierte Ansichten übertragen, die auf dieser Tabelle basieren.

  • Wenn der Basistabelle Spalten hinzugefügt werden, werden diese neuen Spalten nicht automatisch in die materialisierten Ansichten übernommen. Dies gilt auch dann, wenn die materialisierte Ansicht mit SELECT * definiert wurde (z. B. CREATE MATERIALIZED VIEW AS SELECT * FROM table2 ...). Die Spalten der materialisierten Ansicht werden zum Zeitpunkt der Definition der materialisierten Ansicht definiert. SELECT * wird beim Abfragen der materialisierten Ansicht nicht jedes Mal dynamisch interpretiert. Um Verwirrung zu vermeiden, empfiehlt Snowflake, SELECT * in der Definition einer materialisierten Ansicht nicht zu verwenden.

  • Wenn eine Basistabelle so geändert wird, dass vorhandene Spalten geändert oder gelöscht werden, werden alle materialisierten Ansichten dieser Basistabelle angehalten. Die materialisierten Ansichten können nicht verwendet oder gewartet werden. (Dies gilt auch dann, wenn die geänderte oder gelöschte Spalte nicht Teil der materialisierten Ansicht war.) Sie können diese materialisierte Ansicht nicht fortsetzen (RESUME). Wenn Sie sie erneut verwenden möchten, muss sie neu erstellt werden. Sie können sie zwar löschen, eine neue Ansicht erstellen und dann die Befehle GRANT und REVOKE ausführen, um die Berechtigungen für die Ansicht neu zu erstellen. Die effizienteste Methode zur Neuerstellung der Ansicht besteht jedoch meist aus dem Ersetzen in einem einzigen Befehl durch Ausführung von CREATE OR REPLACE VIEW <Ansichtsname> ... COPY GRANTS ....

  • ALTER TABLE ... ADD < Spalte> hält eine materialisierte Ansicht, die für diese Basistabelle erstellt wurde, nicht an.

  • Wenn eine Basistabelle gelöscht wird, wird die materialisierte Ansicht angehalten (aber nicht automatisch gelöscht). In den meisten Fällen muss die materialisierte Ansicht gelöscht werden. Wenn Sie aus irgendeinem Grund die Basistabelle neu erstellen und auch die materialisierte Ansicht mit derselben Definition wie zuvor neu erstellen möchten, erstellen Sie zuerst die Basistabelle neu, und ersetzen Sie dann die Ansicht mithilfe von CREATE OR REPLACE VIEW <Ansichtsname> ... COPY GRANTS ....

Materialisierte Ansichten in geklonten Schemas und Datenbanken

Wenn Sie ein Schema oder eine Datenbank klonen, die eine materialisierte Ansicht enthält, wird die materialisierte Ansicht geklont.

Wenn Sie die materialisierte Ansicht und die entsprechende Basistabelle gleichzeitig klonen (als Teil derselben CREATE SCHEMA ... CLONE- oder CREATE DATABASE ... CLONE-Operation), dann verweist die geklonte Basistabelle auf die geklonte materialisierte Ansicht.

Wenn Sie die materialisierte Ansicht klonen, ohne die Basistabelle zu klonen (z. B. wenn sich die Tabelle in Datenbank1.Schema1 und die Ansicht in Datenbank1.Schema2 befindet und Sie nur Schema2 und nicht die gesamte Datenbank1 klonen), verweist die geklonte Ansicht auf die ursprüngliche Basistabelle.

Wartungskosten für materialisierte Ansichten

Materialisierte Ansichten wirken sich auf Ihre Kosten für Speicher- und Computeressourcen aus:

  • Speicher: In jeder materialisierten Ansicht werden Abfrageergebnisse gespeichert, wodurch sich die monatliche Speichernutzung für Ihr Konto erhöht.

  • Computeressourcen: Um zu verhindern, dass materialisierte Ansichten veraltet werden, führt Snowflake eine automatische Hintergrundwartung der materialisierten Ansichten durch. Wenn sich eine Basistabelle ändert, werden alle auf der Tabelle definierten materialisierten Ansichten von einem Hintergrunddienst aktualisiert, der von Snowflake bereitgestellte Computeressourcen nutzt.

    Diese Aktualisierungen können erhebliche Ressourcen beanspruchen, was zu einer erhöhten Credit-Nutzung führt. Snowflake stellt jedoch eine effiziente Credit-Nutzung sicher, indem nur die tatsächlich verwendeten Ressourcen in Rechnung gestellt werden. Die Abrechnung erfolgt in Schritten von 1 Sekunde.

Abschätzen und Kontrollieren der Kosten

Es gibt keine Tools zum Schätzen der Wartungskosten materialisierter Ansichten. Im Allgemeinen sind die Kosten proportional zur:

  • Anzahl der materialisierten Ansichten, die für einzelne Basistabellen erstellt wurden, und zur Datenmenge, die sich in jeder der materialisierten Ansichten ändert, sobald sich die Basistabelle ändert. Alle Änderungen an Mikropartitionen in der Basistabelle erfordern möglicherweise eine Wartung der materialisierten Ansicht, unabhängig davon, ob diese Änderungen auf ein Reclustering oder auf DML-Anweisungen zurückzuführen sind, die auf der Basistabelle ausgeführt werden.

  • Anzahl der materialisierten Ansichten, die geclustert sind. Das Verwalten des Clustering (entweder einer Tabelle oder einer materialisierten Ansicht) erhöht die Kosten.

    Wenn eine materialisierte Ansicht anders als die Basistabelle geclustert ist, ist die Anzahl der in der materialisierten Ansicht geänderten Mikropartitionen möglicherweise wesentlich größer als die Anzahl der in der Basistabelle geänderten Mikropartitionen.

    Stellen Sie sich beispielsweise den Fall vor, in dem die Basistabelle durch Einfügen (Anhängen) von Daten weitgehend geändert und nicht geclustert wird, sodass die Basistabelle weitgehend in der Reihenfolge vorliegt, in der die Zeilen in die Tabelle eingefügt wurden. Stellen Sie sich dann vor, die materialisierte Ansicht wird durch eine unabhängige Spalte geclustert, z. B. Postleitzahl. Wenn der Basistabelle 100 neue Zeilen hinzugefügt werden, werden diese möglicherweise in eine oder zwei neue Mikropartitionen verschoben, wobei die anderen Mikropartitionen in der Basistabelle unberührt bleiben. Für diese 100 Zeilen müssen jedoch möglicherweise 100 Mikropartitionen in der geclusterten materialisierten Ansicht neu geschrieben werden.

    Betrachten Sie als weiteres Beispiel Löschvorgänge. Das Löschen der ältesten Zeilen in einer nicht geclusterten Basistabelle löscht möglicherweise nur die ältesten Mikropartitionen, erfordert jedoch möglicherweise Änderungen an einer weitaus größeren Anzahl von Mikropartitionen in einer materialisierten Ansicht, die nicht nach Alter geclustert ist.

    (Weitere Informationen zum Clustering materialisierter Ansichten finden Sie unter Materialisierte Ansichten und Clustering.)

Sie können die Kosten für die Wartung materialisierter Ansichten kontrollieren, indem Sie sorgfältig auswählen, wie viele Ansichten erstellt werden sollen, für welche Tabellen sie erstellt werden sollen und welche Definition für eine bestimmte Ansicht genutzt werden soll (inkl. Anzahl der Zeilen und Spalten in dieser Ansicht).

Sie können die Kosten auch kontrollieren, indem Sie die materialisierte Ansicht anhalten oder fortsetzen. Durch das Anhalten der Wartung werden die Kosten allerdings nur verschoben, nicht gesenkt. Je länger die Wartung zurückgestellt wurde, desto mehr Wartungsaufgaben müssen erledigt werden.

Siehe auch Best Practices zum Warten materialisierter Ansichten.

Tipp

Wenn Sie Bedenken hinsichtlich der mit der Wartung materialisierter Ansichten verbundenen Kosten haben, empfiehlt Snowflake, langsam mit der Verwendung dieser Funktion zu beginnen (d. h. zuerst nur einige materialisierte Ansichten für ausgewählte Tabellen zu erstellen) und die Kosten im Laufe der Zeit zu überwachen.

Anzeigen von Kosten

Sie können die Abrechnungskosten für die Wartung materialisierter Ansichten über die Weboberfläche oder mit SQL anzeigen:

Neue Weboberfläche

Klicken Sie als Kontoadministrator auf Account » Usage.

Klassische Weboberfläche

Klicken Sie als Kontoadministrator auf Account Account tab » Billing & Usage.

Die Credit-Kosten werden in einem von Snowflake bereitgestellten virtuellen Warehouse mit dem Namen Snowflake logo in blue (no text) MATERIALIZED_VIEW_MAINTENANCE erfasst.

SQL

Führen Sie eine Abfrage auf einer der folgenden Optionen durch:

Beispiel:

SELECT * FROM TABLE(INFORMATION_SCHEMA.MATERIALIZED_VIEW_REFRESH_HISTORY());

Bemerkung

Ressourcenmonitore bieten Kontrolle über die Nutzung von Credits für virtuelle Warehouses. Sie können sie jedoch nicht zur Kontrolle der Credit-Nutzung für die von Snowflake bereitgestellten Warehouses, einschließlich des Snowflake logo in blue (no text) MATERIALIZED_VIEW_MAINTENANCE-Warehouse, verwenden.

Materialisierte Ansichten und Clustering

Das Definieren von Gruppierungsschlüsseln in einer materialisierten Ansicht wird unterstützt und kann in vielen Situationen zu einer Leistungssteigerung führen. Es erhöht allerdings auch die Kosten.

Wenn Sie Clustering sowohl für die materialisierten Ansichten als auch für die Basistabelle, auf der die materialisierten Ansichten definiert sind, ausführen, können Sie die materialisierten Ansichten auf anderen Spalten als den Spalten clustern, die zum Clustern der Basistabelle verwendet werden.

In den meisten Fällen ist das Clustering einer Teilmenge der materialisierten Ansichten einer Tabelle kostengünstiger als das Clustern der Tabelle selbst. Wenn auf die Daten in der Basistabelle (fast) ausschließlich über die materialisierten Ansichten und (fast) nie direkt über die Basistabelle zugegriffen wird, entstehen durch das Clustering der Basistabelle Kosten, ohne dass sich daraus Vorteile erzielen lassen.

Wenn Sie erwägen, sowohl die Basistabelle als auch die materialisierten Ansichten zu clustern, empfiehlt Snowflake, zunächst nur die materialisierten Ansichten zu clustern und Leistung und Kosten vor und nach dem Hinzufügen von Clustering zur Basistabelle zu überwachen.

Wenn Sie planen, eine Tabelle zu erstellen, diese zu laden und eine oder mehrere geclusterte materialisierte Ansichten für die Tabelle zu erstellen, empfiehlt Snowflake, die materialisierten Ansichten als letztes zu erstellen (nachdem so viele Daten wie möglich geladen wurden). Dies kann Kosten für das anfängliche Laden von Daten sparen, da zusätzlicher Aufwand für die Aufrechterhaltung des Clustering der materialisierten Ansicht beim ersten Laden der materialisierten Ansicht vermieden wird.

Weitere Informationen zum Clustering finden Sie unter Grundlegendes zu Tabellenstrukturen in Snowflake und Automatic Clustering.

Weitere Informationen zu den Kosten für das Clustering materialisierter Ansichten finden Sie unter Wartungskosten für materialisierte Ansichten und Bewährte Methoden für materialisierte Ansichten.

Bewährte Methoden für materialisierte Ansichten

Bewährte Methoden zum Erstellen materialisierter Ansichten

  • Die meisten materialisierten Ansichten sollten eine oder beide der folgenden Aktionen erledigen:

    • Filtern von Daten. Sie können dies tun, indem Sie:

      • Zeilen filtern, z. B. durch das Definieren der materialisierten Ansicht, sodass nur die jüngsten Daten enthalten sind. In einigen Anwendungen sind die abnormalen Daten die besten zu speichernden Daten. Wenn Sie beispielsweise Druck in einer Gasleitung überwachen, um zu schätzen, wann Rohre ausfallen könnten, speichern Sie ggf. alle Druckdaten in der Basistabelle und speichern in der materialisierten Ansicht nur ungewöhnlich hohe Druckwerte. Ähnliches gilt bei der Überwachung des Datenverkehrs über das Netz: So werden in Ihrer Basistabelle möglicherweise alle Überwachungsdaten gespeichert, während in Ihrer materialisierten Ansicht ggf. nur ungewöhnliche und verdächtige Daten gespeichert werden (z. B. von IP-Adressen, die bekannt dafür sind, DOS (Denial-of-Service)-Angriffe auszulösen).

      • Spalten filtern (z. B. durch Auswählen bestimmter Spalten anstelle von „SELECT * …“). Das Definieren einer materialisierten Ansicht mit SELECT * ... ist normalerweise teuer. Außerdem kann es in der Zukunft zu Fehlern führen. Wenn der Basistabelle später Spalten hinzugefügt werden (z. B. ALTER TABLE ... ADD COLUMN ...) enthält die materialisierte Ansicht nicht automatisch die neuen Spalten.

    • Führen Sie ressourcenintensive Operationen durch, und speichern Sie die Ergebnisse, damit die ressourcenintensiven Operationen nicht so häufig ausgeführt werden müssen.

  • Sie können mehr als eine materialisierte Ansicht für dieselbe Basistabelle erstellen. Beispielsweise können Sie eine materialisierte Ansicht erstellen, die nur die neuesten Daten enthält, und eine andere materialisierte Ansicht, in der ungewöhnliche Daten gespeichert werden. Anschließend können Sie eine nicht materialisierte Ansicht erstellen, die die beiden Tabellen verknüpft und aktuelle Daten anzeigt, die mit ungewöhnlichen historischen Daten übereinstimmen. So können Sie Situationen wie einen gerade ausgeführten DOS (Denial-of-Service)-Angriff schneller erkennen.

    Snowflake empfiehlt die Verwendung materialisierter Ansichten für ungewöhnliche Daten nur in folgenden Fällen:

    • Die Basistabelle ist nicht geclustert, oder die Spalten, die die ungewöhnlichen Daten enthalten, sind nicht bereits Teil des Gruppierungsschlüssels der Basistabelle.

    • Die Daten sind so ungewöhnlich, dass sie leicht zu isolieren sind, aber nicht so ungewöhnlich, dass sie nur selten verwendet werden. (Wenn die Daten nur selten verwendet werden, überwiegen die Kosten für die Aufrechterhaltung der materialisierten Ansicht wahrscheinlich den Leistungsvorteil und die Kosteneinsparungen, wenn ein schneller Zugriff auf sie bei Verwendung möglich ist.)

Best Practices zum Warten materialisierter Ansichten

  • Snowflake empfiehlt für DML-Operationen auf der Basistabelle die Verwendung von Batches:

    • DELETE: Wenn in Tabellen Daten für den letzten Zeitraum (z. B. den letzten Tag, die letzte Woche oder den letzten Monat) gespeichert sind, werden die Änderungen an der Basistabelle in die materialisierte Ansicht übernommen, wenn Sie die Basistabelle durch Löschen alter Daten kürzen. Je nachdem, wie die Daten auf die Mikropartitionen verteilt sind, müssen Sie für Hintergrundaktualisierungen der materialisierten Ansichten mehr bezahlen. In einigen Fällen können Sie Kosten ggf. senken, indem Sie Löschoperationen seltener ausführen (z. B. täglich anstatt stundenweise oder stündlich anstatt alle 10 Minuten).

      Wenn Sie bestimmte Mengen alter Daten nicht aufbewahren müssen, sollten Sie experimentieren, um das beste Verhältnis zwischen Kosten und Funktionalität zu finden.

    • INSERT, UPDATE und MERGE: Durch Verwenden von Batches für diese DML-Anweisungen für die Basistabelle können die Kosten zur Verwaltung der materialisierten Ansichten gesenkt werden.

Bewährte Methoden zum Clustern materialisierter Ansichten und ihrer Basistabellen

  • Wenn Sie eine materialisierte Ansicht für eine Basistabelle erstellen und wenn auf die materialisierte Ansicht häufig zugegriffen wird, aber auf die Basistabelle selten, dann ist es meist effizienter, ein Clustering der Basistabelle zu vermeiden. Wenn Sie eine materialisierte Ansicht auf einer Clustertabelle erstellen, sollten Sie das Clustering auf der Basistabelle entfernen, da bei jeder Änderung der Clusterbildung der Basistabelle möglicherweise eine Aktualisierung der materialisierten Ansicht erforderlich ist, was die Wartungskosten der materialisierten Ansicht erhöht.

  • Das Clustering materialisierter Ansichten, insbesondere materialisierter Ansichten auf Basistabellen, die sich häufig ändern, erhöht die Kosten. Clustern Sie nicht mehr materialisierte Ansichten als nötig.

  • Fast alle Informationen zum Clustering von Tabellen gelten auch für das Clustering materialisierter Ansichten. Weitere Informationen zu Clustering-Tabellen finden Sie unter Strategien zur Auswahl von Gruppierungsschlüsseln.

Beispiele

Dieser Abschnitt enthält weitere Beispiele zum Erstellen und Verwenden materialisierter Ansichten. Ein einfaches einleitendes Beispiel finden Sie unter Grundlegendes Beispiel: Erstellen einer materialisierten Ansicht (unter diesem Thema).

Einfache materialisierte Ansicht

Dieses erste Beispiel veranschaulicht eine einfache materialisierte Ansicht und eine einfache Abfrage der Ansicht.

Erstellen Sie die Tabelle, laden Sie die Daten, und erstellen Sie die Ansicht:

CREATE TABLE inventory (product_ID INTEGER, wholesale_price FLOAT,
  description VARCHAR);
    
CREATE OR REPLACE MATERIALIZED VIEW mv1 AS
  SELECT product_ID, wholesale_price FROM inventory;

INSERT INTO inventory (product_ID, wholesale_price, description) VALUES 
    (1, 1.00, 'cog');

Wählen Sie Daten aus der Ansicht aus:

SELECT product_ID, wholesale_price FROM mv1;
+------------+-----------------+
| PRODUCT_ID | WHOLESALE_PRICE |
|------------+-----------------|
|          1 |               1 |
+------------+-----------------+

Verknüpfen einer materialisierten Ansicht

Sie können eine materialisierte Ansicht mit einer Tabelle oder einer anderen Ansicht verknüpfen. Dieses Beispiel baut auf dem vorherigen Beispiel auf. Hier wird zunächst eine weitere Tabelle, und anschließend wird eine nicht materialisierte Ansicht erstellt, in der die Gewinne durch Verknüpfung der materialisierten Ansicht mit einer Tabelle dargestellt werden:

CREATE TABLE sales (product_ID INTEGER, quantity INTEGER, price FLOAT);

INSERT INTO sales (product_ID, quantity, price) VALUES 
   (1,  1, 1.99);

CREATE or replace VIEW profits AS
  SELECT m.product_ID, SUM(IFNULL(s.quantity, 0)) AS quantity,
      SUM(IFNULL(quantity * (s.price - m.wholesale_price), 0)) AS profit
    FROM mv1 AS m LEFT OUTER JOIN sales AS s ON s.product_ID = m.product_ID
    GROUP BY m.product_ID;

Wählen Sie Daten aus der Ansicht aus:

SELECT * FROM profits;
+------------+----------+--------+
| PRODUCT_ID | QUANTITY | PROFIT |
|------------+----------+--------|
|          1 |        1 |   0.99 |
+------------+----------+--------+

Anhalten des Aktualisierens einer materialisierten Ansicht

Im folgenden Beispiel wird die Verwendung (und Wartung) der materialisierten Ansicht mv1 vorübergehend angehalten, und es wird gezeigt, dass Abfragen auf dieser Ansicht aufgrund des Anhaltens der materialisierte Ansicht eine Fehlermeldung generieren:

ALTER MATERIALIZED VIEW mv1 SUSPEND;
    
INSERT INTO inventory (product_ID, wholesale_price, description) VALUES 
    (2, 2.00, 'sprocket');

INSERT INTO sales (product_ID, quantity, price) VALUES 
   (2, 10, 2.99),
   (2,  1, 2.99);

Wählen Sie Daten aus der materialisierten Ansicht aus:

SELECT * FROM profits ORDER BY product_ID;

Ausgabe:

002037 (42601): SQL compilation error:
Failure during expansion of view 'PROFITS': SQL compilation error:
Failure during expansion of view 'MV1': SQL compilation error: Materialized View MV1 is invalid.

Fortsetzen:

ALTER MATERIALIZED VIEW mv1 RESUME;

Wählen Sie Daten aus der materialisierten Ansicht aus:

SELECT * FROM profits ORDER BY product_ID;
+------------+----------+--------+
| PRODUCT_ID | QUANTITY | PROFIT |
|------------+----------+--------|
|          1 |        1 |   0.99 |
|          2 |       11 |  10.89 |
+------------+----------+--------+

Clustern einer materialisierten Ansicht

In diesem Beispiel wird eine materialisierte Ansicht erstellt und später geclustert:

Mit diesen Anweisungen werden zwei Tabellen erstellt, die Informationen zu Segmenten einer Rohrleitung (z. B. für Erdgas) aufzeichnen.

Die Segmente, die in naher Zukunft am wahrscheinlichsten ausfallen, sind häufig die ältesten Segmente bzw. Segmente, die aus Materialien bestehen, die leicht korrodieren oder längere Zeit ungewöhnlich hohem Druck ausgesetzt waren. In diesem Beispiel werden also Alter, Druck und Material (Eisen, Kupfer, PVC-Kunststoff usw.) der einzelnen Rohre überwacht.

CREATE TABLE pipeline_segments (
    segment_ID BIGINT,
    material VARCHAR, -- e.g. copper, cast iron, PVC.
    installation_year DATE,  -- older pipes are more likely to be corroded.
    rated_pressure FLOAT  -- maximum recommended pressure at installation time.
    );
    
INSERT INTO pipeline_segments 
    (segment_ID, material, installation_year, rated_pressure)
  VALUES
    (1, 'PVC', '1994-01-01'::DATE, 60),
    (2, 'cast iron', '1950-01-01'::DATE, 120)
    ;

CREATE TABLE pipeline_pressures (
    segment_ID BIGINT,
    pressure_psi FLOAT,  -- pressure in Pounds per Square Inch
    measurement_timestamp TIMESTAMP
    );
INSERT INTO pipeline_pressures 
   (segment_ID, pressure_psi, measurement_timestamp) 
  VALUES
    (2, 10, '2018-09-01 00:01:00'),
    (2, 95, '2018-09-01 00:02:00')
    ;

Rohrsegmente werden nur selten ausgetauscht, und die ältesten Rohrsegmente sind jene, bei denen die Wahrscheinlichkeit eines Ausfalls am größten ist. Erstellen Sie also eine materialisierte Ansicht mit den ältesten Segmenten.

CREATE MATERIALIZED VIEW vulnerable_pipes 
  (segment_ID, installation_year, rated_pressure) 
  AS
    SELECT segment_ID, installation_year, rated_pressure
        FROM pipeline_segments 
        WHERE material = 'cast iron' AND installation_year < '1980'::DATE;

Sie können Clustering hinzufügen oder den Gruppierungsschlüssel ändern. So gruppieren Sie beispielsweise anhand von installation_year:

ALTER MATERIALIZED VIEW vulnerable_pipes CLUSTER BY (installation_year);

Neue Druckmessungen gehen regelmäßig ein (z. B. alle 10 Sekunden), daher wäre die Wartung einer materialisierten Ansicht für Druckmessungen teuer. Auch wenn eine hohe Leistung (schneller Abruf) der neuesten Druckdaten wichtig ist, wird die pipeline_pressures-Tabelle daher ohne materialisierte Ansicht gestartet.

Wenn die Performance zu gering ist, kann der Dateneigentümer eine materialisierte Ansicht erstellen, die nur aktuelle Druckdaten oder nur Daten zu ungewöhnlichen Hochdruckereignissen enthält.

Erstellen Sie eine (nicht materialisierte) Ansicht, die Informationen aus der materialisierten Ansicht und der Tabelle pipeline_pressures kombiniert:

CREATE VIEW high_risk AS
    SELECT seg.segment_ID, installation_year, measurement_timestamp::DATE AS measurement_date, 
         DATEDIFF('YEAR', installation_year::DATE, measurement_timestamp::DATE) AS age, 
         rated_pressure - age AS safe_pressure, pressure_psi AS actual_pressure
       FROM vulnerable_pipes AS seg INNER JOIN pipeline_pressures AS psi 
           ON psi.segment_ID = seg.segment_ID
       WHERE pressure_psi > safe_pressure
       ;

Listen Sie nun die Rohrleitungssegmente mit hohem Risiko auf:

SELECT * FROM high_risk;
+------------+-------------------+------------------+-----+---------------+-----------------+
| SEGMENT_ID | INSTALLATION_YEAR | MEASUREMENT_DATE | AGE | SAFE_PRESSURE | ACTUAL_PRESSURE |
|------------+-------------------+------------------+-----+---------------+-----------------|
|          2 | 1950-01-01        | 2018-09-01       |  68 |            52 |              95 |
+------------+-------------------+------------------+-----+---------------+-----------------+

Dies zeigt, dass das Rohrleitungssegment mit segment_id = 2, das aus einem korrodierenden Material besteht, alt ist. Dieses Segment war noch nie einem Druck ausgesetzt, der höher war als die maximale Druckstufe zum Zeitpunkt der Installation. Aufgrund der möglichen Korrosion ist der „sichere Grenzwert“ des Segments im Laufe der Zeit gesunken, und der höchste Druck, dem das Segment ausgesetzt war, war höher als der Druck, der für eine Leitung dieses Alters empfohlen wird.

Erstellen einer materialisierten Ansicht für freigegebene Daten

Sie können eine materialisierte Ansicht für freigegebene Daten erstellen.

Konto1:

create or replace table db1.schema1.table1(c1 int);
create or replace share sh1;
grant usage on database db1 to share sh1;
alter share sh1 add accounts = account2;
grant usage on schema db1.schema1 to share sh1;
grant select on table db1.schema1.table1 to share sh1;

Konto2:

create or replace database dbshared from share account1.sh1;
create or replace materialized view mv1 as select * from dbshared.schema1.table1 where c1 >= 50;

Bemerkung

Denken Sie daran, dass das Verwalten materialisierter Ansichten Credits verbraucht. Wenn Sie eine materialisierte Ansicht für die freigegebene Tabelle einer anderen Person erstellen, werden Ihnen Änderungen an dieser freigegebenen Tabelle in Rechnung gestellt, da Ihre materialisierte Ansicht beibehalten wird.

Freigeben einer materialisierten Ansicht

Sie können die Datenfreigabefunktion von Snowflake verwenden, um eine materialisierte Ansicht freizugeben.

Weitere Informationen zur Datenfreigabe finden Sie unter Sicheres Freigeben von Daten in Snowflake.

Bemerkung

Denken Sie daran, dass das Verwalten materialisierter Ansichten Credits verbraucht. Wenn eine andere Person eine materialisierte Ansicht für Ihre freigegebenen Daten erstellt, können Änderungen an Ihren freigegebenen Daten Gebühren für die Personen bedeuten, die materialisierte Ansichten für Ihre freigegebenen Daten erstellt haben. Je größer die Anzahl der materialisierten Ansichten für eine freigegebene Basistabelle ist, desto wichtiger ist es, diese Basistabelle effizient zu aktualisieren, um die Kosten für die Pflege materialisierter Ansichten zu minimieren.

Problembehandlung

Kompilierungsfehler: Failure during expansion of view '<Name>': SQL compilation error: Materialized View <Name> is invalid.

Ursache

In vielen Fällen wird dies durch eine Änderung der zugrunde liegenden Tabelle verursacht, auf der die materialisierte Ansicht basiert. Beispielsweise wird dieser Fehler zurückgegeben, wenn die Tabelle gelöscht wird oder die materialisierte Ansicht auf eine Tabellenspalte verweist, die jedoch gelöscht wurde.

Lösung

Wenn die Tabelle gelöscht wurde und nicht neu erstellt wird, sollten Sie die Ansicht wahrscheinlich löschen.

Wenn die Tabelle geändert wurde, aber immer noch vorhanden ist, können Sie die materialisierte Ansicht ggf. löschen und mithilfe der verbleibenden Spalten neu erstellen.

Der Befehl SHOW MATERIALIZED VIEWS zeigt materialisierte Ansichten an, die nicht aktualisiert werden

Ursache

Eine mögliche Ursache für das Fehlschlagen der Aktualisierung könnte sein, dass die SELECT-Anweisung in der Ansichtsdefinition fehlgeschlagen ist.

Wenn der Befehl SELECT bei der Aktualisierung fehlschlägt, schlägt auch die Aktualisierung fehl. Da die Aktualisierung jedoch im Hintergrund ausgeführt wird, wird dem Benutzer zum Zeitpunkt der Aktualisierung keine Fehlermeldung angezeigt.

Wenn einige oder alle Daten in der materialisierten Ansicht nicht mehr aktuell sind, ruft Snowflake aktuelle Daten aus der Basistabelle ab. Snowflake gibt keine Fehlermeldung darüber aus, dass die materialisierte Ansicht nicht aktualisiert wurde.

Daher wird weder bei der Aktualisierung noch bei nachfolgenden Abfragen unbedingt angezeigt, dass SELECT für die Ansicht fehlgeschlagen ist. Um zu ermitteln, ob Aktualisierungen fehlschlagen, verwenden Sie den Befehl SHOW MATERIALIZED VIEWS, und suchen Sie nach der Spalte mit dem Namen refreshed_on. Wenn die Daten nicht aktualisiert werden, schlägt SELECT möglicherweise fehl.

Lösung

Stellen Sie sicher, dass die zugrunde liegende Tabelle vorhanden ist. Wenn Sie die Tabelle löschen, für die die Ansicht definiert ist, aber die Ansicht selbst nicht löschen, bleibt die Ansicht weiterhin bestehen.

In einigen Fällen können Sie das Problem möglicherweise beheben, indem Sie die SELECT-Anweisung in der Definition der materialisierten Ansicht manuell ausführen oder indem Sie eine einfachere (kostengünstigere) SELECT-Anweisung für die Tabelle ausführen, auf die in der Definition der materialisierten Ansicht verwiesen wird.

Wenn Sie die genaue Definition der materialisierten Ansicht nicht kennen, können Sie diese über die Ausgabe von SHOW MATERIALIZED VIEWS oder mit der Funktion GET_DDL ermitteln.