VARIANT로 저장된 반정형 데이터에 대한 고려 사항¶
이 항목에서는 반정형 데이터가 포함된 VARIANT 값을 로딩 및 사용하기 위한 모범 사례, 일반 지침 및 중요 고려 사항에 대해 설명합니다. 이는 명시적으로 구성된 계층 구조 데이터 또는 JSON, Avro, ORC 및 Parquet과 같은 반정형 데이터 형식에서 로딩된 데이터일 수 있습니다. 이 항목의 정보는 XML 데이터에는 적용되지 않을 수 있습니다.
이 항목의 내용:
데이터 크기 제한¶
VARIANT 데이터 타입의 경우 개별 행에 대한 크기 제한은 16MB입니다.
일부 반정형 데이터 형식(예: JSON)의 경우 데이터 세트는 여러 문서의 단순한 연결일 수 있습니다. 일부 소프트웨어의 JSON 출력은 여러 레코드를 포함하는 하나의 커다란 배열로 구성됩니다. 둘 다 지원되지만 줄 바꿈이나 쉼표로 문서를 구분할 필요는 없습니다.
데이터의 크기가 16 MB를 초과하는 경우, COPY INTO <테이블> 명령에 대해 STRIP_OUTER_ARRAY 파일 형식 옵션을 활성화하여 외부 배열 구조를 제거하고 레코드를 별도의 테이블 행에 로딩하는 것이 좋습니다.
COPY INTO <table>
FROM @~/<file>.json
FILE_FORMAT = (TYPE = 'JSON' STRIP_OUTER_ARRAY = true);
반정형 데이터를 VARIANT 열에 저장하기 vs 중첩 구조 평면화¶
반정형 데이터에 대해 어떤 타입의 작업을 수행하고 싶은지 아직 확실하지 않은 경우에는 임시로 VARIANT 열에 데이터를 저장하는 것이 좋습니다.
대부분이 일반 타입이고 사용 중인 반정형 형식의 기본 데이터 타입만 사용하는 데이터의 경우(예: JSON 형식의 경우 문자열 및 정수), VARIANT 열의 관계형 데이터 및 데이터 작업에 대한 저장 요구 사항 및 쿼리 성능은 매우 유사합니다.
정리를 향상하고 저장소 사용량을 줄이려면 반정형 데이터에 다음이 포함되는 경우 OBJECT 및 키 데이터를 별도의 관계형 열로 데이터 스큐를 수행하는 것이 좋습니다.
날짜 및 타임스탬프, 특히 ISO 8601 이 아닌 날짜 및 타임스탬프, 문자열 값
문자열 내의 숫자
배열
기본이 아닌 값(예: JSON의 날짜 및 타임스탬프)은 VARIANT 열에 로딩될 때 문자열로 저장되므로, 이러한 값에 대한 작업은 해당 데이터 타입으로 관계형 열에 저장할 때보다 속도가 느려지고 공간도 더 많이 사용할 수 있습니다.
데이터에 대한 사용 사례를 알고 있는 경우 일반적인 데이터 세트에 대해 테스트를 수행합니다. 데이터 세트를 테이블의 VARIANT 열에 로드합니다. FLATTEN 함수를 사용하여 쿼리할 OBJECTs 및 키를 별도의 테이블로 추출합니다. 두 테이블에 대해 일반적인 쿼리 세트를 실행하여 최상의 성능을 제공하는 구조를 확인합니다.
키-값 캐스팅¶
예기치 않은 결과를 방지하려면, VARIANT 열에서 키-값을 추출할 때 값을 원하는 데이터 타입으로 캐스팅(:: 표기법 사용)하십시오. 예를 들어, 캐스팅을 수행하지 않고 문자열 키-값을 추출하면 결과가 큰따옴표로 묶입니다(VARIANT 값에 다른 타입이 아닌 문자열이 포함되어 있음을 표시, 즉, "1" 은 문자열이고 1 은 숫자임).
SELECT col1:city;
+----------------------+
| CITY |
|----------------------|
| "Los Angeles" |
+----------------------+
SELECT col1:city::string;
+----------------------+
| CITY |
|----------------------|
| Los Angeles |
+----------------------+
NULL 값¶
Snowflake는 반정형 데이터에서 2가지 타입의 NULL 값을 지원합니다.
SQL NULL: SQL NULL의 의미는 정형 데이터 타입과 반정형 데이터 타입에서 동일하며, 값이 없거나 알 수 없음을 의미합니다.
JSON null(《VARIANT NULL》이라고도 함): VARIANT 열에서 JSON null 값은 SQL NULL 값과 구별하기 위해 《null》이라는 단어가 포함된 문자열로 저장됩니다.
다음 예는 SQL NULL과 JSON null을 비교하여 보여줍니다.
select parse_json(NULL) AS "SQL NULL", parse_json('null') AS "JSON NULL", parse_json('[ null ]') AS "JSON NULL", parse_json('{ "a": null }'):a AS "JSON NULL", parse_json('{ "a": null }'):b AS "ABSENT VALUE"; +----------+-----------+-----------+-----------+--------------+ | SQL NULL | JSON NULL | JSON NULL | JSON NULL | ABSENT VALUE | |----------+-----------+-----------+-----------+--------------| | NULL | null | [ | null | NULL | | | | null | | | | | | ] | | | +----------+-----------+-----------+-----------+--------------+
VARIANT "null" 값을 SQL NULL로 변환하려면 문자열로 캐스팅합니다. 예:
select parse_json('{ "a": null }'):a, to_char(parse_json('{ "a": null }'):a); +-------------------------------+----------------------------------------+ | PARSE_JSON('{ "A": NULL }'):A | TO_CHAR(PARSE_JSON('{ "A": NULL }'):A) | |-------------------------------+----------------------------------------| | null | NULL | +-------------------------------+----------------------------------------+
반정형 데이터 파일 및 세로 행 배치¶
반정형 데이터가 VARIANT 열에 삽입되면 Snowflake는 특정 규칙에 따라 최대한 많은 데이터를 열 형식으로 추출합니다. 나머지는 구문 분석된 반정형 구조의 단일 열로 저장됩니다. 현재 다음과 같은 특성을 가진 요소는 열로 추출되지 않습니다.
단일 《null》 값을 포함하는 요소는 열로 추출되지 않습니다. 이 규칙은 열 형식으로 표시되는 누락 값이 있는 요소가 아닌 《null》 값을 가진 요소에 적용됩니다.
이 규칙을 통해 정보의 손실이 방지됩니다. 즉, VARIANT 《null》 값과 SQL NULL 값 사이에서의 차이가 난독 처리되지 않습니다.
여러 데이터 타입을 포함하는 요소입니다. 예:
한 행의
foo요소에는 숫자가 포함됩니다.{"foo":1}
다른 행의 동일한 요소에 문자열이 포함되어 있습니다.
{"foo":"1"}
반정형 요소를 쿼리할 때:
요소가 열로 추출된 경우 Snowflake의 실행 엔진(열 기반)은 추출된 열만 검색합니다.
요소가 열로 추출되지 않은 경우 실행 엔진은 전체 JSON 구조를 스캔한 후 각 행에 대해 구조를 트래버스하여 값을 출력하므로 성능에 영향을 미칩니다.
이러한 성능 영향을 방지하려면:
관계형 열로 추출한 후 《null》 값을 포함하는 반정형 데이터 요소를 로드합니다.
또는 파일의 《null》 값이 누락된 값을 나타내고 다른 특별한 의미가 없는 경우 반정형 데이터 파일을 로드할 때 파일 형식 옵션 STRIP_NULL_VALUES 을 TRUE로 설정하는 것이 좋습니다. 이 옵션은 《null》 값을 포함하는 OBJECT 요소 또는 ARRAY 요소를 제거합니다.
각 고유 요소가 해당 형식에 고유한 단일 데이터 타입의 값을 저장하는지 확인합니다(예: JSON의 경우 문자열 또는 숫자).
NULL 값 구문 분석하기¶
VARIANT "null" 키-값에서 SQL NULL 값을 출력하려면 TO_CHAR , TO_VARCHAR 함수를 사용하여 값을 문자열로 캐스팅합니다. 예:
SELECT column1
, TO_VARCHAR(PARSE_JSON(column1):a)
FROM
VALUES('{"a" : null}')
, ('{"b" : "hello"}')
, ('{"a" : "world"}');
+-----------------+-----------------------------------+
| COLUMN1 | TO_VARCHAR(PARSE_JSON(COLUMN1):A) |
|-----------------+-----------------------------------|
| {"a" : null} | NULL |
| {"b" : "hello"} | NULL |
| {"a" : "world"} | world |
+-----------------+-----------------------------------+
FLATTEN을 사용하여 WHERE 절에서 결과 필터링하기¶
FLATTEN 함수는 중첩된 값을 별도의 열로 분해합니다. 이 함수를 사용하여 WHERE 절에서 쿼리 결과를 필터링할 수 있습니다.
다음 예는 WHERE 절과 일치하는 키-값 페어를 반환하고 이를 별도의 열에 표시합니다.
CREATE TABLE pets (v variant);
INSERT INTO pets SELECT PARSE_JSON ('{"species":"dog", "name":"Fido", "is_dog":"true"} ');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"Bubby", "is_dog":"false"}');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"dog terror", "is_dog":"false"}');
SELECT a.v, b.key, b.value FROM pets a,LATERAL FLATTEN(input => a.v) b
WHERE b.value LIKE '%dog%';
+-------------------------+---------+--------------+
| V | KEY | VALUE |
|-------------------------+---------+--------------|
| { | species | "dog" |
| "is_dog": "true", | | |
| "name": "Fido", | | |
| "species": "dog" | | |
| } | | |
| { | name | "dog terror" |
| "is_dog": "false", | | |
| "name": "dog terror", | | |
| "species": "cat" | | |
| } | | |
+-------------------------+---------+--------------+
FLATTEN을 사용하여 고유 키 이름 나열하기¶
익숙하지 않은 반정형 데이터 관련 작업을 수행하는 경우에는 OBJECT의 키 이름을 알지 못할 수 있습니다. FLATTEN 함수를 RECURSIVE 인자와 함께 사용하면 OBJECT의 모든 중첩 요소에 있는 고유 키 이름 목록을 반환할 수 있습니다.
SELECT REGEXP_REPLACE(f.path, '\\[[0-9]+\\]', '[]') AS "Path",
TYPEOF(f.value) AS "Type",
COUNT(*) AS "Count"
FROM <table>,
LATERAL FLATTEN(<variant_column>, RECURSIVE=>true) f
GROUP BY 1, 2 ORDER BY 1, 2;
REGEXP_REPLACE 함수는 배열 인덱스 값(예: [0])을 제거하고 이를 대괄호([])로 바꿔 배열 요소를 그룹화합니다.
예:
{"a": 1, "b": 2, "special" : "data"} <--- row 1 of VARIANT column
{"c": 3, "d": 4, "normal" : "data"} <----row 2 of VARIANT column
Output from query:
+---------+---------+-------+
| Path | Type | Count |
|---------+---------+-------|
| a | INTEGER | 1 |
| b | INTEGER | 1 |
| c | INTEGER | 1 |
| d | INTEGER | 1 |
| normal | VARCHAR | 1 |
| special | VARCHAR | 1 |
+---------+---------+-------+
FLATTEN을 사용하여 OBJECT 내 경로 나열하기¶
FLATTEN 함수를 사용하여 고유 키 이름 나열하기 와 관련하여, FLATTEN 함수를 RECURSIVE 인자와 함께 사용하면 OBJECT 내의 모든 키 및 경로를 검색할 수 있습니다.
다음 쿼리는 VARIANT 열에 저장된 모든 데이터 타입에 대한 키, 경로 및 값(VARIANT 《null》 값 포함)을 반환합니다. 이 코드에서는 VARIANT 열의 각 행에 OBJECT가 포함된 것으로 가정합니다.
SELECT
t.<variant_column>,
f.seq,
f.key,
f.path,
REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
TYPEOF(f.value) AS "Type",
f.index,
f.value AS "Current Level Value",
f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f;
다음 쿼리를 첫 번째 쿼리와 유사하지만, 중첩된 OBJECTs 및 ARRAYs를 분해합니다.
SELECT
t.<variant_column>,
f.seq,
f.key,
f.path,
REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
TYPEOF(f.value) AS "Type",
f.value AS "Current Level Value",
f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f
WHERE "Type" NOT IN ('OBJECT','ARRAY');
쿼리에서 반환되는 값은 다음과 같습니다.
- <베리언트_열>
VARIANT 열에 행으로 저장된 OBJECT입니다.
- Seq
행에서 데이터와 연결된 고유 시퀀스 번호입니다.
- 키
데이터 구조에서 값과 연결된 문자열입니다.
- 경로
데이터 구조 내에서 요소로의 경로입니다.
- 레벨
데이터 구조 내에서 키-값 페어의 레벨입니다.
- 타입
값의 데이터 타입입니다.
- 인덱스
데이터 구조에서 요소의 인덱스입니다. ARRAY 값에만 적용되며, 그렇지 않으면 NULL입니다.
- 현재 레벨 값
데이터 구조에서 현재 레벨에서의 값입니다.
- 상위 레벨 값
데이터 구조에서 한 레벨 상위의 값입니다.