JSON 스키마 설계
JSON 스키마 설계 (Designing JSON Schema)
스키마 추론으로 JSON 데이터의 초기 스키마를 만들고 S3 같은 곳의 JSON 데이터 파일을 제자리에서 쿼리할 수 있지만, 데이터를 위해 최적화된 버전 관리 스키마(versioned schema)를 구축하는 것을 목표로 해야 해요. 아래에서 JSON 구조를 모델링하는 권장 접근을 설명할게요.
출처: 문서
본문
정적 vs 동적 JSON
JSON 스키마를 정의하는 주요 작업은 각 키 값에 적절한 타입을 결정하는 거예요. JSON 계층의 각 키에 다음 규칙을 재귀적으로 적용해서 각 키에 적절한 타입을 결정하는 것을 권장해요.
- 기본 타입 (Primitive types) — 키 값이 기본 타입이라면, 하위 객체의 일부든 루트에 있든 상관없이 일반 스키마 설계 모범 사례와 타입 최적화 규칙에 따라 타입을 선택하세요. 아래의
phone_numbers같은 기본 타입 배열은Array(<type>)로 모델링할 수 있어요. 예:Array(String). - 정적 vs 동적 — 키 값이 복잡한 객체(객체 또는 객체 배열)라면 변경될 수 있는지 확인하세요. 새 키가 거의 추가되지 않고, 새 키의 추가를 예측할 수 있으며
ALTER TABLE ADD COLUMN으로 스키마 변경을 처리할 수 있는 객체는 정적(static) 으로 간주해요. 여기에는 일부 JSON 문서에서 키의 일부만 제공되는 객체도 포함돼요. 새 키가 자주 추가되고/예측할 수 없는 객체는 동적(dynamic) 으로 간주해야 해요. 예외는 수백·수천 개의 하위 키가 있는 구조인데, 편의상 동적으로 간주할 수 있어요.
값이 정적인지 동적인지 확인하려면 아래의 정적 객체 처리와 동적 객체 처리 섹션을 참고하세요. 중요: 위 규칙은 재귀적으로 적용해야 해요. 키 값이 동적으로 판단되면 더 이상 평가할 필요 없이 동적 객체 처리 지침을 따르면 돼요. 객체가 정적이면 키 값이 기본 타입이거나 동적 키를 만날 때까지 하위 키를 계속 평가해요. 이 규칙을 설명하기 위해 사람(person)을 나타내는 다음 JSON 예시를 사용할게요.
{
"id": 1,
"name": "Clicky McCliickHouse",
"username": "Clicky",
"email": "[email protected]",
"address": [
{
"street": "Victor Plains",
"suite": "Suite 879",
"city": "Wisokyburgh",
"zipcode": "90566-7771",
"geo": {
"lat": -43.9509,
"lng": -34.4618
}
}
],
"phone_numbers": [
"010-692-6593",
"020-192-3333"
],
"website": "clickhouse.com",
"company": {
"name": "ClickHouse",
"catchPhrase": "The real-time data warehouse for analytics",
"labels": {
"type": "database systems",
"founded": "2021"
}
},
"dob": "2007-03-31",
"tags": {
"hobby": "Databases",
"holidays": [
{
"year": 2024,
"location": "Azores, Portugal"
}
],
"car": {
"model": "Tesla",
"year": 2023
}
}
}
이 규칙들을 적용하면:
- 루트 키
name,username,email,website는String타입으로 표현할 수 있어요.phone_numbers컬럼은Array(String)타입의 기본 배열이고,dob와id는 각각Date와UInt32타입이에요. address객체에는 새 키가 추가되지 않으므로(새 주소 객체만 추가됨) 정적으로 간주할 수 있어요. 재귀하면geo를 제외한 모든 서브컬럼이 기본 타입(String)으로 간주돼요.geo도lat과lon이라는 두Float32컬럼을 가진 정적 구조예요.tags컬럼은 동적(dynamic) 이에요. 이 객체에 어떤 타입과 구조든 새 임의 태그가 추가될 수 있다고 가정해요.company객체는 정적이며 항상 지정된 3개의 키만 포함해요. 하위 키name과catchPhrase는String타입이에요.labels키는 동적이에요. 이 객체에 새 임의 태그가 추가될 수 있다고 가정해요. 값은 항상 String 타입의 키-값 쌍이 될 거예요.
수백·수천 개의 정적 키가 있는 구조는, 컬럼을 정적으로 선언하는 것이 거의 현실적이지 않으므로 동적으로 간주할 수 있어요. 하지만 가능하면 필요하지 않은 경로를 건너뛰어 저장과 추론 오버헤드를 모두 아끼세요.
정적 구조 처리하기
정적 구조는 명명된 튜플, 즉 Tuple로 처리하는 것을 권장해요. 객체 배열은 튜플 배열 즉 Array(Tuple)로 담을 수 있어요. 튜플 안에서도 컬럼과 각 타입을 같은 규칙으로 정의해야 해요. 그러면 아래처럼 중첩 객체를 표현하기 위해 중첩된 Tuple이 생길 수 있어요. 설명을 위해 동적 객체를 뺀 앞선 JSON person 예시를 사용할게요.
{
"id": 1,
"name": "Clicky McCliickHouse",
"username": "Clicky",
"email": "[email protected]",
"address": [
{
"street": "Victor Plains",
"suite": "Suite 879",
"city": "Wisokyburgh",
"zipcode": "90566-7771",
"geo": {
"lat": -43.9509,
"lng": -34.4618
}
}
],
"phone_numbers": [
"010-692-6593",
"020-192-3333"
],
"website": "clickhouse.com",
"company": {
"name": "ClickHouse",
"catchPhrase": "The real-time data warehouse for analytics"
},
"dob": "2007-03-31"
}
이 테이블의 스키마는 아래와 같아요.
CREATE TABLE people
(
`id` Int64,
`name` String,
`username` String,
`email` String,
`address` Array(Tuple(city String, geo Tuple(lat Float32, lng Float32), street String, suite String, zipcode String)),
`phone_numbers` Array(String),
`website` String,
`company` Tuple(catchPhrase String, name String),
`dob` Date
)
ENGINE = MergeTree
ORDER BY username
company 컬럼이 Tuple(catchPhrase String, name String)으로 정의된 걸 눈여겨보세요. address 키는 Array(Tuple)을 사용하고, geo 컬럼을 나타내기 위해 중첩 Tuple을 사용해요. JSON은 현재 구조 그대로 이 테이블에 삽입할 수 있어요.
INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"[email protected]","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}
위 예시에서는 데이터가 적지만, 아래처럼 점으로 구분된 이름으로 튜플 컬럼을 쿼리할 수 있어요.
SELECT
address.street,
company.name
FROM people
┌─address.street────┬─company.name─┐
│ ['Victor Plains'] │ ClickHouse │
└───────────────────┴──────────────┘
address.street 컬럼이 Array로 반환되는 걸 눈여겨보세요. 배열 안의 특정 객체를 위치로 쿼리하려면 컬럼 이름 뒤에 배열 오프셋을 지정해야 해요. 예를 들어 첫 번째 주소에서 street에 접근하려면:
SELECT address.street[1] AS street
FROM people
┌─street────────┐
│ Victor Plains │
└───────────────┘
1 row in set. Elapsed: 0.001 sec.
서브컬럼은 24.12부터 정렬 키에서도 쓸 수 있어요.
CREATE TABLE people
(
`id` Int64,
`name` String,
`username` String,
`email` String,
`address` Array(Tuple(city String, geo Tuple(lat Float32, lng Float32), street String, suite String, zipcode String)),
`phone_numbers` Array(String),
`website` String,
`company` Tuple(catchPhrase String, name String),
`dob` Date
)
ENGINE = MergeTree
ORDER BY company.name
기본값 처리
JSON 객체가 구조화되어 있다 해도 알려진 키의 일부만 제공되는 경우가 많아요. 다행히 Tuple 타입은 JSON 페이로드의 모든 컬럼을 요구하지 않아요. 제공되지 않으면 기본값을 사용해요. 앞선 people 테이블과 suite, geo, phone_numbers, catchPhrase 키가 없는 다음의 sparse JSON을 살펴볼게요.
{
"id": 1,
"name": "Clicky McCliickHouse",
"username": "Clicky",
"email": "[email protected]",
"address": [
{
"street": "Victor Plains",
"city": "Wisokyburgh",
"zipcode": "90566-7771"
}
],
"website": "clickhouse.com",
"company": {
"name": "ClickHouse"
},
"dob": "2007-03-31"
}
아래에서 이 행이 성공적으로 삽입될 수 있음을 볼 수 있어요.
INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"[email protected]","address":[{"street":"Victor Plains","city":"Wisokyburgh","zipcode":"90566-7771"}],"website":"clickhouse.com","company":{"name":"ClickHouse"},"dob":"2007-03-31"}
Ok.
1 row in set. Elapsed: 0.002 sec.
이 단일 행을 쿼리하면 생략된 컬럼(하위 객체 포함)에 기본값이 사용되는 걸 볼 수 있어요.
SELECT *
FROM people
FORMAT PrettyJSONEachRow
{
"id": "1",
"name": "Clicky McCliickHouse",
"username": "Clicky",
"email": "[email protected]",
"address": [
{
"city": "Wisokyburgh",
"geo": {
"lat": 0,
"lng": 0
},
"street": "Victor Plains",
"suite": "",
"zipcode": "90566-7771"
}
],
"phone_numbers": [],
"website": "clickhouse.com",
"company": {
"catchPhrase": "",
"name": "ClickHouse"
},
"dob": "2007-03-31"
}
1 row in set. Elapsed: 0.001 sec.
빈 값과 null 구분하기 값이 비어 있는 것과 제공되지 않은 것을 구분해야 한다면 Nullable 타입을 사용할 수 있어요. 하지만 꼭 필요한 경우가 아니면 피해야 하며, 그 컬럼의 저장과 쿼리 성능에 부정적인 영향을 주기 때문이에요.
새 컬럼 처리
JSON 키가 정적일 때 구조화된 접근이 가장 간단하지만, 스키마 변경을 계획할 수 있다면 — 즉 새 키를 미리 알고 그에 맞게 스키마를 수정할 수 있다면 — 이 접근을 계속 쓸 수 있어요. ClickHouse는 기본적으로 페이로드에 제공되지만 스키마에 없는 JSON 키를 무시한다는 점을 기억하세요. nickname 키가 추가된 다음 수정된 JSON 페이로드를 살펴볼게요.
{
"id": 1,
"name": "Clicky McCliickHouse",
"nickname": "Clicky",
"username": "Clicky",
"email": "[email protected]",
"address": [
{
"street": "Victor Plains",
"suite": "Suite 879",
"city": "Wisokyburgh",
"zipcode": "90566-7771",
"geo": {
"lat": -43.9509,
"lng": -34.4618
}
}
],
"phone_numbers": [
"010-692-6593",
"020-192-3333"
],
"website": "clickhouse.com",
"company": {
"name": "ClickHouse",
"catchPhrase": "The real-time data warehouse for analytics"
},
"dob": "2007-03-31"
}
이 JSON은 nickname 키가 무시된 채 성공적으로 삽입될 수 있어요.
INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","nickname":"Clicky","username":"Clicky","email":"[email protected]","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}
Ok.
1 row in set. Elapsed: 0.002 sec.
컬럼은 ALTER TABLE ADD COLUMN 명령으로 스키마에 추가할 수 있어요. DEFAULT 절로 기본값을 지정할 수 있는데, 이후 삽입에서 지정되지 않으면 사용돼요. 이 값이 없는 행(생성 전에 삽입된 행)도 이 기본값을 반환해요. DEFAULT 값을 지정하지 않으면 타입의 기본값이 사용돼요. 예를 들어:
-- insert initial row (nickname will be ignored)
INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","nickname":"Clicky","username":"Clicky","email":"[email protected]","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}
-- add column
ALTER TABLE people
(ADD COLUMN `nickname` String DEFAULT 'no_nickname')
-- insert new row (same data different id)
INSERT INTO people FORMAT JSONEachRow
{"id":2,"name":"Clicky McCliickHouse","nickname":"Clicky","username":"Clicky","email":"[email protected]","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}
-- select 2 rows
SELECT id, nickname FROM people
┌─id─┬─nickname────┐
│ 2 │ Clicky │
│ 1 │ no_nickname │
└────┴─────────────┘
2 rows in set. Elapsed: 0.001 sec.
반구조화/동적 구조 처리하기
JSON 데이터가 키가 동적으로 추가되고/여러 타입을 가질 수 있는 반구조화 형태라면 JSON 타입을 권장해요. 더 구체적으로, 데이터가 다음과 같을 때 JSON 타입을 사용하세요.
- 시간이 지나면서 바뀔 수 있는 예측 불가능한 키가 있다.
- 타입이 다양한 값을 포함한다 (예: 경로가 어떤 때는 문자열, 어떤 때는 숫자일 수 있음).
- 엄격한 타이핑이 실용적이지 않은 스키마 유연성이 필요하다.
- 수백 수천 개의 정적이지만 명시적으로 선언하기에는 현실적이지 않은 경로가 있다. 이것은 드문 경우예요.
앞선 person JSON에서 company.labels 객체가 동적이라고 판단됐던 것을 살펴볼게요. company.labels가 임의의 키를 포함한다고 가정해 봐요. 또한 이 구조의 어떤 키든 타입이 행 사이에서 일관되지 않을 수 있어요. 예를 들어:
{
"id": 1,
"name": "Clicky McCliickHouse",
"username": "Clicky",
"email": "[email protected]",
"address": [
{
"street": "Victor Plains",
"suite": "Suite 879",
"city": "Wisokyburgh",
"zipcode": "90566-7771",
"geo": {
"lat": -43.9509,
"lng": -34.4618
}
}
],
"phone_numbers": [
"010-692-6593",
"020-192-3333"
],
"website": "clickhouse.com",
"company": {
"name": "ClickHouse",
"catchPhrase": "The real-time data warehouse for analytics",
"labels": {
"type": "database systems",
"founded": "2021",
"employees": 250
}
},
"dob": "2007-03-31",
"tags": {
"hobby": "Databases",
"holidays": [
{
"year": 2024,
"location": "Azores, Portugal"
}
],
"car": {
"model": "Tesla",
"year": 2023
}
}
}
{
"id": 2,
"name": "Analytica Rowe",
"username": "Analytica",
"address": [
{
"street": "Maple Avenue",
"suite": "Apt. 402",
"city": "Dataford",
"zipcode": "11223-4567",
"geo": {
"lat": 40.7128,
"lng": -74.006
}
}
],
"phone_numbers": [
"123-456-7890",
"555-867-5309"
],
"website": "fastdata.io",
"company": {
"name": "FastData Inc.",
"catchPhrase": "Streamlined analytics at scale",
"labels": {
"type": [
"real-time processing"
],
"founded": 2019,
"dissolved": 2023,
"employees": 10
}
},
"dob": "1992-07-15",
"tags": {
"hobby": "Running simulations",
"holidays": [
{
"year": 2023,
"location": "Kyoto, Japan"
}
],
"car": {
"model": "Audi e-tron",
"year": 2022
}
}
}
객체 사이에서 company.labels 컬럼이 키와 타입 측면에서 동적이라는 점을 고려하면, 이 데이터를 모델링할 여러 옵션이 있어요.
- 단일 JSON 컬럼 — 전체 스키마를 단일
JSON컬럼으로 표현해서 그 아래 모든 구조를 동적으로 만드는 방식이에요. - 타겟 JSON 컬럼 —
company.labels컬럼에만JSON타입을 쓰고, 다른 모든 컬럼에는 위에서 사용한 구조화된 스키마를 유지하는 방식이에요.
첫 번째 접근은 이전 방법론과 맞지 않지만, 단일 JSON 컬럼 접근은 프로토타이핑과 데이터 엔지니어링 작업에 유용해요. 규모가 큰 ClickHouse 프로덕션 배포에서는 구조를 구체적으로 지정하고, 가능하면 타겟 동적 하위 구조에만 JSON 타입을 사용하는 것을 권장해요. 엄격한 스키마는 여러 이점이 있어요.
- 데이터 검증 — 엄격한 스키마를 강제하면 특정 구조 밖에서 컬럼 폭발(column explosion) 위험을 피할 수 있어요.
- 컬럼 폭발 위험 회피 — JSON 타입은 잠재적으로 수천 개의 컬럼으로 확장되지만, 서브컬럼이 전용 컬럼으로 저장되면 과도한 수의 컬럼 파일이 생성되어 성능에 영향을 주는 컬럼 파일 폭발로 이어질 수 있어요. 이를 완화하기 위해 JSON이 사용하는 기반 Dynamic 타입은 별도 컬럼 파일로 저장되는 고유 경로 수를 제한하는
max_dynamic_paths파라미터를 제공해요. 임계값에 도달하면 추가 경로는 압축 인코딩 포맷의 공유 컬럼 파일에 저장되어, 유연한 데이터 수집을 지원하면서도 성능과 저장 효율을 유지해요. 다만 이 공유 컬럼 파일 접근은 성능이 좋지 않아요. 하지만 JSON 컬럼은 타입 힌트와 함께 쓸 수 있다는 점을 기억하세요. "힌트된" 컬럼은 전용 컬럼과 같은 성능을 제공해요. - 경로·타입 인트로스펙션 단순화 — JSON 타입이 추론된 타입과 경로를 결정하는 인트로스펙션 함수를 지원하지만, 정적 구조는
DESCRIBE로 더 쉽게 탐색할 수 있어요.
단일 JSON 컬럼
이 접근은 프로토타이핑과 데이터 엔지니어링 작업에 유용해요. 프로덕션에서는 필요한 경우 동적 하위 구조에만 JSON을 사용해 보세요.
성능 고려사항 단일 JSON 컬럼은 필요하지 않은 JSON 경로를 건너뛰고(저장하지 않고) 타입 힌트를 사용해 최적화할 수 있어요. 타입 힌트를 사용하면 사용자가 서브컬럼의 타입을 명시적으로 정의해서 쿼리 시점의 추론과 간접(indirection) 처리를 건너뛸 수 있어요. 이것으로 명시적 스키마를 사용한 것과 같은 성능을 낼 수 있어요. 자세한 내용은 "타입 힌트 사용과 경로 건너뛰기"를 참고하세요.
여기서 단일 JSON 컬럼의 스키마는 단순해요.
SET enable_json_type = 1;
CREATE TABLE people
(
`json` JSON(username String)
)
ENGINE = MergeTree
ORDER BY json.username;
JSON 정의에서 정렬/기본 키에 사용하는 username 컬럼에 타입 힌트를 제공해요. 이것은 ClickHouse가 이 컬럼이 null이 아님을 알게 하고, 어떤 username 서브컬럼을 쓸지 알게 해줘요 (타입마다 여러 개일 수 있으므로 그렇지 않으면 모호해요).
위 테이블에 행을 삽입하는 것은 JSONAsObject 포맷으로 할 수 있어요.
INSERT INTO people FORMAT JSONAsObject
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"[email protected]","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics","labels":{"type":"database systems","founded":"2021","employees":250}},"dob":"2007-03-31","tags":{"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}}}
1 row in set. Elapsed: 0.028 sec.
INSERT INTO people FORMAT JSONAsObject
{"id":2,"name":"Analytica Rowe","username":"Analytica","address":[{"street":"Maple Avenue","suite":"Apt. 402","city":"Dataford","zipcode":"11223-4567","geo":{"lat":40.7128,"lng":-74.006}}],"phone_numbers":["123-456-7890","555-867-5309"],"website":"fastdata.io","company":{"name":"FastData Inc.","catchPhrase":"Streamlined analytics at scale","labels":{"type":["real-time processing"],"founded":2019,"dissolved":2023,"employees":10}},"dob":"1992-07-15","tags":{"hobby":"Running simulations","holidays":[{"year":2023,"location":"Kyoto, Japan"}],"car":{"model":"Audi e-tron","year":2022}}}
1 row in set. Elapsed: 0.004 sec.
SELECT *
FROM people
FORMAT Vertical
Row 1:
──────
json: {"address":[{"city":"Dataford","geo":{"lat":40.7128,"lng":-74.006},"street":"Maple Avenue","suite":"Apt. 402","zipcode":"11223-4567"}],"company":{"catchPhrase":"Streamlined analytics at scale","labels":{"dissolved":"2023","employees":"10","founded":"2019","type":["real-time processing"]},"name":"FastData Inc."},"dob":"1992-07-15","id":"2","name":"Analytica Rowe","phone_numbers":["123-456-7890","555-867-5309"],"tags":{"car":{"model":"Audi e-tron","year":"2022"},"hobby":"Running simulations","holidays":[{"location":"Kyoto, Japan","year":"2023"}]},"username":"Analytica","website":"fastdata.io"}
Row 2:
──────
json: {"address":[{"city":"Wisokyburgh","geo":{"lat":-43.9509,"lng":-34.4618},"street":"Victor Plains","suite":"Suite 879","zipcode":"90566-7771"}],"company":{"catchPhrase":"The real-time data warehouse for analytics","labels":{"employees":"250","founded":"2021","type":"database systems"},"name":"ClickHouse"},"dob":"2007-03-31","email":"[email protected]","id":"1","name":"Clicky McCliickHouse","phone_numbers":["010-692-6593","020-192-3333"],"tags":{"car":{"model":"Tesla","year":"2023"},"hobby":"Databases","holidays":[{"location":"Azores, Portugal","year":"2024"}]},"username":"Clicky","website":"clickhouse.com"}
2 rows in set. Elapsed: 0.005 sec.
인트로스펙션 함수로 추론된 서브컬럼과 그 타입을 결정할 수 있어요. 예를 들어:
SELECT JSONDynamicPathsWithTypes(json) AS paths
FROM people
FORMAT PrettyJsonEachRow
{
"paths": {
"address": "Array(JSON(max_dynamic_types=16, max_dynamic_paths=256))",
"company.catchPhrase": "String",
"company.labels.employees": "Int64",
"company.labels.founded": "String",
"company.labels.type": "String",
"company.name": "String",
"dob": "Date",
"email": "String",
"id": "Int64",
"name": "String",
"phone_numbers": "Array(Nullable(String))",
"tags.car.model": "String",
"tags.car.year": "Int64",
"tags.hobby": "String",
"tags.holidays": "Array(JSON(max_dynamic_types=16, max_dynamic_paths=256))",
"website": "String"
}
}
{
"paths": {
"address": "Array(JSON(max_dynamic_types=16, max_dynamic_paths=256))",
"company.catchPhrase": "String",
"company.labels.dissolved": "Int64",
"company.labels.employees": "Int64",
"company.labels.founded": "Int64",
"company.labels.type": "Array(Nullable(String))",
"company.name": "String",
"dob": "Date",
"id": "Int64",
"name": "String",
"phone_numbers": "Array(Nullable(String))",
"tags.car.model": "String",
"tags.car.year": "Int64",
"tags.hobby": "String",
"tags.holidays": "Array(JSON(max_dynamic_types=16, max_dynamic_paths=256))",
"website": "String"
}
}
2 rows in set. Elapsed: 0.009 sec.
인트로스펙션 함수의 전체 목록은 "Introspection functions"를 참고하세요. 서브 경로는 . 표기법으로 접근할 수 있어요. 예:
SELECT json.name, json.email FROM people
┌─json.name────────────┬─json.email────────────┐
│ Analytica Rowe │ ᴺᵁᴸᴸ │
│ Clicky McCliickHouse │ [email protected] │
└──────────────────────┴───────────────────────┘
2 rows in set. Elapsed: 0.006 sec.
행에 없는 컬럼이 NULL로 반환되는 걸 눈여겨보세요. 또한 같은 타입의 경로에 대해 별도의 서브컬럼이 생성돼요. 예를 들어 company.labels.type에는 String과 Array(Nullable(String)) 둘 다에 대한 서브컬럼이 존재해요. 가능하면 둘 다 반환되지만, .: 문법으로 특정 서브컬럼을 대상으로 할 수 있어요.
SELECT json.company.labels.type
FROM people
┌─json.company.labels.type─┐
│ database systems │
│ ['real-time processing'] │
└──────────────────────────┘
2 rows in set. Elapsed: 0.007 sec.
SELECT json.company.labels.type.:String
FROM people
┌─json.company⋯e.:`String`─┐
│ ᴺᵁᴸᴸ │
│ database systems │
└──────────────────────────┘
2 rows in set. Elapsed: 0.009 sec.
중첩 하위 객체를 반환하려면 ^가 필요해요. 이것은 명시적으로 요청하지 않는 한 많은 컬럼을 읽는 것을 피하기 위한 설계 선택이에요. ^ 없이 접근한 객체는 아래처럼 NULL을 반환해요.
-- sub objects will not be returned by default
SELECT json.company.labels
FROM people
┌─json.company.labels─┐
│ ᴺᵁᴸᴸ │
│ ᴺᵁᴸᴸ │
└─────────────────────┘
2 rows in set. Elapsed: 0.002 sec.
-- return sub objects using ^ notation
SELECT json.^company.labels
FROM people
┌─json.^`company`.labels─────────────────────────────────────────────────────────────────┐
│ {"employees":"250","founded":"2021","type":"database systems"} │
│ {"dissolved":"2023","employees":"10","founded":"2019","type":["real-time processing"]} │
└────────────────────────────────────────────────────────────────────────────────────────┘
2 rows in set. Elapsed: 0.004 sec.
타겟 JSON 컬럼
프로토타이핑과 데이터 엔지니어링 문제에서 유용하지만, 가능하면 프로덕션에서는 명시적 스키마를 사용하는 것을 권장해요. 앞선 예시는 company.labels 컬럼에 단일 JSON 컬럼으로 모델링할 수 있어요.
CREATE TABLE people
(
`id` Int64,
`name` String,
`username` String,
`email` String,
`address` Array(Tuple(city String, geo Tuple(lat Float32, lng Float32), street String, suite String, zipcode String)),
`phone_numbers` Array(String),
`website` String,
`company` Tuple(catchPhrase String, name String, labels JSON),
`dob` Date,
`tags` String
)
ENGINE = MergeTree
ORDER BY username
JSONEachRow 포맷으로 이 테이블에 삽입할 수 있어요.
INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"[email protected]","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics","labels":{"type":"database systems","founded":"2021","employees":250}},"dob":"2007-03-31","tags":{"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}}}
1 row in set. Elapsed: 0.450 sec.
INSERT INTO people FORMAT JSONEachRow
{"id":2,"name":"Analytica Rowe","username":"Analytica","address":[{"street":"Maple Avenue","suite":"Apt. 402","city":"Dataford","zipcode":"11223-4567","geo":{"lat":40.7128,"lng":-74.006}}],"phone_numbers":["123-456-7890","555-867-5309"],"website":"fastdata.io","company":{"name":"FastData Inc.","catchPhrase":"Streamlined analytics at scale","labels":{"type":["real-time processing"],"founded":2019,"dissolved":2023,"employees":10}},"dob":"1992-07-15","tags":{"hobby":"Running simulations","holidays":[{"year":2023,"location":"Kyoto, Japan"}],"car":{"model":"Audi e-tron","year":2022}}}
1 row in set. Elapsed: 0.440 sec.
SELECT *
FROM people
FORMAT Vertical
Row 1:
──────
id: 2
name: Analytica Rowe
username: Analytica
email:
address: [('Dataford',(40.7128,-74.006),'Maple Avenue','Apt. 402','11223-4567')]
phone_numbers: ['123-456-7890','555-867-5309']
website: fastdata.io
company: ('Streamlined analytics at scale','FastData Inc.','{"dissolved":"2023","employees":"10","founded":"2019","type":["real-time processing"]}')
dob: 1992-07-15
tags: {"hobby":"Running simulations","holidays":[{"year":2023,"location":"Kyoto, Japan"}],"car":{"model":"Audi e-tron","year":2022}}
Row 2:
──────
id: 1
name: Clicky McCliickHouse
username: Clicky
email: [email protected]
address: [('Wisokyburgh',(-43.9509,-34.4618),'Victor Plains','Suite 879','90566-7771')]
phone_numbers: ['010-692-6593','020-192-3333']
website: clickhouse.com
company: ('The real-time data warehouse for analytics','ClickHouse','{"employees":"250","founded":"2021","type":"database systems"}')
dob: 2007-03-31
tags: {"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}}
2 rows in set. Elapsed: 0.005 sec.
인트로스펙션 함수로 company.labels 컬럼의 추론된 경로와 타입을 결정할 수 있어요.
SELECT JSONDynamicPathsWithTypes(company.labels) AS paths
FROM people
FORMAT PrettyJsonEachRow
{
"paths": {
"dissolved": "Int64",
"employees": "Int64",
"founded": "Int64",
"type": "Array(Nullable(String))"
}
}
{
"paths": {
"employees": "Int64",
"founded": "String",
"type": "String"
}
}
2 rows in set. Elapsed: 0.003 sec.
타입 힌트 사용과 경로 건너뛰기
타입 힌트를 사용하면 경로와 그 서브컬럼의 타입을 지정해서 불필요한 타입 추론을 막을 수 있어요. company.labels JSON 컬럼 안의 JSON 키 dissolved, employees, founded에 타입을 지정하는 다음 예시를 살펴볼게요.
CREATE TABLE people
(
`id` Int64,
`name` String,
`username` String,
`email` String,
`address` Array(Tuple(
city String,
geo Tuple(
lat Float32,
lng Float32),
street String,
suite String,
zipcode String)),
`phone_numbers` Array(String),
`website` String,
`company` Tuple(
catchPhrase String,
name String,
labels JSON(dissolved UInt16, employees UInt16, founded UInt16)),
`dob` Date,
`tags` String
)
ENGINE = MergeTree
ORDER BY username
INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"[email protected]","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics","labels":{"type":"database systems","founded":"2021","employees":250}},"dob":"2007-03-31","tags":{"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}}}
1 row in set. Elapsed: 0.450 sec.
INSERT INTO people FORMAT JSONEachRow
{"id":2,"name":"Analytica Rowe","username":"Analytica","address":[{"street":"Maple Avenue","suite":"Apt. 402","city":"Dataford","zipcode":"11223-4567","geo":{"lat":40.7128,"lng":-74.006}}],"phone_numbers":["123-456-7890","555-867-5309"],"website":"fastdata.io","company":{"name":"FastData Inc.","catchPhrase":"Streamlined analytics at scale","labels":{"type":["real-time processing"],"founded":2019,"dissolved":2023,"employees":10}},"dob":"1992-07-15","tags":{"hobby":"Running simulations","holidays":[{"year":2023,"location":"Kyoto, Japan"}],"car":{"model":"Audi e-tron","year":2022}}}
1 row in set. Elapsed: 0.440 sec.
이 컬럼들이 이제 우리의 명시적 타입을 갖는다는 걸 눈여겨보세요.
SELECT JSONAllPathsWithTypes(company.labels) AS paths
FROM people
FORMAT PrettyJsonEachRow
{
"paths": {
"dissolved": "UInt16",
"employees": "UInt16",
"founded": "UInt16",
"type": "String"
}
}
{
"paths": {
"dissolved": "UInt16",
"employees": "UInt16",
"founded": "UInt16",
"type": "Array(Nullable(String))"
}
}
2 rows in set. Elapsed: 0.003 sec.
또한 저장하지 않을 JSON 안의 경로는 SKIP 및 SKIP REGEXP 파라미터로 건너뛰어 저장 공간을 최소화하고 불필요한 경로에 대한 불필요한 추론을 피할 수 있어요. 예를 들어 위 데이터에 단일 JSON 컬럼을 사용한다고 가정해 볼게요. address와 company 경로를 건너뛸 수 있어요.
CREATE TABLE people
(
`json` JSON(username String, SKIP address, SKIP company)
)
ENGINE = MergeTree
ORDER BY json.username
INSERT INTO people FORMAT JSONAsObject
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"[email protected]","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics","labels":{"type":"database systems","founded":"2021","employees":250}},"dob":"2007-03-31","tags":{"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}}}
1 row in set. Elapsed: 0.450 sec.
INSERT INTO people FORMAT JSONAsObject
{"id":2,"name":"Analytica Rowe","username":"Analytica","address":[{"street":"Maple Avenue","suite":"Apt. 402","city":"Dataford","zipcode":"11223-4567","geo":{"lat":40.7128,"lng":-74.006}}],"phone_numbers":["123-456-7890","555-867-5309"],"website":"fastdata.io","company":{"name":"FastData Inc.","catchPhrase":"Streamlined analytics at scale","labels":{"type":["real-time processing"],"founded":2019,"dissolved":2023,"employees":10}},"dob":"1992-07-15","tags":{"hobby":"Running simulations","holidays":[{"year":2023,"location":"Kyoto, Japan"}],"car":{"model":"Audi e-tron","year":2022}}}
1 row in set. Elapsed: 0.440 sec.
컬럼들이 데이터에서 제외됐다는 걸 눈여겨보세요.
SELECT *
FROM people
FORMAT PrettyJSONEachRow
{
"json": {
"dob" : "1992-07-15",
"id" : "2",
"name" : "Analytica Rowe",
"phone_numbers" : [
"123-456-7890",
"555-867-5309"
],
"tags" : {
"car" : {
"model" : "Audi e-tron",
"year" : "2022"
},
"hobby" : "Running simulations",
"holidays" : [
{
"location" : "Kyoto, Japan",
"year" : "2023"
}
]
},
"username" : "Analytica",
"website" : "fastdata.io"
}
}
{
"json": {
"dob" : "2007-03-31",
"email" : "[email protected]",
"id" : "1",
"name" : "Clicky McCliickHouse",
"phone_numbers" : [
"010-692-6593",
"020-192-3333"
],
"tags" : {
"car" : {
"model" : "Tesla",
"year" : "2023"
},
"hobby" : "Databases",
"holidays" : [
{
"location" : "Azores, Portugal",
"year" : "2024"
}
]
},
"username" : "Clicky",
"website" : "clickhouse.com"
}
}
2 rows in set. Elapsed: 0.004 sec.
타입 힌트로 성능 최적화하기
타입 힌트는 불필요한 타입 추론을 피하는 방법 그 이상이에요. 저장·처리 간접성을 완전히 제거하고, 최적의 기본 타입을 지정할 수 있게 해줘요. 타입 힌트가 있는 JSON 경로는 항상 전통적인 컬럼처럼 저장되어, 쿼리 시점의 식별자 컬럼(discriminator columns)이나 동적 해석이 필요 없어요. 즉 잘 정의된 타입 힌트를 사용하면 중첩 JSON 키가 처음부터 최상위 컬럼으로 모델링된 것과 같은 성능과 효율을 달성해요. 결과적으로 대부분 일관되지만 여전히 JSON의 유연성이 필요한 데이터셋에서는, 스키마나 수집 파이프라인을 재구성하지 않고도 성능을 유지하는 편리한 방법이 타입 힌트예요.
동적 경로 구성하기
ClickHouse는 각 JSON 경로를 진정한 컬럼형 레이아웃의 서브컬럼으로 저장해서, 압축, SIMD 가속 처리, 최소 디스크 I/O 같은 전통적인 컬럼의 성능 이점을 제공해요. JSON 데이터의 각 고유 경로·타입 조합이 디스크에서 자기만의 컬럼 파일이 될 수 있어요. 예를 들어 서로 다른 타입의 두 JSON 경로가 삽입되면 ClickHouse는 각 구체 타입의 값을 별도의 서브컬럼에 저장해요. 이 서브컬럼들은 독립적으로 접근할 수 있어 불필요한 I/O를 최소화해요. 여러 타입의 컬럼을 쿼리해도 값은 여전히 단일 컬럼형 응답으로 반환된다는 점을 기억하세요. 또한 offset을 활용해 ClickHouse는 이 서브컬럼들이 조밀하게 유지되게 하며, 없는 JSON 경로에 대한 기본값을 저장하지 않아요. 이 접근은 압축을 극대화하고 I/O를 더 줄여요. 하지만 텔레메트리 파이프라인, 로그, 머신러닝 피처 스토어 같은 고카디널리티 또는 매우 변동이 심한 JSON 구조 시나리오에서는 이 동작이 컬럼 파일 폭발로 이어질 수 있어요. 각 새 고유 JSON 경로는 새 컬럼 파일을 만들고, 그 경로의 각 타입 변형은 추가 컬럼 파일을 만들어요. 읽기 성능에는 최적이지만, 파일 디스크립터 고갈, 메모리 사용 증가, 많은 소형 파일로 인한 병합 지연 같은 운영상의 문제를 도입해요. 이를 완화하기 위해 ClickHouse는 오버플로우 서브컬럼이라는 개념을 도입해요. 고유 JSON 경로 수가 임계값을 초과하면 추가 경로는 압축 인코딩 포맷의 단일 공유 파일에 저장돼요. 이 파일은 여전히 쿼리 가능하지만 전용 서브컬럼과 같은 성능 특성의 혜택은 받지 못해요. 이 임계값은 JSON 타입 정의의 max_dynamic_paths 파라미터로 제어돼요. 예를 들어 500개의 동적 경로 임계값을 설정하려면:
CREATE TABLE logs
(
payload JSON(max_dynamic_paths = 500)
)
ENGINE = MergeTree
ORDER BY tuple();
이 파라미터를 너무 높게 설정하지 마세요 — 큰 값은 리소스 소비를 늘리고 효율을 떨어뜨려요. 경험상 10,000 미만으로 유지하세요. 매우 동적인 구조의 워크로드에서는 타입 힌트와 SKIP 파라미터로 저장되는 것을 제한하세요. 이 새 컬럼 타입의 구현이 궁금하다면 상세한 블로그 글 “A New Powerful JSON Data Type for ClickHouse”를 읽어보시길 권장해요.