Snowflake Information Schema

Das Snowflake Information Schema (auch „Datenwörterbuch“ genannt) besteht aus einer Reihe von systemdefinierten Ansichten und Tabellenfunktionen, die umfangreiche Metadateninformationen über die in Ihrem Konto erstellten Objekte liefern. Das Snowflake Information Schema basiert auf dem SQL-92 ANSI Information Schema, jedoch mit zusätzlichen Ansichten und Funktionen, die spezifisch für Snowflake sind.

Das Information Schema ist als ein Schema mit dem Namen INFORMATION_SCHEMA implementiert, das Snowflake automatisch in jeder Datenbank eines Kontos erstellt.

Bemerkung

ANSI verwendet beim Verweis auf Datenbanken den Begriff „Katalog“. Um die Kompatibilität mit dem Standard aufrechtzuerhalten, wird in den Themen des Snowflake Information Schema ggf. „Katalog“ anstelle von „Datenbank“ verwendet. Die Begriffe sind im Grunde genommen konzeptionell gleichwertig und austauschbar.

Unter diesem Thema:

Was ist INFORMATION_SCHEMA?

Jede in Ihrem Konto erstellte Datenbank enthält automatisch ein integriertes, schreibgeschütztes Schema mit dem Namen INFORMATION_SCHEMA. Das Schema enthält die folgenden Objekte:

  • Ansichten für alle in der Datenbank enthaltenen Objekte sowie Ansichten für Objekte auf Kontoebene (d. h. Nicht-Datenbankobjekte wie Rollen, Warehouses und Datenbanken).

  • Tabellenfunktionen für historische Daten und Nutzungsdaten in Ihrem gesamten Konto.

Ansichten und Tabellenfunktionen des Information Schema

Liste der Ansichten

Die Ansichten in INFORMATION_SCHEMA zeigen Metadaten über in der Datenbank definierte Objekte sowie Metadaten für Nicht-Datenbankobjekte auf Kontoebene, die in allen Datenbanken vorkommen. Jede Instanz von INFORMATION_SCHEMA beinhaltet:

  • ANSI-Standardansichten für die für Snowflake relevanten Objekte auf Datenbank- und Kontoebene.

  • Snowflake-spezifische Ansichten für die von Snowflake unterstützten Nicht-Standardobjekte (Stagingbereiche, Dateiformate usw.).

Sofern nicht anders angegeben, entsprechen die Ansichten des Snowflake Information Schema dem ANSI-Standard:

Ansicht

Typ

Snowflake-spezifisch

Anmerkungen

APPLICABLE_ROLES

Konto

CLASS_INSTANCE_FUNCTIONS

Datenbank

CLASS_INSTANCE_PROCEDURES

Datenbank

CLASS_INSTANCES

Datenbank

CLASSES

Datenbank

COLUMNS

Datenbank

CURRENT_PACKAGES_POLICY

Datenbank

DATABASES

Konto

ELEMENT_TYPES

Datenbank

ENABLED_ROLES

Konto

EVENT_TABLES

Datenbank

EXTERNAL_TABLES

Datenbank

FIELDS

Datenbank

FILE FORMATS

Datenbank

FUNCTIONS

Datenbank

HYBRID_TABLES

Datenbank

INDEXES

Datenbank

INDEX_COLUMNS

Datenbank

INFORMATION_SCHEMA_CATALOG_NAME

Konto

LOAD_HISTORY

Konto

Daten werden 14 Tage lang aufbewahrt.

OBJECT_PRIVILEGES

Konto

PACKAGES

Datenbank

PIPES

Datenbank

PROCEDURES

Datenbank

REFERENTIAL_CONSTRAINTS

Datenbank

REPLICATION_DATABASES

Konto

REPLICATION_GROUPS

Konto

SCHEMATA

Datenbank

SEQUENCES

Datenbank

SERVICES

Datenbank

STAGES

Datenbank

TABLE_CONSTRAINTS

Datenbank

TABLE_PRIVILEGES

Datenbank

TABLE_STORAGE_METRICS

Datenbank

TABLES

Datenbank

Zeigt Tabellen und Ansichten an.

USAGE_PRIVILEGES

Datenbank

Zeigt Berechtigungen nur für Sequenzen an. Verwenden Sie OBJECT_PRIVILEGES, um Berechtigungen für andere Objekttypen anzuzeigen.

VIEWS

Datenbank

Liste der Tabellenfunktionen

Die Tabellenfunktionen in INFORMATION_SCHEMA können verwendet werden, um die Nutzung auf Kontoebene und historische Daten zum Speicher, Warehouse, zu Benutzeranmeldungen und Abfragen zurückzugeben:

