Query metadata for staged files

Snowflakeは、内部（つまり、Snowflake）ステージ、または外部（Amazon S3、Google Cloud Storage、またはMicrosoft Azure）ステージのファイルにメタデータを自動的に生成します。このメタデータは、次のような仮想列に「格納」され、以下の作業を実行することが可能です。

  • 標準の SELECT ステートメントを使用してクエリする。

  • COPY INTO <テーブル> を使用して、通常のデータ列とともにテーブルにロードする。ステージングされたデータファイルのクエリに関する一般情報については、 Query data in staged files をご参照ください。

Metadata columns

現在、次のメタデータ列をクエリしたり、テーブルにコピーしたりできます。

METADATA$FILENAME

現在の行が属するステージングされたデータファイルの名前。データファイルへのフルパスを含みます。

METADATA$FILE_ROW_NUMBER

ステージングされたデータファイルの各記録の行番号。

METADATA$FILE_CONTENT_KEY

現在の行が属するステージングされたデータファイルのチェックサム。

METADATA$FILE_LAST_MODIFIED

現在の行が属するステージングされたデータファイルの最終更新タイムスタンプ。TIMESTAMP_NTZ として返されます。

METADATA$START_SCAN_TIME

ステージングされたデータファイルにある各記録の操作開始タイムスタンプ。TIMESTAMP_LTZ として返されます。

Query limitations

Query examples

Example 1: Query the metadata columns for a CSV file

次の例は、複数の CSV データファイル（同じファイル形式）をステージングし、ファイル内のメタデータ列と通常のデータ列をクエリする方法を示しています。

この例では、ファイルが次の名前を持ち、 macOS またはLinux環境のルートディレクトリにあると想定しています。

  • /tmp/data1.csv には、2つのレコードが含まれます。

    a|b
c|d

  • /tmp/data2.csv には、2つのレコードが含まれます。

    e|f
g|h

ファイルをステージングおよびクエリするには、次を実行します。

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

注釈

この例では、ステージングされたファイルのフィールドを正しく解析するために、ファイル形式が必要です。2番目のクエリでは、ファイル形式が省略されているため、 | フィールド区切り文字が無視され、 $1 および $2 の値が返されます。

ただし、ファイル形式がステージ定義に含まれている場合は、 SELECT ステートメントから省略できます。詳細については、次の例をご参照ください。

Example 2: Query the metadata columns for a JSON file

この例では、次のオブジェクトを含む JSON データファイルをステージングし、ファイル内のオブジェクトとともにメタデータ列をクエリします。

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

この例では、ファイルの名前が /tmp/data1.json で、macOS またはLinux環境のルートディレクトリにあると想定しています。

ファイルをステージングおよびクエリするには、次を実行します。

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

COPY INTO <テーブル> コマンドは、ステージングされたデータファイルからターゲットテーブルへのメタデータのコピーをサポートしています。COPY ステートメントでデータ変換構文（SELECT リスト）を使用します。COPY ステートメントを使用したデータ変換の詳細については、『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    |
+--------------+-----------------+---------------------------+-------------------------+-------------------------------+------+------+