스테이징된 파일에 대한 메타데이터 쿼리하기

Snowflake는 내부(즉, Snowflake) 스테이지 또는 외부(Amazon S3, Google Cloud Storage 또는 Microsoft Azure) 스테이지에 파일에 대한 메타데이터를 자동으로 생성합니다. 이 메타데이터는 다음을 수행할 수 있는 가상의 열에 《저장》됩니다.

이 항목의 내용:

메타데이터 열

현재 쿼리하거나 테이블에 복사할 수 있는 메타데이터 열은 다음과 같습니다.

METADATA$FILENAME

현재 행이 속한 스테이징된 데이터 파일의 이름. 스테이지의 데이터 파일에 대한 경로를 포함합니다.

METADATA$FILE_ROW_NUMBER

데이터 파일이 스테이징된 컨테이너에서 각 레코드의 행 번호.

쿼리 제한 사항

쿼리의 예

예 1: CSV 파일의 메타데이터 열 쿼리하기

다음 예에서는 여러 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, t.$1, t.$2 FROM @mystage1 (file_format => myformat) t;

+-------------------+--------------------------+----+----+
| METADATA$FILENAME | METADATA$FILE_ROW_NUMBER | $1 | $2 |
|-------------------+--------------------------+----+----|
| data2.csv.gz      |                        1 | e  | f  |
| data2.csv.gz      |                        2 | g  | h  |
| data1.csv.gz      |                        1 | a  | b  |
| data1.csv.gz      |                        2 | c  | d  |
+-------------------+--------------------------+----+----+

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

+-------------------+--------------------------+-----+------+
| METADATA$FILENAME | METADATA$FILE_ROW_NUMBER | $1  | $2   |
|-------------------+--------------------------+-----+------|
| data2.csv.gz      |                        1 | e|f | NULL |
| data2.csv.gz      |                        2 | g|h | NULL |
| data1.csv.gz      |                        1 | a|b | NULL |
| data1.csv.gz      |                        2 | c|d | NULL |
+-------------------+--------------------------+-----+------+

참고

이 예에서 스테이징된 파일의 필드를 올바르게 구문 분석하려면 파일 형식이 필요합니다. 두 번째 쿼리에서는 파일 형식이 생략되어 | 필드 구분 기호가 무시되며 결과적으로 $1$2 에 대한 값이 반환됩니다.

그러나 스테이지 정의에 파일 형식이 포함된 경우에는 SELECT 문에서 생략할 수 있습니다. 자세한 내용은 다음 예를 참조하십시오.

예 2: JSON 파일의 메타데이터 열 쿼리하기

이 예에서는 다음 오브젝트가 포함된 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"  |
|                   |                          |   }            |
|                   |                          | }              |
+-------------------+--------------------------+----------------+

예 3: 메타데이터 열을 테이블로 로드하기

COPY INTO <테이블> 명령은 스테이징된 데이터 파일에서 대상 테이블로의 메타데이터 복사를 지원합니다. COPY 문에서 데이터 변환 구문(즉, SELECT 목록)을 사용하십시오. COPY 문을 사용한 데이터 변환에 대한 자세한 내용은 로드 중 데이터 변환하기 를 참조하십시오.

다음 예에서는 예 1: CSV 파일의 메타데이터 열 쿼리하기 의 메타데이터 열과 일반 데이터 열을 테이블로 로드합니다.

CREATE OR REPLACE TABLE table1 (
  filename varchar,
  file_row_number varchar,
  col1 varchar,
  col2 varchar
);

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

SELECT * FROM table1;

+--------------+-----------------+------+------+
| FILENAME     | FILE_ROW_NUMBER | COL1 | COL2 |
|--------------+-----------------+------+------|
| data1.csv.gz | 1               | a    | b    |
| data1.csv.gz | 2               | d    | e    |
+--------------+-----------------+------+------+
맨 위로 이동