Tabellenfunktion

Datenaufbewahrung

Anmerkungen

AUTOMATIC_CLUSTERING_HISTORY

14 Tage

Die Ergebnisse hängen von der MONITOR USAGE-Berechtigung ab. [1]

AUTO_REFRESH_REGISTRATION_HISTORY

14 Tage

Die Ergebnisse hängen von der MONITOR USAGE-Berechtigung ab. [1]

COMPLETE_TASK_GRAPHS

60 Minuten

Gibt nur Ergebnisse für die Rolle ACCOUNTADMIN oder den Aufgabeneigentümer zurück (d. h. die Rolle mit der Berechtigung OWNERSHIP für die Aufgabe) oder eine Rolle mit der globalen Berechtigung MONITOR EXECUTION.

COPY_HISTORY

14 Tage

Die Ergebnisse hängen von den Berechtigungen ab, die der aktuellen Rolle des Benutzers zugewiesen sind.

CURRENT_TASK_GRAPHS

N/A

Gibt nur Ergebnisse für die Rolle ACCOUNTADMIN oder den Aufgabeneigentümer zurück (d. h. die Rolle mit der Berechtigung OWNERSHIP für die Aufgabe) oder eine Rolle mit der globalen Berechtigung MONITOR EXECUTION.

DATA_TRANSFER_HISTORY

14 Tage

Die Ergebnisse hängen von der MONITOR USAGE-Berechtigung ab. [1]

DATABASE_REFRESH_HISTORY

14 Tage

Die Ergebnisse hängen von den Berechtigungen ab, die der aktuellen Rolle des Benutzers zugewiesen sind.

DATABASE_REFRESH_PROGRESS , DATABASE_REFRESH_PROGRESS_BY_JOB

14 Tage

Die Ergebnisse hängen von den Berechtigungen ab, die der aktuellen Rolle des Benutzers zugewiesen sind.

DATABASE_REPLICATION_USAGE_HISTORY

14 Tage

Die Ergebnisse werden nur für die Rolle ACCOUNTADMIN zurückgegeben.

DATABASE_STORAGE_USAGE_HISTORY

6 Monate

Die Ergebnisse hängen von der MONITOR USAGE-Berechtigung ab. [1]

DYNAMIC_TABLE_GRAPH_HISTORY

N/A

Die Ergebnisse hängen von der MONITOR USAGE-Berechtigung ab. [1]

DYNAMIC_TABLE_REFRESH_HISTORY

1 Tag

Die Ergebnisse hängen von der MONITOR USAGE-Berechtigung ab. [1]

EXTERNAL_FUNCTIONS_HISTORY

14 Tage

Die Ergebnisse hängen von der MONITOR USAGE-Berechtigung ab. [1]

EXTERNAL_TABLE_FILES

N/A

Die Ergebnisse hängen von den Berechtigungen ab, die der aktuellen Rolle des Benutzers zugewiesen sind.

EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY

30 Tage

Die Ergebnisse hängen von den Berechtigungen ab, die der aktuellen Rolle des Benutzers zugewiesen sind.

LOGIN_HISTORY , LOGIN_HISTORY_BY_USER

7 Tage

Die Ergebnisse hängen von den Berechtigungen ab, die der aktuellen Rolle des Benutzers zugewiesen sind.

MATERIALIZED_VIEW_REFRESH_HISTORY

14 Tage

Die Ergebnisse hängen von der MONITOR USAGE-Berechtigung ab. [1]

NOTIFICATION_HISTORY

14 Tage

Gibt Ergebnisse nur für die ACCOUNTADMIN-Rolle, für den Eigentümer der Integration (d. h. die Rolle mit der Berechtigung OWNERSHIP für die Integration) oder für eine Rolle mit der Berechtigung USAGE für die Integration zurück.

PIPE_USAGE_HISTORY

14 Tage

Die Ergebnisse hängen von der MONITOR USAGE-Berechtigung ab. [1]

POLICY_REFERENCES

N/A

Die Ergebnisse werden nur für die Rolle ACCOUNTADMIN zurückgegeben.

QUERY_ACCELERATION_HISTORY

14 Tage

Die Ergebnisse hängen von der MONITOR USAGE-Berechtigung ab. [1]

QUERY_HISTORY , QUERY_HISTORY_BY_*

7 Tage

Die Ergebnisse hängen von den Berechtigungen ab, die der aktuellen Rolle des Benutzers zugewiesen sind.

REPLICATION_GROUP_REFRESH_HISTORY

14 Tage

Die Ergebnisse werden nur für eine Rolle mit beliebigen Berechtigungen für die Replikations- oder Failover-Gruppe zurückgegeben.

