Другие подходы к моделированию JSON
Ниже представлены альтернативы моделированию JSON в ClickHouse. Эти методы документированы для полноты информации и были актуальны до разработки типа JSON, и поэтому, как правило, не рекомендуется использовать их в большинстве случаев.
Для различных объектов в одной схеме могут использоваться различные техники. Например, некоторые объекты лучше всего решаются с помощью типа String, а другие — с помощью типа Map. Обратите внимание, что как только используется тип String, дальнейшие решения о схеме не требуются. Напротив, в Map можно вложить под-объекты — в том числе строку, представляющую JSON, как мы показываем ниже:
Использование типа String
Если объекты высоко динамичны, не имеют предсказуемой структуры и содержат произвольные вложенные объекты, пользователи должны использовать тип String. Значения могут быть извлечены во время запроса с использованием функций JSON, как мы показываем ниже.
Обработка данных с использованием структурированного подхода, описанного выше, часто не является целесообразной для тех пользователей, у кого динамический JSON, который подвержен изменениям или для которого схема не хорошо понятна. Для абсолютной гибкости пользователи могут просто хранить JSON в виде String, прежде чем использовать функции для извлечения полей по мере необходимости. Это представляет собой крайнюю противоположность обработке JSON как структурированного объекта. Эта гибкость влечет за собой затраты с существенными недостатками — прежде всего увеличение сложности синтаксиса запросов, а также ухудшение производительности.
Как уже упоминалось ранее, для оригинального объекта person мы не можем гарантировать структуру колонки tags. Мы вставляем оригинальную строку (включая company.labels, которую мы игнорируем на данный момент), объявляя колонку Tags как String:
Мы можем выбрать колонку tags и увидеть, что JSON был вставлен как строка:
Функции JSONExtract могут быть использованы для извлечения значений из этого JSON. Рассмотрим простой пример ниже:
Обратите внимание, что функции требуют как ссылку на колонку String tags, так и путь в JSON для извлечения. Вложенные пути требуют вложения функций, например, JSONExtractUInt(JSONExtractString(tags, 'car'), 'year'), который извлекает колонку tags.car.year. Извлечение вложенных путей может быть упрощено с помощью функций JSON_QUERY и JSON_VALUE.
Рассмотрим крайний случай с набором данных arxiv, где мы рассматриваем все содержимое как String.
Чтобы вставить в эту схему, нам нужно использовать формат JSONAsString:
Предположим, мы хотим подсчитать количество опубликованных статей по годам. Сравните следующий запрос, использующий только строку, с структурированной версией схемы:
Обратите внимание на использование выражения XPath здесь для фильтрации JSON по методу, т.е. JSON_VALUE(body, '$.versions[0].created').
Строковые функции заметно медленнее (> 10x), чем явные преобразования типов с индексами. Вышеуказанные запросы всегда требуют полного сканирования таблицы и обработки каждой строки. Хотя эти запросы будут по-прежнему быстры на небольшом наборе данных, таком как этот, производительность ухудшится на больших наборах данных.
Гибкость этого подхода сопряжена с очевидными затратами на производительность и сложностью синтаксиса, и его следует использовать только для высоко динамичных объектов в схеме.
Простые функции JSON
Приведенные выше примеры используют семью функций JSON*. Эти функции используют полный парсер JSON, основанный на simdjson, который строго выполняет разбор и сможет различать одно и то же поле, вложенное на разных уровнях. Эти функции способны обрабатывать JSON, который синтаксически корректен, но не хорошо отформатирован, например, двойные пробелы между ключами.
Доступен более быстрый и строгий набор функций. Эти функции simpleJSON* предлагают потенциально более высокую производительность, главным образом, делая строгие предположения относительно структуры и формата JSON. В частности:
- Имена полей должны быть константами
- Последовательное кодирование имен полей, например,
simpleJSONHas('{"abc":"def"}', 'abc') = 1, ноvisitParamHas('{"\\u0061\\u0062\\u0063":"def"}', 'abc') = 0 - Имена полей уникальны во всех вложенных структурах. Не проводится различия между уровнями вложенности, и совпадение происходит indiscriminately. В случае нескольких совпадающих полей используется первое вхождение.
- Никаких специальных символов вне строковых литералов. Это включает пробелы. Следующее является недопустимым и не будет разобрано.
В то время как следующее будет разобрано корректно:
Вышеуказанный запрос использует simpleJSONExtractString для извлечения ключа created, используя тот факт, что мы хотим только первое значение для даты публикации. В этом случае ограничения функций simpleJSON* приемлемы для получения прибыли в производительности.
Использование типа Map
Если объект используется для хранения произвольных ключей, в основном одного типа, рассмотрите использование типа Map. В идеале количество уникальных ключей не должно превышать нескольких сотен. Тип Map также может быть рассмотрен для объектов с под-объектами, при условии, что последние имеют однородность в своих типах. Как правило, мы рекомендуем использовать тип Map для меток и тегов, например, меток пода Kubernetes в данных логов.
Хотя Map предоставляет простой способ представления вложенных структур, у них есть некоторые заметные ограничения:
- Все поля должны быть одного типа.
- Доступ к под-колонкам требует специального синтаксиса карты, поскольку поля не существуют как колонки. Весь объект является колонкой.
- Доступ к подколонке загружает все значение
Map, т.е. все родственные элементы и их соответствующие значения. Для больших карт это может привести к значительному штрафу по производительности.
При моделировании объектов как Maps используется строковой ключ для хранения имени ключа JSON. Следовательно, карта всегда будет Map(String, T), где T зависит от данных.
Примитивные значения
Самое простое применение Map — это когда объект содержит одни и те же примитивные типы в качестве значений. В большинстве случаев это происходит с использованием типа String для значения T.
Рассмотрим наш ранее упомянутый JSON person, где объект company.labels был определен как динамический. Важно, что мы ожидаем, что в этот объект будут добавлены лишь пары ключ-значение типа String. Таким образом, мы можем объявить его как Map(String, String):
Мы можем вставить наш оригинальный полный JSON объект:
Запрос этих полей внутри объекта запроса требует синтаксиса карты, например:
Полный набор функций Map доступен для выполнения запросов, описанных здесь. Если ваши данные не одного типа, существуют функции для выполнения необходимого приведения типов.
Объектные значения
Тип Map также можно использовать для объектов, которые имеют под-объекты, если последние имеют однородность в своих типах.
Предположим, что ключ tags для нашего объекта persons требует согласованной структуры, где под-объект для каждого tag имеет колонки name и time. Упрощенный пример такого JSON-документа может выглядеть следующим образом:
Это можно смоделировать с помощью Map(String, Tuple(name String, time DateTime)), как показано ниже:
Применение карт в этом случае обычно редкое и предполагает, что данные следует переконструировать так, чтобы динамические имена ключей не имели под-объектов. Например, вышеуказанное может быть переустроено следующим образом, что позволит использовать Array(Tuple(key String, name String, time DateTime)).
Использование типа Nested
Тип Nested может быть использован для моделирования статических объектов, которые редко подвержены изменениям, предлагая альтернативу Tuple и Array(Tuple). Мы обычно рекомендуем избегать использования этого типа для JSON, так как его поведение часто вызывать путаницу. Основное преимущество Nested заключается в том, что под-колонки могут использоваться в ключах сортировки.
Ниже мы приводим пример использования типа Nested для моделирования статического объекта. Рассмотрим следующую простую запись лога в JSON:
Мы можем объявить ключ request как Nested. Подобно Tuple, нам требуется указать подколонки.
flatten_nested
Настройка flatten_nested контролирует поведение вложенных.
flatten_nested=1
Значение 1 (по умолчанию) не поддерживает произвольный уровень вложенности. При этом значении проще всего рассматривать вложенную структуру данных как несколько колонок Array одной и той же длины. Параметры method, path и version по сути являются отдельными колонками Array(Type) с одним критическим ограничением: длина полей method, path и version должна быть одинаковой. Если мы используем SHOW CREATE TABLE, это будет проиллюстрировано:
Ниже мы вставляем в эту таблицу:
Несколько важных моментов, которые следует отметить здесь:
- Нам необходимо использовать настройку
input_format_import_nested_json, чтобы вставить JSON как вложенную структуру. Без этого нам нужно будет развернуть JSON, т.е.
- Вложенные поля
method,pathиversionнеобходимо передавать как JSON массивы, т.е.
Колонки можно запрашивать с использованием точечной нотации:
Обратите внимание, что использование Array для под-колонок означает, что можно потенциально использовать полный набор функций Array, включая конструкцию ARRAY JOIN — полезную, если ваши колонки имеют несколько значений.
flatten_nested=0
Это позволяет произвольный уровень вложенности и означает, что вложенные колонки остаются в виде одного массива Tuple — по сути, они становятся теми же, что и Array(Tuple).
Это представляет собой предпочтительный способ, и часто самый простой способ, использовать JSON с Nested. Как мы показываем ниже, это требует только, чтобы все объекты были списком.
Ниже мы вновь создаем нашу таблицу и вставляем строку:
Несколько важных моментов, которые следует отметить здесь:
input_format_import_nested_jsonне требуется для вставки.- Тип
Nestedсохраняется вSHOW CREATE TABLE. Под этой колонкой фактически находитсяArray(Tuple(Nested(method LowCardinality(String), path String, version LowCardinality(String)))) - В результате нам необходимо вставить
requestв виде массива, т.е.
Колонки снова можно запрашивать с использованием точечной нотации:
Пример
Более крупный пример вышеуказанных данных доступен в публичной корзине в s3 по адресу: s3://datasets-documentation/http/.
Учитывая ограничения и формат ввода для JSON, мы вставляем этот образец данных, используя следующий запрос. Здесь мы устанавливаем flatten_nested=0.
Следующее выражение вставляет 10 миллионов строк, поэтому это может занять несколько минут для выполнения. При необходимости используйте LIMIT:
Запрос этих данных требует от нас доступа к полям запроса как к массивам. Ниже мы подводим итоги по ошибкам и методам http за фиксированный период времени.
Использование парных массивов
Парные массивы обеспечивают баланс между гибкостью представлять JSON как строки и производительностью более структурированного подхода. Схема гибкая в том, что любые новые поля могут быть потенциально добавлены в корень. Однако это требует значительно более сложного синтаксиса запросов и не совместимо с вложенными структурами.
В качестве примера рассмотрим следующую таблицу:
Чтобы вставить в эту таблицу, нам необходимо структурировать JSON как список ключей и значений. Следующий запрос иллюстрирует использование JSONExtractKeysAndValues для достижения этой цели:
Обратите внимание, что колонка запроса остается вложенной структурой, представленной как строка. Мы можем вставлять любые новые ключи в корень. Мы также можем иметь произвольные различия в самом JSON. Чтобы вставить в нашу локальную таблицу, выполните следующее:
Запрос этой структуры требует использования функции indexOf для определения индекса необходимого ключа (который должен соответствовать порядку значений). Это можно использовать для доступа к массиву значений колонки, т.е. values[indexOf(keys, 'status')]. Нам по-прежнему требуется метод разбора JSON для колонки запроса — в этом случае simpleJSONExtractString.
