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 에는 다음의 두 레코드가 포함됩니다.
```
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 <테이블> 명령은 스테이징된 데이터 파일에서 대상 테이블로의 메타데이터 복사를 지원합니다. 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    |
+--------------+-----------------+---------------------------+-------------------------+-------------------------------+------+------+