Übersicht zu Ansichten

Unter diesem Thema werden Konzepte zur Verwendung von Ansichten beschrieben.

Unter diesem Thema:

Was ist eine Ansicht?

In einer Ansicht kann auf das Ergebnis einer Abfrage so zugegriffen werden, als wäre es eine Tabelle. Die Abfrage wird in der Anweisung CREATE VIEW angegeben.

Ansichten dienen einer Vielzahl von Zwecken, darunter dem Kombinieren, Segregieren und Schützen von Daten. Sie können beispielsweise separate Ansichten erstellen, die den Anforderungen verschiedener Typen von Mitarbeitern entsprechen wie Ärzten und Buchhaltern in einem Krankenhaus:

CREATE TABLE hospital_table (patient_id INTEGER,
                             patient_name VARCHAR, 
                             billing_address VARCHAR,
                             diagnosis VARCHAR, 
                             treatment VARCHAR,
                             cost NUMBER(10,2));
INSERT INTO hospital_table 
        (patient_ID, patient_name, billing_address, diagnosis, treatment, cost) 
    VALUES
        (1, 'Mark Knopfler', '1982 Telegraph Road', 'Industrial Disease', 
            'a week of peace and quiet', 2000.00),
        (2, 'Guido van Rossum', '37 Florida St.', 'python bite', 'anti-venom', 
            70000.00)
        ;
CREATE VIEW doctor_view AS
    SELECT patient_ID, patient_name, diagnosis, treatment FROM hospital_table;

CREATE VIEW accountant_view AS
    SELECT patient_ID, patient_name, billing_address, cost FROM hospital_table;

Eine Ansicht kann fast überall verwendet werden, wo eine Tabelle verwendet werden kann (in Verknüpfungen, Unterabfragen usw.): Verwenden Sie beispielsweise die oben erstellten Ansichten:

  • Anzeigen aller Arten von medizinischen Problemen, die Patienten hatten:

    SELECT DISTINCT diagnosis FROM doctor_view;
    +--------------------+
    | DIAGNOSIS          |
    |--------------------|
    | Industrial Disease |
    | python bite        |
    +--------------------+
    
  • Anzeigen der Kosten für jede Behandlung (ohne Anzeige von Informationen, mit denen sich Patienten identifizieren lassen):

    SELECT treatment, cost 
        FROM doctor_view AS dv, accountant_view AS av
        WHERE av.patient_ID = dv.patient_ID;
    +---------------------------+----------+
    | TREATMENT                 |     COST |
    |---------------------------+----------|
    | a week of peace and quiet |  2000.00 |
    | anti-venom                | 70000.00 |
    +---------------------------+----------+
    

Ein CREATE VIEW-Befehl kann einen vollqualifizierten, teilweise qualifizierten oder nicht qualifizierten Tabellennamen verwenden. Beispiel:

CREATE VIEW v1 AS SELECT ... FROM my_database.my_schema.my_table;

CREATE VIEW v1 AS SELECT ... FROM my_schema.my_table;

CREATE VIEW v1 AS SELECT ... FROM my_table;

Wenn das Schema nicht angegeben ist, geht Snowflake davon aus, dass sich die Tabelle in demselben Schema wie die Ansicht befindet. (Wenn angenommen wird, dass sich die Tabelle im aktiven Schema befindet, kann die Ansicht zu unterschiedlichen Zeiten auf verschiedene Tabellen verweisen.)

Typen von Ansichten

Snowflake unterstützt zwei Arten von Ansichten:

  • Nicht materialisierte Ansichten (normalerweise einfach als „Ansichten“ bezeichnet).

  • Materialisierte Ansichten.

Nicht materialisierte Ansichten

Der Begriff „Ansicht“ bezieht sich allgemein auf alle Arten von Ansichten. Der Begriff wird hier jedoch verwendet, um sich spezifisch auf nicht materialisierte Ansichten zu beziehen.

Eine Ansicht ist im Wesentlichen eine benannte Definition einer Abfrage. Die Ergebnisse einer nicht materialisierten Ansicht werden erstellt, indem die Abfrage zu dem Zeitpunkt ausgeführt wird, zu dem die Ansicht in einer Abfrage referenziert wird. Die Ergebnisse werden nicht für die zukünftige Verwendung gespeichert. Die Leistung ist langsamer als bei materialisierten Ansichten. Nicht materialisierte Ansichten sind der häufigste Ansichtstyp.

Jeder Abfrageausdruck, der ein gültiges Ergebnis zurückgibt, kann zum Erstellen einer materialisierten Ansicht verwendet werden, wie z. B.:

  • Auswählen einiger (oder aller) Spalten in einer Tabelle

  • Auswählen eines bestimmten Datenbereichs in Tabellenspalten

  • Verknüpfen (Join) von Daten aus zwei oder mehr Tabellen

Materialisierte Ansichten

