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

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

Хотя выявление схемы может использоваться для установления начальной схемы для данных 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.

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

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

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

  1. Примитивные типы - Если значение ключа является примитивным типом, независимо от того, является ли оно частью подпункта или находится на корне, убедитесь, что вы выбрали его тип в соответствии с общими лучшими практиками проектирования схемы и правилами оптимизации типов. Массивы примитивов, такие как phone_numbers ниже, могут быть смоделированы как Array(<type>), например, Array(String).
  2. Статический против динамического - Если значение ключа представляет собой сложный объект, т.е. либо объект, либо массив объектов, определите, подвержен ли он изменениям. Объекты, которые редко имеют новые ключи, где добавление нового ключа может быть предсказано и обработано с помощью изменения схемы через 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 возвращается как 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, которое будет использоваться, если оно не указано при последующих вставках. Строки, для которых это значение отсутствует (так как они были вставлены до его создания), также будут возвращать это значение по умолчанию. Если значение по умолчанию не указано, будет использоваться значение по умолчанию для типа.

Например:

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

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

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

  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)).