Kategorien:

DDL für Tabellen, Ansichten und Sequenzen

CREATE VIEW

Erstellt eine neue Ansicht im aktuellen/spezifizierten Schema, basierend auf einer Abfrage einer oder mehrerer bestehender Tabellen (oder eines anderen gültigen Abfrageausdrucks).

Syntax

CREATE [ OR REPLACE ] [ SECURE ] [ RECURSIVE ] VIEW [ IF NOT EXISTS ] <name>
  [ ( <column_list> ) ]
  [ 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ß- und Kleinschreibung zu beachten.

Weitere Details dazu finden Sie unter Anforderungen an Bezeichner.

Select-Anweisung

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)

Spaltenliste:

Wenn Sie nicht möchten, dass die Spaltennamen in der Ansicht den Spaltennamen der zugrunde liegenden Tabelle entsprechen, können Sie eine Spaltenliste hinzufügen, in der Sie die Spaltennamen angeben. (Sie müssen die Datentypen der Spalten nicht 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 (x, x_times_2) AS SELECT x, x * 2 FROM table1;
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 = 'Zeichenfolgenliteral'

Gibt einen Kommentar für die Ansicht an.

Standard: Kein Wert

Nutzungshinweise

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

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

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 wird beim Verwenden von FORCE der Server nicht dazu gezwungen, 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          |
+----------------------------+-------------+------------+-----------------------------+----------------------------+