Табличные функции file, s3, url, hdfs, azureBlobStorage.
structure, задающий структуру входных данных. Если этот аргумент не указан или задан как auto, структура будет автоматически определена по данным.
Пример:
Допустим, у нас есть файл hobbies.jsonl в формате JSONEachRow в каталоге user_files со следующим содержимым:
JSONEachRow был автоматически определён по расширению файла .jsonl.
Автоматически определённую структуру можно посмотреть с помощью запроса DESCRIBE:
Движки таблиц File, S3, URL, HDFS, azureBlobStorage
CREATE TABLE, структура таблицы будет автоматически определена по данным.
Пример:
Используем файл hobbies.jsonl. Мы можем создать таблицу с движком File, используя данные из этого файла:
clickhouse-local есть необязательный параметр -S/--structure, задающий структуру входных данных. Если этот параметр не указан или имеет значение auto, структура будет определена по данным.
Пример:
Возьмем файл hobbies.jsonl. Мы можем выполнить запрос к данным из этого файла с помощью clickhouse-local:
file/s3/url/hdfs,
можно брать структуру из таблицы, в которую выполняется вставка, вместо того чтобы извлекать её из данных.
Это может повысить производительность вставки, поскольку определение схемы может занимать некоторое время. Кроме того, это полезно, если у таблицы оптимизированная схема, поэтому
преобразования между типами не потребуются.
Существует специальная настройка use_structure_from_insertion_table_in_table_functions,
которая управляет этим поведением. У неё есть 3 возможных значения:
- 0 - табличная функция будет извлекать структуру из данных.
- 1 - табличная функция будет использовать структуру таблицы, в которую выполняется вставка.
- 2 - ClickHouse автоматически определит, можно ли использовать структуру таблицы, в которую выполняется вставка, или нужно определение схемы. Значение по умолчанию.
hobbies1 со следующей структурой:
hobbies.jsonl:
hobbies2 со следующей структурой:
hobbies.jsonl:
SELECT присутствуют в таблице, поэтому ClickHouse будет использовать структуру таблицы, в которую выполняется вставка.
Обратите внимание: это работает только для входных форматов, поддерживающих чтение подмножества столбцов, таких как JSONEachRow, TSKV, Parquet и т. д. (например, для формата TSV это не сработает).
Пример 3:
Давайте создадим таблицу hobbies3 со следующей структурой:
hobbies.jsonl:
id используется в запросе SELECT, но в таблице такого столбца нет (в ней есть столбец с именем identifier),
поэтому ClickHouse не может использовать структуру таблицы для вставки, и будет использовано автоматическое определение схемы.
Пример 4:
Давайте создадим таблицу hobbies4 со следующей структурой:
hobbies.jsonl:
SELECT над столбцом hobbies выполняются некоторые операции перед вставкой в таблицу, поэтому ClickHouse не может использовать структуру таблицы, в которую выполняется вставка, и будет использоваться автоматическое определение схемы.
Для большинства входных форматов при определении схемы считывается часть данных, чтобы определить их структуру, и этот процесс может занимать некоторое время.
Чтобы не определять одну и ту же схему каждый раз, когда ClickHouse считывает данные из одного и того же файла, определённая схема кэшируется, и при повторном обращении к тому же файлу ClickHouse использует схему из кэша.
Существуют специальные настройки, которые управляют этим кэшем:
schema_inference_cache_max_elements_for_{file/s3/hdfs/url/azure}- максимальное количество кэшированных схем для соответствующей табличной функции. Значение по умолчанию —4096. Эти настройки следует задавать в конфигурации сервера.schema_inference_use_cache_for_{file,s3,hdfs,url,azure}- позволяет включать или отключать использование кэша для определения схемы. Эти настройки можно использовать в запросах.
url, могут не содержать информацию о времени последнего изменения; для таких случаев есть специальная настройка
schema_inference_cache_require_modification_time_for_url. Отключение этой настройки позволяет использовать схему из кэша без времени последнего изменения для таких файлов.
Также есть системная таблица schema_inference_cache со всеми текущими схемами в кэше и системный запрос SYSTEM CLEAR SCHEMA CACHE [FOR File/S3/URL/HDFS],
который позволяет очищать кэш схем для всех источников или для конкретного источника.
Примеры:
Давайте попробуем определить структуру примера набора данных из S3 github-2022.ndjson.gz и посмотрим, как работает кэш определения схемы:
system.schema_inference_cache:
input_format_max_rows_to_read_for_schema_inference (по умолчанию 25000) и input_format_max_bytes_to_read_for_schema_inference (по умолчанию 32Mb).
По умолчанию все автоматически определённые типы — Nullable, но это можно изменить с помощью настройки schema_inference_make_columns_nullable (см. примеры в разделе настроек).
В форматах JSON ClickHouse разбирает значения в соответствии со спецификацией JSON, а затем пытается подобрать для них наиболее подходящий тип данных.
Рассмотрим, как это работает, какие типы могут быть выведены автоматически и какие специальные настройки можно использовать в JSON formats.
Примеры
Здесь и далее в примерах будет использоваться табличная функция format.
Integers, Floats, Bools, Strings:
null, ClickHouse определит типы на основе остальных элементов массива:
input_format_json_infer_array_of_dynamic_from_array_of_different_types включён (по умолчанию он включён), такой массив получит тип Array(Dynamic):
input_format_json_try_infer_named_tuples_from_objects, при определении схемы ClickHouse попытается вывести именованный Tuple из объектов JSON.
Полученный именованный Tuple будет содержать все элементы из всех соответствующих объектов JSON в выборке данных.
input_format_json_infer_array_of_dynamic_from_array_of_different_types отключена, в форматах JSON массивы Array с элементами разных типов рассматриваются как безымянные Tuple.
null или пусты, мы используем типы соответствующих значений из других строк:
input_format_json_read_objects_as_strings и input_format_json_try_infer_named_tuples_from_objects.
String, если включена настройка input_format_json_infer_incomplete_types_as_strings; в противном случае будет сгенерировано исключение:
input_format_json_try_infer_numbers_from_strings
input_format_json_try_infer_named_tuples_from_objects
Query
Response
Query
Response
input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects
input_format_json_try_infer_named_tuples_from_objects) вместо генерации исключения.
Это позволяет считывать объекты JSON как именованные Tuple даже при наличии неоднозначных путей.
По умолчанию отключено.
Примеры
Если настройка отключена:
Query
Response
Query
Response
input_format_json_read_objects_as_strings
input_format_json_try_infer_named_tuples_from_objects.
input_format_json_read_numbers_as_strings
input_format_json_read_bools_as_numbers
input_format_json_read_bools_as_strings
input_format_json_read_arrays_as_strings
input_format_json_infer_incomplete_types_as_strings
Null/{}/[].
В форматах JSON любое значение можно считывать как String, если включены все соответствующие настройки (по умолчанию они все включены), и за счёт использования типа String для ключей с неизвестными типами можно избежать ошибок вида Cannot determine type for column 'column_name' by first 25000 rows of data, most likely this column contains only Nulls or empty Arrays/Maps при определении схемы.
Пример:
Query
Response
input_format_csv_use_best_effort_in_schema_inference,
и тогда ClickHouse будет рассматривать все столбцы как String.
Если настройка input_format_csv_detect_header включена, ClickHouse попытается определить заголовок с именами столбцов (и, возможно, типами) при определении схемы. Эта настройка включена по умолчанию.
Примеры:
Целые числа, числа с плавающей точкой, логические значения, строки:
input_format_csv_use_best_effort_in_schema_inference:
input_format_csv_detect_header включен):
Только имена:
input_format_csv_try_infer_numbers_from_strings
input_format_tsv_use_best_effort_in_schema_inference,
и ClickHouse будет обрабатывать все столбцы как Strings.
Если включена настройка input_format_tsv_detect_header, ClickHouse при определении схемы попытается определить заголовок с именами столбцов (и, возможно, типами). По умолчанию эта настройка включена.
Примеры:
Целые числа, числа с плавающей точкой, булевы значения, строки:
input_format_tsv_use_best_effort_in_schema_inference:
input_format_tsv_detect_header включен):
Только имена:
input_format_tsv_use_best_effort_in_schema_inference:
input_format_custom_detect_header, ClickHouse при определении схемы попытается обнаружить заголовок с именами столбцов (и, возможно, типами). Эта настройка включена по умолчанию.
Пример
input_format_custom_detect_header включен):
resultset со следующим содержимым:
row_format со следующим содержанием:
input_format_max_rows_to_read_for_schema_inference/input_format_max_bytes_to_read_for_schema_inference
25000дляinput_format_max_rows_to_read_for_schema_inference.33554432(32 Mb) дляinput_format_max_bytes_to_read_for_schema_inference.
c1,c2,c3,.... Формат: column1,column2,column3,....
Пример
Nullable при выводе схемы для форматов, не содержащих информации о допустимости NULL. Возможные значения:
- 0 - определённый тип никогда не будет
Nullable, - 1 - все автоматически определённые типы будут
Nullable, - 2 или ‘auto’ — для текстовых форматов тип будет выведен как
Nullableтолько в том случае, если столбец содержитNULLв выборке, разбираемой при автоматическом определении схемы; для форматов со строгой типизацией (Parquet, ORC, Arrow) информация о допустимостиNULLберётся из метаданных файла, - 3 - для текстовых форматов используйте
Nullable; для строго типизированных форматов используйте метаданные файла.
Этот параметр не применяется к типу данных
JSON.Int64; если хотя бы одно число является числом с плавающей точкой, результирующим типом будет Float64.
Если выборочные данные содержат только целые числа и хотя бы одно из них является положительным и выходит за пределы Int64, ClickHouse определит тип UInt64.
Включен по умолчанию.
Примеры
DateTime или DateTime64 по строковым полям при определении схемы для текстовых форматов.
Если все поля столбца в выборочных данных были успешно разобраны как значения даты и времени, результирующим типом будет DateTime или DateTime64(9) (если у какого-либо значения даты и времени есть дробная часть),
если хотя бы одно поле не удалось разобрать как значение даты и времени, результирующим типом будет String.
Включено по умолчанию.
Примеры
DateTime64(9), когда включен input_format_try_infer_datetimes, даже если значения datetime не содержат дробной части.
По умолчанию отключено.
Примеры
Date по строковым полям при определении схемы для текстовых форматов.
Если все поля столбца в выборке данных были успешно разобраны как даты, результирующим типом будет Date,
если хотя бы одно поле не удалось разобрать как дату, результирующим типом будет String.
Включено по умолчанию.
Примеры
Другие типы Avro не поддерживаются.
В формате Parquet ClickHouse считывает схему из данных и преобразует её в схему ClickHouse в соответствии со следующими соответствиями типов:
Другие типы Parquet не поддерживаются.
В формате Arrow ClickHouse считывает схему из данных и преобразует её в схему ClickHouse в соответствии со следующими типами:
Другие типы Arrow не поддерживаются.
В формате ORC ClickHouse считывает схему из данных и преобразует её в схему ClickHouse в соответствии со следующим соответствием типов:
Другие типы ORC не поддерживаются.
Формат Native используется внутри ClickHouse и содержит схему в самих данных.
При автоматическом определении схемы ClickHouse считывает её из данных без каких-либо преобразований.
Для таких форматов требуется отдельный файл со схемой, описывающей данные на определённом языке схем.
Чтобы автоматически определить схему по файлам в таких форматах, ClickHouse считывает внешнюю схему из отдельного файла и преобразует её в схему таблицы ClickHouse.
При автоматическом определении схемы для формата Protobuf ClickHouse использует следующие соответствия типов:
При автоматическом определении схемы для формата CapnProto ClickHouse использует следующие соответствия типов:
В таких форматах каждое сериализованное значение содержит информацию о своём типе (а возможно, и о своём имени), но информация обо всей таблице отсутствует.
При автоматическом определении схемы для таких форматов ClickHouse читает данные строка за строкой (до
input_format_max_rows_to_read_for_schema_inference строк или input_format_max_bytes_to_read_for_schema_inference байт) и извлекает
тип (и, возможно, имя) каждого значения из данных, а затем преобразует эти типы в типы ClickHouse.
В формате MsgPack между строками нет разделителя, поэтому для использования автоматического определения схемы для этого формата следует указать количество столбцов в таблице
с помощью настройки input_format_msgpack_number_of_columns. ClickHouse использует следующие соответствия типов:
По умолчанию все выведенные типы имеют обёртку
Nullable, но это можно изменить с помощью настройки schema_inference_make_columns_nullable.
В формате BSONEachRow каждая строка данных представлена BSON-документом. При автоматическом определении схемы ClickHouse читает BSON-документы по одному и извлекает
из данных значения, имена и типы, а затем преобразует эти типы в типы ClickHouse, используя следующие соответствия типов:
По умолчанию все определённые типы оборачиваются в
Nullable, но это можно изменить с помощью настройки schema_inference_make_columns_nullable.
В таких форматах данные всегда имеют одну и ту же схему.
В этом формате ClickHouse считывает всю строку данных в один столбец с типом данных String. Для этого формата тип всегда определяется как String, а имя столбца — line.
Пример
String. Определённый для этого формата тип всегда — String, а имя столбца — json.
Пример
JSON. Определённый для этого формата тип всегда — JSON, а имя столбца — json.
Пример
default и union.
Режим задаётся настройкой schema_inference_mode.
В режиме по умолчанию ClickHouse считает, что у всех файлов одна и та же схема, и пытается определить её, читая файлы один за другим, пока это не удастся.
Пример:
Допустим, у нас есть 3 файла data1.jsonl, data2.jsonl и data3.jsonl со следующим содержимым:
data1.jsonl:
data2.jsonl:
data3.jsonl:
Query
Response
field3 из файла data3.jsonl.
Это происходит потому, что ClickHouse сначала попытался вывести схему по файлу data1.jsonl, но не смог из-за того, что в поле field2 были только значения NULL,
а затем попытался вывести схему по data2.jsonl и успешно справился, поэтому данные из файла data3.jsonl не были прочитаны.
В режиме union ClickHouse предполагает, что у файлов могут быть разные схемы, поэтому определяет схемы всех файлов, а затем объединяет их в одну общую схему.
Допустим, у нас есть 3 файла data1.jsonl, data2.jsonl и data3.jsonl со следующим содержимым:
data1.jsonl:
data2.jsonl:
data3.jsonl:
Query
Response
- Поскольку в некоторых файлах могут отсутствовать отдельные столбцы из итоговой схемы, режим union поддерживается только для форматов, которые позволяют читать подмножество столбцов (например, JSONEachRow, Parquet, TSVWithNames и т. д.), и не будет работать с другими форматами (например, CSV, TSV, JSONCompactEachRow и т. д.).
- Если ClickHouse не сможет вывести схему для одного из файлов, будет сгенерировано исключение.
- Если файлов много, чтение схемы из всех них может занять много времени.
data со следующим содержимым:
ClickHouse может автоматически определять лишь некоторые форматы, и это требует времени, поэтому формат всегда лучше указывать явно.