Вывод схемы JSON
ClickHouse может автоматически определить структуру JSON-данных. Это можно использовать для запроса JSON-данных напрямую, например, на диске с помощью clickhouse-local или на S3, и/или для автоматического создания схем перед загрузкой данных в ClickHouse.
Когда использовать вывод типов
- Последовательная структура - Данные, из которых вы собираетесь выводить типы, содержат все ключи, которые вас интересуют. Вывод типов основан на выборке данных до максимального количества строк или байтов. Данные после выборки с дополнительными столбцами будут игнорироваться и не могут быть запрошены.
- Совместимые типы - Типы данных для конкретных ключей должны быть совместимыми, т.е. должно быть возможно автоматически преобразовать один тип в другой.
Если у вас более динамический JSON, в который добавляются новые ключи и для одного и того же пути возможны несколько типов, смотрите "Работа с полуструктурированными и динамическими данными".
Определение типов
Следующее предполагает, что JSON имеет последовательную структуру и имеет единственный тип для каждого пути.
Наши предыдущие примеры использовали простую версию наборов данных Python PyPI в формате NDJSON. В этом разделе мы исследуем более сложный набор данных со вложенными структурами - набор данных arXiv, содержащий 2.5 миллиона научных работ. Каждая строка в этом наборе данных, распределенная в формате NDJSON, представляет собой опубликованную академическую статью. Пример строки показан ниже:
Эти данные требуют значительно более сложной схемы, чем предыдущие примеры. Мы описываем процесс определения этой схемы ниже, вводя сложные типы, такие как Tuple и Array.
Этот набор данных хранится в публичном S3 bucket по адресу s3://datasets-documentation/arxiv/arxiv.json.gz.
Вы можете увидеть, что вышеуказанный набор данных содержит вложенные JSON-объекты. Хотя пользователи должны составить и версионировать свои схемы, вывод позволяет типам быть выведенными из данных. Это позволяет автоматически генерировать DDL схемы, избегая необходимости создавать ее вручную и ускоряя процесс разработки.
Кроме определения схемы, вывод схемы JSON автоматически выведет формат данных из расширения файла и содержимого. Вышеуказанный файл определяется как NDJSON автоматически.
Используя функцию s3 с командой DESCRIBE, мы можем увидеть типы, которые будут выведены.
Вы можете заметить, что многие столбцы определены как Nullable. Мы не рекомендуем использовать Nullable тип, когда это не абсолютно необходимо. Вы можете использовать schema_inference_make_columns_nullable, чтобы контролировать поведение, когда применяется Nullable.
Мы видим, что большинство столбцов были автоматически определены как String, с правильно определенным столбцом update_date как Date. Столбец versions был создан как Array(Tuple(created String, version String)), чтобы хранить список объектов, а authors_parsed определяется как Array(Array(String)) для вложенных массивов.
Автоопределение дат и временных меток можно контролировать с помощью настроек input_format_try_infer_dates и input_format_try_infer_datetimes соответственно (оба включены по умолчанию). Вывод объектов как кортежей контролируется настройкой input_format_json_try_infer_named_tuples_from_objects. Другие настройки, которые контролируют вывод схемы для JSON, такие как автоопределение чисел, можно найти здесь.
Запрос JSON
Следующее предполагает, что JSON имеет последовательную структуру и имеет единственный тип для каждого пути.
Мы можем полагаться на вывод схемы для запроса JSON-данных на месте. Ниже мы находим ведущих авторов за каждый год, используя тот факт, что даты и массивы автоматически определяются.
Вывод схемы позволяет нам запрашивать JSON-файлы без необходимости указывать схему, ускоряя задачи анализа данных по запросу.
Создание таблиц
Мы можем полагаться на вывод схемы для создания схемы таблицы. Следующая команда CREATE AS EMPTY вызывает вывод DDL для таблицы и создается таблица. Это не загружает никаких данных:
Чтобы подтвердить схему таблицы, мы используем команду SHOW CREATE TABLE:
Выше представлена правильная схема для этих данных. Вывод схемы основан на выборке данных и чтении данных построчно. Значения столбцов извлекаются в соответствии с форматом, с использованием рекурсивных парсеров и эвристик для определения типа для каждого значения. Максимальное количество строк и байтов, считываемых из данных в выводе схемы, контролируется настройками input_format_max_rows_to_read_for_schema_inference (по умолчанию 25000) и input_format_max_bytes_to_read_for_schema_inference (по умолчанию 32 МБ). В случае, если обнаружение не прошло успешно, пользователи могут предоставлять подсказки, как описано здесь.
Создание таблиц из фрагментов
Вышеуказанный пример использует файл на S3 для создания схемы таблицы. Пользователи могут захотеть создать схему из фрагмента с единственной строкой. Это можно сделать с помощью функции format, как показано ниже:
Загрузка данных JSON
Следующее предполагает, что JSON имеет последовательную структуру и имеет единственный тип для каждого пути.
Предыдущие команды создали таблицу, в которую можно загружать данные. Теперь вы можете вставить данные в свою таблицу с помощью следующего INSERT INTO SELECT:
Для примеров загрузки данных из других источников, например, файла, смотрите здесь.
После загрузки мы можем запрашивать наши данные, при необходимости используя формат PrettyJSONEachRow, чтобы показать строки в их оригинальной структуре:
Обработка ошибок
Иногда у вас могут быть неверные данные. Например, в определенных столбцах данные могут не соответствовать правильному типу или JSON-объект может быть неправильно отформатирован. Для этого вы можете использовать настройки input_format_allow_errors_num и input_format_allow_errors_ratio, чтобы разрешить определенное количество строк игнорировать, если данные вызывают ошибки вставки. Кроме того, подсказки могут быть предоставлены для помощи в выводе.
Работа с полуструктурированными и динамическими данными
Наш предыдущий пример использовал JSON, который был статичным с известными именами и типами ключей. Однако это часто не так - ключи могут добавляться или их типы могут изменяться. Это распространено в таких случаях, как данные наблюдаемости.
ClickHouse справляется с этим через выделенный тип JSON.
Если вы знаете, что ваш JSON сильно динамичен с множеством уникальных ключей и несколькими типами для тех же ключей, мы рекомендуем не использовать вывод схемы с JSONEachRow, чтобы пытаться вывести столбец для каждого ключа, даже если данные находятся в формате JSON с разделителями строк.
Рассмотрим следующий пример из расширенной версии вышеуказанного набора данных Python PyPI. Здесь мы добавили произвольный столбец tags с случайными парами ключ-значение.
Образец этих данных доступен в открытом доступе в формате JSON с разделителями строк. Если мы попытаемся вывести типы для этого файла, вы обнаружите, что производительность плохая с крайне многословным ответом:
Основная проблема здесь заключается в том, что формат JSONEachRow используется для вывода. Это пытается вывести тип столбца для каждого ключа в JSON - фактически пытаясь применить статическую схему к данным без использования типа JSON.
С тысячами уникальных столбцов этот подход к выводу замедляет процесс. В качестве альтернативы пользователи могут использовать формат JSONAsObject.
JSONAsObject рассматривает весь вход как один JSON-объект и хранит его в единственном столбце типа JSON, что лучше подходит для сильно динамичных или вложенных JSON-данных.
Этот формат также необходим в случаях, когда столбцы имеют несколько типов, которые невозможно согласовать. Например, рассмотрите файл sample.json со следующим JSON с разделителями строк:
В этом случае ClickHouse может преобразовать столкновение типов и разрешить столбец a как Nullable(String).
Это преобразование типов можно контролировать с помощью нескольких настроек. Приведенный выше пример зависит от настройки input_format_json_read_numbers_as_strings.
Однако некоторые типы несовместимы. Рассмотрим следующий пример:
В этом случае любая форма преобразования типов невозможна. Команда DESCRIBE таким образом завершится неудачно:
В этом случае JSONAsObject рассматривает каждую строку как единый тип JSON (который поддерживает одинаковый столбец с несколькими типами). Это важно:
Дополнительные материалы
Чтобы узнать больше о выводе типов данных, вы можете обратиться к этой странице документации.
