반정형 데이터 로드를 위한 Schema Detection 기능 알아보기
Snowflake에서 반정형 데이터를 완벽하게 지원하기 위해서는 Parquet, ORC, AVRO, JSON등의 반정형 데이터들도 마치 DBMS에 저장된 데이터와 유사하게 처리하기 위해서 기존 빅데이터의 Schema-On-Read 방식의 처리가 아닌 Schema-On-Write로 처리되어야 합니다. Snowflake는 파일 내부에 스키마 정보가 저장된 Parquet, ORC, JSON과 같은 반정형 데이터의 빠른 통합을 위해 Schema Detection 및 Schema Evolution기능을 Private Preview 형식으로 지원하고 있습니다.
Schema Detection 기능은 기존 반정형 데이터 통합을 위해 관리자가 수작업으로 수행해야 하는 테이블 DDL과 같은 작업을 쉽게 수행할 수 있게 해주며, 내부 테이블 및 외부 테이블 정의와 같은 다양한 용도로 활용할 수 있습니다.
Snowflake의 Schema Detection 기능을 위해 다음과 같은 세부 기능을 제공하고 있습니다:
1. INFER_SCHEMA: Stage에 위치한 반정형(Parquet, ORC, Avro, JSON)에서 파일 메타 데이터 스키마를 자동으로 감지하여, 컬럼 정의에 관련된 메타 정보를 반환.
2. GENERATE_COLUMN_DESCRIPTION: Generate_column_description() 함수는 Stage에 저장된 반정형 데이터를 참조하여, Snowflake 내부 테이블, 외부 테이블 및 VIEW를 생성하는 데 필요한 컬럼 목록을 출력해 줍니다.
3. CREATE TABLE … USING TEMPLATE: Snowflake의 CREATE TABLE 기능을 확장하여 컬럼의 정의를 추가로 지정하지 않고, 반정형 데이터에서 감지한 스키마를 사용하여 구조화된 테이블을 생성하는 용도로 활용됩니다.
이 반정형 데이터의 스키마 감지 기능은 과거 반정형 데이터를 통합하기 위해 수행되어야 했던 테이블 정의와 같은 번거로운 작업이 단순화 되었으며, 복잡한 구조의 반정형 데이터를 쉽고 빠르게 Snowflake에 구조화하여 저장하여 데이터 파이프라인 간소화에 도움이 됩니다.
JSON 예: Stage에 “HR_data.json”을 Snowflake에 로드하여 사용
- Schema Detection을 사용하지 않는 경우
- Variant 컬럼의 원천 테이블(JSON_L0)을 생성 및 2) JSON 파일을 “COPY INTO” 문을 사용하여 사전에 생성한 테이블에 로드
2. 테이블 조회 결과: 다음과 같이 JSON의 데이터는 Variant라는 컬럼에 통합 저장
3. JSON_L0의 테이블의 데이터를 활용하기 위해서는 다음과 같이 “:” 또는 “.”기호를 확인하여 쿼리하여 활용할 수 있습니다.
Snowflake는 Variant라는 특수한 데이터 유형을 지원하기 때문에 JSON과 같은 반정형 데이터를 별도의 사전 가공(파싱하여 정형화)없이 빠르게 조회하는 기능을 제공하고 있습니다. 하지만, 반정형 데이터를 정형화 하여 BI에서 직접 분석하는 요구사항이 있는 경우 다음 그림과 같이 정형화 하는 과정이 필요합니다 1) “STREAM + TASK 사용” 또는 2) “VIEW 사용”
Variant 타입에 대해서는 이전 블로그 내용 참조:
- Schema Detection을 사용하는 경우
다음은 Schema Detection 기능을 사용하여 JSON을 포함한 다양한 반정형 데이터를 수집 시점에 별다른 가공 없이 바로 정형화 하는 과정을 설명하고 있습니다:
1. INFER_SCHMA: JSON 파일의 스키마 정보를 감지
select array_agg(object_construct(*)) from table(
infer_schema(LOCATION => '@DEMO_DB.EXTERNAL_STAGES.JSONSTAGE' , FILE_FORMAT => 'DEMO_DB.FILE_FORMATS.JSONFORMAT'));
2. INFER_SCHEMA를 사용하여 테이블 생성:
create or replace table demo_db.public.json_sd_test
using template(
select array_agg(object_construct(*)) from table(
infer_schema(LOCATION => ‘@DEMO_DB.EXTERNAL_STAGES.JSONSTAGE’ , FILE_FORMAT => ‘DEMO_DB.FILE_FORMATS.JSONFORMAT’))
);
3. 생성된 테이블 확인:
desc table demo_db.public.json_sd_test;
4. COPY INTO 문을 사용하여 반정형 데이터를 사전에 INFER_SCHEMA로 정의한 테이블에 로드:
COPY INTO demo_db.public.json_sd_test
FROM ‘@DEMO_DB.EXTERNAL_STAGES.JSONSTAGE’
file_format = (format_name = ‘DEMO_DB.FILE_FORMATS.JSONFORMAT’)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
force=true;* 주의: 반정형 데이터 파일을 로드할 때 COPY INTO의 옵션으로 “MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE”으로 반드시 설정 필요
5. SELECT 문을 사용하여 데이터 조회
Snowflake Schema Detection 기능은 현재 Parquet, Avro, ORC, Json 및 CSV에 PuPr 및 PrPr 기능으로 제공되고 있습니다. 다음 블로그에서는 반정형 데이터 파일의 Schema Evolution에 대해서 살펴보도록 하겠습니다.
