ClickHouse는 JSON 데이터의 구조를 자동으로 파악할 수 있습니다. 이를 사용하면 예를 들어 디스크의 clickhouse-local 또는 S3 버킷에 있는 JSON 데이터를 직접 쿼리하거나, 데이터를 ClickHouse에 로드하기 전에 스키마(schema)를 자동으로 생성할 수 있습니다.
- 일관된 구조 - 유형을 추론할 데이터에 관심 있는 모든 키가 포함되어 있어야 합니다. 유형 추론은 데이터에서 최대 행 수 또는 바이트 수까지 샘플링한 결과를 기반으로 합니다. 샘플 범위 이후의 데이터에 추가 컬럼이 있으면 무시되며, 쿼리할 수 없습니다.
- 일관된 유형 - 특정 키의 데이터 유형은 서로 호환되어야 합니다. 즉, 한 유형을 다른 유형으로 자동 변환할 수 있어야 합니다.
새 키가 추가되고 동일한 경로에 여러 유형이 올 수 있는, 더 동적인 JSON을 사용하는 경우 “반정형 및 동적 데이터 작업”을 참조하십시오.
다음에서는 JSON 구조가 일관되며 각 경로의 유형이 하나로 고정되어 있다고 가정합니다.
이전 예시에서는 NDJSON 포맷의 간단한 Python PyPI 데이터셋 버전을 사용했습니다. 이 섹션에서는 중첩 구조를 포함한 더 복잡한 데이터셋인 arXiv 데이터셋을 살펴봅니다. 이 데이터셋에는 250만 편의 학술 논문이 포함되어 있으며, NDJSON로 제공되는 각 행은 출판된 학술 논문 1편을 나타냅니다. 아래에 예시 행을 보여줍니다:
{
"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으로 자동 감지됩니다.
DESCRIBE 명령과 함께 사용하면 추론될 유형을 확인할 수 있습니다.
DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
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))) │
└────────────────┴─────────────────────────────────────────────────────────────────────────┘
String으로 감지되었고, update_date 컬럼은 올바르게 Date로 감지되었습니다. versions 컬럼은 객체 목록을 저장하기 위해 Array(Tuple(created String, version String))로 생성되었으며, authors_parsed는 중첩 배열을 위해 Array(Array(String))로 정의되었습니다.
다음 내용은 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')
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행. 경과 시간: 20.172초. 처리된 행: 252만 행, 1.39 GB (초당 124,720행, 68.76 MB/s.)
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')
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의 파일을 사용해 테이블 스키마(schema)를 생성합니다. 한 행만 있는 스니펫으로 스키마를 생성할 수도 있습니다. 이는 아래와 같이 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
INSERT INTO SELECT 구문을 사용하여 테이블에 데이터를 삽입할 수 있습니다:
INSERT INTO arxiv SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
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.
input_format_allow_errors_num 및 input_format_allow_errors_ratio를 사용할 수 있습니다. 또한 추론을 돕기 위해 힌트를 제공할 수 있습니다.
이전 예시에서는 키 이름과 유형이 명확히 알려진 정적인 JSON을 사용했습니다. 하지만 실제로는 그렇지 않은 경우가 많습니다. 키가 추가되거나 유형이 변경될 수 있습니다. 이는 관측성 데이터와 같은 사용 사례에서 흔히 볼 수 있습니다.
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"
}
}
DESCRIBE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/pypi_with_tags/sample_rows.json.gz')
-- 결과는 간결성을 위해 생략
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', 'JSONAsObject')
SETTINGS describe_compact_output = 1
┌─name─┬─type─┐
│ json │ JSON │
└──────┴──────┘
1 행 조회됨. Elapsed: 0.005 sec.
sample.json 파일을 살펴보십시오:
이 경우 ClickHouse는 유형 충돌을 강제로 변환하고 컬럼 a를 Nullable(String)으로 해석할 수 있습니다.
DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/json/sample.json')
SETTINGS describe_compact_output = 1
┌─name─┬─type─────────────┐
│ a │ Nullable(String) │
└──────┴──────────────────┘
1 row in set. Elapsed: 0.081 sec.
DESCRIBE 명령이 실패합니다:
DESCRIBE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/json/conflict_sample.json')
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', JSONAsObject)
SETTINGS enable_json_type = 1, describe_compact_output = 1
┌─name─┬─type─┐
│ json │ JSON │
└──────┴──────┘
1 row in set. Elapsed: 0.010 sec.
