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

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

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

Извлечение, где это возможно​

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

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

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

Материализованные колонки представляют собой самый простой способ извлечения данных во время запроса и предпочтительны, если логика извлечения может быть зафиксирована как простое 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 является динамическим. Мы предполагаем, что новые произвольные теги могут быть добавлены в этот объект. Значения всегда будут парами ключ-значение типа строка.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Колонки могут быть добавлены в схему с помощью команды ALTER TABLE ADD COLUMN. Значение по умолчанию может быть указано с помощью условия DEFAULT, которое будет использоваться, если оно не указано во время последующих вставок. Строки, для которых это значение отсутствует (так как они были вставлены до его создания), также будут возвращать это значение по умолчанию. Если не указано значение 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 был вставлен как строка:

Для извлечения значений из этого JSON могут использоваться функции JSONExtract. Рассмотрим простой пример ниже:

Обратите внимание, что функции требуют как ссылки на колонку 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

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

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

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

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

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

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

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

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

При моделировании объектов как Map используется строковой ключ для хранения имени ключа 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)).