Вывод схемы 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-файлы без необходимости указывать схему, ускоряя выполнение задач анализа данных ad-hoc.
Создание таблиц
Мы можем полагаться на вывод схемы для создания схемы таблицы. Следующая команда 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 (по умолчанию 32MB). В случае неправильного обнаружения пользователи могут предоставить подсказки, как указано здесь.
Создание таблиц из фрагментов
В приведенном выше примере используется файл на S3 для создания схемы таблицы. Пользователи могут захотеть создать схему из фрагмента с одной строкой. Это можно сделать, используя функцию format, как показано ниже:
Загрузка данных JSON
Предыдущие команды создали таблицу, в которую можно загружать данные. Теперь вы можете вставить данные в вашу таблицу, используя следующее INSERT INTO SELECT:
Для примеров загрузки данных из других источников, например, файла, смотрите здесь.
После загрузки мы можем запрашивать наши данные, по желанию используя формат PrettyJSONEachRow, чтобы показать строки в их первоначальной структуре:
Обработка ошибок
Иногда у вас могут быть некорректные данные. Например, определенные колонки могут не иметь правильного типа или неправильно отформатированный JSON. Для этого вы можете использовать настройку input_format_allow_errors_ratio, чтобы разрешить игнорирование определенного числа строк, если данные вызывают ошибки вставки. Кроме того, подсказки могут быть предоставлены для помощи в выводе.
Дополнительные материалы
Чтобы узнать больше о выводе типов данных, вы можете обратиться к этой странице документации.