Query metadata for staged files¶

Snowflake 会自动为内部（即 Snowflake）暂存区或外部（Amazon S3、Google Cloud Storage 或 Microsoft Azure）暂存区中的文件生成元数据。此元数据“存储”在虚拟列中，这些列可以：

使用标准 SELECT 语句查询。
与常规数据列一起，使用 COPY INTO <table> 加载到表中。有关查询暂存数据文件的一般信息，请参阅 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 包含两条记录：
```
a|b
c|d
```
/tmp/data2.csv 包含两条记录：
```
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 |
+-------------------+--------------------------+---------------------------+-----------------------------+-------------------------------+-----+------+

备注

在此示例中，需要文件格式才能正确分析暂存文件中的字段。在第二个查询中，文件格式省略，导致 | 字段分隔符被忽略，并返回 $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 <table> 命令支持将元数据从暂存数据文件复制到目标表中。在 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    |
+--------------+-----------------+---------------------------+-------------------------+-------------------------------+------+------+