데이터 언로딩 고려 사항

데이터 언로딩 고려 사항

이 주제는 테이블에서 데이터를 언로딩할 때의 모범 사례, 일반 지침, 중요한 고려 사항을 제공해요. COPY INTO <location> 명령으로 Snowflake 테이블의 데이터를 스테이지의 파일로 내보내는 것을 단순화하는 데 목적이 있어요.

출처: Snowflake User Guide - Data unloading considerations

본문

빈 문자열과 NULL 값

빈 문자열(empty string)은 길이가 0이거나 문자가 없는 문자열이고, NULL 값은 데이터가 없음을 나타내요. CSV 파일에서 NULL 값은 일반적으로 구분자가 두 개 연속으로 나오는 것(예: ,,)으로 표현되어 필드에 데이터가 없음을 나타내요. 하지만 문자열 값(예: null)이나 어떤 고유 문자열을 사용해 NULL을 나타낼 수도 있어요. 빈 문자열은 일반적으로 따옴표로 감싼 빈 문자열(예: '')로 표현되어 문자열이 0개의 문자를 포함한다는 것을 나타내요.

다음 파일 형식 옵션은 언로딩하거나 로딩할 때 빈 문자열과 NULL 값을 구분할 수 있게 해줘요.

FIELD_OPTIONALLY_ENCLOSED_BY = 'character' | NONE

