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

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

Использование 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 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 для достижения этого:

Обратите внимание, как колонка request остается вложенной структурой, представленной в виде строки. Мы можем добавить любые новые ключи в корень. Мы также можем иметь произвольные различия в самом JSON. Чтобы вставить данные в нашу локальную таблицу, выполните следующее:

Запрос этой структуры требует использовать функцию indexOf для определения индекса необходимого ключа (который должен соответствовать порядку значений). Это можно использовать для доступа к массиву значений т.е. values[indexOf(keys, 'status')]. Нам все еще требуется метод парсинга JSON для колонки request - в этом случае simpleJSONExtractString.