Einführung in externe Tabellen

In einer normalen Tabelle werden die Daten in der Datenbank gespeichert. Bei 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.

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 eingegangene Ereignisbenachrichtigungen.

Außerdem wird ein geringer Wartungsaufwand für die manuelle Aktualisierung der Metadaten der externen Tabelle (mit ALTER EXTERNAL TABLE … REFRESH) berechnet. Diese Verwaltungskosten werden gemäß dem Standardabrechnungsmodell für Clouddienste berechnet, wie alle ähnlichen Aktivitäten in Snowflake.

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), 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), 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 Storage.

  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), 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. Anschließend wird die Aktualisierung der Metadaten automatisch durch Event Grid-Benachrichtigungen ausgelöst.

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

Externe Tabellen abfragen

Führen Sie Abfragen externer Tabellen genauso aus, wie Sie es mit Standardtabellen tun würden.

Wenn Snowflake beim Scannen einer Datei im Cloudspeicher während eines Abfragevorgangs auf einen Fehler stößt, wird die Datei übersprungen und das Scannen mit der nächsten Datei fortgesetzt. Eine Abfrage kann eine Datei teilweise scannen und die Zeilen zurückgeben, die gescannt wurden, bevor der Fehler auftrat.

Persistente Abfrageergebnisse

Ähnlich wie bei standardmäßigen (lokalen) Tabellen bleiben die Abfrageergebnisse für externe Tabellen für 24 Stunden persistent. Innerhalb dieses 24-Stunden-Zeitraums wird der Abfrageergebnis-Cache für externe Tabellen durch die folgenden Operationen ungültig gemacht und bereinigt:

  • Jede DDL-Operation, die die externe Tabellendefinition modifiziert. Dazu gehört das explizite Ändern der externen Tabellendefinition (mit ALTER EXTERNAL TABLE) oder das Neuerstellen der externen Tabelle (mit CREATE OR REPLACE EXTERNAL TABLE).

  • Änderungen an der Menge der Dateien im Cloudspeicher, die in den Metadaten der externen Tabelle registriert sind. Der Ergebnis-Cache wird entweder durch automatische Aktualisierungsoperationen unter Verwendung des Ereignisbenachrichtigungsdienstes für den Speicherort oder durch manuelle Aktualisierungsvorgänge (unter Verwendung von ALTER EXTERNAL TABLE … REFRESH) ungültig gemacht.

Beachten Sie, dass Änderungen in den referenzierten Dateien im Cloudspeicher den Cache für Abfrageergebnisse unter den folgenden Umständen nicht ungültig machen, was zu veralteten Abfrageergebnissen führt:

  • Die automatische Aktualisierungsoperation ist deaktiviert (d. h. AUTO_REFRESH = FALSE) oder nicht korrekt konfiguriert.

  • Die Metadaten der externen Tabelle werden nicht manuell aufgefrischt.

Entfernen älterer Stagingdateien aus den Metadaten externer Tabellen

Eine gespeicherte Prozedur kann ältere Stagingdateien aus den Metadaten einer externen Tabelle mithilfe einer ALTER EXTERNAL TABLE … REMOVE FILES-Anweisung entfernen. Die gespeicherte Prozedur entfernt Dateien aus den Metadaten auf der Grundlage des letzten Änderungsdatums im Stagingbereich entfernen.

Beispiel:

  1. Erstellen Sie die gespeicherte Prozedur mit einer CREATE PROCEDURE-Anweisung:

    CREATE or replace PROCEDURE remove_old_files(external_table_name varchar, num_days float)
      RETURNS varchar
      LANGUAGE javascript
      EXECUTE AS CALLER
      AS
      $$
      // 1. Get the relative path of the external table
      // 2. Find all files registered before the specified time period
      // 3. Remove the files
    
    
      var resultSet1 = snowflake.execute({ sqlText:
        `call exttable_bucket_relative_path('` + EXTERNAL_TABLE_NAME + `');`
      });
      resultSet1.next();
      var relPath = resultSet1.getColumnValue(1);
    
    
      var resultSet2 = snowflake.execute({ sqlText:
        `select file_name
         from table(information_schema.EXTERNAL_TABLE_FILES (
             TABLE_NAME => '` + EXTERNAL_TABLE_NAME +`'))
         where last_modified < dateadd(day, -` + NUM_DAYS + `, current_timestamp());`
      });
    
      var fileNames = [];
      while (resultSet2.next())
      {
        fileNames.push(resultSet2.getColumnValue(1).substring(relPath.length));
      }
    
      if (fileNames.length == 0)
      {
        return 'nothing to do';
      }
    
    
      var alterCommand = `ALTER EXTERNAL TABLE ` + EXTERNAL_TABLE_NAME + ` REMOVE FILES ('` + fileNames.join(`', '`) + `');`;
    
      var resultSet3 = snowflake.execute({ sqlText: alterCommand });
    
      var results = [];
      while (resultSet3.next())
      {
        results.push(resultSet3.getColumnValue(1) + ' -> ' + resultSet3.getColumnValue(2));
      }
    
      return results.length + ' files: \n' + results.join('\n');
    
      $$;
    
      CREATE or replace PROCEDURE exttable_bucket_relative_path(external_table_name varchar)
      RETURNS varchar
      LANGUAGE javascript
      EXECUTE AS CALLER
      AS
      $$
      var resultSet = snowflake.execute({ sqlText:
        `show external tables like '` + EXTERNAL_TABLE_NAME + `';`
      });
    
      resultSet.next();
      var location = resultSet.getColumnValue(10);
    
      var relPath = location.split('/').slice(3).join('/');
      return relPath.endsWith("/") ? relPath : relPath + "/";
    
      $$;
    
  2. Rufen Sie die gespeicherte Prozedur auf:

    -- Remove all files from the exttable external table metadata:
    call remove_old_files('exttable', 0);
    
    -- Remove files staged longer than 90 days ago from the exttable external table metadata:
    call remove_old_files('exttable', 90);
    

    Alternativ können Sie mit CREATE TASK eine Aufgabe erstellen, die die gespeicherte Prozedur periodisch aufruft, um ältere Dateien regelmäßig aus den Metadaten der externen Tabelle zu entfernen.

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.