Verwenden von materialisierten Ansichten¶

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.

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:

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.

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.

DDL-Befehle für materialisierte Ansichten¶

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

• CREATE MATERIALIZED VIEW

• ALTER MATERIALIZED VIEW

• DROP MATERIALIZED VIEW

• DESCRIBE MATERIALIZED VIEW

• SHOW MATERIALIZED VIEWS

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:

• GRANT <Berechtigungen>

• REVOKE <Berechtigungen>

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

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.

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 vom Feature 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 die Ausdrücke enthalten, deren Auswertung sehr teuer ist (z. B. teure Funktionsaufrufe oder teure Operationen auf semistrukturierten 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 das Feature 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).

Beim Erstellen einer materialisierten Ansicht müssen Sie Folgendes beachten:

• Behandeln von Spaltennamen, die in materialisierten Ansichten nicht zulässig sind

• Verweisen auf die Basistabelle

• Festlegen von Filtern für die Optimierung von Abfragen

CREATE OR REPLACE MATERIALIZED VIEW mv AS
  SELECT SYSTEM$ALPHA AS col1, ...

Einschränkungen beim Erstellen materialisierter Ansichten¶

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

  • Die Mengenoperatoren MINUS, EXCEPT und INTERSECT.

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

  • Folgende Aggregationsfunktionen werden in materialisierten Ansichten unterstützt:

    • APPROX_COUNT_DISTINCT (HLL).

    • AVG (außer bei Verwendung in PIVOT).

    • BITAND_AGG

    • BITOR_AGG.

    • BITXOR_AGG.

    • COUNT.

    • COUNT_IF.

    • MAX.

    • MIN.

    • STDDEV, STDDEV_SAMP.

    • STDDEV_POP.

    • SUM.

    • VARIANCE (VARIANCE_SAMP, VAR_SAMP).

    • VARIANCE_POP (VAR_POP).

    Die anderen Aggregationsfunktionen werden in materialisierten Ansichten nicht unterstützt.

  Bemerkung

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

  • Aggregationsfunktionen können nicht verschachtelt werden.

  • In komplexen Ausdrücken verwendete Aggregationsfunktionen (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;
    

    Copy

    Folgender Befehl ist beispielsweise nicht zulässig:

    create materialized view mv2 as
        select
            y + 10
          from (
            select
              sum(x) as y
            from t
          );
    

    Copy

  • DISTINCT kann nicht mit Aggregationsfunktionen kombiniert werden.

  • In einer materialisierten Ansicht können AVG, COUNT, COUNT_IF, MIN, MAX und SUM als Aggregationsfunktionen, 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> ] )
    

    Copy

  • Wenn sich eine Aggregationsfunktion 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);
    

    Copy

    Der Ausdruck „c1 + 10“ ist ein Ausdruck über einer Aggregationsfunktion 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 Aggregationsfunktionen 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);
    

    Copy

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

  Copy

  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;
  

  Copy

  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.

• Bei der Definition einer materialisierten Ansicht wird das Auswählen der Spalte SEQ aus der Ausgabe der Funktion FLATTEN nicht unterstützt.

  Für die Werte in der Spalte SEQ ist nicht garantiert, dass sie in irgendeiner Weise sortiert sind, wenn sie aus einer materialisierten Ansicht ausgewählt werden. Wenn Sie diese Spalte in der Definition der materialisierten Ansicht auswählen, kann die Ausgabe indeterministisch sein.

• Materialisierte Ansichten können nicht mit dem Time Travel-Feature erstellt werden.

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;

Copy

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

