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

Проектирование вашей схемы

Хотя вывод схемы может использоваться для установления начальной схемы для данных JSON и выполнения запросов к JSON-файлам на месте, например, в S3, пользователям следует стремиться установить оптимизированную версионированную схему для своих данных. Ниже мы обсудим варианты моделирования структур JSON.

Извлечение по возможности​

При возможности пользователи должны извлекать ключи JSON, которые они часто запрашивают, в столбцы на корне схемы. Это не только упрощает синтаксис запросов, но и позволяет пользователям использовать эти столбцы в своем условии ORDER BY, если это необходимо, или указывать вторичный индекс.

Рассмотрим набор данных arXiv, рассмотренный в руководстве Вывод схемы JSON:

Предположим, мы хотим сделать первое значение versions.created основным ключом сортировки - желательно под именем published_date. Это значение должно быть извлечено либо до вставки, либо во время вставки, используя материализованные представления ClickHouse или материализованные столбцы.

Материализованные столбцы представляют собой самый простой способ извлечения данных во время выполнения запроса и предпочтительны, если логику извлечения можно представить в виде простого SQL-выражения. Например, published_date можно добавить в схему arXiv как материализованный столбец и определить как ключ сортировки следующим образом:

Выражение столбца для вложенных

Вышеуказанное требует доступа к кортежу с использованием нотации versions[1].1, ссылаясь на столбец created по позиции, а не на предпочтительный синтаксис versions.created_at[1].

При загрузке данных столбец будет извлечен:

Поведение материализованного столбца

Значения материализованных столбцов всегда вычисляются во время вставки и не могут быть указаны в запросах INSERT. Материализованные столбцы по умолчанию не будут возвращены в SELECT *. Это сделано для сохранения инварианта, что результат SELECT * всегда может быть вставлен обратно в таблицу с помощью INSERT. Это поведение может быть отключено, установив asterisk_include_materialized_columns=1.

Для более сложных задач фильтрации и преобразования мы рекомендуем использовать материализованные представления.

Статический vs динамический JSON​

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

  1. Простые типы - Если значение ключа является простым типом, независимо от того, является ли оно частью вложенного объекта или находится на корне, убедитесь, что вы выбираете его тип в соответствии с общими лучшими практиками проектирования схем и правилами оптимизации типов. Массивы примитивов, такие как phone_numbers ниже, могут быть смоделированы как Array(<type>), например, Array(String).
  2. Статический vs динамический - Если значение ключа является сложным объектом, т.е. либо объектом, либо массивом объектов, установите, подвержено ли оно изменениям. Объекты, которые редко имеют новые ключи, где добавление нового ключа может быть предсказано и обработано изменением схемы с помощью ALTER TABLE ADD COLUMN, могут считаться статическими. Это включает объекты, в которых только подмножество ключей может быть предоставлено в некоторых документах JSON. Объекты, в которых часто добавляются новые ключи и/или это невозможно предсказать, следует считать динамическими. Чтобы установить, является ли значение статическим или динамическим, см. соответствующие разделы Обработка статических объектов и Обработка динамических объектов ниже.

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

Чтобы проиллюстрировать эти правила, мы используем следующий пример JSON, представляющий человека:

Применяя эти правила:

  • Корневые ключи name, username, email, website могут быть представлены как тип String. Столбец phone_numbers является массивом примитивов типа Array(String), а dob и id имеют тип Date и UInt32 соответственно.
  • Новые ключи не будут добавлены в объект address (только новые объекты адреса), и его можно считать статическим. Если мы углубимся, все под-колонки можно считать примитивами (типа String), кроме geo. Это также статическая структура с двумя столбцами Float32, lat и lon.
  • Столбец tags является динамическим. Мы предполагаем, что новые произвольные теги могут быть добавлены в этот объект любого типа и структуры.
  • Объект company является статическим и всегда будет содержать не более 3 указанных ключей. Подключи name и catchPhrase имеют тип String. Ключ labels является динамическим. Мы предполагаем, что новые произвольные теги могут быть добавлены в этот объект. Значения всегда будут парами ключ-значение типа string.

Обработка статических объектов​

