Вывод схемы JSON
ClickHouse может автоматически определить структуру данных JSON. Это может быть использовано для запросов к данным JSON напрямую, например, на диске с помощью clickhouse-local или S3 бакетов, и/или для автоматического создания схем перед загрузкой данных в ClickHouse.
Когда использовать вывод типов
- Последовательная структура - Данные, из которых вы собираетесь выводить типы, содержат все столбцы, которые вас интересуют. Данные с дополнительными столбцами, добавленными после вывода типов, будут проигнорированы и не могут быть запрошены.
- Совместимые типы - Типы данных для конкретных столбцов должны быть совместимыми.
Если у вас более динамичный JSON, к которому добавляются новые ключи без достаточного предупреждения о необходимости изменения схемы, например, метки Kubernetes в логах, мы рекомендуем ознакомиться с Проектированием схемы JSON.
Обнаружение типов
Наши предыдущие примеры использовали простую версию набора данных Python PyPI в формате NDJSON. В этом разделе мы исследуем более сложный набор данных с вложенными структурами - набор данных arXiv, содержащий 2.5 миллиона научных работ. Каждая строка в этом наборе данных, распределенном в формате NDJSON, представляет собой опубликованную научную работу. Пример строки показан ниже:
Эти данные требуют гораздо более сложной схемы, чем предыдущие примеры. Мы описываем процесс определения этой схемы ниже, вводя сложные типы, такие как Tuple и Array.
Этот набор данных хранится в публичном S3 бакете по адресу 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 без необходимости указывать схему, ускоряя выполнение задач анализа данных по требованию.
Создание таблиц
Мы можем полагаться на вывод схемы для создания схемы таблицы. Следующая команда 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
Предыдущие команды создавали таблицу, в которую можно загружать данные. Теперь вы можете вставить данные в вашу таблицу, используя следующую команду INSERT INTO SELECT:
Для примеров загрузки данных из других источников, например, файла, смотрите здесь.
После загрузки мы можем запрашивать наши данные, при желании используя формат PrettyJSONEachRow, чтобы показать строки в их оригинальной структуре:
Обработка ошибок
Иногда у вас могут быть плохие данные. Например, специфические столбцы, которые не имеют правильного типа или неправильно отформатированный JSON. Для этого вы можете использовать настройку input_format_allow_errors_ratio, чтобы разрешить игнорировать определенное количество строк, если данные вызывают ошибки при вставке. Кроме того, подсказки могут быть предоставлены для помощи в выводе.
Дополнительное чтение
Чтобы узнать больше о выводе типов данных, вы можете обратиться к этой странице документации.