스테이징된 파일에서 데이터 쿼리하기

Snowflake는 표준 SQL을 사용하여 내부(예: Snowflake) 스테이지 또는 명명된 외부(Amazon S3, Google Cloud Storage 또는 Microsoft Azure) 스테이지에 위치한 데이터 파일의 쿼리를 지원합니다. 이러한 기능은 특히 데이터의 로드 전 또는 언로드 후 스테이징된 파일의 내용을 검사/확인하는 데 유용할 수 있습니다.

또한, 스테이징된 파일에서 메타데이터 열 을 참조하면 스테이징된 데이터 쿼리에서 파일에 대한 파일 이름 및 행 번호 등의 추가 정보를 반환할 수 있습니다.

Snowflake는 로딩 중에 데이터 변환 이 가능하도록 스테이징된 데이터 쿼리 지원을 사용합니다.

참고

이 기능은 특히 데이터를 로드 및/또는 변환할 때 주로 간단한 쿼리를 수행하기 위한 것이지만, 테이블로의 데이터 로드 및 테이블에서 쿼리 수행을 대체하기 위한 용도가 아닙니다.

이 항목의 내용:

쿼리 구문 및 매개 변수

다음 구문과 함께 SELECT 문을 사용하여 스테이징된 데이터 파일을 쿼리합니다.

SELECT [<alias>.]$<file_col_num>[:<element>] [ , [<alias>.]$<file_col_num>[:<element>] , ...  ]
  FROM { <internal_location> | <external_location> }
  [ ( FILE_FORMAT => '<namespace>.<named_file_format>', PATTERN => '<regex_pattern>' ) ]
  [ <alias> ]
Copy

로드 중에 데이터를 변환하기 위한 구문에 대해서는 COPY INTO <테이블> 를 참조하십시오.

중요

외부 스테이지에 대해 반환된 오브젝트의 목록에는 1개 이상의 “디렉터리 blob”가 포함될 수 있으며, 이는 기본적으로 슬래시 문자(/)로 끝나는 경로입니다. 예를 들면 다음과 같습니다.

LIST @my_gcs_stage;

+---------------------------------------+------+----------------------------------+-------------------------------+
| name                                  | size | md5                              | last_modified                 |
|---------------------------------------+------+----------------------------------+-------------------------------|
| my_gcs_stage/load/                    |  12  | 12348f18bcb35e7b6b628ca12345678c | Mon, 11 Sep 2019 16:57:43 GMT |
| my_gcs_stage/load/data_0_0_0.csv.gz   |  147 | 9765daba007a643bdff4eae10d43218y | Mon, 11 Sep 2019 18:13:07 GMT |
+---------------------------------------+------+----------------------------------+-------------------------------+
Copy

Google에서 제공하는 다른 도구를 사용하지 않고 Google Cloud Platform 콘솔에서 디렉터리를 만들 때 이러한 blob이 나열됩니다.

오브젝트 목록에 디렉터리 blob이 포함되면 스테이지를 참조하는 SELECT 문이 실패할 수 있습니다. 오류를 방지하려면 스테이지의 파일 목록에 디렉터리 blob이 포함된 경우 파일 패턴(즉, PATTERN 절) 일치를 사용하여 포함할 파일을 식별하는 것이 좋습니다.

필수 매개 변수

[alias.]$file_col_num[:element] [ , [alias.]$file_col_num[:element] , ...  ]

내부 또는 외부 위치에 스테이징된 데이터 파일에 명시적인 필드/열 세트를 지정합니다. 여기서,

alias

FROM 절에 정의된 선택적 “테이블” 별칭을 지정합니다.

file_col_num

로딩할 데이터가 있는 (파일 내) 필드/열의 위치 번호를 지정합니다(첫 번째 필드는 1 , 두 번째 필드는 2 등).

element

반복되는 값의 경로 및 요소 이름을 지정합니다(반정형 데이터 파일 에만 적용됨).

internal_location 또는 external_location

데이터 파일이 스테이징되는 위치를 지정합니다.

  • internal_location 는 데이터가 포함된 파일이 스테이징되는 Snowflake 내 위치에 대한 URI 지정자입니다.

    @[namespace.]internal_stage_name[/path]

    파일이 지정된 명명된 내부 스테이지에 있습니다.

    @[namespace.]%table_name[/path]

    파일이 지정된 테이블의 스테이지에 있습니다.

    @~[/path]

    파일이 현재 사용자의 스테이지에 있습니다.

  • external_location 는 명명된 외부 스테이지 또는 데이터가 포함된 파일이 스테이징되는 외부 위치(Amazon S3, Google Cloud Storage 또는 Microsoft Azure)에 대한 URI 지정자입니다.

    @[namespace.]external_stage_name[/path]

    파일이 지정된 명명된 외부 스테이지에 있습니다.

