Abfragen von Daten in Stagingdateien

Snowflake unterstützt die Verwendung von Standard-SQL zum Abfragen von Datendateien, die sich in einem internen (d. h. Snowflake) oder einem benannten externen (Amazon S3, Google Cloud Storage oder Microsoft Azure) Stagingbereich befinden. Dies kann nützlich sein, um den Inhalt der Stagingdateien zu überprüfen/anzuzeigen, insbesondere vor dem Laden oder nach dem Entladen von Daten.

Darüber hinaus können bei einer Abfrage auf Staging-Daten durch Referenzieren der Metadatenspalten einer Staging-Datei zusätzliche Informationen zu der Datei wie Dateiname und Zeilennummern zurückgeben werden.

Snowflake nutzt die Unterstützung von Abfragen auf Staging-Daten, um das Transformieren von Daten während des Ladevorgangs zu ermöglichen.

Bemerkung

Diese Funktionalität ist in erster Linie nur für einfache Abfragen gedacht, insbesondere beim Laden und/oder Transformieren von Daten. Sie ist keinesfalls dafür vorgesehen, das Laden von Daten in Tabellen und das Abfragen von Tabellen zu ersetzen.

Unter diesem Thema:

Abfragesyntax und -parameter

Abfragen von Staging-Datendateien unter Verwendung einer SELECT-Anweisung mit folgender Syntax:

SELECT [<alias>.]$<file_col_num>[:<element>] [ , [<alias>.]$<file_col_num>[:<element>] , ...  ]
  FROM { <internal_location> | <external_location> }
  [ ( FILE_FORMAT => '<namespace>.<named_file_format>', PATTERN => '<regex_pattern>' ) ]
  [ <alias> ]
Copy

Die Syntax für das Transformieren von Daten während eines Ladevorgangs finden Sie unter COPY INTO <Tabelle>.

Wichtig

Die Liste der für einen externen Stagingbereich zurückgegebenen Objekte kann einen oder mehrere „Verzeichnis-Blobs“ enthalten. Dies sind im Wesentlichen Pfade, die mit einem Schrägstrich (/) enden. Beispiel:

LIST @my_gcs_stage;

+---------------------------------------+------+----------------------------------+-------------------------------+
| name                                  | size | md5                              | last_modified                 |
|---------------------------------------+------+----------------------------------+-------------------------------|
| my_gcs_stage/load/                    |  12  | 12348f18bcb35e7b6b628ca12345678c | Mon, 11 Sep 2019 16:57:43 GMT |
| my_gcs_stage/load/data_0_0_0.csv.gz   |  147 | 9765daba007a643bdff4eae10d43218y | Mon, 11 Sep 2019 18:13:07 GMT |
+---------------------------------------+------+----------------------------------+-------------------------------+
Copy

Diese Blobs werden aufgelistet, wenn Verzeichnisse über die Google Cloud Platform Console erstellt werden, anstatt andere von Google bereitgestellte Tools zu verwenden.

SELECT-Anweisungen, die auf einen Stagingbereich verweisen, können fehlschlagen, wenn die Objektliste Verzeichnis-Blobs enthält. Um Fehler durch Dateilisten von Stagingbereichen zu vermeiden, die Verzeichnis-Blobs enthalten, empfehlen wir die Verwendung der Dateimustererkennung (d. h. die PATTERN-Klausel) zum Erkennen der einzubeziehenden Dateien.

Erforderliche Parameter

[alias.]$file_col_num[:element] [ , [alias.]$file_col_num[:element] , ...  ]

Gibt einen expliziten Satz von Feldern/Spalten in Datendateien an, die entweder an einem internen oder einem externen Speicherort bereitgestellt werden, wobei:

alias

Gibt den optionalen „table“-Alias an, der gegebenenfalls in der FROM-Klausel definiert ist.

file_col_num

Gibt die Positionsnummer des Feldes bzw. der Spalte (in der Datei) an, das/die die zu ladenden Daten enthält (1 für das erste Feld, 2 für das zweite Feld usw.).

element

Gibt den Pfad und Elementnamen eines sich wiederholenden Wertes an (gilt nur für semistrukturierte Datendateien).

internal_location oder external_location

Gibt den Speicherort an, an dem die Datendateien bereitgestellt werden:

  • internal_location ist der URI-Spezifizierer für den Speicherort in Snowflake, an dem Dateien mit Daten bereitgestellt werden:

    @[namespace.]internal_stage_name[/path]

    Die Dateien befinden sich im angegebenen, benannten internen Stagingbereich.

    @[namespace.]%table_name[/path]

    Die Dateien befinden sich im Stagingbereich der angegebenen Tabelle.

    @~[/path]

    Die Dateien befinden sich im Stagingbereich des aktuellen Benutzers.

  • external_location ist der URI-Spezifizierer für den benannten externen Stagingbereich oder den externen Speicherort (Amazon S3, Google Cloud Storage oder Microsoft Azure), an dem Dateien mit Daten bereitgestellt werden:

    @[namespace.]external_stage_name[/path]

    Dateien befinden sich im angegebenen, benannten externen Stagingbereich.

