Einführung in externe Tabellen

In einer typischen Tabelle werden die Daten in der Datenbank gespeichert. In einer externen Tabelle werden die Daten jedoch in Dateien in einem externen Stagingbereich gespeichert. In externen Tabellen werden Metadaten zu den Datendateien auf Dateiebene gespeichert, z. B. der Dateiname, ein Versionsbezeichner und zugehörige Eigenschaften. Dies ermöglicht das Abfragen von Daten, die in Dateien in einem externen Stagingbereich gespeichert sind, als befänden sie sich in einer Datenbank. Externe Tabellen können auf Daten zugreifen, die in einem beliebigen von COPY INTO <Tabelle> -Anweisungen unterstützten Format gespeichert sind.

Externe Tabellen sind schreibgeschützt, daher können keine DML-Operationen für sie ausgeführt werden. Externe Tabellen können jedoch für Abfrage- und Join-Vorgänge verwendet werden. Ansichten können für externe Tabellen erstellt werden.

Das Abfragen von außerhalb der Datenbank gespeicherten Daten ist wahrscheinlich langsamer als das Abfragen von nativen Datenbanktabellen; auf externen Tabellen basierende materialisierte Ansichten können die Abfrageleistung jedoch verbessern.

Unter diesem Thema:

Planen des Schemas einer externen Tabelle

In diesem Abschnitt werden die Optionen zum Entwerfen externer Tabellen beschrieben.

Schema beim Lesen

Alle externen Tabellen enthalten die folgenden Spalten:

VALUE

Eine Spalte vom Typ VARIANT, die eine einzelne Zeile in der externen Datei darstellt.

METADATA$FILENAME

Eine Pseudospalte, die den Namen jeder in der externen Tabelle enthaltenen Staging-Datendatei einschließlich ihres Pfads im Stagingbereich angibt.

Um externe Tabellen zu erstellen, müssen Sie nur einige Kenntnisse über das Dateiformat und das Datensatzformat der Quelldatendateien haben. Es ist nicht erforderlich, das Schema der Datendateien zu kennen. Bei der Abfrage wandeln externe Tabellen alle regulären oder semistrukturierten Daten in eine Variante in der Spalte VALUE um.

Virtuelle Spalten

Wenn Sie mit dem Schema der Quelldatendateien vertraut sind, können Sie mithilfe der VALUE-Spalte und/oder der METADATA$FILENAME-Pseudospalte zusätzliche virtuelle Spalten als Ausdrücke erstellen. Beim Scannen der externen Daten müssen die Datentypen aller angegebenen Felder oder semistrukturierten Datenelemente in der Datendatei mit den Datentypen dieser zusätzlichen Spalten in der externen Tabelle übereinstimmen. Dies ermöglicht eine starke Typprüfung und Schemavalidierung der externen Daten.

Partitionierte externe Tabellen

Es wird dringend empfohlen, Ihre externen Tabellen zu partitionieren. Dazu müssen Ihre zugrunde liegenden Daten unter Verwendung logischer Pfade organisiert sein, die Datum, Uhrzeit, Land oder ähnliche Dimensionen im Pfad enthalten. Durch Partitionierung werden Ihre externen Tabellendaten mithilfe von Partitionsspalten in mehrere Teile unterteilt. Partitionsspalten müssen als Ausdrücke auswerten, die die in der METADATA$FILENAME-Pseudospalte gespeicherten Pfad- und/oder Dateinameninformationen analysieren. Eine Partition besteht aus allen Datendateien, die mit dem Pfad und/oder Dateinamen im Ausdruck für die Partitionsspalte übereinstimmen.

Eine externe Tabellendefinition kann mehrere Partitionsspalten enthalten, die den externen Daten eine mehrdimensionale Struktur auferlegen.