이 옵션으로 문자열을 지정된 문자(작은따옴표 ', 큰따옴표 ", 또는 NONE)로 감싸요. 언로딩 중 문자열 값을 따옴표로 감싸는 것은 필수는 아니에요. EMPTY_FIELD_AS_NULL 옵션을 FALSE로 설정하면 COPY INTO <location> 명령은 따옴표로 감싸지 않고 빈 문자열 값을 언로딩할 수 있어요. EMPTY_FIELD_AS_NULL 옵션이 TRUE(금지됨)라면 출력 파일에서 빈 문자열과 NULL 값을 구분할 수 없어요. 필드에 이 문자가 포함되면 같은 문자로 이스케이프해요. 예를 들어 값이 큰따옴표 문자이고 필드에 문자열 "A"가 포함되어 있다면 큰따옴표를 ""A""처럼 이스케이프해요. 기본값: NONE.

EMPTY_FIELD_AS_NULL = TRUE | FALSE

테이블에서 빈 문자열 데이터를 언로딩할 때 다음 옵션 중 하나를 선택해요.

  • 권장: FIELD_OPTIONALLY_ENCLOSED_BY 옵션을 설정해 문자열을 따옴표로 감싸 출력 CSV 파일에서 빈 문자열과 NULL을 구분해요.
  • FIELD_OPTIONALLY_ENCLOSED_BY 옵션을 NONE(기본값)으로 설정해 문자열 필드를 감싸지 않은 채 두고, EMPTY_FIELD_AS_NULL 값을 FALSE로 설정해 빈 문자열을 빈 필드로 언로딩해요.

    중요: 이 옵션을 선택하면 NULL_IF 옵션으로 NULL 데이터의 대체 문자열을 지정해 출력 파일에서 NULL 값과 빈 문자열을 구분해야 해요. 나중에 출력 파일에서 데이터를 로딩하기로 선택한다면 데이터 파일에서 NULL 값을 식별하기 위해 같은 NULL_IF 값을 지정해요.

테이블에 데이터를 로딩할 때 이 옵션은 입력 파일의 빈 필드에 SQL NULL을 삽입할지 여부를 지정해요. FALSE로 설정하면 Snowflake는 빈 필드를 해당 컬럼 타입으로 캐스트하려고 시도해요. STRING 데이터 타입 컬럼에는 빈 문자열이 삽입돼요. 다른 컬럼 타입에서는 COPY 명령이 오류를 생성해요. 기본값: TRUE.

NULL_IF = ( 'string1' [ , 'string2' ... ] )

테이블에서 언로딩할 때 Snowflake는 SQL NULL 값을 목록의 첫 번째 값으로 변환해요. NULL로 해석되길 원하는 값을 지정하도록 주의해요. 예를 들어 다른 시스템이 읽을 파일로 데이터를 언로딩한다면, 그 시스템이 NULL로 해석할 값을 지정해야 해요. 기본값: \N(즉 ESCAPE_UNENCLOSED_FIELD 값이 \(기본값)라고 가정한 NULL).

예시: 따옴표로 감싸 언로딩·로딩

다음 예시에서 데이터 집합이 null_empty1 테이블에서 사용자의 스테이지로 언로딩돼요. 그런 다음 출력 데이터 파일이 null_empty2 테이블에 데이터를 로딩하는 데 사용돼요.

-- 소스 테이블(null_empty1) 내용
+---+------+--------------+
| i | V    | D            |
|---+------+--------------|
| 1 | NULL | NULL value   |
| 2 |      | Empty string |
+---+------+--------------+

-- 데이터와 처리 지침을 설명하는 파일 형식 만들기
create or replace file format my_csv_format
  field_optionally_enclosed_by = '0x27'
  null_if = ('null');

-- 테이블 데이터를 스테이지로 언로딩
copy into @mystage
  from null_empty1
  file_format = (format_name = 'my_csv_format');

-- 데이터 파일 내용 출력
1,'null','NULL value'
2,'','Empty string'

-- 스테이지된 파일에서 대상 테이블(null_empty2)로 데이터 로딩
copy into null_empty2
  from @mystage/data_0_0_0.csv.gz
  file_format = (format_name = 'my_csv_format');

select * from null_empty2;
+---+------+--------------+
| i | V    | D            |
|---+------+--------------|
| 1 | NULL | NULL value   |
| 2 |      | Empty string |
+---+------+--------------+

예시: 따옴표로 감싸지 않고 언로딩·로딩

다음 예시에서 데이터 집합이 null_empty1 테이블에서 사용자의 스테이지로 언로딩돼요. 그런 다음 출력 데이터 파일이 null_empty2 테이블에 데이터를 로딩하는 데 사용돼요.

-- 소스 테이블(null_empty1) 내용
+---+------+--------------+
| i | V    | D            |
|---+------+--------------|
| 1 | NULL | NULL value   |
| 2 |      | Empty string |
+---+------+--------------+

-- 데이터와 처리 지침을 설명하는 파일 형식 만들기
create or replace file format my_csv_format
  empty_field_as_null = false
  null_if = ('null');

-- 테이블 데이터를 스테이지로 언로딩
copy into @mystage
  from null_empty1
  file_format = (format_name = 'my_csv_format');

-- 데이터 파일 내용 출력
1,null,NULL value
2,,Empty string

-- 스테이지된 파일에서 대상 테이블(null_empty2)로 데이터 로딩
copy into null_empty2
  from @mystage/data_0_0_0.csv.gz
  file_format = (format_name = 'my_csv_format');

select * from null_empty2;
+---+------+--------------+
| i | V    | D            |
|---+------+--------------|
| 1 | NULL | NULL value   |
| 2 |      | Empty string |
+---+------+--------------+

단일 파일로 언로딩

기본적으로 COPY INTO 문은 병렬 연산을 활용하기 위해 테이블 데이터를 출력 파일 집합으로 나눠요. 각 파일의 최대 크기는 MAX_FILE_SIZE 복사 옵션으로 설정돼요. 기본값은 16777216(16MB)이지만 더 큰 파일을 수용하도록 늘릴 수 있어요. Amazon S3, Google Cloud Storage, Microsoft Azure 스테이지에서 지원되는 최대 파일 크기는 5GB예요.

단일 출력 파일로 언로딩하려면(성능 저하의 가능성을 감수하고) 문에서 SINGLE = true 복사 옵션을 지정해요. 선택적으로 경로에 파일 이름을 지정할 수 있어요.

참고: COMPRESSION 옵션이 true로 설정되면 출력 파일이 압축 해제될 수 있도록 압축 방식에 맞는 적절한 파일 확장자를 가진 파일명을 지정해요. 예를 들어 GZIP 압축 방식을 지정하면 GZ 확장자를 지정해요.

예를 들어 mytable 테이블 데이터를 명명된 스테이지의 myfile.csv 단일 파일로 언로딩해요. 큰 데이터 집합을 수용하기 위해 MAX_FILE_SIZE 한도를 높여요.

copy into @mystage/myfile.csv.gz from mytable
  file_format = (type = csv compression = 'gzip')
  single = true
  max_file_size = 4900000000;

관계형 테이블을 JSON으로 언로딩

OBJECT_CONSTRUCT 함수를 COPY 명령과 함께 사용해 관계형 테이블의 행을 단일 VARIANT 컬럼으로 변환하고 행을 파일로 언로딩할 수 있어요. 예:

-- 테이블 만들기
CREATE OR REPLACE TABLE mytable (
  id number(8) NOT NULL,
  first_name varchar(255) default NULL,
  last_name varchar(255) default NULL,
  city varchar(255),
  state varchar(255)
);

-- 테이블에 데이터 채우기
INSERT INTO mytable (id, first_name, last_name, city, state)
  VALUES
    (1, 'Ryan', 'Dalton', 'Salt Lake City', 'UT'),
    (2, 'Upton', 'Conway', 'Birmingham', 'AL'),
    (3, 'Kibo', 'Horton', 'Columbus', 'GA');

-- 데이터를 스테이지의 파일로 언로딩
COPY INTO @mystage
  FROM (
    SELECT OBJECT_CONSTRUCT('id', id, 'first_name', first_name,
                            'last_name', last_name, 'city', city,
                            'state', state)
    FROM mytable
  )
  FILE_FORMAT = (TYPE = JSON);

-- COPY INTO <location> 문은 스테이지에 data_0_0_0.json.gz 파일을 생성합니다.
-- 파일은 다음 데이터를 포함합니다.
{"city":"Salt Lake City","first_name":"Ryan","id":1,"last_name":"Dalton","state":"UT"}
{"city":"Birmingham","first_name":"Upton","id":2,"last_name":"Conway","state":"AL"}
{"city":"Columbus","first_name":"Kibo","id":3,"last_name":"Horton","state":"GA"}

여러 컬럼이 있는 관계형 테이블을 Parquet으로 언로딩

COPY 문에 입력으로 SELECT 문을 사용해 관계형 테이블의 데이터를 여러 컬럼이 있는 Parquet 파일로 언로딩할 수 있어요. SELECT 문은 언로딩된 파일에 포함할 관계형 테이블의 컬럼 데이터를 지정해요. HEADER = TRUE 복사 옵션을 사용해 출력 파일에 컬럼 헤더를 포함해요.

예를 들어 mytable 테이블의 세 컬럼(id, name, start_date)의 행을 myfile.parquet 명명 형식의 파일 하나 이상으로 언로딩해요.

COPY INTO @mystage/myfile.parquet
  FROM (SELECT id, name, start_date FROM mytable)
  FILE_FORMAT = (TYPE = 'parquet')
  HEADER = TRUE;

참고: COPY INTO <location>는 다음 제한과 함께 지원돼요.

  • VARIANT, GEOMETRY, GEOGRAPHY는 JSON으로 인코딩된 문자열로 언로딩돼요.
  • TIMESTAMP_NTZ(9)는 나노초가 아닌 밀리초로 언로딩돼요.
  • TIMESTAMP_LTZ(9), ARRAY, OBJECT, MAP은 다른 데이터 타입으로 캐스트해야 해요.

숫자 컬럼을 Parquet 데이터 타입으로 명시적으로 변환

기본적으로 테이블 데이터가 Parquet 파일로 언로딩될 때 고정 소수점 숫자 컬럼은 DECIMAL 컬럼으로, 부동 소수점 숫자 컬럼은 DOUBLE 컬럼으로 언로딩돼요. 언로딩된 데이터 집합의 Parquet 데이터 타입을 선택하려면 COPY INTO <location> 문에서 CAST(::) 함수를 호출해 특정 테이블 컬럼을 명시적 데이터 타입으로 변환해요. COPY INTO 문의 쿼리는 언로딩할 특정 컬럼을 선택할 수 있고, 컬럼 데이터를 변환하는 변환 SQL 함수를 받아들여요. COPY INTO 문의 쿼리는 언로딩할 특정 Snowflake 테이블 컬럼을 쿼리하는 SELECT 문의 문법·의미를 지원해요. CAST(::) 함수로 숫자 컬럼의 데이터를 특정 데이터 타입으로 변환해요.

다음 표는 Snowflake 숫자 데이터 타입을 Parquet 물리·논리 데이터 타입에 매핑해요.

Snowflake 논리 데이터 타입 Parquet 물리 데이터 타입 Parquet 논리 데이터 타입
TINYINT INT32 INT(8)
SMALLINT INT32 INT(16)
INT INT32 INT(32)
BIGINT INT64 INT(64)
FLOAT FLOAT N/A
DOUBLE DOUBLE N/A

다음 예시는 언로딩된 각 컬럼의 숫자 데이터를 서로 다른 데이터 타입으로 변환해 Parquet 파일의 데이터 타입을 명시적으로 선택하는 COPY INTO <location> 문을 보여줘요.

COPY INTO @mystage
  FROM (
    SELECT CAST(C1 AS TINYINT),
           CAST(C2 AS SMALLINT),
           CAST(C3 AS INT),
           CAST(C4 AS BIGINT)
    FROM mytable
  )
  FILE_FORMAT = (TYPE = PARQUET);

부동 소수점 숫자 잘림

부동 소수점 숫자 컬럼을 CSV나 JSON 파일로 언로딩하면 Snowflake는 값을 대략 (15,9)로 잘라요. Parquet 파일로 언로딩할 때는 값이 잘리지 않아요.

더 알아보기