Wobei:

  • namespace ist die Datenbank und/oder das Schema, in dem sich der interne oder externe Stagingbereich befindet. Es ist optional, ob in der Benutzersitzung aktuell eine Datenbank und ein Schema verwendet werden, andernfalls ist es erforderlich.

  • Der optionale Parameter path schränkt die Menge der abzufragenden Dateien auf die Dateien unter dem Ordnerpräfix ein. Wenn path angegeben ist, aber im Pfad keine Datei explizit genannt wird, werden alle Datendateien im Pfad abgefragt.

Bemerkung

  • Die URI-Zeichenfolge für einen externen Speicherort (Amazon S3, Google Cloud Storage oder Microsoft Azure) muss in einfache Anführungszeichen eingeschlossen sein. Sie können jedoch jede URI-Zeichenfolge in einfache Anführungszeichen einschließen, wodurch in Speicherort- und Dateinamen die Verwendung von Sonderzeichen, einschließlich Leerzeichen, möglich ist. Beispiel:

    Intern:

    '@~/path 1/file 1.csv'

    '@%my table/path 1/file 1.csv'

    '@my stage/path 1/file 1.csv'

  • Relative Pfadmodifikatoren wie /./ und /../ werden literal interpretiert, da „Pfade“ literale Präfixe für einen Namen sind. Beispiel:

    S3:

    COPY INTO mytable FROM @mystage/./../a.csv

    In diesen COPY-Anweisungen sucht das System nach einer Datei, die am Speicherort literal ./../a.csv benannt ist.

Optionale Parameter

( FILE_FORMAT => 'namespace.named_file_format' )

Gibt ein benanntes Dateiformat an, das das Format der Staging-Datendateien für die Abfrage beschreibt.

Beachten Sie, dass dieser Parameter optional ist, wenn eine der folgenden Bedingungen erfüllt ist:

  • Die Dateien sind im Standard-Dateiformat (CSV) mit den folgenden Standardtrennzeichen formatiert: , (als Feldtrennzeichen) und das Neue-Zeile-Zeichen (als Datensatz-Trennzeichen).

  • Die Dateien befinden sich in einem internen oder externen Stagingbereich, und die Definition des Stagingbereichs beschreibt das Dateiformat.

Wenn Sie ein Dateiformat im aktuellen Namespace Ihrer Benutzersitzung referenzieren, können Sie die einfachen Anführungszeichen um den Formatbezeichner herum weglassen.

Andernfalls ist dieser Parameter erforderlich. Weitere Details dazu finden Sie unter Dateiformate (unter diesem Thema).

namespace gibt optional die Datenbank und/oder das Schema für die Tabelle in der Form database_name.schema_name oder schema_name an. Die Angabe ist optional, wenn in der Benutzersitzung aktuell eine Datenbank und ein Schema verwendet werden, andernfalls ist die Angabe obligatorisch.

Wenn der Bezeichner Leerzeichen, Sonderzeichen oder Zeichen in Groß- und Kleinschreibung enthält, muss die gesamte Zeichenfolge in doppelte Anführungszeichen eingeschlossen werden. Bei Bezeichnern, die in doppelte Anführungszeichen eingeschlossen sind, ist auch die Groß-/Kleinschreibung zu beachten.

PATTERN => 'regex_pattern'

Eine Musterzeichenfolge mit regulären Ausdrücken, die in einfache Anführungszeichen eingeschlossen ist und die die Dateinamen und/oder Pfade zum externen Stagingbereich angibt, die übereinstimmen sollen.

Tipp

Um die beste Leistung zu erzielen, sollten Sie vermeiden, Muster anzuwenden, die eine große Anzahl von Dateien filtern.

alias

Gibt einen „table“-Alias für den internen/externen Speicherort an, der als Stagingbereich für die Bereitstellung der Dateien dient.

Dateiformate

Um eine Staging-Datendatei zu analysieren, ist es notwendig, ihr Dateiformat zu beschreiben. Das Standard-Dateiformat ist durch Trennzeichen getrennter UTF-8-Text (d. h. CSV), mit dem Kommazeichen (,) als Feldtrennzeichen und dem Zeilenumbruchzeichen als Datensatz-Trennzeichen. Wenn die Quelldaten in einem anderen Format vorliegen (JSON, Avro usw.), müssen Sie den entsprechenden Dateiformattyp (und Optionen) angeben.

Um Dateiformatoptionen explizit anzugeben, legen Sie diese auf eine der folgenden Arten fest:

Abfragen von Staging-Datendateien:

Als Dateiformatoptionen, die für ein benanntes Dateiformat oder Stagingobjekt angegeben werden. Das benannte Dateiformat/Stagingobjekt kann dann in der SELECT-Anweisung referenziert werden.