REPLICATION_GROUP_REFRESH_PROGRESS, REPLICATION_GROUP_REFRESH_PROGRESS_BY_JOB

14 Tage

Die Ergebnisse werden nur für eine Rolle mit beliebigen Berechtigungen für die Replikations- oder Failover-Gruppe zurückgegeben.

REPLICATION_GROUP_USAGE_HISTORY

14 Tage

Die Ergebnisse hängen von der MONITOR USAGE-Berechtigung ab. [1]

REPLICATION_USAGE_HISTORY

14 Tage

Die Ergebnisse werden nur für die Rolle ACCOUNTADMIN zurückgegeben.

REST_EVENT_HISTORY

7 Tage

Die Ergebnisse werden nur für die Rolle ACCOUNTADMIN zurückgegeben.

SEARCH_OPTIMIZATION_HISTORY

14 Tage

Die Ergebnisse hängen von der MONITOR USAGE-Berechtigung ab. [1]

SERVERLESS_TASK_HISTORY

14 Tage

Die Ergebnisse hängen von der MONITOR USAGE-Berechtigung ab. [1]

STAGE_DIRECTORY_FILE_REGISTRATION_HISTORY

14 Tage

Die Ergebnisse hängen von den Berechtigungen ab, die der aktuellen Rolle des Benutzers zugewiesen sind.

STAGE_STORAGE_USAGE_HISTORY

6 Monate

Die Ergebnisse hängen von der MONITOR USAGE-Berechtigung ab. [1]

TAG_REFERENCES

N/A

Es werden nur Ergebnisse für die Rolle zurückgegeben, die Zugriff auf das angegebene Objekt hat.

TAG_REFERENCES_ALL_COLUMNS

N/A

Es werden nur Ergebnisse für die Rolle zurückgegeben, die Zugriff auf das angegebene Objekt hat.

TASK_DEPENDENTS

N/A

Ergebnisse werden nur für die Rolle ACCOUNTADMIN oder den Aufgabeneigentümer (Rolle mit der Berechtigung OWNERSHIP für die Aufgabe) zurückgegeben.

TASK_HISTORY

7 Tage

Gibt nur Ergebnisse für die Rolle ACCOUNTADMIN oder den Aufgabeneigentümer zurück (d. h. die Rolle mit der Berechtigung OWNERSHIP für die Aufgabe) oder eine Rolle mit der globalen Berechtigung MONITOR EXECUTION.

VALIDATE_PIPE_LOAD

14 Tage

Die Ergebnisse hängen von den Berechtigungen ab, die der aktuellen Rolle des Benutzers zugewiesen sind.

WAREHOUSE_LOAD_HISTORY

14 Tage

Die Ergebnisse hängen von der MONITOR USAGE-Berechtigung ab. [1]

WAREHOUSE_METERING_HISTORY

6 Monate

Die Ergebnisse hängen von der MONITOR USAGE-Berechtigung ab. [1]

[1] Gibt Ergebnisse zurück, wenn der Rolle die globale Berechtigung MONITOR USAGE zugewiesen wurde; andernfalls werden Ergebnisse nur für die Rolle ACCOUNTADMIN zurückgegeben.

Allgemeine Nutzungshinweise

  • Jedes INFORMATION_SCHEMA-Schema ist schreibgeschützt (d. h. das Schema und alle Ansichten und Tabellenfunktionen im Schema können weder geändert noch gelöscht werden).

  • Abfragen bei INFORMATION_SCHEMA-Ansichten garantieren keine Konsistenz in Bezug auf konkurrierende DDL. Wenn beispielsweise mehrere Tabellen erstellt werden, während eine lang laufende INFORMATION_SCHEMA-Abfrage ausgeführt wird, kann das Ergebnis der Abfrage einige, keine oder alle der erstellten Tabellen enthalten.

  • Die Ausgabe einer Ansicht oder Tabellenfunktion hängt von den Berechtigungen ab, die der aktuellen Rolle des Benutzers gewährt wurden. Bei der Abfrage einer INFORMATION_SCHEMA-Ansicht oder -Tabellenfunktion werden nur Objekte zurückgegeben, für die der aktuellen Rolle Zugriffsrechte gewährt wurden.

  • Um Leistungsprobleme zu vermeiden, wird der folgende Fehler zurückgegeben, wenn die in einer INFORMATION_SCHEMA-Abfrage angegebenen Filter nicht ausreichend selektiv sind:

    Information schema query returned too much data. Please repeat query with more selective predicates.

  • Die Snowflake-spezifischen Ansichten können sich noch ändern. Vermeiden Sie es, in diesen Ansichten alle Spalten auszuwählen. Wählen Sie stattdessen nur die wirklich erforderlichen Spalten aus. Wenn Sie zum Beispiel die Spalte name benötigen, verwenden Sie SELECT name und nicht SELECT *.

