[Snowflake] JSON 구조 데이터 쿼리

Snowflake

[Snowflake] JSON 구조 데이터 쿼리

비번변경 2024. 8. 6. 11:52

개요

지금까지 Snowflake 상에서 CSV 데이터를 주로 다루었다. 이번 글에서는 JSON 형식으로 반구조화된 데어티를 쿼리 하는 방법에 대해 적어둔다.

테스트 데이터 저장

author_with_header.json 파일을 Snowflake 데이터로 저장해보자. 이를 위해서는 VARIENT라는 형식의 컬럼을 포함하는 테이블을 생성해주어야 한다.

CREATE TABLE LIBRARY_CARD_CATALOG.PUBLIC.AUTHOR_INGEST_JSON_2
(
  RAW_AUTHOR VARIANT
);

VARIENT 형식은 object나 array 등을 포함해 다른 유형의 값을 저장할 때 사용하는 데이터 타입이다. 압축되지 않은 데이터 기준으로 최대 16MB까지의 데이터를 저장할 수 있다.

테이블을 생성했다면 JSON 데이터를 처리하는 File Format을 생성한다.

CREATE FILE FORMAT LIBRARY_CARD_CATALOG.PUBLIC.JSON_FILE_FORMAT 
TYPE = 'JSON' 
COMPRESSION = 'AUTO' 
ENABLE_OCTAL = FALSE
ALLOW_DUPLICATE = FALSE 
STRIP_OUTER_ARRAY = TRUE
STRIP_NULL_VALUES = FALSE
IGNORE_UTF8_ERRORS = FALSE;

이제 file format을 사용하여 파일 데이터를 테이블에 저장한다.

COPY INTO LIBRARY_CARD_CATALOG.PUBLIC.AUTHOR_INGEST_JSON_2
FROM @util_db.public.like_a_window_into_an_s3_bucket
files = ( 'author_with_header.json' )
file_format = ( format_name=LIBRARY_CARD_CATALOG.PUBLIC.JSON_FILE_FORMAT );

이 테이블은 RAW_AUTHOR이라는 컬럼에 네 가지 속성이 저장되어 있는 상태이다.

{
  "AUTHOR_UID": 1,
  "FIRST_NAME": "Fiona",
  "LAST_NAME": "Macdonald",
  "MIDDLE_NAME": null
}

데이터 접근

Dot Notation (:)

VARIENT 데이터의 하위 속성은 Dot Notation을 사용하여 접근할 수 있다.

<column>:<level1_element>
<column>:"<level1_element>"."<level2_element>"

-- 예시
SELECT raw_author:AUTHOR_UID
FROM library_card_catalog.public.author_ingest_json_2;

Bracket Notation ([])

또는 Bracket Notation으로 접근할 수도 있다.

<column>['<level1_element>']['<level2_element>']

-- 예시
SELECT raw_author['FIRST_NAME']
FROM library_card_catalog.public.author_ingest_json_2;

데이터 형변환

VARIENT로 저장된 데이터를 숫자, 문자열 등의 데이터를 다른 유형의 데이터로 형변환 할 때는 :: 또는 CAST 함수를 사용한다. 반대로 값을 VARIANT로 변환할 때는 TO_VARIENT 함수를 사용한다.

SELECT raw_author:AUTHOR_UID::INT AS AUTHOR_UID
     , raw_author['FIRST_NAME']::STRING as FIRST_NAME
     , CAST(raw_author:MIDDLE_NAME AS STRING) as MIDDLE_NAME
     , CAST(raw_author['LAST_NAME'] AS STRING) as LAST_NAME
  FROM AUTHOR_INGEST_JSON;