JSON 스키마 추론
JSON 스키마 추론 (JSON Schema Inference)
ClickHouse는 JSON 데이터의 구조를 자동으로 결정할 수 있어요. 이것은 clickhouse-local이나 S3 버킷 같은 디스크 위의 JSON 데이터를 직접 쿼리하고, 그리고/또는 데이터를 ClickHouse에 적재하기 전에 스키마를 자동으로 만드는 데 사용할 수 있어요.
출처: 문서
본문
타입 추론을 언제 사용할까
- 일관된 구조 — 타입을 추론할 데이터에 관심 있는 모든 키가 포함되어 있어야 해요. 타입 추론은 최대 행 수나 바이트까지 데이터를 샘플링하는 방식을 기반으로 해요. 샘플 이후의 추가 컬럼이 있는 데이터는 무시되며 쿼리할 수 없어요.
- 일관된 타입 — 특정 키에 대한 데이터 타입이 호환되어야 해요. 즉 한 타입을 다른 타입으로 자동 변환(coerce)할 수 있어야 해요.
새 키가 추가되고 같은 경로에 여러 타입이 가능한 더 동적인 JSON이라면 "반구조화·동적 데이터 사용"을 참고하세요.
타입 감지하기
다음 내용은 JSON이 일관되게 구조화되어 있고 각 경로에 단일 타입이 있다고 가정해요. 앞선 예시들은 Python PyPI 데이터셋의 단순 버전을 NDJSON 포맷으로 사용했어요. 이 섹션에서는 중첩 구조를 가진 더 복잡한 데이터셋 — 250만 편의 학술 논문을 담은 arXiv 데이터셋 — 을 살펴볼게요. NDJSON으로 배포되는 이 데이터셋의 각 행은 출판된 학술 논문 하나를 나타내요. 예시 행은 아래와 같아요.
{
"id": "2101.11408",
"submitter": "Daniel Lemire",
"authors": "Daniel Lemire",
"title": "Number Parsing at a Gigabyte per Second",
"comments": "Software at https://github.com/fastfloat/fast_float and\n https://github.com/lemire/simple_fastfloat_benchmark/",
"journal-ref": "Software: Practice and Experience 51 (8), 2021",
"doi": "10.1002/spe.2984",
"report-no": null,
"categories": "cs.DS cs.MS",
"license": "http://creativecommons.org/licenses/by/4.0/",
"abstract": "With disks and networks providing gigabytes per second ....\n",
"versions": [
{
"created": "Mon, 11 Jan 2021 20:31:27 GMT",
"version": "v1"
},
{
"created": "Sat, 30 Jan 2021 23:57:29 GMT",
"version": "v2"
}
],
"update_date": "2022-11-07",
"authors_parsed": [
[
"Lemire",
"Daniel",
""
]
]
}
이 데이터는 이전 예시들보다 훨씬 복잡한 스키마를 요구해요. 아래에서 Tuple과 Array 같은 복잡한 타입을 소개하며 이 스키마를 정의하는 과정을 설명할게요. 이 데이터셋은 s3://datasets-documentation/arxiv/arxiv.json.gz 공개 S3 버킷에 저장되어 있어요. 위 데이터셋이 중첩 JSON 객체를 포함하는 걸 볼 수 있죠. 스키마를 작성하고 버전 관리해야 하지만, 추론을 통해 타입을 데이터에서 얻을 수 있어요. 이렇게 하면 스키마 DDL을 자동 생성해서 수동으로 만들 필요를 없애고 개발 과정을 가속화할 수 있어요.
자동 포맷 감지 스키마 감지뿐 아니라 JSON 스키마 추론은 파일 확장자와 내용에서 데이터 포맷을 자동 추론해요. 위 파일은 그 결과 자동으로 NDJSON으로 감지돼요.
s3 함수를 DESCRIBE 명령과 함께 사용하면 추론될 타입이 보여요.
DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz', NOSIGN)
SETTINGS describe_compact_output = 1
┌─name───────────┬─type────────────────────────────────────────────────────────────────────┐
│ id │ Nullable(String) │
│ submitter │ Nullable(String) │
│ authors │ Nullable(String) │
│ title │ Nullable(String) │
│ comments │ Nullable(String) │
│ journal-ref │ Nullable(String) │
│ doi │ Nullable(String) │
│ report-no │ Nullable(String) │
│ categories │ Nullable(String) │
│ license │ Nullable(String) │
│ abstract │ Nullable(String) │
│ versions │ Array(Tuple(created Nullable(String),version Nullable(String))) │
│ update_date │ Nullable(Date) │
│ authors_parsed │ Array(Array(Nullable(String))) │
└────────────────┴─────────────────────────────────────────────────────────────────────────┘
NULL 피하기 많은 컬럼이 Nullable로 감지되는 걸 볼 수 있어요. 꼭 필요하지 않다면 Nullable 타입 사용을 권장하지 않아요. schema_inference_make_columns_nullable로 Nullable이 적용되는 시점의 동작을 제어할 수 있어요.
대부분의 컬럼이 자동으로 String으로 감지되고, update_date 컬럼은 Date로 올바르게 감지되는 걸 볼 수 있어요. versions 컬럼은 객체 목록을 저장하기 위해 Array(Tuple(created String, version String))로, authors_parsed는 중첩 배열을 위해 Array(Array(String))로 정의돼요.
타입 감지 제어 날짜·날짜시간의 자동 감지는 각각 input_format_try_infer_dates와 input_format_try_infer_datetimes 설정으로 제어할 수 있어요 (둘 다 기본적으로 활성화됨). 객체를 튜플로 추론하는 것은 input_format_json_try_infer_named_tuples_from_objects 설정으로 제어돼요. 숫자 자동 감지 같은 JSON 스키마 추론을 제어하는 다른 설정은 여기에서 찾을 수 있어요.
JSON 쿼리하기
다음 내용은 JSON이 일관되게 구조화되어 있고 각 경로에 단일 타입이 있다고 가정해요. 스키마 추론에 의존해서 JSON 데이터를 제자리에서 쿼리할 수 있어요. 아래에서 날짜와 배열이 자동 감지된다는 점을 활용해 연도별 상위 저자들을 찾아볼게요.
SELECT
toYear(update_date) AS year,
authors,
count() AS c
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz', NOSIGN)
GROUP BY
year,
authors
ORDER BY
year ASC,
c DESC
LIMIT 1 BY year
┌─year─┬─authors────────────────────────────────────┬───c─┐
│ 2007 │ The BABAR Collaboration, B. Aubert, et al │ 98 │
│ 2008 │ The OPAL collaboration, G. Abbiendi, et al │ 59 │
│ 2009 │ Ashoke Sen │ 77 │
│ 2010 │ The BABAR Collaboration, B. Aubert, et al │ 117 │
│ 2011 │ Amelia Carolina Sparavigna │ 21 │
│ 2012 │ ZEUS Collaboration │ 140 │
│ 2013 │ CMS Collaboration │ 125 │
│ 2014 │ CMS Collaboration │ 87 │
│ 2015 │ ATLAS Collaboration │ 118 │
│ 2016 │ ATLAS Collaboration │ 126 │
│ 2017 │ CMS Collaboration │ 122 │
│ 2018 │ CMS Collaboration │ 138 │
│ 2019 │ CMS Collaboration │ 113 │
│ 2020 │ CMS Collaboration │ 94 │
│ 2021 │ CMS Collaboration │ 69 │
│ 2022 │ CMS Collaboration │ 62 │
│ 2023 │ ATLAS Collaboration │ 128 │
│ 2024 │ ATLAS Collaboration │ 120 │
└──────┴────────────────────────────────────────────┴─────┘
18 rows in set. Elapsed: 20.172 sec. Processed 2.52 million rows, 1.39 GB (124.72 thousand rows/s., 68.76 MB/s.)
스키마 추론 덕분에 스키마를 지정하지 않고도 JSON 파일을 쿼리할 수 있어서, 임시 데이터 분석 작업을 가속화할 수 있어요.
테이블 만들기
스키마 추론을 통해 테이블의 스키마를 만들 수 있어요. 다음 CREATE AS EMPTY 명령은 테이블의 DDL을 추론하고 테이블을 생성해요. 이것은 데이터를 적재하지 않아요.
CREATE TABLE arxiv
ENGINE = MergeTree
ORDER BY update_date EMPTY
AS SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz', NOSIGN)
SETTINGS schema_inference_make_columns_nullable = 0
테이블 스키마를 확인하려면 SHOW CREATE TABLE 명령을 사용해요.
SHOW CREATE TABLE arxiv
CREATE TABLE arxiv
(
`id` String,
`submitter` String,
`authors` String,
`title` String,
`comments` String,
`journal-ref` String,
`doi` String,
`report-no` String,
`categories` String,
`license` String,
`abstract` String,
`versions` Array(Tuple(created String, version String)),
`update_date` Date,
`authors_parsed` Array(Array(String))
)
ENGINE = MergeTree
ORDER BY update_date
위 코드가 이 데이터에 대한 올바른 스키마예요. 스키마 추론은 데이터를 샘플링하고 행 단위로 읽는 것을 기반으로 해요. 컬럼 값은 포맷에 따라 추출되며, 재귀 파서와 휴리스틱으로 각 값의 타입을 결정해요. 스키마 추론에서 데이터에서 읽는 최대 행 수와 바이트는 input_format_max_rows_to_read_for_schema_inference(기본 25000)와 input_format_max_bytes_to_read_for_schema_inference(기본 32MB) 설정으로 제어돼요. 감지가 올바르지 않다면 여기에 설명된 대로 힌트를 제공할 수 있어요.
스니펫에서 테이블 만들기
위 예시는 S3의 파일로 테이블 스키마를 만들어요. 단일 행 스니펫으로 스키마를 만들 수도 있어요. 아래처럼 format 함수로 할 수 있어요.
CREATE TABLE arxiv
ENGINE = MergeTree
ORDER BY update_date EMPTY
AS SELECT *
FROM format(JSONEachRow, '{"id":"2101.11408","submitter":"Daniel Lemire","authors":"Daniel Lemire","title":"Number Parsing at a Gigabyte per Second","comments":"Software at https://github.com/fastfloat/fast_float and","doi":"10.1002/spe.2984","report-no":null,"categories":"cs.DS cs.MS","license":"http://creativecommons.org/licenses/by/4.0/","abstract":"Withdisks and networks providing gigabytes per second ","versions":[{"created":"Mon, 11 Jan 2021 20:31:27 GMT","version":"v1"},{"created":"Sat, 30 Jan 2021 23:57:29 GMT","version":"v2"}],"update_date":"2022-11-07","authors_parsed":[["Lemire","Daniel",""]]}') SETTINGS schema_inference_make_columns_nullable = 0
SHOW CREATE TABLE arxiv
CREATE TABLE arxiv
(
`id` String,
`submitter` String,
`authors` String,
`title` String,
`comments` String,
`doi` String,
`report-no` String,
`categories` String,
`license` String,
`abstract` String,
`versions` Array(Tuple(created String, version String)),
`update_date` Date,
`authors_parsed` Array(Array(String))
)
ENGINE = MergeTree
ORDER BY update_date
JSON 데이터 적재
다음 내용은 JSON이 일관되게 구조화되어 있고 각 경로에 단일 타입이 있다고 가정해요. 이전 명령들이 데이터를 적재할 테이블을 만들었어요. 이제 다음 INSERT INTO SELECT로 데이터를 테이블에 삽입할 수 있어요.
INSERT INTO arxiv SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz', NOSIGN)
0 rows in set. Elapsed: 38.498 sec. Processed 2.52 million rows, 1.39 GB (65.35 thousand rows/s., 36.03 MB/s.)
Peak memory usage: 870.67 MiB.
파일 같은 다른 소스에서 데이터를 적재하는 예시는 여기를 참고하세요. 적재가 끝나면 PrettyJSONEachRow 포맷을 사용해 행을 원래 구조로 보여주면서 데이터를 쿼리할 수 있어요.
SELECT *
FROM arxiv
LIMIT 1
FORMAT PrettyJSONEachRow
{
"id": "0704.0004",
"submitter": "David Callan",
"authors": "David Callan",
"title": "A determinant of Stirling cycle numbers counts unlabeled acyclic",
"comments": "11 pages",
"journal-ref": "",
"doi": "",
"report-no": "",
"categories": "math.CO",
"license": "",
"abstract": " We show that a determinant of Stirling cycle numbers counts unlabeled acyclic\nsingle-source automata.",
"versions": [
{
"created": "Sat, 31 Mar 2007 03:16:14 GMT",
"version": "v1"
}
],
"update_date": "2007-05-23",
"authors_parsed": [
[
"Callan",
"David"
]
]
}
1 row in set. Elapsed: 0.009 sec.
오류 처리
때로는 잘못된 데이터가 있을 수 있어요. 예를 들어 올바른 타입이 아닌 특정 컬럼이나 잘못 포맷된 JSON 객체 같은 것들이요. 이를 위해 input_format_allow_errors_num과 input_format_allow_errors_ratio 설정으로, 데이터가 삽입 오류를 유발하는 경우 무시할 행 수를 허용할 수 있어요. 또한 힌트를 제공해 추론을 도울 수도 있어요.
반구조화·동적 데이터 사용하기
앞선 예시들은 키 이름과 타입이 잘 알려진 정적 JSON을 사용했어요. 하지만 실제로는 그렇지 않은 경우가 많아요. 키가 추가되거나 타입이 바뀔 수 있거든요. 이런 경우는 Observability 데이터 같은 사용 사례에서 흔해요. ClickHouse는 전용 JSON 타입으로 이를 처리해요. JSON이 매우 동적이고 고유 키가 많으며 같은 키에 여러 타입이 있는 경우, 데이터가 줄바꿈 구분 JSON 포맷이라도 JSONEachRow로 각 키마다 컬럼을 추론하려고 하는 것은 권장하지 않아요. 위 Python PyPI 데이터셋의 확장 버전에서 온 다음 예시를 살펴볼게요. 여기서는 임의의 키-값 쌍을 가진 tags 컬럼을 추가했어요.
{
"date": "2022-09-22",
"country_code": "IN",
"project": "clickhouse-connect",
"type": "bdist_wheel",
"installer": "bandersnatch",
"python_minor": "",
"system": "",
"version": "0.2.8",
"tags": {
"5gTux": "f3to*PMvaTYZsz!*rtzX1",
"nD8CV": "value"
}
}
이 데이터의 샘플은 줄바꿈 구분 JSON 포맷으로 공개되어 있어요. 이 파일에 스키마 추론을 시도하면 성능이 나쁘고 응답도 지나치게 장황할 거예요.
DESCRIBE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/pypi_with_tags/sample_rows.json.gz', NOSIGN)
-- result omitted for brevity
9 rows in set. Elapsed: 127.066 sec.
여기서 핵심 문제는 추론에 JSONEachRow 포맷이 사용된다는 거예요. 이것은 JSON의 키마다 컬럼 타입 하나를 추론하려고 시도해요. 즉 JSON 타입을 사용하지 않고 데이터에 정적 스키마를 적용하려는 것과 같아요. 수천 개의 고유 컬럼이 있으면 이 추론 접근은 느려요. 대안으로 JSONAsObject 포맷을 사용할 수 있어요. JSONAsObject는 입력 전체를 단일 JSON 객체로 취급해 JSON 타입의 단일 컬럼에 저장하므로, 매우 동적이거나 중첩된 JSON 페이로드에 더 잘 맞아요.
DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/pypi_with_tags/sample_rows.json.gz', NOSIGN, 'JSONAsObject')
SETTINGS describe_compact_output = 1
┌─name─┬─type─┐
│ json │ JSON │
└──────┴──────┘
1 row in set. Elapsed: 0.005 sec.
이 포맷은 컬럼이 조정할 수 없는 여러 타입을 가질 때도 필수적이에요. 예를 들어 sample.json 파일에 다음 줄바꿈 구분 JSON이 있다고 해볼게요.
{"a":1}
{"a":"22"}
이 경우 ClickHouse는 타입 충돌을 변환해서 a 컬럼을 Nullable(String)로 해결할 수 있어요.
DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/json/sample.json', NOSIGN)
SETTINGS describe_compact_output = 1
┌─name─┬─type─────────────┐
│ a │ Nullable(String) │
└──────┴──────────────────┘
1 row in set. Elapsed: 0.081 sec.
타입 변환(Type coercion) 이 타입 변환은 여러 설정으로 제어할 수 있어요. 위 예시는 input_format_json_read_numbers_as_strings 설정에 의존해요.
하지만 일부 타입은 호환되지 않아요. 다음 예시를 살펴볼게요.
{"a":1}
{"a":{"b":2}}
이 경우 어떤 형태의 타입 변환도 불가능해요. 그래서 DESCRIBE 명령이 실패해요.
DESCRIBE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/json/conflict_sample.json', NOSIGN)
Elapsed: 0.755 sec.
Received exception from server (version 24.12.1):
Code: 636. DB::Exception: Received from sql-clickhouse.clickhouse.com:9440. DB::Exception: The table structure cannot be extracted from a JSON format file. Error:
Code: 53. DB::Exception: Automatically defined type Tuple(b Int64) for column 'a' in row 1 differs from type defined by previous rows: Int64. You can specify the type for this column using setting schema_inference_hints.
이 경우 JSONAsObject는 각 행을 단일 JSON 타입으로 간주해요 (같은 컬럼이 여러 타입을 갖는 것을 지원함). 이것이 필수적이에요.
DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/json/conflict_sample.json', NOSIGN, JSONAsObject)
SETTINGS enable_json_type = 1, describe_compact_output = 1
┌─name─┬─type─┐
│ json │ JSON │
└──────┴──────┘
1 row in set. Elapsed: 0.010 sec.
더 읽어보기
데이터 타입 추론에 대해 더 알아보려면 이 문서 페이지를 참고하세요.