Мы рекомендуем обрабатывать статические объекты с помощью именованных кортежей, т.е. Tuple. Массивы объектов могут быть представлены с помощью массивов кортежей, т.е. Array(Tuple). Внутри самих кортежей столбцы и их соответствующие типы должны быть определены с использованием тех же правил. Это может привести к вложенным кортежам для представления вложенных объектов, как показано ниже.

Чтобы проиллюстрировать это, мы используем предыдущий JSON-пример человека, опуская динамические объекты:

Схема для этой таблицы показана ниже:

Обратите внимание, как столбец company определяется как Tuple(catchPhrase String, name String). Поле address использует Array(Tuple) с вложенным Tuple для представления столбца geo.

JSON может быть вставлен в эту таблицу в его текущей структуре:

В нашем примере выше у нас минимальные данные, но, как показано ниже, мы можем запрашивать поля кортежей по их именам, разделенным точкой.

Обратите внимание, что столбец address.street возвращается как массив. Чтобы запросить конкретный объект внутри массива по позиции, необходимо указать индекс массива после имени столбца. Например, чтобы получить улицу из первого адреса:

Основной недостаток кортежей заключается в том, что подколонки не могут использоваться в ключах сортировки. Следующее, таким образом, не удастся:

Кортежи в ключе сортировки

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

Обработка значений по умолчанию​

Даже если объекты JSON структурированы, они часто бывают разреженными и содержат только подмножество известных ключей. К счастью, тип Tuple не требует, чтобы все столбцы были в полезной нагрузке JSON. Если они не предоставлены, будут использоваться значения по умолчанию.

Рассмотрим нашу ранее созданную таблицу people и следующий разреженный JSON, в котором отсутствуют ключи suite, geo, phone_numbers и catchPhrase.

Мы можем видеть, что эта строка может быть успешно вставлена:

Запрашивая эту единственную строку, мы можем увидеть, что значения по умолчанию были использованы для столбцов (включая под-объекты), которые были опущены:

Различение пустых и null

Если пользователям необходимо различать между пустым значением и не предоставленным, можно использовать Nullable тип. Это должно быть избегнуто, если это абсолютно не требуется, так как это негативно повлияет на производительность хранения и запросов по этим столбцам.

Обработка новых столбцов​

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

Обратите внимание, что ClickHouse по умолчанию будет игнорировать ключи JSON, которые предоставлены в полезной нагрузке и отсутствуют в схеме. Рассмотрим следующий измененный JSON с добавлением ключа nickname:

Этот JSON может быть успешно вставлен с игнорированием ключа nickname:

Столбцы могут быть добавлены в схему с помощью команды ALTER TABLE ADD COLUMN. Значение по умолчанию может быть указано через оператор DEFAULT, который будет использоваться, если оно не указано во время последующих вставок. Ряды, для которых это значение отсутствует (так как они были вставлены до его создания), также будут возвращать это значение по умолчанию. Если значение по умолчанию не указано, будет использоваться значение по умолчанию для типа.

Например:

Обработка динамических объектов​

Существуют два рекомендуемых подхода к обработке динамических объектов:

Следующие правила могут быть применены для определения наиболее подходящего.

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

Примените подход на уровне объекта

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

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

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

Как упоминалось ранее, для оригинального объекта человека мы не можем гарантировать структуру столбца 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').

Функции String значительно медленнее (> 10x), чем явные преобразования типов с использованием индексов. Вышеуказанные запросы всегда требуют полного сканирования таблицы и обработки каждой строки. Хотя эти запросы все еще будут быстрыми на небольших наборах данных, таких как этот, производительность будет ухудшаться на более крупных наборах данных.

Гибкость этого подхода имеет явные затраты на производительность и синтаксис, и его следует использовать только для сильно динамических объектов в схеме.

Простые функции JSON​

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

Доступен более быстрый и строгий набор функций. Эти функции simpleJSON* предлагают потенциально превосходную производительность, в первую очередь за счет строгих предположений относительно структуры и формата JSON. В частности:

  • Имена полей должны быть константами

  • Последовательное кодирование имен полей, например, simpleJSONHas('{"abc":"def"}', 'abc') = 1, но visitParamHas('{"\\u0061\\u0062\\u0063":"def"}', 'abc') = 0

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

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

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

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

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

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

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

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

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

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

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

Рассмотрим наш предыдущий JSON для человека, где объект 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)).