Abfragen von Metadaten für bereitgestellte Dateien

Snowflake generiert automatisch Metadaten für Dateien in internen (d. h. Snowflake) Stagingbereichen oder externen (Amazon S3, Google Cloud Storage oder Microsoft Azure) Stagingbereichen. Diese Metadaten werden in virtuellen Spalten „gespeichert“ und können für Folgendes verwendet werden:

Unter diesem Thema:

Spalten für Metadaten

Derzeit können die folgenden Metadatenspalten abgefragt oder in Tabellen kopiert werden:

METADATA$FILENAME

Name der Staging-Datendatei, zu der die aktuelle Zeile gehört. Enthält den Pfad zur Datendatei im Stagingbereich.

METADATA$FILE_ROW_NUMBER

Zeilennummer für jeden Datensatz in der Staging-Datendatei.

Einschränkungen für Abfragen

Abfragebeispiele

Beispiel 1: Abfragen der Metadatenspalten einer CSV-Datei

Im folgenden Beispiel wird das Staging mehrerer CSV-Datendateien (mit dem gleichen Dateiformat) und das anschließende Abfragen der Metadatenspalten sowie der regulären 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
    
  • /tmp/data2.csv enthält zwei Datensätze:

    e|f
    g|h
    

So führen Sie die Bereitstellung und Abfrage 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 a data file
PUT file:///tmp/data*.csv @mystage1;

-- Query the filename and row number metadata columns and the regular data columns in the staged file
-- Note that the table alias is provided to make the statement easier to read and is not required
SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, t.$1, t.$2 FROM @mystage1 (file_format => myformat) t;

+-------------------+--------------------------+----+----+
| METADATA$FILENAME | METADATA$FILE_ROW_NUMBER | $1 | $2 |
|-------------------+--------------------------+----+----|
| data2.csv.gz      |                        1 | e  | f  |
| data2.csv.gz      |                        2 | g  | h  |
| data1.csv.gz      |                        1 | a  | b  |
| data1.csv.gz      |                        2 | c  | d  |
+-------------------+--------------------------+----+----+

SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, t.$1, t.$2 FROM @mystage1 t;

+-------------------+--------------------------+-----+------+
| METADATA$FILENAME | METADATA$FILE_ROW_NUMBER | $1  | $2   |
|-------------------+--------------------------+-----+------|
| data2.csv.gz      |                        1 | e|f | NULL |
| data2.csv.gz      |                        2 | g|h | NULL |
| data1.csv.gz      |                        1 | a|b | NULL |
| data1.csv.gz      |                        2 | c|d | NULL |
+-------------------+--------------------------+-----+------+

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. Details dazu finden Sie im nächsten Beispiel.

Beispiel 2: Abfragen der Metadatenspalten einer JSON-Datei

In diesem Beispiel wird das Bereitstellen einer JSON-Datendatei mit den folgenden Objekten und das anschließende Abfragen der Metadatenspalten sowie der Objekte in der Datei veranschaulicht:

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

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 die Bereitstellung und Abfrage 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 a data file
PUT file:///tmp/data1.json @mystage2;

-- Query the filename and row number metadata columns and the regular data columns in the staged file
SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, parse_json($1) FROM @mystage2/data1.json.gz;

+-------------------+--------------------------+----------------+
| METADATA$FILENAME | METADATA$FILE_ROW_NUMBER | PARSE_JSON($1) |
|-------------------+--------------------------+----------------|
| data1.json.gz     |                        1 | {              |
|                   |                          |   "a": {       |
|                   |                          |     "b": "x1", |
|                   |                          |     "c": "y1"  |
|                   |                          |   }            |
|                   |                          | }              |
| data1.json.gz     |                        2 | {              |
|                   |                          |   "a": {       |
|                   |                          |     "b": "x2", |
|                   |                          |     "c": "y2"  |
|                   |                          |   }            |
|                   |                          | }              |
+-------------------+--------------------------+----------------+

Beispiel 3: Laden von Metadatenspalten in eine Tabelle

Der Befehl COPY INTO <Tabelle> unterstützt das Kopieren von Metadaten aus Staging-Datendateien in eine Zieltabelle. Verwenden Sie die Datentransformationssyntax (d. h. eine SELECT-Liste) in Ihrer COPY-Anweisung. Weitere Informationen zur Transformation von Daten mit einer COPY-Anweisung finden Sie unter Transformieren von Daten während eines Ladevorgangs.

Im folgenden Beispiel werden die Metadatenspalten und die regulären Datenspalten aus Beispiel 1: Abfragen der Metadatenspalten einer CSV-Datei in eine Tabelle geladen:

CREATE OR REPLACE TABLE table1 (
  filename varchar,
  file_row_number varchar,
  col1 varchar,
  col2 varchar
);

COPY INTO table1(filename, file_row_number, col1, col2)
  FROM (SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, t.$1, t.$2 FROM @mystage1/data1.csv.gz (file_format => myformat) t);

SELECT * FROM table1;

+--------------+-----------------+------+------+
| FILENAME     | FILE_ROW_NUMBER | COL1 | COL2 |
|--------------+-----------------+------+------|
| data1.csv.gz | 1               | a    | b    |
| data1.csv.gz | 2               | d    | e    |
+--------------+-----------------+------+------+