로드 중 데이터 변환하기¶
Snowflake는 COPY INTO <테이블> 명령을 사용하여 데이터를 테이블에 로드하는 동안 데이터 변환을 지원하여 기본 변환을 위한 ETL 파이프라인을 대폭 간소화합니다. 이 기능을 사용하면 데이터를 로드하는 동안 열을 재정렬할 때 미리 변환된 데이터를 저장하기 위해 임시 테이블을 사용하지 않도록 할 수 있습니다. 이 기능은 대량 로딩과 Snowpipe에 모두 적용됩니다.
COPY 명령에서는 다음이 지원됩니다.
SELECT 문을 사용한 열 재정렬, 열 생략 및 캐스트. 데이터 파일의 열 개수 및 순서는 대상 테이블과 같지 않아도 됩니다.
ENFORCE_LENGTH | TRUNCATECOLUMNS 옵션, 대상 열 길이를 초과하는 텍스트 문자열을 자를 수 있습니다.
스테이징된 데이터 파일 쿼리에 대한 일반 정보는 스테이징된 파일에서 데이터 쿼리하기 를 참조하십시오.
이 항목의 내용:
사용법 노트¶
이 섹션에서는 로드 중에 스테이징된 데이터 파일을 변환하기 위한 사용 정보를 제공합니다.
지원되는 파일 형식¶
COPY 변환을 위해 지원되는 파일 형식은 다음과 같습니다.
CSV
JSON
Avro
ORC
Parquet
XML
스테이징된 데이터 파일을 구문 분석하려면 해당 파일 형식을 설명해야 합니다.
- CSV
기본 형식은 문자로 구분된 UTF-8 텍스트입니다. 기본 필드 구분 기호는 쉼표 문자(
,)입니다. 기본 레코드 구분 기호는 줄 바꿈 문자입니다. 소스 데이터의 타입이 다른 경우 파일 형식 타입 및 옵션을 지정합니다.스테이징된 데이터 파일을 쿼리하는 경우에는 데이터
ERROR_ON_COLUMN_COUNT_MISMATCH옵션이 무시됩니다. 데이터 파일의 열 개수 및 순서는 대상 테이블과 같지 않아도 됩니다.- JSON
로딩 작업 중에 JSON 데이터를 변환하려면 데이터 파일을 NDJSON (《줄 바꿈으로 구분된 JSON》) 표준 형식으로 구조화해야 하며, 그렇지 않으면 다음 오류가 발생할 수 있습니다.
Error parsing JSON: more than one document in the input- 기타 모든 파일 형식 유형
데이터 파일과 일치하는 형식 타입 및 옵션을 지정합니다.
파일 형식 옵션을 명시적으로 지정하려면 다음 방법 중 하나로 설정합니다.
SELECT 문을 사용하여 스테이징된 데이터 파일 쿼리하기: |
|
COPY INTO <테이블> 문을 사용하여 스테이징된 데이터 파일에서 열 로드하기: |
|
지원되는 함수¶
현재 Snowflake에서 COPY 변환을 위해 지원하는 함수의 하위 세트는 다음과 같습니다.
-
이 함수를 사용하여 값을 명시적으로 캐스팅하는 경우 DATE_FORMAT 파일 형식 옵션이나 DATE_INPUT_FORMAT 매개 변수가 적용되지 않습니다.
-
이 함수를 사용하여 값을 명시적으로 캐스팅하는 경우 TIME_FORMAT 파일 형식 옵션이나 TIME_INPUT_FORMAT 매개 변수가 적용되지 않습니다.
-
이 함수를 사용하여 값을 명시적으로 캐스팅하는 경우 TIMESTAMP_FORMAT 파일 형식 옵션이나 TIMESTAMP_INPUT_FORMAT 매개 변수가 적용되지 않습니다.
-
COPY INTO <테이블> 명령은 이 함수에 대한 선택적
format인자를 지원하지 않습니다. -
COPY INTO <테이블> 명령은 이 함수에 대한 선택적
format인자를 지원하지 않습니다. TRY_TO_TIMESTAMP / TRY_TO_TIMESTAMP_*
COPY INTO <테이블> 명령은 이 함수에 대한 선택적
format인자를 지원하지 않습니다.
특히 VALIDATE 함수는 COPY INTO <테이블> 문에서 SELECT 목록을 무시합니다. 이 함수는 문에서 참조된 파일을 구문 분석하고 구문 분석 오류를 반환합니다. 함수가 COPY INTO <테이블> 식의 컨텍스트에서 파일을 평가할 것으로 예상했다면 이런 동작이 놀라울 수 있습니다.
COPY 변환은 FLATTEN 함수나 JOIN 또는 GROUP BY (집계) 구문을 지원하지 않습니다.
지원되는 기능 목록은 차후에 확장될 수 있습니다.
지원되는 함수 카테고리는 다음과 같습니다.
스칼라 SQL UDFs.
참고
Scalar SQL UDF의 경우 Snowflake는 변환 오류 처리를 제한적으로 지원하므로 일관성이 없거나 예기치 않은 ON_ERROR 복사 옵션 동작이 발생할 수 있습니다.
결과 필터링하기¶
WHERE 절을 사용한 FROM 절 결과 필터링은 지원되지 않습니다. SELECT 문의 ORDER BY, LIMIT, FETCH, TOP 키워드도 지원되지 않습니다.
SELECT 문의 DISTINCT 키워드는 완전히 지원되지는 않습니다. 이 키워드를 지정하면 일관되지 않거나 예기치 않은 ON_ERROR 복사 옵션 동작이 발생할 수 있습니다.
VALIDATION_MODE 매개 변수¶
VALIDATION_MODE 매개 변수는 로드 중에 데이터를 변환하는 COPY 문을 지원하지 않습니다.
CURRENT_TIME, CURRENT_TIMESTAMP 기본 열 값¶
CURRENT_TIME, CURRENT_TIMESTAMP 기본 열 값을 사용하여 로드 시간을 캡처하는 대신, METADATA$START_SCAN_TIME를 쿼리하여 레코드 로딩의 정확한 시간 값을 가져오는 것이 좋습니다. 자세한 내용은 스테이징된 파일에 대한 메타데이터 쿼리하기 섹션을 참조하십시오.
CSV 데이터 변환하기¶
테이블 데이터 하위 세트 로드¶
데이터의 하위 세트를 테이블에 로드합니다. 열이 누락된 경우 Snowflake는 기본값을 삽입합니다. 다음 예에서는 스테이징된 CSV 파일의 1, 2, 6, 7열에서 데이터를 로드합니다.
copy into home_sales(city, zip, sale_date, price) from (select t.$1, t.$2, t.$6, t.$7 from @mystage/sales.csv.gz t) FILE_FORMAT = (FORMAT_NAME = mycsvformat);
로드 중 CSV 열 재정렬하기¶
다음 예에서는 스테이징된 CSV 파일을 테이블에 로드하기 전에 열 데이터를 다시 정렬합니다. 또한, COPY 문은 SUBSTR , SUBSTRING 함수를 사용하여 문자열을 삽입하기 전에 문자열의 처음 몇 문자를 제거합니다.
copy into home_sales(city, zip, sale_date, price) from (select SUBSTR(t.$2,4), t.$1, t.$5, t.$4 from @mystage t) FILE_FORMAT = (FORMAT_NAME = mycsvformat);
로드 중 데이터 타입 변환하기¶
데이터를 로드하는 동안 스테이징된 데이터를 다른 데이터 타입으로 변환합니다. 모든 변환 함수 가 지원됩니다.
예를 들어, TO_BINARY, TO_DECIMAL , TO_NUMBER , TO_NUMERIC 및 TO_TIMESTAMP / TO_TIMESTAMP_* 함수를 각각 사용하여 문자열을 이진 값, 십진수 또는 타임스탬프로 변환합니다.
샘플 CSV 파일:
snowflake,2.8,2016-10-5 warehouse,-12.3,2017-01-23
SQL 문:
-- Stage a data file in the internal user stage PUT file:///tmp/datafile.csv @~; -- Query the staged data file select t.$1,t.$2,t.$3 from @~/datafile.csv.gz t; -- Create the target table create or replace table casttb ( col1 binary, col2 decimal, col3 timestamp_ntz ); -- Convert the staged CSV column data to the specified data types before loading it into the destination table copy into casttb(col1, col2, col3) from ( select to_binary(t.$1, 'utf-8'),to_decimal(t.$2, '99.9', 9, 5),to_timestamp_ntz(t.$3) from @~/datafile.csv.gz t ) file_format = (type = csv); -- Query the target table select * from casttb; +--------------------+------+-------------------------+ | COL1 | COL2 | COL3 | |--------------------+------+-------------------------| | 736E6F77666C616B65 | 3 | 2016-10-05 00:00:00.000 | | 77617265686F757365 | -12 | 2017-01-23 00:00:00.000 | +--------------------+------+-------------------------+
로드된 데이터에 시퀀스 열 포함¶
CREATE SEQUENCE 를 사용하여 시퀀스 오브젝트를 생성합니다. COPY 명령을 사용하여 테이블에 데이터를 로드할 때 NEXTVAL 식을 사용하여 오브젝트에 액세스하여 대상 번호 열에서 데이터의 순서를 지정합니다. 쿼리에서 시퀀스를 사용하는 방법에 대한 자세한 내용은 시퀀스 사용하기 를 참조하십시오.
-- Create a sequence create sequence seq1; -- Create the target table create or replace table mytable ( col1 number default seq1.nextval, col2 varchar, col3 varchar ); -- Stage a data file in the internal user stage PUT file:///tmp/myfile.csv @~; -- Query the staged data file select $1, $2 from @~/myfile.csv.gz t; +-----+-----+ | $1 | $2 | |-----+-----| | abc | def | | ghi | jkl | | mno | pqr | | stu | vwx | +-----+-----+ -- Include the sequence nextval expression in the COPY statement copy into mytable (col1, col2, col3) from ( select seq1.nextval, $1, $2 from @~/myfile.csv.gz t ) ; select * from mytable; +------+------+------+ | COL1 | COL2 | COL3 | |------+------+------| | 1 | abc | def | | 2 | ghi | jkl | | 3 | mno | pqr | | 4 | stu | vwx | +------+------+------+
로드된 데이터에 AUTOINCREMENT / IDENTITY 열 포함¶
숫자 열에 대한 AUTOINCREMENT 또는 IDENTITY 기본값을 설정합니다. COPY 명령을 사용하여 테이블에 데이터를 로드할 때 SELECT 문에서 열을 생략합니다. 이 명령문은 열을 자동으로 채웁니다.
-- Create the target table create or replace table mytable ( col1 number autoincrement start 1 increment 1, col2 varchar, col3 varchar ); -- Stage a data file in the internal user stage PUT file:///tmp/myfile.csv @~; -- Query the staged data file select $1, $2 from @~/myfile.csv.gz t; +-----+-----+ | $1 | $2 | |-----+-----| | abc | def | | ghi | jkl | | mno | pqr | | stu | vwx | +-----+-----+ -- Omit the sequence column in the COPY statement copy into mytable (col2, col3) from ( select $1, $2 from @~/myfile.csv.gz t ) ; select * from mytable; +------+------+------+ | COL1 | COL2 | COL3 | |------+------+------| | 1 | abc | def | | 2 | ghi | jkl | | 3 | mno | pqr | | 4 | stu | vwx | +------+------+------+
반정형 데이터 변환하기¶
명시된 경우를 제외하고 이 섹션의 예는 모든 반정형 데이터 타입에 적용됩니다.
반정형 데이터를 별도의 열에 로드하기¶
다음 예에서는 스테이징된 반정형 파일의 반복 요소를 데이터 타입이 다른 별도의 테이블 열로 로드합니다.
이 예에서는 location 오브젝트 값을 VARIANT 열에 로드하고 나머지 값을 관계형 열에 로드하여 다음과 같은 반정형 데이터를 관계형 테이블의 별도 열에 로드합니다.
-- Sample data:
{"location": {"city": "Lexington","zip": "40503"},"dimensions": {"sq_ft": "1000"},"type": "Residential","sale_date": "4-25-16","price": "75836"},
{"location": {"city": "Belmont","zip": "02478"},"dimensions": {"sq_ft": "1103"},"type": "Residential","sale_date": "6-18-16","price": "92567"},
{"location": {"city": "Winchester","zip": "01890"},"dimensions": {"sq_ft": "1122"},"type": "Condo","sale_date": "1-31-16","price": "89921"}
다음 SQL 문은 mystage 내부 스테이지에서 sales.json 파일을 로드합니다.
참고
이 예시에서는 JSON 데이터를 로드하지만, SQL 문은 다른 타입(예: Avro, ORC 등)의 반정형 데이터를 로드하는 경우와 유사합니다.
Parquet 데이터를 사용하는 다른 예는 이 항목에서 Parquet 데이터를 별도의 열에 로드 를 참조하십시오.
-- Create an internal stage with the file type set as JSON.
CREATE OR REPLACE STAGE mystage
FILE_FORMAT = (TYPE = 'json');
-- Stage a JSON data file in the internal stage.
PUT file:///tmp/sales.json @mystage;
-- Query the staged data. The data file comprises three objects in NDJSON format.
SELECT t.$1 FROM @mystage/sales.json.gz t;
+------------------------------+
| $1 |
|------------------------------|
| { |
| "dimensions": { |
| "sq_ft": "1000" |
| }, |
| "location": { |
| "city": "Lexington", |
| "zip": "40503" |
| }, |
| "price": "75836", |
| "sale_date": "2022-08-25", |
| "type": "Residential" |
| } |
| { |
| "dimensions": { |
| "sq_ft": "1103" |
| }, |
| "location": { |
| "city": "Belmont", |
| "zip": "02478" |
| }, |
| "price": "92567", |
| "sale_date": "2022-09-18", |
| "type": "Residential" |
| } |
| { |
| "dimensions": { |
| "sq_ft": "1122" |
| }, |
| "location": { |
| "city": "Winchester", |
| "zip": "01890" |
| }, |
| "price": "89921", |
| "sale_date": "2022-09-23", |
| "type": "Condo" |
| } |
+------------------------------+
-- Create a target table for the data.
CREATE OR REPLACE TABLE home_sales (
CITY VARCHAR,
POSTAL_CODE VARCHAR,
SQ_FT NUMBER,
SALE_DATE DATE,
PRICE NUMBER
);
-- Copy elements from the staged file into the target table.
COPY INTO home_sales(city, postal_code, sq_ft, sale_date, price)
FROM (select
$1:location.city::varchar,
$1:location.zip::varchar,
$1:dimensions.sq_ft::number,
$1:sale_date::date,
$1:price::number
FROM @mystage/sales.json.gz t);
-- Query the target table.
SELECT * from home_sales;
+------------+-------------+-------+------------+-------+
| CITY | POSTAL_CODE | SQ_FT | SALE_DATE | PRICE |
|------------+-------------+-------+------------+-------|
| Lexington | 40503 | 1000 | 2022-08-25 | 75836 |
| Belmont | 02478 | 1103 | 2022-09-18 | 92567 |
| Winchester | 01890 | 1122 | 2022-09-23 | 89921 |
+------------+-------------+-------+------------+-------+
Parquet 데이터를 별도의 열에 로드¶
이전 예와 유사하지만 Parquet 형식의 파일에서 반정형 데이터를 로드합니다. 이 예는 Apache Parquet에 익숙한 사용자를 위해 제공됩니다.
-- Create a file format object that sets the file format type. Accept the default options. create or replace file format my_parquet_format type = 'parquet'; -- Create an internal stage and specify the new file format create or replace temporary stage mystage file_format = my_parquet_format; -- Create a target table for the data. create or replace table parquet_col ( custKey number default NULL, orderDate date default NULL, orderStatus varchar(100) default NULL, price varchar(255) ); -- Stage a data file in the internal stage put file:///tmp/mydata.parquet @mystage; -- Copy data from elements in the staged Parquet file into separate columns -- in the target table. -- Note that all Parquet data is stored in a single column ($1) -- SELECT list items correspond to element names in the Parquet file -- Cast element values to the target column data type copy into parquet_col from (select $1:o_custkey::number, $1:o_orderdate::date, $1:o_orderstatus::varchar, $1:o_totalprice::varchar from @mystage/mydata.parquet); -- Query the target table SELECT * from parquet_col; +---------+------------+-------------+-----------+ | CUSTKEY | ORDERDATE | ORDERSTATUS | PRICE | |---------+------------+-------------+-----------| | 27676 | 1996-09-04 | O | 83243.94 | | 140252 | 1994-01-09 | F | 198402.97 | ... +---------+------------+-------------+-----------+
데이터 스큐 반정형 데이터¶
FLATTEN 은 VARIANT, OBJECT 또는 ARRAY 열의 래터럴 뷰를 생성하는 테이블 함수입니다. 반정형 데이터를 별도의 열에 로드 에서 제공되는 샘플 데이터를 사용하여 오브젝트의 각 요소에 대한 별도의 행이 있는 테이블을 생성합니다.
-- Create an internal stage with the file delimiter set as none and the record delimiter set as the new line character
create or replace stage mystage
file_format = (type = 'json');
-- Stage a JSON data file in the internal stage with the default values
put file:///tmp/sales.json @mystage;
-- Create a table composed of the output from the FLATTEN function
create or replace table flattened_source
(seq string, key string, path string, index string, value variant, element variant)
as
select
seq::string
, key::string
, path::string
, index::string
, value::variant
, this::variant
from @mystage/sales.json.gz
, table(flatten(input => parse_json($1)));
select * from flattened_source;
+-----+-----------+-----------+-------+-------------------------+-----------------------------+
| SEQ | KEY | PATH | INDEX | VALUE | ELEMENT |
|-----+-----------+-----------+-------+-------------------------+-----------------------------|
| 1 | location | location | NULL | { | { |
| | | | | "city": "Lexington", | "location": { |
| | | | | "zip": "40503" | "city": "Lexington", |
| | | | | } | "zip": "40503" |
| | | | | | }, |
| | | | | | "price": "75836", |
| | | | | | "sale_date": "2017-3-5", |
| | | | | | "sq__ft": "1000", |
| | | | | | "type": "Residential" |
| | | | | | } |
...
| 3 | type | type | NULL | "Condo" | { |
| | | | | | "location": { |
| | | | | | "city": "Winchester", |
| | | | | | "zip": "01890" |
| | | | | | }, |
| | | | | | "price": "89921", |
| | | | | | "sale_date": "2017-3-21", |
| | | | | | "sq__ft": "1122", |
| | | | | | "type": "Condo" |
| | | | | | } |
+-----+-----------+-----------+-------+-------------------------+-----------------------------+
반정형 요소를 분할하고 VARIANT 값으로 별도의 열에 로드¶
반정형 데이터를 별도의 열에 로드 에서 제공되는 지침에 따라 반정형 데이터의 개별 요소를 대상 테이블의 다른 열로 로드할 수 있습니다. 또한, SPLIT 함수를 사용하여 구분 기호가 포함된 요소 값을 분할하여 배열로 로드할 수도 있습니다.
예를 들어, 반복 요소에서 점 구분 기호로 IP 주소를 분할할 수 있습니다. IP 주소를 별도의 열에 배열로 로드합니다.
-- Create an internal stage with the file delimiter set as none and the record delimiter set as the new line character create or replace stage mystage file_format = (type = 'json'); -- Stage a semi-structured data file in the internal stage put file:///tmp/ipaddress.json @mystage auto_compress=true; -- Query the staged data select t.$1 from @mystage/ipaddress.json.gz t; +----------------------------------------------------------------------+ | $1 | |----------------------------------------------------------------------| | {"ip_address": {"router1": "192.168.1.1","router2": "192.168.0.1"}}, | | {"ip_address": {"router1": "192.168.2.1","router2": "192.168.3.1"}} | +----------------------------------------------------------------------+ -- Create a target table for the semi-structured data create or replace table splitjson ( col1 array, col2 array ); -- Split the elements into individual arrays using the SPLIT function and load them into separate columns -- Note that all JSON data is stored in a single column ($1) copy into splitjson(col1, col2) from ( select split($1:ip_address.router1, '.'),split($1:ip_address.router2, '.') from @mystage/ipaddress.json.gz t ); -- Query the target table select * from splitjson; +----------+----------+ | COL1 | COL2 | |----------+----------| | [ | [ | | "192", | "192", | | "168", | "168", | | "1", | "0", | | "1" | "1" | | ] | ] | | [ | [ | | "192", | "192", | | "168", | "168", | | "2", | "3", | | "1" | "1" | | ] | ] | +----------+----------+