JSON 모델링의 다른 접근
JSON 모델링의 다른 접근 (Other Approaches to Modeling JSON)
다음은 ClickHouse에서 JSON을 모델링하는 대안들(레거시)이에요. 완전성을 위해 문서화되어 있지만, JSON 타입이 개발되기 전에 적용되던 방식이라 대부분의 사용 사례에서는 일반적으로 권장되지 않거나 적용되지 않아요.
객체 수준 접근 적용하기 같은 스키마에서도 객체마다 다른 기법을 적용할 수 있어요. 예를 들어 어떤 객체는 String 타입으로, 어떤 객체는 Map 타입으로 해결하는 게 가장 좋을 수 있어요. String 타입을 사용하면 더 이상 스키마 결정을 내릴 필요가 없다는 점을 기억하세요. 반대로 Map 키 안에 하위 객체를 중첩할 수도 있는데 — JSON을 나타내는 String까지 포함해서 — 아래에서 보여드릴게요.
출처: 문서
본문
String 타입 사용하기
객체가 매우 동적이고 예측 가능한 구조가 없으며 임의의 중첩 객체를 포함한다면 String 타입을 사용해야 해요. 아래에서 보여드리는 것처럼 쿼리 시점에 JSON 함수를 사용해 값을 추출할 수 있어요. 위에서 설명한 구조화된 접근으로 데이터를 처리하는 것은, 동적이고 변경되기 쉬우며 스키마가 잘 이해되지 않는 JSON을 가진 사용자에게는 종종 실용적이지 않아요. 절대적인 유연성을 위해 필요한 필드를 추출하는 함수를 사용하기 전에 JSON을 그냥 String으로 저장할 수도 있어요. 이것은 JSON을 구조화된 객체로 다루는 것과 극단적으로 반대되는 방식이에요. 이 유연성은 상당한 단점 — 주로 쿼리 문법 복잡성 증가와 성능 저하 — 을 수반해요. 앞서 언급한 원본 person 객체에서 우리는 tags 컬럼의 구조를 보장할 수 없어요. 원본 행을 (지금은 무시할 company.labels까지 포함해서) 삽입하고, Tags 컬럼을 String으로 선언해 볼게요.
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,
`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"}},"dob":"2007-03-31","tags":{"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}}}
Ok.
1 row in set. Elapsed: 0.002 sec.
tags 컬럼을 선택하면 JSON이 문자열로 삽입된 걸 볼 수 있어요.
SELECT tags
FROM people
┌─tags───────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}} │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 row in set. Elapsed: 0.001 sec.
JSONExtract 함수들로 이 JSON에서 값을 가져올 수 있어요. 아래의 간단한 예시를 살펴볼게요.
SELECT JSONExtractString(tags, 'holidays') AS holidays FROM people
┌─holidays──────────────────────────────────────┐
│ [{"year":2024,"location":"Azores, Portugal"}] │
└───────────────────────────────────────────────┘
1 row in set. Elapsed: 0.002 sec.
이 함수들은 String 컬럼 tags에 대한 참조와, 추출할 JSON의 경로가 모두 필요하다는 점을 눈여겨보세요. 중첩 경로는 함수를 중첩해야 해요. 예: JSONExtractUInt(JSONExtractString(tags, 'car'), 'year')는 tags.car.year 컬럼을 추출해요. 중첩 경로 추출은 JSON_QUERY와 JSON_VALUE 함수로 단순화할 수 있어요. arxiv 데이터셋에서 전체 본문을 String으로 간주하는 극단적인 경우를 살펴볼게요.
CREATE TABLE arxiv (
body String
)
ENGINE = MergeTree ORDER BY ()
이 스키마에 삽입하려면 JSONAsString 포맷을 사용해야 해요.
INSERT INTO arxiv SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz', NOSIGN, 'JSONAsString')
0 rows in set. Elapsed: 25.186 sec. Processed 2.52 million rows, 1.38 GB (99.89 thousand rows/s., 54.79 MB/s.)
연도별 출판 논문 수를 세고 싶다고 가정해 볼게요. 문자열만 사용하는 다음 쿼리를 스키마의 구조화 버전과 비교해 보세요.
-- using structured schema
SELECT
toYear(parseDateTimeBestEffort(versions.created[1])) AS published_year,
count() AS c
FROM arxiv_v2
GROUP BY published_year
ORDER BY c ASC
LIMIT 10
┌─published_year─┬─────c─┐
│ 1986 │ 1 │
│ 1988 │ 1 │
│ 1989 │ 6 │
│ 1990 │ 26 │
│ 1991 │ 353 │
│ 1992 │ 3190 │
│ 1993 │ 6729 │
│ 1994 │ 10078 │
│ 1995 │ 13006 │
│ 1996 │ 15872 │
└────────────────┴───────┘
10 rows in set. Elapsed: 0.264 sec. Processed 2.31 million rows, 153.57 MB (8.75 million rows/s., 582.58 MB/s.)
-- using unstructured String
SELECT
toYear(parseDateTimeBestEffort(JSON_VALUE(body, '$.versions[0].created'))) AS published_year,
count() AS c
FROM arxiv
GROUP BY published_year
ORDER BY published_year ASC
LIMIT 10
┌─published_year─┬─────c─┐
│ 1986 │ 1 │
│ 1988 │ 1 │
│ 1989 │ 6 │
│ 1990 │ 26 │
│ 1991 │ 353 │
│ 1992 │ 3190 │
│ 1993 │ 6729 │
│ 1994 │ 10078 │
│ 1995 │ 13006 │
│ 1996 │ 15872 │
└────────────────┴───────┘
10 rows in set. Elapsed: 1.281 sec. Processed 2.49 million rows, 4.22 GB (1.94 million rows/s., 3.29 GB/s.)
Peak memory usage: 205.98 MiB.
여기서 JSON_VALUE(body, '$.versions[0].created')처럼 XPath 표현식으로 JSON을 필터링하는 것을 눈여겨보세요. 문자열 함수는 인덱스가 있는 명시적 타입 변환보다 눈에 띄게 느려요(> 10x). 위 쿼리는 항상 전체 테이블 스캔과 모든 행 처리가 필요해요. 이 데이터셋처럼 작은 데이터셋에서는 여전히 빠르겠지만, 더 큰 데이터셋에서는 성능이 저하돼요. 이 접근의 유연성은 명확한 성능과 문법 비용을 수반하며, 스키마에서 매우 동적인 객체에만 사용해야 해요.
간단한 JSON 함수
위 예시들은 JSON* 함수 계열을 사용해요. 이것들은 simdjson 기반의 전체 JSON 파서를 활용하는데, 파싱이 엄격하고 서로 다른 수준에 중첩된 같은 필드를 구분해요. 이 함수들은 문법적으로 올바르지만 잘 포맷되지 않은 JSON(예: 키 사이에 공백이 두 번)도 처리할 수 있어요. 더 빠르고 엄격한 함수 집합도 있어요. 이 simpleJSON* 함수들은 주로 JSON의 구조와 포맷에 대한 엄격한 가정을 함으로써 더 나은 성능을 제공해요. 구체적으로:
-
필드 이름은 상수여야 해요
-
필드 이름의 일관된 인코딩. 예:
simpleJSONHas('{"abc":"def"}', 'abc') = 1이지만visitParamHas('{\"\\u0061\\u0062\\u0063\":\"def\"}', 'abc') = 0 -
필드 이름은 모든 중첩 구조에 걸쳐 고유해야 해요. 중첩 수준을 구분하지 않고 무차별적으로 매칭돼요. 여러 매칭 필드가 있으면 첫 번째 발생이 사용돼요.
-
문자열 리터럴 밖에 특수 문자가 없어야 해요. 공백도 포함돼요. 다음은 유효하지 않아 파싱되지 않아요.
{"@timestamp": 893964617, "clientip": "40.135.0.0", "request": {"method": "GET", "path": "/images/hm_bg.jpg", "version": "HTTP/1.0"}, "status": 200, "size": 24736}
반면 다음은 올바르게 파싱돼요.
{"@timestamp":893964617,"clientip":"40.135.0.0","request":{"method":"GET",
"path":"/images/hm_bg.jpg","version":"HTTP/1.0"},"status":200,"size":24736}
성능이 중요하고 JSON이 위 요건을 충족하는 어떤 상황에서는 이 함수들이 적절할 수 있어요. 앞선 쿼리를 simpleJSON* 함수로 다시 쓴 예시는 아래와 같아요.
```sql
SELECT
toYear(parseDateTimeBestEffort(simpleJSONExtractString(simpleJSONExtractRaw(body, 'versions'), 'created'))) AS published_year,
count() AS c
FROM arxiv
GROUP BY published_year
ORDER BY published_year ASC
LIMIT 10
┌─published_year─┬─────c─┐
│ 1986 │ 1 │
│ 1988 │ 1 │
│ 1989 │ 6 │
│ 1990 │ 26 │
│ 1991 │ 353 │
│ 1992 │ 3190 │
│ 1993 │ 6729 │
│ 1994 │ 10078 │
│ 1995 │ 13006 │
│ 1996 │ 15872 │
└────────────────┴───────┘
10 rows in set. Elapsed: 0.964 sec. Processed 2.48 million rows, 4.21 GB (2.58 million rows/s., 4.36 GB/s.)
Peak memory usage: 211.49 MiB.
```
위 쿼리는 created 키를 추출하기 위해 simpleJSONExtractString을 사용하는데, 출판 날짜에는 첫 번째 값만 필요하다는 점을 활용해요. 이 경우 simpleJSON* 함수의 한계는 성능 향상에 비해 감수할 만해요.
Map 타입 사용하기
객체가 대부분 한 타입의 임의의 키를 저장하는 데 사용된다면 Map 타입을 고려해 보세요. 이상적으로 고유 키의 수는 수백 개를 넘지 않아야 해요. Map 타입은 타입이 균일한 하위 객체가 있는 객체에도 고려할 수 있어요. 일반적으로 Map 타입은 라벨과 태그 — 예를 들어 로그 데이터의 Kubernetes 팟 라벨 — 에 사용하는 것을 권장해요. Map은 중첩 구조를 나타내는 간단한 방법이지만 몇 가지 주목할 만한 한계가 있어요.
- 필드는 모두 같은 타입이어야 해요.
- 필드가 컬럼으로 존재하지 않기 때문에 서브컬럼에 접근하려면 특별한 map 문법이 필요해요. 전체 객체 자체가 컬럼이에요.
- 서브컬럼에 접근하면 전체
Map값(즉 모든 형제와 각각의 값)을 적재해요. 더 큰 map에서는 상당한 성능 저하가 생길 수 있어요.
String 키 객체를 Map으로 모델링할 때 JSON 키 이름을 저장하는 데 String 키를 사용해요. 그래서 map은 항상 Map(String, T)가 되며, T는 데이터에 따라 달라져요.
기본 값
Map의 가장 단순한 적용은 객체가 값으로 같은 기본 타입을 포함할 때예요. 대부분의 경우 값 T에 String 타입을 사용해요. 이전 person JSON에서 company.labels 객체가 동적이라고 판단됐던 것을 기억해 보세요. 여기서 중요한 점은 이 객체에 String 타입의 키-값 쌍만 추가될 것으로 예상한다는 거예요. 그래서 이것을 Map(String, String)으로 선언할 수 있어요.
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 Map(String,String)),
`dob` Date,
`tags` String
)
ENGINE = MergeTree
ORDER BY username
원본 전체 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","labels":{"type":"database systems","founded":"2021"}},"dob":"2007-03-31","tags":{"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}}}
Ok.
1 row in set. Elapsed: 0.002 sec.
request 객체 안의 이 필드들을 쿼리하려면 map 문법이 필요해요. 예:
SELECT company.labels FROM people
┌─company.labels───────────────────────────────┐
│ {'type':'database systems','founded':'2021'} │
└──────────────────────────────────────────────┘
1 row in set. Elapsed: 0.001 sec.
SELECT company.labels['type'] AS type FROM people
┌─type─────────────┐
│ database systems │
└──────────────────┘
1 row in set. Elapsed: 0.001 sec.
이 데이터를 쿼리하기 위한 전체 Map 함수 집합이 여기에 설명되어 있어요. 데이터가 일관된 타입이 아니라면 필요한 타입 변환을 수행하는 함수들이 있어요.
객체 값
Map 타입은 타입이 일관된 하위 객체가 있는 객체에도 고려할 수 있어요. persons 객체의 tags 키가 일관된 구조를 요구하고, 각 tag의 하위 객체가 name과 time 컬럼을 갖는다고 가정해 볼게요. 그런 JSON 문서의 단순화된 예는 다음과 같아요.
{
"id": 1,
"name": "Clicky McCliickHouse",
"username": "Clicky",
"email": "[email protected]",
"tags": {
"hobby": {
"name": "Diving",
"time": "2024-07-11 14:18:01"
},
"car": {
"name": "Tesla",
"time": "2024-07-11 15:18:23"
}
}
}
이것은 아래처럼 Map(String, Tuple(name String, time DateTime))으로 모델링할 수 있어요.
CREATE TABLE people
(
`id` Int64,
`name` String,
`username` String,
`email` String,
`tags` Map(String, Tuple(name String, time DateTime))
)
ENGINE = MergeTree
ORDER BY username
INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"[email protected]","tags":{"hobby":{"name":"Diving","time":"2024-07-11 14:18:01"},"car":{"name":"Tesla","time":"2024-07-11 15:18:23"}}}
Ok.
1 row in set. Elapsed: 0.002 sec.
SELECT tags['hobby'] AS hobby
FROM people
FORMAT JSONEachRow
{"hobby":{"name":"Diving","time":"2024-07-11 14:18:01"}}
1 row in set. Elapsed: 0.001 sec.
이 경우 map을 적용하는 것은 일반적으로 드물며, 동적 키 이름에 하위 객체가 없도록 데이터를 재모델링해야 함을 시사해요. 예를 들어 위 데이터는 Array(Tuple(key String, name String, time DateTime))를 사용할 수 있도록 다음과 같이 재모델링할 수 있어요.
{
"id": 1,
"name": "Clicky McCliickHouse",
"username": "Clicky",
"email": "[email protected]",
"tags": [
{
"key": "hobby",
"name": "Diving",
"time": "2024-07-11 14:18:01"
},
{
"key": "car",
"name": "Tesla",
"time": "2024-07-11 15:18:23"
}
]
}
Nested 타입 사용하기
Nested 타입은 거의 변경되지 않는 정적 객체를 모델링하는 데 사용할 수 있으며, Tuple과 Array(Tuple)의 대안을 제공해요. JSON에 이 타입을 사용하는 것은 그 동작이 혼란스러운 경우가 많아 일반적으로 피하는 걸 권장해요. Nested의 주요 이점은 서브컬럼을 정렬 키에 사용할 수 있다는 거예요. 아래에서 정적 객체를 모델링하는 데 Nested 타입을 사용하는 예시를 제공할게요. 다음의 간단한 JSON 로그 항목을 살펴볼게요.
{
"timestamp": 897819077,
"clientip": "45.212.12.0",
"request": {
"method": "GET",
"path": "/french/images/hm_nav_bar.gif",
"version": "HTTP/1.0"
},
"status": 200,
"size": 3305
}
request 키를 Nested로 선언할 수 있어요. Tuple과 마찬가지로 서브컬럼을 지정해야 해요.
-- default
SET flatten_nested=1
CREATE table http
(
timestamp Int32,
clientip IPv4,
request Nested(method LowCardinality(String), path String, version LowCardinality(String)),
status UInt16,
size UInt32,
) ENGINE = MergeTree() ORDER BY (status, timestamp);
flatten_nested
flatten_nested 설정이 nested의 동작을 제어해요.
flatten_nested=1
1(기본값)은 임의 깊이의 중첩을 지원하지 않아요. 이 값에서는 중첩 데이터 구조를 같은 길이의 여러 Array 컬럼으로 생각하는 것이 가장 쉬워요. method, path, version 필드는 사실상 각각 별도의 Array(Type) 컬럼이지만, method, path, version 필드의 길이가 같아야 한다는 중요한 제약이 있어요. SHOW CREATE TABLE을 사용하면 이것이 드러나요.
SHOW CREATE TABLE http
CREATE TABLE http
(
`timestamp` Int32,
`clientip` IPv4,
`request.method` Array(LowCardinality(String)),
`request.path` Array(String),
`request.version` Array(LowCardinality(String)),
`status` UInt16,
`size` UInt32
)
ENGINE = MergeTree
ORDER BY (status, timestamp)
아래에서 이 테이블에 삽입할게요.
SET input_format_import_nested_json = 1;
INSERT INTO http
FORMAT JSONEachRow
{"timestamp":897819077,"clientip":"45.212.12.0","request":[{"method":"GET","path":"/french/images/hm_nav_bar.gif","version":"HTTP/1.0"}],"status":200,"size":3305}
여기서 중요한 몇 가지 점이 있어요.
-
JSON을 중첩 구조로 삽입하려면
input_format_import_nested_json설정을 사용해야 해요. 이것 없이는 JSON을 평탄화해야 해요. 즉INSERT INTO http FORMAT JSONEachRow {"timestamp":897819077,"clientip":"45.212.12.0","request":{"method":["GET"],"path":["/french/images/hm_nav_bar.gif"],"version":["HTTP/1.0"]},"status":200,"size":3305} -
중첩 필드
method,path,version은 JSON 배열로 전달해야 해요. 즉{ "@timestamp": 897819077, "clientip": "45.212.12.0", "request": { "method": [ "GET" ], "path": [ "/french/images/hm_nav_bar.gif" ], "version": [ "HTTP/1.0" ] }, "status": 200, "size": 3305 }
컬럼은 점 표기법으로 쿼리할 수 있어요.
SELECT clientip, status, size, `request.method` FROM http WHERE has(request.method, 'GET');
┌─clientip────┬─status─┬─size─┬─request.method─┐
│ 45.212.12.0 │ 200 │ 3305 │ ['GET'] │
└─────────────┴────────┴──────┴────────────────┘
1 row in set. Elapsed: 0.002 sec.
서브컬럼에 Array를 사용한다는 것은 전체 Array 함수를 활용할 수 있다는 뜻이에요. 여기에는 ARRAY JOIN 절도 포함됩니다 — 컬럼이 여러 값을 가질 때 유용해요.
flatten_nested=0
이 설정은 임의 깊이의 중첩을 허용하며, 중첩 컬럼이 Tuple들의 단일 배열로 유지되게 해요 — 실질적으로 Array(Tuple)과 같아져요. 이것이 JSON과Nested를 함께 쓰는 선호되고, 종종 가장 간단한 방법이에요. 아래에서 보여드리듯, 모든 객체가 목록이기만 하면 돼요. 아래에서 테이블을 다시 만들고 행을 다시 삽입할게요.
CREATE TABLE http
(
`timestamp` Int32,
`clientip` IPv4,
`request` Nested(method LowCardinality(String), path String, version LowCardinality(String)),
`status` UInt16,
`size` UInt32
)
ENGINE = MergeTree
ORDER BY (status, timestamp)
SHOW CREATE TABLE http
-- note Nested type is preserved.
CREATE TABLE default.http
(
`timestamp` Int32,
`clientip` IPv4,
`request` Nested(method LowCardinality(String), path String, version LowCardinality(String)),
`status` UInt16,
`size` UInt32
)
ENGINE = MergeTree
ORDER BY (status, timestamp)
INSERT INTO http
FORMAT JSONEachRow
{"timestamp":897819077,"clientip":"45.212.12.0","request":[{"method":"GET","path":"/french/images/hm_nav_bar.gif","version":"HTTP/1.0"}],"status":200,"size":3305}
여기서 중요한 몇 가지 점이 있어요.
-
삽입에
input_format_import_nested_json이 필요하지 않아요. -
SHOW CREATE TABLE에서Nested타입이 보존돼요. 이 컬럼은 사실상Array(Tuple(Nested(method LowCardinality(String), path String, version LowCardinality(String))))예요. -
결과적으로
request를 배열로 삽입해야 해요. 즉{ "timestamp": 897819077, "clientip": "45.212.12.0", "request": [ { "method": "GET", "path": "/french/images/hm_nav_bar.gif", "version": "HTTP/1.0" } ], "status": 200, "size": 3305 }
컬럼은 다시 점 표기법으로 쿼리할 수 있어요.
SELECT clientip, status, size, `request.method` FROM http WHERE has(request.method, 'GET');
┌─clientip────┬─status─┬─size─┬─request.method─┐
│ 45.212.12.0 │ 200 │ 3305 │ ['GET'] │
└─────────────┴────────┴──────┴────────────────┘
1 row in set. Elapsed: 0.002 sec.
예시
위 데이터의 더 큰 예시는 s3의 공개 버킷 s3://datasets-documentation/http/에서 확인할 수 있어요.
SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/http/documents-01.ndjson.gz', NOSIGN, 'JSONEachRow')
LIMIT 1
FORMAT PrettyJSONEachRow
{
"@timestamp": "893964617",
"clientip": "40.135.0.0",
"request": {
"method": "GET",
"path": "\/images\/hm_bg.jpg",
"version": "HTTP\/1.0"
},
"status": "200",
"size": "24736"
}
1 row in set. Elapsed: 0.312 sec.
JSON의 제약과 입력 포맷을 고려해 다음 쿼리로 이 샘플 데이터셋을 삽입해요. 여기서는 flatten_nested=0을 설정해요. 다음 문은 1000만 행을 삽입하므로 실행에 몇 분 걸릴 수 있어요. 필요하면 LIMIT을 적용하세요.
INSERT INTO http
SELECT `@timestamp` AS `timestamp`, clientip, [request], status,
size FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/http/documents-01.ndjson.gz', NOSIGN,
'JSONEachRow');
이 데이터를 쿼리하려면 request 필드를 배열로 접근해야 해요. 아래에서 고정 시간 범위에 걸친 오류와 http 메서드를 요약해 볼게요.
SELECT status, request.method[1] AS method, count() AS c
FROM http
WHERE status >= 400
AND toDateTime(timestamp) BETWEEN '1998-01-01 00:00:00' AND '1998-06-01 00:00:00'
GROUP BY method, status
ORDER BY c DESC LIMIT 5;
┌─status─┬─method─┬─────c─┐
│ 404 │ GET │ 11267 │
│ 404 │ HEAD │ 276 │
│ 500 │ GET │ 160 │
│ 500 │ POST │ 115 │
│ 400 │ GET │ 81 │
└────────┴────────┴───────┘
5 rows in set. Elapsed: 0.007 sec.
쌍을 이루는 배열 사용하기
쌍을 이루는 배열(pairwise arrays)은 JSON을 String으로 나타내는 유연성과 더 구조화된 접근의 성능 사이의 균형을 제공해요. 루트에 새 필드를 잠재적으로 추가할 수 있다는 점에서 스키마가 유연해요. 다만 상당히 더 복잡한 쿼리 문법이 필요하고 중첩 구조와는 호환되지 않아요. 예시로 다음 테이블을 살펴볼게요.
CREATE TABLE http_with_arrays (
keys Array(String),
values Array(String)
)
ENGINE = MergeTree ORDER BY tuple();
이 테이블에 삽입하려면 JSON을 키와 값의 목록으로 구조화해야 해요. 다음 쿼리는 이를 위해 JSONExtractKeysAndValues를 사용하는 방법을 보여줘요.
SELECT
arrayMap(x -> (x.1), JSONExtractKeysAndValues(json, 'String')) AS keys,
arrayMap(x -> (x.2), JSONExtractKeysAndValues(json, 'String')) AS values
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/http/documents-01.ndjson.gz', NOSIGN, 'JSONAsString')
LIMIT 1
FORMAT Vertical
Row 1:
──────
keys: ['@timestamp','clientip','request','status','size']
values: ['893964617','40.135.0.0','{"method":"GET","path":"/images/hm_bg.jpg","version":"HTTP/1.0"}','200','24736']
1 row in set. Elapsed: 0.416 sec.
request 컬럼이 문자열로 표현된 중첩 구조로 남아 있는 걸 눈여겨보세요. 루트에 새 키를 삽입할 수 있어요. JSON 자체에 임의의 차이를 둘 수도 있어요. 로컬 테이블에 삽입하려면 다음을 실행하세요.
INSERT INTO http_with_arrays
SELECT
arrayMap(x -> (x.1), JSONExtractKeysAndValues(json, 'String')) AS keys,
arrayMap(x -> (x.2), JSONExtractKeysAndValues(json, 'String')) AS values
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/http/documents-01.ndjson.gz', NOSIGN, 'JSONAsString')
0 rows in set. Elapsed: 12.121 sec. Processed 10.00 million rows, 107.30 MB (825.01 thousand rows/s., 8.85 MB/s.)
이 구조를 쿼리하려면 필요 키의 인덱스(값의 순서와 일치해야 함)를 식별하기 위해 indexOf 함수를 사용해야 해요. 이것으로 values 배열 컬럼에 접근할 수 있어요. 즉 values[indexOf(keys, 'status')]. request 컬럼에는 여전히 JSON 파싱 메서드가 필요해요 — 이 경우 simpleJSONExtractString이에요.
SELECT toUInt16(values[indexOf(keys, 'status')]) AS status,
simpleJSONExtractString(values[indexOf(keys, 'request')], 'method') AS method,
count() AS c
FROM http_with_arrays
WHERE status >= 400
AND toDateTime(values[indexOf(keys, '@timestamp')]) BETWEEN '1998-01-01 00:00:00' AND '1998-06-01 00:00:00'
GROUP BY method, status ORDER BY c DESC LIMIT 5;
┌─status─┬─method─┬─────c─┐
│ 404 │ GET │ 11267 │
│ 404 │ HEAD │ 276 │
│ 500 │ GET │ 160 │
│ 500 │ POST │ 115 │
│ 400 │ GET │ 81 │
└────────┴────────┴───────┘
5 rows in set. Elapsed: 0.383 sec. Processed 8.22 million rows, 1.97 GB (21.45 million rows/s., 5.15 GB/s.)
Peak memory usage: 51.35 MiB.