본문으로 건너뛰기
snowflake검색
SQL과 데이터

반정형 데이터 VARIANT (JSON, FLATTEN, 구조화 타입)

JSON, Avro, Parquet 같은 반정형 데이터를 VARIANT 에 넣고 경로 표기와 FLATTEN 으로 꺼내는 법, 자동 열 추출로 빨라지는 원리, 구조화된 ARRAY, OBJECT, MAP 타입, 크기 제한과 흔한 실수를 정리합니다.

VARIANTJSONFLATTENLATERALOBJECTARRAY스키마 추론2026-10 기준, 약 7분

VARIANT 기본

VARIANT 는 JSON 같은 반정형 값을 그대로 담는 타입입니다. 한 값의 최대 크기는 압축 전 기준으로 정해져 있어 아주 큰 문서는 잘라서 넣어야 합니다 (2026-10 기준 공식 문서의 VARIANT 크기 제한을 확인하세요). OBJECT, ARRAY 도 같은 계열입니다.

CREATE TABLE raw.events (
  src       VARIANT,
  loaded_at TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP()
);

-- JSON 파일 적재 (스테이지와 파일 포맷은 COPY 문서 참고)
COPY INTO raw.events (src)
FROM @raw.s3_stage/events/
FILE_FORMAT = (TYPE = JSON STRIP_OUTER_ARRAY = TRUE);

경로로 꺼내기

SELECT
  src:user.id::NUMBER            AS user_id,
  src:user.name::STRING          AS user_name,
  src:"event-type"::STRING       AS event_type,   -- 특수문자가 있는 키는 큰따옴표
  src['device']['os']::STRING    AS os,           -- 대괄호 표기도 가능
  src:items[0].sku::STRING       AS first_sku
FROM raw.events;
  • : 로 첫 단계, . 으로 하위 키, [n] 으로 배열 위치를 씁니다.
  • 키 이름은 대소문자를 구분합니다. src:User 와 src:user 는 다릅니다. 열 이름이 대소문자를 구분하지 않는 것과 반대라 자주 틀립니다.
  • 꺼낸 값은 여전히 VARIANT 이므로 ::STRING, ::NUMBER 로 바꿔야 비교와 집계가 정확합니다. 바꾸지 않은 문자열은 결과에 큰따옴표가 붙어 보입니다.
  • 형 변환이 실패할 수 있는 값은 TRY_CAST 나 TRY_TO_NUMBER 로 받아 NULL 로 처리합니다.

FLATTEN 으로 배열 펼치기

-- 주문 하나에 상품 배열이 들어 있을 때 상품마다 한 행으로
SELECT
  e.src:order_id::STRING AS order_id,
  i.index                AS line_no,
  i.value:sku::STRING    AS sku,
  i.value:qty::NUMBER    AS qty
FROM raw.events e,
     LATERAL FLATTEN(INPUT => e.src:items) i;

-- 배열이 비어도 주문 행을 남기려면 OUTER => TRUE
SELECT e.src:order_id::STRING, i.value:sku::STRING
FROM raw.events e,
     LATERAL FLATTEN(INPUT => e.src:items, OUTER => TRUE) i;

-- 모르는 구조 탐색: 모든 경로 나열
SELECT DISTINCT f.path, TYPEOF(f.value)
FROM raw.events, LATERAL FLATTEN(INPUT => src, RECURSIVE => TRUE) f
LIMIT 200;

FLATTEN 결과에는 SEQ, KEY, PATH, INDEX, VALUE, THIS 열이 있습니다. 객체를 펼치면 KEY 에 키 이름이 들어옵니다.

만들고 합치기

SELECT OBJECT_CONSTRUCT('id', id, 'name', name)          AS obj,
       ARRAY_AGG(sku) WITHIN GROUP (ORDER BY sku)        AS skus,
       OBJECT_AGG(attr_key, attr_value::VARIANT)         AS attrs
FROM ...
GROUP BY id, name;