여기서:

  • namespace 는 내부 또는 외부 스테이지가 위치하는 데이터베이스 및/또는 스키마입니다. 데이터베이스와 스키마가 현재 사용자 세션 내에서 사용 중인 경우 이는 선택 사항 입니다. 사용 중이지 않으면 필수 사항입니다.

  • 선택 사항인 path 매개 변수는 쿼리할 수 있는 파일 세트를 폴더 접두사 아래의 파일로 제한합니다. path 가 지정되어 있지만 경로에 명시적으로 명명된 파일이 없는 경우, 경로의 모든 데이터 파일이 쿼리됩니다.

참고

  • 외부 저장소 위치(Amazon S3, Google Cloud Storage 또는 Microsoft Azure)에 대한 URI 문자열은 반드시 작은따옴표로 묶어야 하지만, 모든 URI 문자열을 작은따옴표로 묶어도 됩니다. 그러면 위치 및 파일 이름에 공백을 포함한 특수 문자를 사용할 수 있습니다. 예:

    내부:

    '@~/path 1/file 1.csv'

    '@%my table/path 1/file 1.csv'

    '@my stage/path 1/file 1.csv'

  • “경로”는 이름의 리터럴 접두사이므로, /.//../ 와 같은 상대 경로 한정자는 문자 그대로 해석됩니다. 예:

    S3:

    COPY INTO mytable FROM @mystage/./../a.csv

    이러한 COPY 문에서 시스템은 저장소 위치에 문자 그대로 이름이 ./../a.csv 로 지정된 파일을 찾습니다.

선택적 매개 변수

( FILE_FORMAT => 'namespace.named_file_format' )

쿼리할 스테이징된 데이터 파일의 형식을 설명하는 명명된 파일 형식을 지정합니다.

다음 조건 중 1개가 true인 경우 이 매개 변수는 선택 사항입니다.

  • 파일이 기본 구분 기호인 , (필드 구분 기호) 및 줄 바꿈 문자(레코드 구분 기호)를 사용하여 기본 파일 형식(CSV)으로 형식이 지정됩니다.

  • 파일이 내부 또는 외부 스테이지에 위치하며 스테이지 정의가 파일 형식을 설명합니다.

사용자 세션의 현재 네임스페이스에서 파일 형식을 참조하는 경우 형식 식별자를 둘러싸는 작은따옴표를 생략할 수 있습니다.

그렇지 않으면, 이 매개 변수는 필수입니다. 자세한 내용은 이 항목의 파일 형식 섹션을 참조하십시오.

namespacedatabase_name.schema_name 또는 schema_name 형식으로 테이블의 데이터베이스 및/또는 스키마를 선택적으로 지정합니다. 데이터베이스와 스키마가 현재 사용자 세션 내에서 사용 중인 경우 이는 선택 사항 입니다. 사용 중이지 않으면 필수 사항입니다.

식별자에 공백, 특수 문자 또는 대/소문자가 혼합된 문자가 포함된 경우 전체 문자열을 큰따옴표로 묶어야 합니다. 큰따옴표로 묶인 식별자도 대/소문자를 구분합니다.

PATTERN => 'regex_pattern'

작은따옴표로 묶인 정규식 패턴 문자열로, 외부 스테이지에서 일치시킬 파일 이름 및/또는 경로를 지정합니다.

최상의 성능을 위해서는 많은 수의 파일을 필터링하는 패턴을 적용하지 마십시오.

alias

파일이 스테이징되는 내부/외부 위치에 대한 “테이블” 별칭을 지정합니다.

파일 형식

스테이징된 데이터 파일을 구문 분석하려면 해당 파일 형식을 설명해야 합니다. 기본 파일 형식은 쉼표 문자(,)를 필드 구분 기호로 사용하고 줄 바꿈 문자를 레코드 구분 기호로 사용하는 문자로 구분된 UTF-8 텍스트(즉, CSV)입니다. 소스 데이터가 다른 타입(JSON, Avro 등)인 경우에는 해당 파일 형식 타입(및 옵션)을 지정해야 합니다.