Zu den Vorteilen der Partitionierung gehört eine verbesserte Abfrageleistung. Da die externen Daten in separate Segmente/Teile unterteilt sind, ist die Antwortzeit bei Abfragen kürzer, wenn ein nur kleiner Teil der Daten verarbeitet wird, anstatt das gesamte Dataset zu scannen.

Partitionsspalten können nur beim Erstellen einer externen Tabelle mithilfe der CREATE EXTERNAL TABLE … PARTITION BY-Syntax und unter Verwendung einer Liste von Spaltendefinitionen für Partitionierung definiert werden.

Syntax

Definieren Sie Partitionen in externen Tabellendaten, indem Sie die Partitionsspalten definieren:

CREATE EXTERNAL TABLE
  <table_name>
     ( <part_col_name> <col_type> AS <part_expr> )
     [ , ... ]
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  ..

Weitere Beispiele finden Sie unter CREATE EXTERNAL TABLE TYPE = CSV | JSON | AVRO | ORC | PARQUET [ ... ].

Hinzufügen oder Löschen von Spalten

Ändern Sie eine vorhandene externe Tabelle, um mithilfe der folgenden ALTER TABLE-Syntax Spalten hinzuzufügen oder zu entfernen:

  • Hinzufügen von Spalten: ALTER TABLE … ADD COLUMN.

  • Entfernen von Spalten: ALTER TABLE … DROP COLUMN.

Bemerkung

Die Standardspalten VALUE und METADATA$FILENAME können nicht gelöscht werden.

Siehe Beispiel unter ALTER TABLE.

Materialisierte Ansichten für externe Tabellen

In vielen Fällen können materialisierte Ansichten für externe Tabellen schnellere Leistung liefern als entsprechende Abfragen an der zugrunde liegenden externen Tabelle. Dieser Leistungsunterschied kann signifikant sein, wenn eine Abfrage häufig ausgeführt wird oder hinreichend komplex ist.

Automatische Aktualisierung der Metadaten externer Tabellen

Die Metadaten einer externen Tabelle können mithilfe des folgenden Ereignismeldungsdienstes für jeden Speicherort automatisch aktualisiert werden:

Beim Aktualisierungsvorgang werden die Metadaten mit dem neuesten Satz zugeordneter Dateien im externen Stagingbereich und Pfad synchronisiert, d. h.:

  • Neue Dateien im Pfad werden zu den Tabellenmetadaten hinzugefügt.

  • Änderungen an Dateien im Pfad werden in den Tabellenmetadaten aktualisiert.

  • Dateien, die sich nicht mehr im Pfad befinden, werden aus den Tabellenmetadaten entfernt.

Bemerkung

Derzeit besteht keine Möglichkeit zum automatischen Aktualisieren der Metadaten bei externen Tabellen, die auf Google Cloud Storage-Stagingbereiche verweisen.

Als Problemumgehung empfehlen wir Ihnen, unsere Best Practices für das Staging Ihrer Datendateien zu befolgen und regelmäßig eine ALTER EXTERNAL TABLE … REFRESH-Anweisung auszuführen, um fehlende Dateien zu registrieren. Für eine zufriedenstellende Leistung empfehlen wir außerdem die Verwendung eines selektiven Pfadpräfixes mit ALTER EXTERNAL TABLE, um die Anzahl der Dateien zu reduzieren, die aufgelistet und daraufhin geprüft werden müssen, ob sie bereits registriert wurden (z. B. Bucketname/YYYY/MM/DD/ oder auch Bucketname/YYYY/MM/DD/HH/, abhängig vom Volumen).

Abrechnung für Aktualisierung der Metadaten externer Tabellen

Verwaltungskosten für Ereignisbenachrichtigungen (für die in Cloudspeicher hinzugefügten Dateien) sind in Ihren Gebühren enthalten. Diese Verwaltungskosten steigen in Abhängigkeit von der Anzahl der Dateien, die für die externen Stagingbereiche und Pfade in Cloudspeicher hinzugefügt werden, die für Ihre externen Tabellen angegeben sind. Snowflake berechnet 0,06 Credits pro 1.000 erhaltene Ereignisbenachrichtigungen.