Laden von Spalten aus Staging-Datendateien:
  • Als Dateiformatoptionen, die direkt in COPY INTO <Tabelle> angegeben werden.

  • Als Dateiformatoptionen, die für ein benanntes Dateiformat oder Stagingobjekt angegeben werden. Das benannte Dateiformat/Stagingobjekt kann dann in der COPY INTO <Tabelle>-Anweisung referenziert werden.

Abfragebeispiele

Beispiel 1: Abfragen von Spalten in einer CSV-Datei

Im folgenden Beispiel wird das Staging mehrerer CSV-Datendateien (mit dem gleichen Dateiformat) und das anschließende Abfragen der Datenspalten in den Dateien veranschaulicht.

In diesem Beispiel wird davon ausgegangen, dass die Dateien folgende Namen haben und sich im Stammverzeichnis einer macOS- oder Linux-Umgebung befinden:

  • /tmp/data1.csv enthält zwei Datensätze:

    a|b
    c|d
    
    Copy
  • /tmp/data2.csv enthält zwei Datensätze:

    e|f
    g|h
    
    Copy

So führen Sie das Staging und Abfragen der Dateien aus:

-- Create a file format.
CREATE OR REPLACE FILE FORMAT myformat TYPE = 'csv' FIELD_DELIMITER = '|';

-- Create an internal stage.
CREATE OR REPLACE STAGE mystage1;

-- Stage the data files.
PUT file:///tmp/data*.csv @mystage1;

-- Query the filename and row number metadata columns and the regular data columns in the staged file.
-- Optionally apply pattern matching to the set of files in the stage and optional path.
-- Note that the table alias is provided to make the statement easier to read and is not required.
SELECT t.$1, t.$2 FROM @mystage1 (file_format => 'myformat', pattern=>'.*data.*[.]csv.gz') t;

+----+----+
| $1 | $2 |
|----+----|
| a  | b  |
| c  | d  |
| e  | f  |
| g  | h  |
+----+----+

SELECT t.$1, t.$2 FROM @mystage1 t;

+-----+------+
| $1  | $2   |
|-----+------|
| a|b | NULL |
| c|d | NULL |
| e|f | NULL |
| g|h | NULL |
+-----+------+
Copy

Bemerkung

Das Dateiformat wird in diesem Beispiel benötigt, um die Felder in den Stagingdateien korrekt zu analysieren. Bei der zweiten Abfrage wird das Dateiformat weggelassen, wodurch das Feldtrennzeichen | ignoriert wird und die Werte für $1 und $2 zurückgegeben werden.

Wenn das Dateiformat jedoch in der Stagingbereichsdefinition enthalten ist, können Sie es in der SELECT-Anweisung weglassen. Siehe Beispiel 3: Abfragen von Elementen in einer JSON-Datei.

Beispiel 2: Aufrufen von Funktionen beim Abfragen von Staging-Datendateien

Ermitteln Sie den ASCII-Code für das erste Zeichen jeder Spalte in den Staging-Datendateien aus Beispiel 1: Abfragen von Spalten in einer CSV-Datei:

SELECT ascii(t.$1), ascii(t.$2) FROM @mystage1 (file_format => myformat) t;

+-------------+-------------+
| ASCII(T.$1) | ASCII(T.$2) |
|-------------+-------------|
|          97 |          98 |
|          99 |         100 |
|         101 |         102 |
|         103 |         104 |
+-------------+-------------+
Copy

Bemerkung

Wenn das Dateiformat in der Stagingbereichsdefinition enthalten ist, können Sie es in der SELECT-Anweisung weglassen. Siehe Beispiel 3: Abfragen von Elementen in einer JSON-Datei.

Beispiel 3: Abfragen von Elementen in einer JSON-Datei.

In diesem Beispiel wird das Staging einer JSON-Datendatei, die die folgenden Objekte enthält, und das anschließende Abfragen einzelner Elemente in den Objekten der Datei veranschaulicht:

{"a": {"b": "x1","c": "y1"}},
{"a": {"b": "x2","c": "y2"}}
Copy

In diesem Beispiel wird davon ausgegangen, dass die Datei den Namen /tmp/data1.json trägt und sich im Stammverzeichnis einer macOS- oder Linux-Umgebung befindet.

So führen Sie das Staging und Abfragen der Datei aus:

-- Create a file format
CREATE OR REPLACE FILE FORMAT my_json_format TYPE = 'json';

-- Create an internal stage
CREATE OR REPLACE STAGE mystage2 FILE_FORMAT = my_json_format;

-- Stage the data file
PUT file:///tmp/data1.json @mystage2;

-- Query the repeating a.b element in the staged file
SELECT parse_json($1):a.b FROM @mystage2/data1.json.gz;

+--------------------+
| PARSE_JSON($1):A.B |
|--------------------|
| "x1"               |
| "x2"               |
+--------------------+
Copy