Query metadata for staged files¶

Snowflake generiert automatisch Metadaten für Dateien in internen (d. h. Snowflake) 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:

Abfragen der Metadaten mit einer standardmäßigen SELECT-Anweisung.
Laden der Metadaten zusammen mit den regulären Datenspalten in eine Tabelle mithilfe von COPY INTO <Tabelle>. Allgemeine Informationen zum Abfragen von Staging-Datendateien finden Sie unter Query data in staged files.

Metadata columns¶

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 vollständigen Pfad zu der Datendatei.
METADATA$FILE_ROW_NUMBER: Zeilennummer für jeden Datensatz in der Staging-Datendatei.
METADATA$FILE_CONTENT_KEY: Prüfsumme der Staging-Datendatei, zu der die aktuelle Zeile gehört.
METADATA$FILE_LAST_MODIFIED: Zeitstempel der letzten Änderung an der Staging-Datendatei, zu der die aktuelle Zeile gehört. Wird als TIMESTAMP_NTZ zurückgegeben.
METADATA$START_SCAN_TIME: Zeitstempel für den Beginn der Operation für jeden Datensatz in der Staging-Datendatei. Wird als TIMESTAMP_LTZ zurückgegeben.

Query limitations¶

Metadaten können nicht in bestehende Tabellenzeilen eingefügt werden.
Metadatenspalten können nur nach Namen abgefragt werden. Sie sind daher bei keiner der folgenden Anweisungen in die Ausgabe einbezogen:

Query examples¶

Example 1: Query the metadata columns for a CSV file¶

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 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 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, METADATA$FILE_CONTENT_KEY, METADATA$FILE_LAST_MODIFIED, METADATA$START_SCAN_TIME, t.$1, t.$2 FROM @mystage1 (file_format => myformat) t;

+-------------------+--------------------------+---------------------------+-----------------------------+-------------------------------+----+----+
| METADATA$FILENAME | METADATA$FILE_ROW_NUMBER | METADATA$FILE_CONTENT_KEY | METADATA$FILE_LAST_MODIFIED |      METADATA$START_SCAN_TIME | $1 | $2 |
|-------------------+--------------------------+---------------------------+-----------------------------+-------------------------------+----+----|
| data2.csv.gz      |                        1 | aaa11bb2cccccaaaaac1234d9 |     2022-05-01 10:15:57.000 |  2023-02-02 01:31:00.713 +0000| e  | f  |
| data2.csv.gz      |                        2 | aaa11bb2cccccaaaaac1234d9 |     2022-05-01 10:05:35.000 |  2023-02-02 01:31:00.755 +0000| g  | h  |
| data1.csv.gz      |                        1 | 39ab11bb2cdeacdcdac1234d9 |     2022-08-03 10:15:26.000 |  2023-02-02 01:31:00.778 +0000| a  | b  |
| data1.csv.gz      |                        2 | 39ab11bb2cdeacdcdac1234d9 |     2022-08-03 11:15:55.000 |  2023-02-02 01:31:00.778 +0000| c  | d  |
+-------------------+--------------------------+---------------------------+-----------------------------+-------------------------------+----+----+

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

+-------------------+--------------------------+---------------------------+-----------------------------+-------------------------------+-----+------+
| METADATA$FILENAME | METADATA$FILE_ROW_NUMBER | METADATA$FILE_CONTENT_KEY | METADATA$FILE_LAST_MODIFIED |      METADATA$START_SCAN_TIME | $1  | $2   |
|-------------------+--------------------------+---------------------------+-----------------------------+-------------------------------+-----+------|
| data2.csv.gz      |                        1 | aaa11bb2cccccaaaaac1234d9 |     2022-05-01 10:15:57.000 |  2023-02-02 01:31:00.713 +0000| e|f | NULL |
| data2.csv.gz      |                        2 | aaa11bb2cccccaaaaac1234d9 |     2022-05-01 10:05:35.000 |  2023-02-02 01:31:00.755 +0000| g|h | NULL |
| data1.csv.gz      |                        1 | 39ab11bb2cdeacdcdac1234d9 |     2022-08-03 10:15:26.000 |  2023-02-02 01:31:00.778 +0000| a|b | NULL |
| data1.csv.gz      |                        2 | 39ab11bb2cdeacdcdac1234d9 |     2022-08-03 11:15:55.000 |  2023-02-02 01:31:00.778 +0000| 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.

Example 2: Query the metadata columns for a JSON file¶

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 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 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"  |
|                   |                          |   }            |
|                   |                          | }              |
+-------------------+--------------------------+----------------+

Example 3: Load metadata columns into a table¶

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 Transform data during a load.

The following example loads the metadata columns and regular data columns from Example 1: Query the metadata columns for a CSV file into a table:

CREATE OR REPLACE TABLE table1 (
  filename varchar,
  file_row_number int,
  file_content_key varchar,
  file_last_modified timestamp_ntz,
  start_scan_time timestamp_ltz,
  col1 varchar,
  col2 varchar
);

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

SELECT * FROM table1;

+--------------+-----------------+---------------------------+-------------------------+-------------------------------+------+------+
| FILENAME     | FILE_ROW_NUMBER | FILE_CONTENT_KEY          | FILE_LAST_MODIFIED      |  START_SCAN_TIME              | COL1 | COL2 |
|--------------+-----------------+---------------------------+-------------------------+-------------------------------+------+------+
| data1.csv.gz | 1               | 39ab11bb2cdeacdcdac1234d9 | 2022-08-03 10:15:26.000 | 2023-02-02 01:31:00.778 +0000 | a    | b    |
| data1.csv.gz | 2               | 39ab11bb2cdeacdcdac1234d9 | 2022-09-10 11:15:55.000 | 2023-02-02 01:31:00.778 +0000 | c    | d    |
+--------------+-----------------+---------------------------+-------------------------+-------------------------------+------+------+