Kategorien:

DDL für Tabellen, Ansichten und Sequenzen

CREATE VIEW

Erstellt eine neue Ansicht im aktuellen/angegebenen Schema auf Basis einer Abfrage auf einer oder mehreren bestehenden Tabellen (oder eines anderen gültigen Abfrageausdrucks).

Siehe auch:

ALTER VIEW, DROP VIEW, SHOW VIEWS, DESCRIBE VIEW

Unter diesem Thema:

Syntax

CREATE [ OR REPLACE ] [ SECURE ] [ RECURSIVE ] VIEW [ IF NOT EXISTS ] <name>
  [ ( <column_list> ) ]
  [ <col1> [ WITH ] MASKING POLICY <policy_name> [ USING ( <col1> , <cond_col1> , ... ) ]
           [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ , <col2> [ ... ] ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  AS <select_statement>

Erforderliche Parameter

name

Gibt den Bezeichner für die Ansicht an. Dieser muss für das Schema, in dem die Ansicht erstellt wird, eindeutig sein.

Darüber hinaus muss der Bezeichner mit einem Buchstaben beginnen und darf keine Leer- oder Sonderzeichen enthalten, es sei denn, die gesamte Bezeichnerzeichenfolge wird in doppelte Anführungszeichen gesetzt (z. B. "My object"). Bei Bezeichnern, die in doppelte Anführungszeichen eingeschlossen sind, ist auch die Groß-/Kleinschreibung zu beachten.

Weitere Details dazu finden Sie unter Anforderungen an Bezeichner.

select_statement

Gibt die Abfrage an, die zum Erstellen der Ansicht verwendet wird. Sie kann sich auf eine oder mehrere Quelltabellen oder eine andere gültige SELECT-Anweisung beziehen. Diese Abfrage dient als Text/Definition für die Ansicht und wird in der SHOW VIEWS-Ausgabe und der Information Schema-Ansicht VIEWS angezeigt.

Optionale Parameter

SECURE

Gibt an, dass die Ansicht sicher ist. Weitere Informationen zu sicheren Ansichten finden Sie unter Verwenden von sicheren Ansichten.

Standard: Kein Wert (Ansicht ist nicht sicher)

RECURSIVE

Gibt an, dass sich eine rekursive Ansicht mithilfe rekursiver Syntax ohne Verwendung eines CTE (allgemeiner Tabellenausdruck) auf sich selbst beziehen kann. Weitere Informationen zu rekursiven Ansichten im Allgemeinen und zum Schlüsselwort RECURSIVE im Besonderen finden Sie unter Rekursive Ansichten (nur nicht materialisierte Ansichten) und in den Beispielen für rekursive Ansichten unten.

Standard: Kein Wert (Ansicht ist nicht rekursiv oder nur mithilfe eines CTE rekursiv)

column_list

Wenn Sie in der neuen Ansicht den Namen einer Spalte ändern oder einen Kommentar zu einer Spalte hinzufügen möchten, fügen Sie eine Spaltenliste ein, die die Spaltennamen und (falls erforderlich) Kommentare zu den Spalten angibt. (Sie müssen nicht die Datentypen der Spalten angeben.)

Wenn eine der Spalten in der Ansicht auf Ausdrücken basiert (nicht nur auf einfachen Spaltennamen), müssen Sie für jede Spalte in der Ansicht einen Spaltennamen angeben. Beispielsweise sind die Spaltennamen in folgendem Fall erforderlich:

CREATE VIEW v1 (pre_tax_profit, taxes, after_tax_profit) AS
    SELECT revenue - cost, (revenue - cost) * tax_rate, (revenue - cost) * (1.0 - tax_rate)
    FROM table1;

Sie können für jede Spalte einen optionalen Kommentar angeben. Beispiel:

CREATE VIEW v1 (pre_tax_profit COMMENT 'revenue minus cost',
                taxes COMMENT 'assumes taxes are a fixed percentage of profit',
                after_tax_profit)
    AS
    SELECT revenue - cost, (revenue - cost) * tax_rate, (revenue - cost) * (1.0 - tax_rate)
    FROM table1;

Kommentare sind besonders hilfreich, wenn die Spaltennamen kryptisch sind.

Um Kommentare anzuzeigen, verwenden Sie DESCRIBE VIEW.

MASKING POLICY = policy_name

Gibt die Maskierungsrichtlinie an, die für eine Spalte festgelegt werden soll.

USING ( col_name , cond_col_1 ... )

Gibt die Argumente an, die an den SQL-Ausdruck für die bedingte Maskierungsrichtlinie übergeben werden sollen.

Die erste Spalte in der Liste gibt die Spalte für die Richtlinienbedingungen zur Maskierung oder Tokenisierung der Daten an. Sie muss mit der Spalte übereinstimmen, für die die Maskierungsrichtlinie festgelegt ist.

Die zusätzlichen Spalten geben an, welche Spalten ausgewertet werden sollen, um zu ermitteln, ob die Daten in der jeweiligen Zeile des Abfrageergebnisses maskiert oder tokenisiert werden sollen, wenn auf der ersten Spalte eine Abfrage ausgeführt wird.

Wenn die USING-Klausel weggelassen wird, behandelt Snowflake die bedingte Maskierungsrichtlinie wie eine normale Maskierungsrichtlinie.

ROW ACCESS POLICY policy_name ON ( col_name [ , col_name ... ] )

Gibt die Zeilenzugriffsrichtlinie an, die für eine Ansicht festgelegt werden soll.

TAG tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ]

Gibt den Namen des Tags und den Wert der Tag-Zeichenfolge an.

Der Tag-Wert ist immer eine Zeichenfolge, die maximale 256 Zeichen lang sein kann.

Weitere Informationen zur Angabe von Tags in einer Anweisung finden Sie unter Tag-Kontingente für Objekte und Spalten.

COPY GRANTS

Behält die Zugriffsberechtigungen von der ursprünglichen Ansicht bei, wenn eine neue Ansicht mit der OR REPLACE-Klausel erstellt wird.

Der Parameter kopiert alle Berechtigungen außer OWNERSHIP aus der vorhandenen Ansicht in die neue Ansicht. Die neue Ansicht übernimmt keine zukünftigen Berechtigungen, die für den Objekttyp im Schema definiert werden. Die Rolle, die die Anweisung CREATE VIEW ausführt, besitzt standardmäßig die neue Ansicht.

Wenn der Parameter nicht in der CREATE VIEW-Anweisung enthalten ist, übernimmt die neue Ansicht keine expliziten Zugriffsberechtigungen für die ursprüngliche Ansicht, übernimmt aber alle zukünftigen Berechtigungen, die für den Objekttyp im Schema definiert werden.

Beachten Sie, dass die Operation zum Kopieren von Berechtigungen atomar mit der CREATE VIEW-Anweisung erfolgt (d. h. innerhalb derselben Transaktion).

Standard: Kein Wert (Berechtigungen werden nicht kopiert)

COMMENT = 'string_literal'

Gibt einen Kommentar für die Ansicht an.

Standard: Kein Wert

Anforderungen an die Zugriffssteuerung

Eine Rolle, die zur Ausführung dieses SQL-Befehls verwendet wird, muss mindestens die folgenden Berechtigungen haben:

Berechtigung

Objekt

Anmerkungen

CREATE VIEW

Schema

SELECT

Tabelle, externe Tabelle, Ansicht

Erforderlich für alle in der Ansichtsdefinition abgefragten Tabellen und/oder Ansichten.

APPLY

Maskierungsrichtlinie, Zeilenzugriffsrichtlinie, Tag

Nur erforderlich, wenn beim Erstellen von Ansichten eine Maskierungsrichtlinie, eine Zeilenzugriffsrichtlinie, Objekt-Tags oder eine beliebige Kombination dieser Governance-Features angewendet wird.

Beachten Sie, dass für die Bearbeitung eines Objekts in einem Schema auch die Berechtigung USAGE für die übergeordnete Datenbank und das Schema erforderlich ist.

Eine Anleitung zum Erstellen einer benutzerdefinierten Rolle mit einer bestimmten Gruppe von Berechtigungen finden Sie unter Erstellen von benutzerdefinierten Rollen.

Allgemeine Informationen zu Rollen und Berechtigungen zur Durchführung von SQL-Aktionen auf sicherungsfähigen Objekten finden Sie unter Zugriffssteuerung in Snowflake.

Nutzungshinweise

  • Eine Ansichtsdefinition kann eine ORDER BY-Klausel enthalten (z. B. create view v1 as select * from t1 ORDER BY column1). Snowflake empfiehlt jedoch, die ORDER BY-Klausel aus den meisten Ansichtsdefinitionen auszuschließen. Wenn die Ansicht in Kontexten verwendet wird, die nicht von der Sortierung profitieren, dann fügt die ORDER BY-Klausel unnötige Kosten hinzu. Wenn die Ansicht beispielsweise in einer Verknüpfung (Join) verwendet wird und die Verknüpfungsspalte nicht die gleiche ist wie die ORDER BY-Spalte, werden die zusätzlichen Kosten für das Sortieren der Ergebnisse der Ansicht typischerweise verschwendet. Wenn Sie die Abfrageergebnisse sortieren müssen, ist es in der Regel effizienter, ORDER BY in der Abfrage anzugeben, die die Ansicht verwendet, als in der Ansicht selbst.

  • Ansichtsdefinitionen sind nicht dynamisch. Eine Ansicht wird nicht automatisch aktualisiert, wenn die zugrunde liegenden Quellen so geändert werden, dass sie nicht mehr mit der Ansichtsdefinition übereinstimmen, insbesondere wenn Spalten gelöscht werden. Beispiel:

    • Es wird eine Ansicht erstellt, die auf eine bestimmte Spalte einer Quelltabelle verweist, und die Spalte wird anschließend aus der Tabelle gelöscht.

    • Eine Ansicht wird mit SELECT * aus einer Tabelle erstellt und jede Spalte wird anschließend aus der Tabelle gelöscht.

    In beiden Szenarien gibt die Abfrage der Ansicht einen Spaltenkonfliktfehler zurück.

  • Wenn eine Quelltabelle für eine Ansicht gelöscht wird, gibt die Abfrage der Ansicht einen object does not exist-Fehler zurück.

  • Ein Schema darf keine Tabelle und keine Ansicht gleichen Namens enthalten. Eine CREATE VIEW-Anweisung erzeugt einen Fehler, wenn im Schema bereits eine Tabelle mit dem gleichen Namen vorhanden ist.

  • Wenn eine Ansicht erstellt wird, werden nicht qualifizierte Verweise auf Tabellen und andere Datenbankobjekte im Schema der Ansicht und nicht im aktuellen Schema der Sitzung aufgelöst. Ebenso werden teilweise qualifizierte Objekte (z. B. Schema.Objekt) in der Datenbank der Ansicht aufgelöst und nicht in der aktuellen Datenbank der Sitzung.

    Der Sitzungsparameter SEARCH_PATH (falls vorhanden) wird ignoriert.

  • Die Verwendung von OR REPLACE ist gleichbedeutend mit der Ausführung von DROP VIEW auf der vorhandenen Ansicht und der anschließenden Erstellung einer neuen Ansicht mit demselben Namen.

    CREATE OR REPLACE <Objekt>-Anweisungen sind atomar. Das heißt, wenn das Objekt ersetzt wird, werden die Löschung des alten Objekts und die Erstellung des neuen Objekts in einer einzigen Transaktion verarbeitet.

    Das bedeutet, dass alle Abfragen, die gleichzeitig mit der Operation CREATE OR REPLACE VIEW ausgeführt werden, entweder die alte oder die neue Version der Ansicht verwenden.

    Wenn Sie eine Ansicht neu erstellen oder austauschen, werden deren Änderungsdaten gelöscht, wodurch jeder Stream der Ansicht veraltet. Ein veralteter Stream kann nicht mehr gelesen werden.

  • Verwenden von COPY GRANTS:

    • Data Sharing:

      • Wenn die vorhandene sichere Ansicht einem anderen Konto mitgeteilt wurde, wird auch die Ersatzansicht freigegeben.

      • Wenn die vorhandene sichere Ansicht mit Ihrem Konto als Datenkonsument geteilt wurde und der Zugriff auf andere Rollen im Konto weiter gewährt wurde (unter Verwendung von GRANT IMPORTED PRIVILEGES in der übergeordneten Datenbank), wird auch der Zugriff auf die Ersatzansicht gewährt.

    • Die SHOW GRANTS-Ausgabe für die Ersetzungsansicht listet den Berechtigten für die kopierten Berechtigungen als Rolle auf, die die CREATE VIEW-Anweisung ausgeführt hat, mit dem aktuellen Zeitstempel, als die Anweisung ausgeführt wurde.

  • Wenn Sie eine Ansicht erstellen und dann einer Rolle Berechtigungen für diese Ansicht erteilen, kann die Rolle die Ansicht verwenden, auch wenn die Rolle keine Berechtigungen für die zugrunde liegenden Tabellen hat, auf die die Ansicht zugreift. Das bedeutet, dass Sie eine Ansicht verwenden können, um einer Rolle Zugriff auf nur eine Teilmenge einer Tabelle zu gewähren. Sie können beispielsweise eine Ansicht erstellen, die auf Informationen zur medizinischen Abrechnung, nicht aber auf Informationen zur medizinischen Diagnose in derselben Tabelle zugreifen kann. Dann können Sie der „Buchhalter“-Rolle Berechtigungen für diese Ansicht erteilen, sodass die Buchhalter zwar die Rechnungsinformationen, aber nicht die Diagnose des Patienten sehen können.

  • Der Befehl SHOW VIEWS liefert per Design keine Informationen über sichere Ansichten. Um Informationen über eine sichere Ansicht anzuzeigen, müssen Sie die Ansicht VIEWS im Information Schema verwenden und die Rolle verwenden, der die Ansicht gehört.

  • Eine rekursive Ansicht muss eine Spaltennamenliste enthalten.

  • Vermeiden Sie beim Definieren rekursiver Ansichten die unendliche Rekursion. Die WHERE-Klausel in der Definition der rekursiven Ansicht sollte ermöglichen, dass die Rekursion möglicherweise angehalten wird, indem normalerweise keine Daten mehr vorhanden sind, nachdem die letzte Ebene einer Datenhierarchie verarbeitet wurde.

  • Metadaten:

    Achtung

    Kunden müssen sicherstellen, dass bei der Nutzung des Snowflake-Dienstes keine personenbezogenen Daten (außer für ein Objekt „Benutzer“), sensible Daten, exportkontrollierte Daten oder andere regulierte Daten als Metadaten eingegeben werden. Weitere Informationen dazu finden Sie unter Metadatenfelder in Snowflake.

  • Wenn Sie eine Ansicht mit Maskierungsrichtlinie auf einer oder mehreren Spalten oder die Ansicht selbst mit Zeilenzugriffsrichtlinie ändern, dann können Sie mit der Funktion POLICY_CONTEXT eine Abfrage auf den mit Maskierungsrichtlinie geschützten Spalten bzw. auf der mit einer Zeilenzugriffsrichtlinie geschützten Ansicht simulieren.

  • Erstellen Sie keine Ansichten mit Streams als Quellobjekt, es sei denn, dieselbe Rolle ist Eigentümer sowohl der Ansicht als auch des Quellstreams (d. h. dieselbe Rolle oder eine niedrigere Rolle in einer Rollenhierarchie hat die OWNERSHIP-Berechtigung für die Ansicht und die Quellstreams). Erstellen Sie stattdessen Ansichten, die die zu verfolgenden Objekte als Quellobjekte haben. Erstellen Sie dann Streams auf diesen Ansichten. Weitere Informationen dazu finden Sie unter Streams auf Ansichten.

Portierungshinweise

  • Einige Anbieter unterstützen das Schlüsselwort FORCE:

    CREATE OR REPLACE FORCE VIEW ...
    

    Snowflake akzeptiert das Schlüsselwort FORCE, unterstützt es jedoch nicht. Anders ausgedrückt: Wenn Sie dieses Schlüsselwort verwenden, wird kein Syntaxfehler angezeigt. Allerdings führt das Verwenden von FORCE nicht zwangsläufig dazu, eine Ansicht zu erstellen, wenn die zugrunde liegenden Datenbankobjekte (Tabellen oder Ansichten) noch nicht vorhanden sind. Der Versuch, eine Ansicht einer nicht vorhandenen Tabelle oder Ansicht zu erstellen, führt zu einer Fehlermeldung, auch wenn das Schlüsselwort FORCE verwendet wird.

  • Bei der Suche nach den Tabellen in einer Ansicht suchen einige Anbieter nach nicht qualifizierten Tabellennamen im aktiven Schema. Snowflake führt die Suche nach nicht qualifizierten Tabellennamen in demselben Schema wie die Ansicht aus. Wenn Sie auf Snowflake portieren, sollten Sie die Ansichten aktualisieren, um vollqualifizierte Tabellennamen zu erhalten.

Beispiele

Erstellen einer Ansicht im aktuellen Schema mit einem Kommentar, der alle Zeilen einer Tabelle auswählt:

CREATE VIEW myview COMMENT='Test view' AS SELECT col1, col2 FROM mytable;

SHOW VIEWS;

+---------------------------------+-------------------+----------+---------------+-------------+----------+-----------+--------------------------------------------------------------------------+
| created_on                      | name              | reserved | database_name | schema_name | owner    | comment   | text                                                                     |
|---------------------------------+-------------------+----------+---------------+-------------+----------+-----------+--------------------------------------------------------------------------|
| Thu, 19 Jan 2017 15:00:37 -0800 | MYVIEW            |          | MYTEST1       | PUBLIC      | SYSADMIN | Test view | CREATE VIEW myview COMMENT='Test view' AS SELECT col1, col2 FROM mytable |
+---------------------------------+-------------------+----------+---------------+-------------+----------+-----------+--------------------------------------------------------------------------+

Das nächste Beispiel ist dasselbe, wie das vorherige Beispiel, jedoch ist die Ansicht sicher:

CREATE OR REPLACE SECURE VIEW myview COMMENT='Test secure view' AS SELECT col1, col2 FROM mytable;

SELECT is_secure FROM information_schema.views WHERE view_name = 'MYVIEW';

Im Folgenden werden zwei Möglichkeiten zum Erstellen rekursiver Ansichten gezeigt:

Erstellen und laden Sie die Tabelle:

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);
INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('President', 1, NULL),  -- The President has no manager.
        ('Vice President Engineering', 10, 1),
            ('Programmer', 100, 10),
            ('QA Engineer', 101, 10),
        ('Vice President HR', 20, 1),
            ('Health Insurance Analyst', 200, 20);