SELECT PARSE_JSON('{"a":1}') AS v, TO_JSON(v) AS s;

OBJECT_CONSTRUCT 는 값이 NULL 인 키를 빼고, OBJECT_CONSTRUCT_KEEP_NULL 은 남깁니다. 2026년 3월에는 ARRAY_REPEAT, MAP_ENTRIES 함수가 추가되었습니다.

왜 생각보다 빠른가 (하위 열 추출)

Snowflake 는 VARIANT 를 적재할 때 자주 나오는 경로를 내부적으로 별도 열처럼 뽑아 열 단위로 저장하고 통계도 따로 냅니다. 그래서 src:user.id = 123 같은 조건도 프루닝이 됩니다. 다만 다음 경우에는 이 최적화가 약해집니다.

  • 같은 경로에 숫자와 문자열이 섞여 들어옴
  • 키가 너무 많거나 문서마다 구조가 제각각
  • 배열 안의 값으로 거름 (배열은 펼쳐야 함)

자주 쓰는 경로는 적재 후 일반 열로 꺼내 두는 것이 가장 확실합니다. Dynamic Tables 로 원본 VARIANT 에서 정형 테이블을 자동으로 만들어 두면 관리도 쉽습니다.

스키마 추론과 자동 진화

Parquet, Avro, ORC, CSV, JSON 파일은 INFER_SCHEMA 로 열 정의를 뽑아 테이블을 만들 수 있습니다.

CREATE TABLE raw.orders
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
    FROM TABLE(INFER_SCHEMA(LOCATION => '@raw.s3_stage/orders/', FILE_FORMAT => 'raw.parquet_fmt'))
  );

ALTER TABLE raw.orders SET ENABLE_SCHEMA_EVOLUTION = TRUE;

스키마 진화를 켜면 COPY 나 Snowpipe 가 새 열을 만나 자동으로 열을 추가합니다. 테이블 소유 역할이 필요하고, 열 삭제나 타입 축소는 자동으로 하지 않습니다.

구조화된 타입

일반 VARIANT 와 달리 원소 타입을 정해 둔 ARRAY(NUMBER), OBJECT(name STRING, age NUMBER), MAP(STRING, NUMBER) 를 쓸 수 있습니다. Iceberg 테이블과 주고받을 때나 Snowpark 에서 타입을 엄격하게 지키고 싶을 때 씁니다. 2026년 1월에는 구조화 타입 열에도 검색 최적화를 걸 수 있게 되었습니다.

CREATE TABLE app.profiles (
  id    NUMBER,
  tags  ARRAY(STRING),
  attrs MAP(STRING, NUMBER)
);

자주 하는 실수

실수결과고치기
형 변환 없이 비교 src:status = 'paid'의도대로 동작하지만 타입이 섞이면 놓침src:status::STRING = 'paid'
키 대소문자 틀림전부 NULLTYPEOF, 재귀 FLATTEN 으로 실제 키 확인
STRIP_OUTER_ARRAY 없이 큰 JSON 배열 파일 적재한 행에 파일 전체가 들어가 크기 제한 초과파일 포맷에 STRIP_OUTER_ARRAY = TRUE
FLATTEN 후 집계에서 행이 늘어남합계가 배로 뻥튀기펼친 뒤 주문 단위로 다시 묶거나 하위 집계 먼저
NULL 과 JSON null 혼동IS NULL 로 안 걸림JSON null 은 IS_NULL_VALUE(src:x) 로 확인

트러블슈팅

  • 증상: COPY 중 "Max LOB size exceeded" 류의 오류가 납니다.
  • 원인: JSON 파일 전체가 하나의 배열이라 한 VARIANT 값으로 들어가려 했습니다.
  • 해결: 파일 포맷에 STRIP_OUTER_ARRAY = TRUE 를 주어 원소마다 한 행으로 넣습니다. 원소 하나도 크다면 원천에서 쪼갭니다. 적재 오류 전반은 적재 오류 트러블슈팅 에 있습니다.

공식 문서

이 문서에서