파일 형식 옵션을 명시적으로 지정하려면 다음 방법 중 하나로 설정합니다.

스테이징된 데이터 파일 쿼리하기:

명명된 파일 형식 또는 스테이지 오브젝트에 대해 지정된 파일 형식 옵션으로 설정. 그러면 SELECT 문에서 명명된 파일 형식/스테이지 오브젝트를 참조할 수 있습니다.

스테이징된 데이터 파일에서 열 로드하기:
  • COPY INTO <테이블> 에 직접 지정된 파일 형식 옵션.

  • 명명된 파일 형식 또는 스테이지 오브젝트에 대해 지정된 파일 형식 옵션으로 설정. 그러면 COPY INTO <테이블> 문에서 명명된 파일 형식/스테이지 오브젝트를 참조할 수 있습니다.

쿼리의 예

예 1: CSV 파일에서 열 쿼리하기

다음 예에서는 여러 CSV 데이터 파일(파일 형식이 동일한)을 스테이징한 후 파일에서 데이터 열을 쿼리하는 방법을 보여줍니다.

이 예에서는 파일 이름이 다음과 같으며 위치가 macOS 또는 Linux 환경의 루트 디렉터리인 것으로 가정합니다.

  • /tmp/data1.csv 에는 다음의 두 레코드가 포함됩니다.

    a|b
    c|d
    
    Copy
  • /tmp/data2.csv 에는 다음의 두 레코드가 포함됩니다.

    e|f
    g|h
    
    Copy

파일을 스테이징 및 쿼리하려면:

-- Create a file format.
CREATE OR REPLACE FILE FORMAT myformat TYPE = 'csv' FIELD_DELIMITER = '|';

-- Create an internal stage.
CREATE OR REPLACE STAGE mystage1;

-- Stage the data files.
PUT file:///tmp/data*.csv @mystage1;

-- Query the filename and row number metadata columns and the regular data columns in the staged file.
-- Optionally apply pattern matching to the set of files in the stage and optional path.
-- Note that the table alias is provided to make the statement easier to read and is not required.
SELECT t.$1, t.$2 FROM @mystage1 (file_format => 'myformat', pattern=>'.*data.*[.]csv.gz') t;

+----+----+
| $1 | $2 |
|----+----|
| a  | b  |
| c  | d  |
| e  | f  |
| g  | h  |
+----+----+

SELECT t.$1, t.$2 FROM @mystage1 t;

+-----+------+
| $1  | $2   |
|-----+------|
| a|b | NULL |
| c|d | NULL |
| e|f | NULL |
| g|h | NULL |
+-----+------+
Copy

참고

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

그러나 스테이지 정의에 파일 형식이 포함된 경우에는 SELECT 문에서 생략할 수 있습니다. 예 3: JSON 파일에서 열 쿼리하기 를 참조하십시오.

예 2: 스테이징된 데이터 파일을 쿼리할 때 함수 호출하기

예 1: CSV 파일에서 열 쿼리하기 에서 스테이징된 데이터 파일에서 각 열의 첫 번째 문자에 대한 ASCII 코드를 가져옵니다.

SELECT ascii(t.$1), ascii(t.$2) FROM @mystage1 (file_format => myformat) t;

+-------------+-------------+
| ASCII(T.$1) | ASCII(T.$2) |
|-------------+-------------|
|          97 |          98 |
|          99 |         100 |
|         101 |         102 |
|         103 |         104 |
+-------------+-------------+
Copy

참고

스테이지 정의에 파일 형식이 포함된 경우에는 SELECT 문에서 생략할 수 있습니다. 예 3: JSON 파일에서 열 쿼리하기 를 참조하십시오.

예 3: JSON 파일에서 열 쿼리하기

이 예에서는 다음 오브젝트가 포함된 JSON 데이터 파일을 스테이징한 후 파일 내의 개별 요소를 쿼리하는 방법을 보여줍니다.

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

이 예에서는 파일 이름이 /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 the data file
PUT file:///tmp/data1.json @mystage2;

-- Query the repeating a.b element in the staged file
SELECT parse_json($1):a.b FROM @mystage2/data1.json.gz;

+--------------------+
| PARSE_JSON($1):A.B |
|--------------------|
| "x1"               |
| "x2"               |
+--------------------+
Copy