Erläuterungen zum Verwalten 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 optimale Zeitpunkt anhand des Workloads auf Basistabelle und materialisierter Ansicht ermittelt wird.

  • Dieser Prozess aktualisiert die materialisierte Ansicht mit Änderungen, die durch DML-Operationen auf der Basistabelle vorgenommen wurden (Einfügungen, Aktualisierungen und Löschungen).

    Darüber hinaus kann Clustering auf der Basistabelle auch zu Aktualisierungen einer materialisierten Ansicht führen. Weitere Informationen dazu finden Sie unter Bewährte Methoden zum Clustern materialisierter Ansichten und ihrer Basistabellen.

  • Wenn Zeilen in die Basistabelle eingefügt werden, führt der Prozess eine Aktualisierungsoperation aus, um die neuen Zeilen in die materialisierte Ansicht einzufügen.

  • Wenn Zeilen in der Basistabelle gelöscht werden, führt der Prozess eine Komprimierungsoperation auf der materialisierten Ansicht aus und löscht dabei diese Zeilen aus der materialisierten Ansicht.

• Um zu ermitteln, wann eine materialisierte Ansicht das letzte Mal aktualisiert wurde, führen Sie den Befehl SHOW MATERIALIZED VIEWS aus.

  Überprüfen Sie die Spalten REFRESHED_ON und BEHIND_BY in der Ausgabe:

  • Die Spalten REFRESHED_ON und COMPACTED_ON zeigen den Zeitstempel der letzten DML-Operation auf der Basistabelle an, die von den Aktualisierungs- bzw. Komprimierungsoperationen verarbeitet wurde.

  • Die Spalte BEHIND_BY gibt an, wie viel Zeit die Aktualisierungen der materialisierten Ansicht hinter den Aktualisierungen der Basistabelle zurückbleiben.

• Wenn die Wartung zurückbleibt, werden Abfragen möglicherweise langsamer ausgeführt als bei aktuellen Ansichten, aber die Ergebnisse sind immer aktuell.

  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.

• Wenn der Hintergrundprozess auf bestimmte Benutzerfehler stößt (z. B. wenn die Abfrage der Ansicht zu einem „Division durch Null“-Fehler führt), macht der Prozess die materialisierte Ansicht ungültig.

  Die Abfrage einer ungültigen materialisierten Ansicht führt zu einem Fehler. Die Fehlermeldung enthält den Grund, warum die materialisierte Ansicht für ungültig erklärt wurde. Beispiel:

  Failure during expansion of view 'MY_MV':
    SQL compilation error: Materialized View MY_MV is invalid.
    Invalidation reason: Division by zero
  

  Wenn dies der Fall ist, beheben Sie das in der Fehlermeldung beschriebene Problem (löschen Sie z. B. die Zeilen, die den „Division durch Null“-Fehler verursachen), und setzen Sie die materialisierte Ansicht mit dem Befehl ALTER MATERIALIZED VIEW … RESUME fort.

Anhalten und Fortsetzen der Wartung einer materialisierten Ansicht¶

Wenn Sie Wartung und Verwendung einer materialisierten Ansicht anhalten müssen, führen Sie den Befehl ALTER MATERIALIZED VIEW mit dem Parameter SUSPEND aus:

ALTER MATERIALIZED VIEW <name> SUSPEND

Wenn Sie die Wartung einer Ansicht anhalten, können Sie die Ansicht erst wieder abfragen, nachdem Sie die Wartung fortgesetzt haben.

Um die Wartung und Verwendung einer materialisierten Ansicht fortzusetzen, führen Sie den Befehl ALTER MATERIALIZED VIEW mit dem Parameter RESUME aus:

ALTER MATERIALIZED VIEW <name> RESUME

Ein Beispiel dazu finden Sie unter Anhalten des Aktualisierens einer materialisierten Ansicht.

Anzeigen von Informationen zu materialisierten Ansichten¶

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

• Der Befehl SHOW VIEWS gibt Informationen zu materialisierten Ansichten und Standardansichten zurück.

• Die Ansicht 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

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

Einschränkungen bei der Verwendung von materialisierten Ansichten¶

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

