Перейти к основному содержимому
Перейти к основному содержимому

Используйте JSON, когда это уместно

В ClickHouse теперь есть встроенный тип столбца JSON, предназначенный для полуструктурированных и динамических данных. Важно уточнить, что это тип столбца, а не формат данных — вы можете вставлять JSON в ClickHouse как строку или через поддерживаемые форматы, такие как JSONEachRow, но это само по себе не означает использование типа столбца JSON. Тип JSON следует применять только тогда, когда структура ваших данных является динамической, а не когда вы просто храните JSON.

Когда использовать тип JSON​

Тип JSON предназначен для выполнения запросов, фильтрации и агрегации отдельных полей внутри JSON-объектов с динамической или непредсказуемой структурой. Это достигается за счёт разбиения JSON-объектов на отдельные подстолбцы, что значительно сокращает объём считываемых данных и ускоряет запросы по выбранным полям по сравнению с альтернативами, такими как Map или разбор строк.

Однако это связано с важными компромиссами:

  • Более медленные операции INSERT — Разбиение JSON на подстолбцы, вывод типов и управление гибкими структурами хранения делает вставки медленнее по сравнению с хранением JSON в простом столбце типа String.
  • Медленнее при чтении целых объектов — Если нужно извлекать полные JSON-документы (а не отдельные поля), тип JSON работает медленнее, чем чтение из столбца типа String. Накладные расходы на реконструкцию объектов из отдельных подстолбцов не дают преимуществ, если вы не выполняете запросы на уровне отдельных полей.
  • Накладные расходы на хранение — Поддержка отдельных подстолбцов добавляет структурные накладные расходы по сравнению с хранением JSON как одного строкового значения.

Используйте тип JSON, когда:​

  • Ваши данные имеют динамическую или непредсказуемую структуру с различающимися ключами в разных документах
  • Типы полей или схемы со временем изменяются или различаются между записями
  • Вам нужно выполнять запросы, фильтрацию или агрегирование по конкретным путям внутри объектов JSON, структуру которых невозможно заранее предсказать
  • Ваш сценарий использования включает полуструктурированные данные, такие как логи, события или пользовательский контент с непоследовательными схемами

Используйте столбец String (или структурированные типы), когда:​

  • Структура ваших данных известна и стабильна — в этом случае используйте обычные столбцы или типы Tuple, Array, Dynamic либо Variant
  • Документы JSON рассматриваются как непрозрачные двоичные объекты, которые только сохраняются и извлекаются целиком без анализа на уровне полей
  • Вам не нужно выполнять запросы или фильтрацию по отдельным полям JSON в базе данных
  • JSON используется только как формат передачи/хранения и не анализируется в ClickHouse
Совет

Если JSON — это непрозрачный документ, который не анализируется внутри базы данных и только сохраняется и затем извлекается, его следует хранить в столбце типа String. Преимущества типа JSON проявляются только тогда, когда вам нужно эффективно выполнять запросы, фильтрацию или агрегацию по конкретным полям внутри динамических структур JSON.

Вы также можете комбинировать подходы — использовать стандартные столбцы для предсказуемых верхнеуровневых полей и столбец JSON для динамических частей полезной нагрузки.

Соображения и советы по использованию JSON​

Тип JSON обеспечивает эффективное столбцовое хранение за счёт разворачивания путей в подстолбцы. Но с гибкостью приходит ответственность. Чтобы использовать его эффективно:

  • Явно указывайте типы путей, используя подсказки в определении столбца, чтобы задавать типы для известных подстолбцов и избегать ненужного вывода типов.
  • Пропускайте пути, если вам не нужны их значения, с помощью SKIP и SKIP REGEXP, чтобы сократить объём хранения и повысить производительность.
  • Избегайте слишком большого значения max_dynamic_paths — большие значения увеличивают потребление ресурсов и снижают эффективность. В качестве эмпирического правила держите его ниже 10 000.
Подсказки типов

Подсказки типов дают больше, чем просто способ избежать ненужного вывода типов — они полностью устраняют издержки на косвенное хранение и обработку. JSON‑пути с подсказками типов всегда хранятся так же, как обычные столбцы, устраняя необходимость в столбцах‑дискриминаторах или динамическом разрешении во время выполнения запроса. Это означает, что при хорошо заданных подсказках типов вложенные поля JSON достигают такой же производительности и эффективности, как если бы они изначально моделировались как поля верхнего уровня. В результате для наборов данных, которые в целом однородны, но всё же выигрывают от гибкости JSON, подсказки типов предоставляют удобный способ сохранить производительность без необходимости переработки схемы или конвейера приёма.

Расширенные возможности​

  • JSON-столбцы могут использоваться в первичных ключах, как и любые другие столбцы. Кодеки не могут быть указаны для подстолбцов.
  • Они поддерживают интроспекцию с помощью функций вроде JSONAllPathsWithTypes() и JSONDynamicPaths().
  • Вы можете считывать вложенные подобъекты, используя синтаксис .^.
  • Синтаксис запросов может отличаться от стандартного SQL и может требовать специального приведения типов или операторов для вложенных полей.

Для получения дополнительной информации см. документацию по JSON в ClickHouse или ознакомьтесь с нашей статьёй в блоге Новый мощный тип данных JSON для ClickHouse.

Примеры​

Рассмотрим следующий образец JSON, представляющий строку из набора данных Python PyPI:

{
  "date": "2022-11-15",
  "country_code": "ES",
  "project": "clickhouse-connect",
  "type": "bdist_wheel",
  "installer": "pip",
  "python_minor": "3.9",
  "system": "Linux",
  "version": "0.3.0"
}