Sobald externe Tabellen allgemein verfügbar sind (Datum TBD), werden geringe Verwaltungskosten für das manuelle Aktualisieren der Metadaten externer Tabellen (mit ALTER EXTERNAL TABLE … REFRESH) berechnet. Diese Verwaltungskosten sind für die Ressourcen enthalten, die beim Abrufen der Objektliste zusammen mit beschreibenden Informationen zu jedem Objekt im externen Stagingbereich und Pfad verbraucht werden. Die Rate für diese Verwaltungskosten wurde noch nicht festgelegt.

Workflow

Amazon S3

Dieser Abschnitt bietet einen umfassenden Überblick über den Setup- und Lade-Workflow bei externen Tabellen, die auf Amazon S3-Stagingbereiche verweisen. Eine vollständige Anleitung finden Sie unter Automatisches Aktualisieren externer Tabellen für Amazon S3.

  1. Erstellen Sie ein benanntes Stagingobjekt (mit CREATE STAGE), das auf den externen Speicherort (d. h. S3-Bucket) verweist, in dem das Staging Ihrer Datendateien erfolgt.

  2. Erstellen Sie eine externe Tabelle (mit CREATE EXTERNAL TABLE TYPE = CSV | JSON | AVRO | ORC | PARQUET [ ... ]), die auf den angegebenen Stagingbereich verweist.

  3. Aktualisieren Sie die Metadaten der externen Tabellen mit ALTER EXTERNAL TABLE … REFRESH, um die Metadaten mit der aktuellen Liste der Dateien im Stagingbereichspfad zu synchronisieren. In diesem Schritt werden auch die Einstellungen in der Definition der externen Tabellen überprüft.

  4. Konfigurieren Sie eine Ereignisbenachrichtigung für den S3-Bucket. Snowflake verwendet Ereignisbenachrichtigungen, um die Metadaten einer externen Tabelle kontinuierlich zu aktualisieren und die Konsistenz mit den Stagingdateien sicherzustellen.

  5. Aktualisieren Sie die Metadaten der externen Tabelle noch einmal manuell mit ALTER EXTERNAL TABLE … REFRESH, um die Metadaten mit allen Änderungen zu synchronisieren, die seit Schritt 3 aufgetreten sind. Danach lösen die S3-Ereignisbenachrichtigungen die Aktualisierung der Metadaten automatisch aus.

  6. Konfigurieren Sie Snowflake-Zugriffssteuerungsrechte für beliebige zusätzliche Rollen, um ihnen Abfragezugriff auf die externe Tabelle zu gewähren.

Google Cloud Storage

Dieser Abschnitt bietet einen umfassenden Überblick über den Setup- und Lade-Workflow bei externen Tabellen, die auf Google Cloud Storage (GCS)-Stagingbereiche verweisen.

  1. Erstellen Sie ein benanntes Stagingobjekt (mit CREATE STAGE), das auf den externen Speicherort (d. h. GCS-Bucket) verweist, in dem das Staging Ihrer Datendateien erfolgt.

  2. Erstellen Sie eine externe Tabelle (mit CREATE EXTERNAL TABLE TYPE = CSV | JSON | AVRO | ORC | PARQUET [ ... ]), die auf den angegebenen Stagingbereich verweist.

  3. Konfigurieren Sie Snowflake-Zugriffssteuerungsrechte für beliebige zusätzliche Rollen, um ihnen Abfragezugriff auf die externe Tabelle zu gewähren.

  4. Aktualisieren Sie regelmäßig die Metadaten der externen Tabelle mit ALTER EXTERNAL TABLE … REFRESH, um die Metadaten der externen Tabelle mit dem Stagingbereich zu synchronisieren und fehlende Dateien zu registrieren. In diesem Schritt werden auch die Einstellungen in der Definition der externen Tabellen überprüft.

