Abfragen von Metadaten für bereitgestellte Dateien¶

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 Abfragen von Daten in Stagingdateien.

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

Einschränkungen für Abfragen¶

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:

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
```
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 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 | aa387aabb2ccedaaaaac123b8 |     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 | 2289aab2abcdeaacaaac348d0 |     2022-09-10 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 | aa387aabb2ccedaaaaac123b8 |     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 | 2289aab2abcdeaacaaac348d0 |     2022-09-10 11:15:55.000 |  2023-02-02 01:31:00.778 +0000| c|d | 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. 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"}}

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

Copy

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 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               | 2289aab2abcdeaacaaac348d0 | 2022-09-10 11:15:55.000 | 2023-02-02 01:31:00.778 +0000 | c    | d    |
+--------------+-----------------+---------------------------+-------------------------+-------------------------------+------+------+

Copy