• Snowflake unterstützt nicht die Verwendung des Time Travel-Features zur Abfrage von materialisierten Ansichten zu einem Zeitpunkt in der Vergangenheit (z. B. Verwendung der AT-Klausel beim Abfragen einer materialisierten Ansicht).

  Sie können jedoch Time Travel verwenden, um eine Datenbank oder ein Schema, das eine materialisierte Ansicht enthält, zu einem Zeitpunkt in der Vergangenheit zu klonen. Weitere Details dazu finden Sie unter Materialisierte Ansichten und Time Travel.

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

Auswirkungen von Änderungen an Basistabellen auf materialisierte Ansichten¶

In den folgenden Abschnitten wird erläutert, wie materialisierte Ansichten von Änderungen an den Basistabellen beeinflusst werden.

• Hinzufügen von Spalten zur Basistabelle

• Ändern oder Löschen von Spalten in der Basistabelle

• Umbenennen oder Austauschen der Basistabelle

• Löschen der Basistabelle

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.

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.

Anzeigen von Kosten¶

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

Snowsight:

Wählen Sie als Benutzer mit den erforderlichen Berechtigungen Admin » Usage aus.

Classic Console:

Wählen Sie als Kontoadministrator Account » Billing & Usage aus.

Die Credit-Kosten werden in einem von Snowflake bereitgestellten virtuellen Warehouse mit dem Namen MATERIALIZED_VIEW_MAINTENANCE erfasst.

SQL:

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

• Tabellenfunktion MATERIALIZED_VIEW_REFRESH_HISTORY (in Snowflake Information Schema).

  Beispiel:

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

  Copy

• Ansicht MATERIALIZED_VIEW_REFRESH_HISTORY-Ansicht (in Account Usage).

  Die folgenden Abfragen können auf der Ansicht MATERIALIZED_VIEW_REFRESH_HISTORY ausgeführt werden:

  Abfrage: Kostenverlauf für materialisierte Ansichten (nach Tag, nach Objekt)

  Diese Abfrage liefert eine umfassende Liste der materialisierten Ansichten und das Volumen der über den Dienst in den letzten 30 Tagen verbrauchten Credits, aufgeschlüsselt nach Tagen. Unregelmäßigkeiten beim Credit-Verbrauch oder ein gleichbleibend hoher Verbrauch sind Anzeichen, dass weitere Untersuchungen erforderlich sind.

  SELECT TO_DATE(start_time) AS date,
    database_name,
    schema_name,
    table_name,
    SUM(credits_used) AS credits_used
  FROM snowflake.account_usage.materialized_view_refresh_history
  WHERE start_time >= DATEADD(month,-1,CURRENT_TIMESTAMP())
  GROUP BY 1,2,3,4
  ORDER BY 5 DESC;
  

  Copy

  Abfrage: Historie von materialisierten Ansichten und Tagesdurchschnitt über m Tage

  Diese Abfrage gibt den durchschnittlichen täglichen Credit-Verbrauch durch materialisierte Ansichten für das letzte Jahr, gruppiert nach Woche zurück. Sie kann dazu beitragen, Anomalien in den Tagesdurchschnittswerten über das Jahr hinweg zu erkennen, sodass Sie Verbrauchsspitzen oder unerwarteten Nutzungsänderungen untersuchen können.

  WITH credits_by_day AS (
    SELECT TO_DATE(start_time) AS date,
      SUM(credits_used) AS credits_used
    FROM snowflake.account_usage.materialized_view_refresh_history
    WHERE start_time >= DATEADD(year,-1,CURRENT_TIMESTAMP())
    GROUP BY 1
    ORDER BY 2 DESC
  )
  
  SELECT DATE_TRUNC('week',date),
    AVG(credits_used) AS avg_daily_credits
  FROM credits_by_day
  GROUP BY 1
  ORDER BY 1;
  

  Copy

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.

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

Copy

Wählen Sie Daten aus der Ansicht aus:

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

Copy

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;

Copy

Wählen Sie Daten aus der Ansicht aus:

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

Copy

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

Copy

Wählen Sie Daten aus der materialisierten Ansicht aus:

SELECT * FROM profits ORDER BY product_ID;

