Другие подходы к моделированию 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 может быть потенциально использован, включая 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, чтобы определить индекс нужного ключа (который должен быть согласован с порядком значений). Это может быть использовано для доступа к массиву значений i.e. values[indexOf(keys, 'status')]. По-прежнему требуется метод разбора JSON для столбца запроса - в этом случае simpleJSONExtractString.