Obwohl eine materialisierte Ansicht als Ansicht bezeichnet wird, verhält sie sich in vielerlei Hinsicht eher wie eine Tabelle. Die Ergebnisse einer materialisierten Ansicht werden fast so gespeichert, als wären sie eine Tabelle. Dies ermöglicht schnelleren Zugriff, erfordert jedoch Speicherplatz und aktive Wartung, wobei jeweils Kosten in Form von Credits anfallen.

Außerdem unterliegen materialisierte Ansichten Einschränkungen, die für nicht materialisierte Ansichten nicht gelten.

Weitere Details dazu finden Sie unter Verwenden von materialisierten Ansichten.

Sichere Ansichten

Sowohl nicht materialisierte als auch materialisierte Ansichten können als sicher definiert werden. Sichere Ansichten bieten Vorteile gegenüber Standardansichten, einschließlich verbessertem Datenschutz und Datenaustausch. Allerdings müssen auch einige Leistungseinbußen berücksichtigt werden.

Weitere Details dazu finden Sie unter Verwenden von sicheren Ansichten.

Rekursive Ansichten (nur nicht materialisierte Ansichten)

Eine nicht materialisierte Ansicht kann rekursiv sein (d. h. die Ansicht kann auf sich selbst verweisen).

Die Verwendung der Rekursion in Ansichten ähnelt der Verwendung der Rekursion in rekursiven CTEs. Tatsächlich kann eine Ansicht mit einem rekursiven CTE definiert werden. Beispiel:

CREATE VIEW employee_hierarchy (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE") AS (
   WITH RECURSIVE employee_hierarchy_cte (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE") AS (
      -- Start at the top of the hierarchy ...
      SELECT title, employee_ID, manager_ID, NULL AS "MGR_EMP_ID (SHOULD BE SAME)", 'President' AS "MGR TITLE"
        FROM employees
        WHERE title = 'President'
      UNION ALL
      -- ... and work our way down one level at a time.
      SELECT employees.title, 
             employees.employee_ID, 
             employees.manager_ID, 
             employee_hierarchy_cte.employee_id AS "MGR_EMP_ID (SHOULD BE SAME)", 
             employee_hierarchy_cte.title AS "MGR TITLE"
        FROM employees INNER JOIN employee_hierarchy_cte
       WHERE employee_hierarchy_cte.employee_ID = employees.manager_ID
   )
   SELECT * 
      FROM employee_hierarchy_cte
);

Anstatt einen rekursiven CTE zu verwenden, können Sie eine rekursive Ansicht mit dem Schlüsselwort RECURSIVE erstellen. Beispiel:

CREATE RECURSIVE VIEW employee_hierarchy_02 (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE") AS (
      -- Start at the top of the hierarchy ...
      SELECT title, employee_ID, manager_ID, NULL AS "MGR_EMP_ID (SHOULD BE SAME)", 'President' AS "MGR TITLE"
        FROM employees
        WHERE title = 'President'
      UNION ALL
      -- ... and work our way down one level at a time.
      SELECT employees.title, 
             employees.employee_ID, 
             employees.manager_ID, 
             employee_hierarchy_02.employee_id AS "MGR_EMP_ID (SHOULD BE SAME)", 
             employee_hierarchy_02.title AS "MGR TITLE"
        FROM employees INNER JOIN employee_hierarchy_02
        WHERE employee_hierarchy_02.employee_ID = employees.manager_ID
);

Weitere Details einschließlich Beispielen finden Sie unter CREATE VIEW.

Vorteile von Ansichten

Ansichten ermöglichen Schreiben von mehr modularem Code

Ansichten helfen Ihnen, klareren SQL-Code mit höherer Modularität zu schreiben. Angenommen, Ihre Krankenhausdatenbank enthält eine Tabelle mit Informationen zu allen Mitarbeitern. Sie können Ansichten erstellen, um das Erfassen von Informationen nur über das medizinische Personal oder nur über das Wartungspersonal zu vereinfachen. Sie können sogar Hierarchien von Ansichten erstellen.

Sie können beispielsweise eine Ansicht für die Ärzte und eine für das Pflegepersonal erstellen. Anschließend können Sie die Ansicht medical_staff erstellen, indem auf die Ärzte-Ansicht und die Pflegepersonal-Ansicht verwiesen wird:

CREATE TABLE employees (id INTEGER, title VARCHAR);
INSERT INTO employees (id, title) VALUES
    (1, 'doctor'),
    (2, 'nurse'),
    (3, 'janitor')
    ;

CREATE VIEW doctors as SELECT * FROM employees WHERE title = 'doctor';
CREATE VIEW nurses as SELECT * FROM employees WHERE title = 'nurse';
CREATE VIEW medical_staff AS
    SELECT * FROM doctors
    UNION
    SELECT * FROM nurses
    ;
SELECT * 
    FROM medical_staff
    ORDER BY id;
+----+--------+
| ID | TITLE  |
|----+--------|
|  1 | doctor |
|  2 | nurse  |
+----+--------+

