JSON 스키마 추론

JSON 스키마 추론 사용 방법

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으로 자동 감지됩니다.

s3 함수를 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)))                                          │
└────────────────┴─────────────────────────────────────────────────────────────────────────┘

NULL 사용 피하기

많은 컬럼이 널 허용(Nullable)으로 감지된 것을 확인할 수 있습니다. 꼭 필요한 경우가 아니라면 널 허용(Nullable) 유형은 사용하지 않는 것을 권장합니다. 널 허용(Nullable) 적용 여부는 schema_inference_make_columns_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')
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.)

스키마 추론을 사용하면 스키마를 지정하지 않고도 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')
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

​

JSON 데이터 로드

다음은 JSON의 구조가 일관되고 각 경로마다 단일 유형만 있다고 가정합니다.

이전 명령으로 데이터를 로드할 테이블이 생성되었습니다. 이제 다음 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.

​

오류 처리

때로는 잘못된 데이터가 포함될 수 있습니다. 예를 들어, 일부 컬럼의 유형이 올바르지 않거나 JSON 객체의 포맷이 잘못되었을 수 있습니다. 이런 경우 데이터가 삽입 오류를 일으키더라도 일정 수의 행을 무시할 수 있도록 설정 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"
  }
}

이 데이터의 샘플 일부는 줄바꿈으로 구분된 JSON 포맷으로 공개되어 있습니다. 이 파일에 대해 스키마 추론을 시도해 보면, 응답이 지나치게 장황하여 성능이 좋지 않다는 것을 확인할 수 있습니다:

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.

이 포맷은 컬럼에 여러 유형이 있어 서로 맞출 수 없는 경우에도 필수적입니다. 예를 들어, 다음과 같이 줄바꿈으로 구분된 JSON이 들어 있는 sample.json 파일을 살펴보십시오:

{"a":1}
{"a":"22"}

이 경우 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.

유형 강제 변환

이 유형 강제 변환은 여러 설정으로 제어할 수 있습니다. 위 예시는 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')

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', JSONAsObject)
SETTINGS enable_json_type = 1, describe_compact_output = 1

┌─name─┬─type─┐
│ json │ JSON │
└──────┴──────┘

1 row in set. Elapsed: 0.010 sec.

​

추가 읽을거리

데이터 유형 추론에 대해 더 알아보려면 이 문서를 참조하십시오.