Microsoft Azure

Dieser Abschnitt bietet einen umfassenden Überblick über den Setup- und Lade-Workflow bei externen Tabellen, die auf Azure-Stagingbereiche verweisen. Eine vollständige Anleitung finden Sie unter Automatisches Aktualisieren externer Tabellen für Azure Blob-Speicher.

  1. Konfigurieren Sie ein Event Grid-Abonnement für Azure-Speicherereignisse.

  2. Erstellen Sie eine Benachrichtigungsintegration in Snowflake. Eine Benachrichtigungsintegration ist ein Snowflake-Objekt, das eine Schnittstelle zwischen Snowflake und Cloud-Nachrichtenwarteschlangendiensten von Drittanbietern wie Microsoft Event Grid bereitstellt.

  3. Erstellen Sie ein benanntes Stagingobjekt (mit CREATE STAGE), das auf den externen Speicherort (d. h. Azure-Container) verweist, in dem das Staging Ihrer Datendateien erfolgt.

  4. Erstellen Sie eine externe Tabelle (mit CREATE EXTERNAL TABLE TYPE = CSV | JSON | AVRO | ORC | PARQUET [ ... ]), die auf den benannten Stagingbereich und die Integration verweist.

  5. Aktualisieren Sie die Metadaten der externen Tabelle manuell mit ALTER EXTERNAL TABLE … REFRESH, um die Metadaten mit allen Änderungen zu synchronisieren, die seit Schritt 4 vorgenommen wurden. Danach lösen die Event Grid-Benachrichtigungen die Aktualisierung der Metadaten automatisch aus.

  6. Konfigurieren Sie Snowflake-Zugriffssteuerungsrechte für beliebige zusätzliche Rollen, um ihnen Abfragezugriff auf die externe Tabelle zu gewähren.

Apache Hive Metastore-Integration

Snowflake unterstützt die Integration von Apache Hive-Metastores in Snowflake mithilfe externer Tabellen. Der Hive-Konnektor erkennt Metastore-Ereignisse und überträgt sie an Snowflake, um die externen Tabellen mit dem Hive-Metastore synchron zu halten. Auf diese Weise können Benutzer ihre Daten in Hive verwalten, während sie über Snowflake abgefragt werden.

Anweisungen dazu finden Sie unter Integration von Apache Hive-Metastores in Snowflake.

Externe Tabellen-DDL

Um das Erstellen und Verwalten von externen Tabellen zu unterstützen, bietet Snowflake den folgenden Satz von speziellen DDL-Befehlen:

Erforderliche Zugriffsrechte

Für das Erstellen und Verwalten externer Tabellen ist eine Rolle mit mindestens den folgenden Rollenberechtigungen erforderlich:

Objekt

Berechtigung

Datenbank

USAGE

Schema

USAGE, CREATE STAGE (wenn ein neuer Stagingbereich erstellt wird), CREATE EXTERNAL TABLE

Stagingbereich (wenn ein vorhandener Stagingbereich verwendet wird)

USAGE

Information Schema

Das Snowflake Information Schema enthält Ansichten und Tabellenfunktionen, die Sie abfragen können, um Informationen über Ihre externen Tabellen und ihre Staging-Datendateien abzurufen.

Ansicht

EXTERNAL_TABLES-Ansicht

Rufen Sie eine Liste der externen Tabellen in der angegebenen (oder aktuellen) Datenbank ab.

Tabellenfunktionen

EXTERNAL_TABLE_FILES

Rufen Sie Informationen zu den Staging-Datendateien ab, die in den Metadaten für eine angegebene externe Tabelle enthalten sind.

EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY

Rufen Sie Informationen zum Metadatenverlauf für eine externe Tabelle ab, einschließlich etwaiger Fehler beim Aktualisieren der Metadaten.