Другие подходы к моделированию JSON

Следующие альтернативы моделированию JSON в ClickHouse документированы для полноты и применимы до разработки типа JSON, поэтому они, как правило, не рекомендуются и не подходят в большинстве случаев использования.

Примените предметный подход

Разные техники могут быть применены к разным объектам в одной схеме. Например, некоторые объекты могут быть лучше решены с использованием типа String, а другие - с использованием типа Map. Обратите внимание, что после использования типа String больше никаких решений по схеме принимать не нужно. В свою очередь, возможно вложение под-объектов в ключ Map - включая String, представляющий 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

• Имена полей уникальны во всех вложенных структурах. Никто не делает различия между уровнями вложенности, и сопоставление является неразборчивым. В случае нескольких совпадающих полей используется первое вхождение.

• Никаких специальных символов вне строковых литералов. Это включает пробелы. Следующее недействительно и не будет разобрано.

В то время как следующее будет правильно разобрано:

Вышеуказанный запрос использует simpleJSONExtractString для извлечения ключа created, используя тот факт, что нам нужно только первое значение для опубликованной даты. В этом случае ограничения функций simpleJSON* приемлемы для полученной производительности.

Использование Map

Если объект используется для хранения произвольных ключей, в основном одного типа, рассмотрите использование типа Map. В идеале количество уникальных ключей не должно превышать нескольких сотен. Тип Map также можно рассмотреть для объектов с под-объектами, при условии, что последние имеют однородность в своих типах. Как правило, мы рекомендуем использовать тип Map для меток и тегов, например, меток пода Kubernetes в логах.

Хотя Map дают простой способ представления вложенных структур, они имеют некоторые заметные ограничения:

• Поля должны быть одного и того же типа.
• Доступ к под-столбцам требует специального синтаксиса карты, поскольку поля не существуют как колонки. Весь объект является колонкой.
• Доступ к подколонке загружает все значение Map, т.е. все родственные и их соответствующие значения. Для больших карт это может привести к значительному уменьшению производительности.

Строковые ключи

При моделировании объектов как Map используется строковой ключ для хранения имени ключа JSON. Таким образом, карта всегда будет иметь тип Map(String, T), где T зависит от данных.

Примитивные значения

Самое простое использование Map - это когда объект содержит одни и те же примитивные типы в качестве значений. В большинстве случаев это связано с использованием типа String для значения T.

Рассмотрим наш ранее упомянутый объект person, где объект company.labels был признан динамическим. Важно отметить, что мы ожидаем, что в этот объект будут добавляться только пары ключ-значение типа String. Таким образом, мы можем объявить его как Map(String, String):

Мы можем вставить наш оригинальный полный JSON-объект:

Запрос этих полей внутри объекта запроса требует синтаксиса карты, например:

Полный набор функций Map доступен для запроса в это время, описанный здесь. Если ваши данные не одного и того же типа, доступны функции для выполнения необходимейшей приведения типа.

Объектные значения

Тип Map также можно рассмотреть для объектов, имеющих под-объекты, при условии, что последние имеют консистентность в своих типах.

Предположим, что ключ tags для нашего объекта persons требует консистентной структуры, где под-объект для каждого тега имеет колонки 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.