Erstellen Sie eine Ansicht mit einem rekursiven CTE, und fragen Sie dann die Ansicht ab.

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
);
SELECT * 
    FROM employee_hierarchy 
    ORDER BY employee_ID;
+----------------------------+-------------+------------+-----------------------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MGR_EMP_ID (SHOULD BE SAME) | MGR TITLE                  |
|----------------------------+-------------+------------+-----------------------------+----------------------------|
| President                  |           1 |       NULL |                        NULL | President                  |
| Vice President Engineering |          10 |          1 |                           1 | President                  |
| Vice President HR          |          20 |          1 |                           1 | President                  |
| Programmer                 |         100 |         10 |                          10 | Vice President Engineering |
| QA Engineer                |         101 |         10 |                          10 | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 |                          20 | Vice President HR          |
+----------------------------+-------------+------------+-----------------------------+----------------------------+

Erstellen Sie eine Ansicht mit dem Schlüsselwort RECURSIVE, und fragen Sie die Ansicht ab.

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
);
SELECT * 
    FROM employee_hierarchy_02 
    ORDER BY employee_ID;
+----------------------------+-------------+------------+-----------------------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MGR_EMP_ID (SHOULD BE SAME) | MGR TITLE                  |
|----------------------------+-------------+------------+-----------------------------+----------------------------|
| President                  |           1 |       NULL |                        NULL | President                  |
| Vice President Engineering |          10 |          1 |                           1 | President                  |
| Vice President HR          |          20 |          1 |                           1 | President                  |
| Programmer                 |         100 |         10 |                          10 | Vice President Engineering |
| QA Engineer                |         101 |         10 |                          10 | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 |                          20 | Vice President HR          |
+----------------------------+-------------+------------+-----------------------------+----------------------------+
Zurück zum Anfang