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.

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 das INFORMATION_SCHEMA-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

COLUMNS

Datenbank

DATABASES

Konto

ENABLED_ROLES

Konto

EXTERNAL_TABLES

Datenbank

FILE FORMATS

Datenbank

FUNCTIONS

Datenbank

INFORMATION_SCHEMA_CATALOG_NAME

Konto

LOAD_HISTORY

Konto

Daten werden 14 Tage lang aufbewahrt.

OBJECT_PRIVILEGES

Konto

PIPES

Datenbank

PROCEDURES

Datenbank

REFERENTIAL_CONSTRAINTS

Datenbank

REPLICATION_DATABASES

Konto

SCHEMATA

Datenbank

SEQUENCES

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]

COPY_HISTORY

7 Tage

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

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_STORAGE_USAGE_HISTORY

6 Monate

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

14 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]

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_HISTORY , QUERY_HISTORY_BY_*

7 Tage

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

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]

STAGE_STORAGE_USAGE_HISTORY

6 Monate

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

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.

Entity-Relationship-Diagramm (ERD) für Ansichten

Das ERD für die Ansichten im INFORMATION_SCHEMA-Schema ist zu groß, um unter diesem Thema angezeigt zu werden. Um das Format ERD in PDF anzuzeigen und/oder herunterzuladen, klicken Sie auf das folgende Bild:

ER diagram of Information Schema views

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-Abfrage lieferte zu viele Daten. Wiederholen Sie die Abfrage mit selektiveren Prädikaten.

Tipp

Die Ansichten des Information Schema sind für Abfragen optimiert, die eine kleine Teilmenge von Objekten aus dem Wörterbuch 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 von Ansicht und Tabellenfunktion in Form von Datenbank.information_schema.Name:

    SELECT * FROM testdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ... ;
    
    SELECT * FROM TABLE(testdb.INFORMATION_SCHEMA.LOGIN_HISTORY( ... ));
    
  • Abfrage mit den qualifizierten Namen der Ansicht und Tabellenfunktion in Form von information_schema.Name:

    USE DATABASE testdb;
    
    SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ... ;
    
    SELECT * FROM TABLE(INFORMATION_SCHEMA.LOGIN_HISTORY( ... ));
    
  • So fragen Sie das INFORMATION_SCHEMA-Schema ab, das für die Sitzung verwendet wird:

    USE SCHEMA testdb.INFORMATION_SCHEMA;
    
    SELECT * FROM TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ... ;
    
    SELECT * FROM TABLE(LOGIN_HISTORY( ... ));
    

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