Предположим, что эта схема фиксирована и типы могут быть чётко определены. Даже если данные находятся в формате NDJSON (одна JSON-строка в каждой строке), нет необходимости использовать тип JSON для такой схемы. Просто задайте схему, используя обычные типы.

CREATE TABLE pypi (
  `date` Date,
  `country_code` String,
  `project` String,
  `type` String,
  `installer` String,
  `python_minor` String,
  `system` String,
  `version` String
)
ENGINE = MergeTree
ORDER BY (project, date)

и вставьте строки JSON:

INSERT INTO pypi FORMAT JSONEachRow
{"date":"2022-11-15","country_code":"ES","project":"clickhouse-connect","type":"bdist_wheel","installer":"pip","python_minor":"3.9","system":"Linux","version":"0.3.0"}

Рассмотрим набор данных arXiv, содержащий 2,5 млн научных статей. Каждая строка этого набора данных в формате 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",
      ""
    ]
  ]
}

Хотя приведённый здесь JSON и сложный, с вложенными структурами, он предсказуем. Количество и тип полей не изменятся. В этом примере мы могли бы использовать тип JSON, но также можем просто явно задать структуру с помощью типов Tuples и Nested:

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

Снова вставим данные в формате JSON:

INSERT INTO arxiv 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\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",""]]}

Предположим, что добавлен ещё один столбец под названием tags. Если бы это был просто список строк, мы могли бы смоделировать его как Array(String), но давайте предположим, что вы можете добавлять произвольные структуры тегов со смешанными типами (обратите внимание, что score — это строка или целое число). Наш модифицированный JSON-документ:

{
 "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",
   ""
 ]
 ],
 "tags": {
   "tag_1": {
     "name": "ClickHouse user",
     "score": "A+",
     "comment": "A good read, applicable to ClickHouse"
   },
   "28_03_2025": {
     "name": "professor X",
     "score": 10,
     "comment": "Didn't learn much",
     "updates": [
       {
         "name": "professor X",
         "comment": "Wolverine found more interesting"
       }
     ]
   }
 }
}

В этом случае мы могли бы смоделировать документы arXiv либо полностью в формате JSON, либо просто добавить столбец JSON tags. Ниже приведены оба варианта:

CREATE TABLE arxiv
(
  `doc` JSON(update_date Date)
)
ENGINE = MergeTree
ORDER BY doc.update_date
Примечание

Мы указываем подсказку типа данных для столбца update_date в JSON-определении, так как используем его в сортировке / первичном ключе. Это помогает ClickHouse знать, что этот столбец не может быть NULL, и гарантирует, что система понимает, какой подстолбец update_date использовать (для каждого типа их может быть несколько, поэтому без этого возникает неоднозначность).

Мы можем выполнить вставку в эту таблицу и просмотреть автоматически выведенную схему с помощью функции JSONAllPathsWithTypes и формата вывода PrettyJSONEachRow:

INSERT INTO arxiv FORMAT JSONAsObject 
{"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",""]],"tags":{"tag_1":{"name":"ClickHouse user","score":"A+","comment":"A good read, applicable to ClickHouse"},"28_03_2025":{"name":"professor X","score":10,"comment":"Didn't learn much","updates":[{"name":"professor X","comment":"Wolverine found more interesting"}]}}}

SELECT JSONAllPathsWithTypes(doc)
FROM arxiv
FORMAT PrettyJSONEachRow

{
  "JSONAllPathsWithTypes(doc)": {
    "abstract": "String",
    "authors": "String",
    "authors_parsed": "Array(Array(Nullable(String)))",
    "categories": "String",
    "comments": "String",
    "doi": "String",
    "id": "String",
    "journal-ref": "String",
    "license": "String",
    "submitter": "String",
    "tags.28_03_2025.comment": "String",
    "tags.28_03_2025.name": "String",
    "tags.28_03_2025.score": "Int64",
    "tags.28_03_2025.updates": "Array(JSON(max_dynamic_types=16, max_dynamic_paths=256))",
    "tags.tag_1.comment": "String",
    "tags.tag_1.name": "String",
    "tags.tag_1.score": "String",
    "title": "String",
    "update_date": "Date",
    "versions": "Array(JSON(max_dynamic_types=16, max_dynamic_paths=256))"
  }
}

1 row in set. Elapsed: 0.003 sec.

В качестве альтернативы мы могли бы смоделировать это, используя нашу предыдущую схему и столбец JSON tags. Такой подход обычно предпочтителен, так как он сводит к минимуму объём работы по автоматическому выводу, которую должен выполнять ClickHouse:

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)),
    `tags` JSON()
)
ENGINE = MergeTree
ORDER BY update_date

INSERT INTO arxiv 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\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",""]],"tags":{"tag_1":{"name":"ClickHouse user","score":"A+","comment":"A good read, applicable to ClickHouse"},"28_03_2025":{"name":"professor X","score":10,"comment":"Didn't learn much","updates":[{"name":"professor X","comment":"Wolverine found more interesting"}]}}}

Теперь мы можем определить типы подстолбца tags.

SELECT JSONAllPathsWithTypes(tags)
FROM arxiv
FORMAT PrettyJSONEachRow

{
  "JSONAllPathsWithTypes(tags)": {
    "28_03_2025.comment": "String",
    "28_03_2025.name": "String",
    "28_03_2025.score": "Int64",
    "28_03_2025.updates": "Array(JSON(max_dynamic_types=16, max_dynamic_paths=256))",
    "tag_1.comment": "String",
    "tag_1.name": "String",
    "tag_1.score": "String"
  }
}

1 row in set. Elapsed: 0.002 sec.