Oft können Sie statt einer großen und schwer verständlichen Abfrage eine Abfrage in kleinere Teilabfragen zerlegen und für jede diese Teilabfragen eine Ansicht erstellen. Dadurch wird nicht nur der Code leichter verständlich, sondern in vielen Fällen auch das Debuggen vereinfacht, da Sie nur eine Ansicht auf einmal debuggen müssen und nicht die gesamte Abfrage.

Eine Ansicht kann von vielen verschiedenen Abfragen referenziert werden, sodass Ansichten dazu beitragen, die Wiederverwendung von Code zu verbessern.

Ansichten erlauben das Gewähren von Zugriff auf eine Teilmenge einer Tabelle

Mit Ansichten können Sie Zugriff auf nur einen Teil der Daten in Tabellen gewähren. Angenommen, Sie verfügen über eine Tabelle mit medizinischen Patientendaten. Das medizinische Personal soll Zugang zu allen medizinischen Informationen (z. B. Diagnosen) haben, nicht jedoch zu den Finanzdaten (z. B. Kreditkartennummer des Patienten). Das Buchhaltungspersonal soll Zugriff auf die abrechnungsrelevanten Daten haben, z. B. auf die Kosten für einzelne Verschreibungen, die der Patient erhalten hat; es soll jedoch keinen Zugriff auf die vertraulichen medizinischen Daten erhalten (wie Diagnosen des psychischen Gesundheit). Sie können zwei separate Ansichten erstellen (eine für das medizinische Personal und eine andere für das Buchhaltungspersonal), sodass jeder dieser Rollen nur die Informationen angezeigt werden, die zur Erledigung ihrer Aufgaben erforderlich sind. Ansichten erlauben dies, weil Sie einer bestimmten Rolle Berechtigungen für eine bestimmte Ansicht erteilen können, ohne dass die Berechtigungsempfänger-Rolle über Berechtigungen für die der Ansicht zugrunde liegenden Tabellen verfügt.

Im medizinischen Beispiel:

  • Das medizinische Personal hätte keine Berechtigungen für die Datentabellen, aber Berechtigungen für die Ansicht, die Informationen zu Diagnose und Behandlung enthält.

  • Das Buchhaltungspersonal hätte keine Berechtigungen für die Datentabellen, aber Berechtigungen für die Ansicht mit den Rechnungsdaten.

Für zusätzliche Sicherheit unterstützt Snowflake das Definieren einer Ansicht als sicher. Weitere Informationen zu sicheren Ansichten finden Sie unter Verwenden von sicheren Ansichten.

Ansichten können die Leistung verbessern

In einigen Situationen können Ansichten die Leistung verbessern.

Nicht materialisierte Ansichten

Wenn in einer Abfrage eine nicht materialisierte Ansicht referenziert wird, verwendet der Snowflake-Abfrageoptimierer interne Optimierungsmethoden, um die Ansicht und Abfrage zusammen zu verarbeiten, sodass sich die Ansicht zum Teil effizienter auswerten lässt, als wenn sie separat ausgewertet werden würde.

Materialisierte Ansichten

Materialisierte Ansichten dienen dazu, die Leistung zu verbessern. Materialisierte Ansichten enthalten eine Kopie einer Teilmenge der Daten in einer Tabelle. Abhängig von der Datenmenge in der Tabelle und in der materialisierten Ansicht kann das Scannen der materialisierten Ansicht deutlich schneller ausfallen als das Scannen der Tabelle. Materialisierte Ansichten unterstützen auch Clustering. So können Sie verschiedene materialisierte Ansichten für dieselben Daten erstellen, wobei jede materialisierte Ansicht anhand einer anderen Spalte geclustert wird. Das bedeutet, dass für jede Ansicht unterschiedliche Abfragen mit optimalen Clustering für die jeweilige Abfrage ausgeführt werden können.

Einschränkungen bei Ansichten

  • Die Definition einer Ansicht kann nicht aktualisiert werden (d. h. ALTER VIEW und ALTER MATERIALIZED VIEW können nicht verwendet werden, um die Definition einer Ansicht zu ändern). Stattdessen müssen Sie zum Ändern der Definition einer Ansicht, die Ansicht mit der neuen Definition neu erstellen.

  • Änderungen an einer Tabelle werden nicht automatisch an Ansichten weitergegeben, die für diese Tabelle erstellt wurden. Wenn Sie beispielsweise eine Spalte in einer Tabelle löschen, werden die Ansichten dieser Tabelle möglicherweise ungültig.

  • Ansichten sind schreibgeschützt (d. h. Sie können DML-Befehle nicht direkt auf einer Ansicht ausführen). Sie können jedoch eine Ansicht in einer Unterabfrage innerhalb einer DML-Anweisung verwenden, die die zugrunde liegende Basistabelle aktualisiert. Beispiel:

    DELETE FROM hospital_table 
        WHERE cost > (SELECT AVG(cost) FROM accountant_view);