Copy

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.

Copy

Fortsetzen:

ALTER MATERIALIZED VIEW mv1 RESUME;

Copy

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

Copy

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

Copy

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;

Copy

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

Copy

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
       ;

Copy

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

Copy

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;

Freigeben einer materialisierten Ansicht¶

Sie können das Data Sharing-Feature von Snowflake verwenden, um eine materialisierte Ansicht freizugeben.

Weitere Informationen zur Datenfreigabe finden Sie unter Übersicht zur Datenfreigabe in Snowflake.

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

Mögliche Ursachen:

• Die materialisierte Ansicht wurde angehalten. Weitere Informationen zum Anhalten und Fortsetzen von Ansichten finden Sie unter ALTER MATERIALIZED VIEW.

• Eine Änderung an der Basistabelle der materialisierten Ansicht hat die materialisierte Ansicht ungültig gemacht. Der folgende Fehler wird zum Beispiel zurückgegeben, wenn:

  • Die Basistabelle wurde gelöscht.

  • Eine Spalte in der Basistabelle wurde gelöscht.

• Der Hintergrundprozess ist auf einen Fehler eines bestimmten Typs gestoßen (z. B. ein „Division durch Null“-Fehler) und konnte die materialisierte Ansicht nicht aktualisieren.

Mögliche Lösungen:

• Wenn die Ansicht angehalten wurde:

  • Setzen Sie die Ansicht fort, indem Sie ALTER MATERIALIZED VIEW … RESUME ausführen.

  • Führen Sie die Abfrage auf der Basistabelle aus. Dies wird jedoch wahrscheinlich mehr Credits verbrauchen und länger dauern als die Ausführung der Abfrage auf der materialisierten Ansicht.

• Wenn die Basistabelle geändert oder gelöscht wurde:

  • Wenn die Basistabelle gelöscht wurde, dann löschen Sie die materialisierte Ansicht.

  • Wenn die Basistabelle geändert wurde (z. B. wenn eine Spalte, auf die die Ansicht verweist, gelöscht wurde), und wenn die materialisierte Ansicht auch mit der neuen Version der Tabelle immer noch nützlich wäre, dann sollten Sie erwägen, die materialisierte Ansicht zu löschen und neu mit den Spalten zu erstellen, die in der Basistabelle verblieben sind.

  • Wenn keine andere Ursache für die Fehlermeldung ersichtlich ist, erwägen Sie, die materialisierte Ansicht zu löschen und neu zu erstellen.

  • Führen Sie die Abfrage auf der Basistabelle aus. Dies wird jedoch wahrscheinlich mehr Credits verbrauchen und länger dauern als die Ausführung der Abfrage auf der materialisierten Ansicht.

• Wenn der Hintergrundprozess die materialisierte Ansicht aufgrund eines Fehlers nicht aktualisieren konnte, sollte die Fehlermeldung Details darüber enthalten, warum die materialisierte Ansicht ungültig geworden ist. Beispiel:

  Failure during expansion of view 'MY_MV':
    SQL compilation error: Materialized View MY_MV is invalid.
    Invalidation reason: Division by zero
  

Wenn dies der Fall ist, beheben Sie das in der Fehlermeldung beschriebene Problem, und setzen Sie dann die materialisierte Ansicht mit dem Befehl ALTER MATERIALIZED VIEW … RESUME fort.

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

Mögliche Ursache:

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

Da die Aktualisierung jedoch im Hintergrund ausgeführt wird, wird dem Benutzer zum Zeitpunkt der Aktualisierung keine Fehlermeldung angezeigt. Stattdessen wird die Fehlermeldung angezeigt, wenn Sie die materialisierte Ansicht abfragen oder wenn Sie SHOW MATERIALIZED VIEWS ausführen.

Mögliche Lösung:

Wenn die Spalte invalid den Wert true hat, suchen Sie in der Spalte invalid_reason nach dem Grund, warum die Ansicht ungültig gemacht wurde.

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