Tipp

Die Ansichten des Information Schema sind für Abfragen optimiert, die eine kleine Teilmenge von Objekten aus dem Dictionary abrufen. Maximieren Sie nach Möglichkeit die Leistung Ihrer Abfragen, indem Sie nach Schema- und Objektnamen filtern.

Weitere Informationen und Details zur Verwendung finden Sie im Blogbeitrag Snowflake Information Schema.

Hinweise zum Ersetzen von SHOW-Befehlen durch Information Schema-Ansichten

Die INFORMATION_SCHEMA-Ansichten bieten eine SQL-Schnittstelle zu den gleichen Informationen, die von den SHOW <Objekte>-Befehlen bereitgestellt werden. Sie können die Ansichten verwenden, um diese Befehle zu ersetzen; es gibt jedoch einige wichtige Unterschiede, die Sie vor dem Wechsel beachten sollten:

Hinweise

SHOW-Befehle

Information Schema-Ansichten

Warehouses

Nicht erforderlich zur Ausführung.

Das Warehouse muss zur Abfrage der Ansichten laufen und gerade verwendet werden.

Mustererkennung/Filtern

Groß- und Kleinschreibung wird nicht berücksichtigt (beim Filtern mit LIKE).

Standard (Groß- und Kleinschreibung wird beachtet) SQL-Semantik. Snowflake konvertiert Bezeichner ohne Anführungszeichen, bei denen die Groß- und Kleinschreibung nicht beachtet werden muss, automatisch in Großbuchstaben, sodass Objektnamen ohne Anführungszeichen in den Ansichten des Information Schema in Großbuchstaben abgefragt werden müssen.

Abfrageergebnisse

Die meisten SHOW-Befehle beschränken die Ergebnisse standardmäßig auf das aktuelle Schema.

Ansichten zeigen alle Objekte in der aktuellen/spezifizierten Datenbank an. Um nach einem bestimmten Schema zu suchen, müssen Sie ein Filterprädikat verwenden (z. B. ... WHERE table_schema = CURRENT_SCHEMA()...). Beachten Sie, dass Abfragen im Information Schema, denen es an ausreichend selektiven Filtern mangelt, einen Fehler zurückgeben und nicht ausgeführt werden (siehe Allgemeine Nutzungshinweise unter diesem Thema).

Qualifizieren der Namen von Information Schema-Ansichten und -Tabellenfunktionen in Abfragen

Bei der Abfrage einer INFORMATION_SCHEMA-Ansicht oder -Tabellenfunktion müssen Sie den qualifizierten Namen der Ansicht/Tabellenfunktion verwenden oder das INFORMATION_SCHEMA-Schema muss für die Sitzung verwendet werden.

Beispiel:

  • Abfrage mit den vollqualifizierten Namen der Ansicht und Tabellenfunktion im Format database.information_schema.name:

    SELECT table_name, comment FROM testdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ... ;
    
    SELECT event_timestamp, user_name FROM TABLE(testdb.INFORMATION_SCHEMA.LOGIN_HISTORY( ... ));
    
    Copy
  • Abfrage mit den qualifizierten Namen der Ansicht und Tabellenfunktion im Format information_schema.name:

    USE DATABASE testdb;
    
    SELECT table_name, comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ... ;
    
    SELECT event_timestamp, user_name FROM TABLE(INFORMATION_SCHEMA.LOGIN_HISTORY( ... ));
    
    Copy
  • Abfragen des INFORMATION_SCHEMA-Schemas, das für die Sitzung verwendet wird:

    USE SCHEMA testdb.INFORMATION_SCHEMA;
    
    SELECT table_name, comment FROM TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ... ;
    
    SELECT event_timestamp, user_name FROM TABLE(LOGIN_HISTORY( ... ));
    
    Copy

    Bemerkung

    Wenn Sie eine Datenbank verwenden, die aus einer Freigabe erstellt wurde, und Sie INFORMATION_SCHEMA als aktuelles Schema für die Sitzung ausgewählt haben, schlägt die SELECT-Anweisung möglicherweise mit dem folgenden Fehler fehl:

    INFORMATION_SCHEMA does not exist or is not authorized

    Wählen Sie in diesem Fall ein anderes Schema als das aktuelle Schema der Sitzung aus.

Detaillierte Beispiele finden Sie in der Referenzdokumentation der einzelnen Ansicht/Tabellenfunktion.