Автоматическое определение схемы

Страница с описанием автоматического определения схемы по входным данным в ClickHouse

ClickHouse может автоматически определять структуру входных данных почти во всех поддерживаемых входных форматах. В этом документе описано, когда используется автоматическое определение схемы, как оно работает с различными входными форматами и какие настройки позволяют им управлять.

​

Использование

Автоматическое определение схемы используется, когда ClickHouse нужно прочитать данные в определённом формате, а структура неизвестна.

​

Табличные функции file, s3, url, hdfs, azureBlobStorage.

У этих табличных функций есть необязательный аргумент structure, задающий структуру входных данных. Если этот аргумент не указан или задан как auto, структура будет автоматически определена по данным.

Пример:

Допустим, у нас есть файл hobbies.jsonl в формате JSONEachRow в каталоге user_files со следующим содержимым:

{"id" :  1, "age" :  25, "name" :  "Josh", "hobbies" :  ["football", "cooking", "music"]}
{"id" :  2, "age" :  19, "name" :  "Alan", "hobbies" :  ["tennis", "art"]}
{"id" :  3, "age" :  32, "name" :  "Lana", "hobbies" :  ["fitness", "reading", "shopping"]}
{"id" :  4, "age" :  47, "name" :  "Brayan", "hobbies" :  ["movies", "skydiving"]}

ClickHouse может читать эти данные, не указывая их структуру:

SELECT * FROM file('hobbies.jsonl')

┌─id─┬─age─┬─name───┬─hobbies──────────────────────────┐
│  1 │  25 │ Josh   │ ['football','cooking','music']   │
│  2 │  19 │ Alan   │ ['tennis','art']                 │
│  3 │  32 │ Lana   │ ['fitness','reading','shopping'] │
│  4 │  47 │ Brayan │ ['movies','skydiving']           │
└────┴─────┴────────┴──────────────────────────────────┘

Примечание: формат JSONEachRow был автоматически определён по расширению файла .jsonl.

Автоматически определённую структуру можно посмотреть с помощью запроса DESCRIBE:

DESCRIBE file('hobbies.jsonl')

┌─name────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id      │ Nullable(Int64)         │              │                    │         │                  │                │
│ age     │ Nullable(Int64)         │              │                    │         │                  │                │
│ name    │ Nullable(String)        │              │                    │         │                  │                │
│ hobbies │ Array(Nullable(String)) │              │                    │         │                  │                │
└─────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

Движки таблиц File, S3, URL, HDFS, azureBlobStorage

Если список столбцов не указан в запросе CREATE TABLE, структура таблицы будет автоматически определена по данным.

Пример:

Используем файл hobbies.jsonl. Мы можем создать таблицу с движком File, используя данные из этого файла:

CREATE TABLE hobbies ENGINE=File(JSONEachRow, 'hobbies.jsonl')

Ok.

SELECT * FROM hobbies

┌─id─┬─age─┬─name───┬─hobbies──────────────────────────┐
│  1 │  25 │ Josh   │ ['football','cooking','music']   │
│  2 │  19 │ Alan   │ ['tennis','art']                 │
│  3 │  32 │ Lana   │ ['fitness','reading','shopping'] │
│  4 │  47 │ Brayan │ ['movies','skydiving']           │
└────┴─────┴────────┴──────────────────────────────────┘

DESCRIBE TABLE hobbies

┌─name────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id      │ Nullable(Int64)         │              │                    │         │                  │                │
│ age     │ Nullable(Int64)         │              │                    │         │                  │                │
│ name    │ Nullable(String)        │              │                    │         │                  │                │
│ hobbies │ Array(Nullable(String)) │              │                    │         │                  │                │
└─────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

clickhouse-local

У clickhouse-local есть необязательный параметр -S/--structure, задающий структуру входных данных. Если этот параметр не указан или имеет значение auto, структура будет определена по данным.

Пример:

Возьмем файл hobbies.jsonl. Мы можем выполнить запрос к данным из этого файла с помощью clickhouse-local:

clickhouse-local --file='hobbies.jsonl' --table='hobbies' --query='DESCRIBE TABLE hobbies'

id    Nullable(Int64)
age    Nullable(Int64)
name    Nullable(String)
hobbies    Array(Nullable(String))

clickhouse-local --file='hobbies.jsonl' --table='hobbies' --query='SELECT * FROM hobbies'

1    25    Josh    ['football','cooking','music']
2    19    Alan    ['tennis','art']
3    32    Lana    ['fitness','reading','shopping']
4    47    Brayan    ['movies','skydiving']

​

Использование структуры таблицы, в которую выполняется вставка

Когда для вставки данных в таблицу используются табличные функции file/s3/url/hdfs, можно брать структуру из таблицы, в которую выполняется вставка, вместо того чтобы извлекать её из данных. Это может повысить производительность вставки, поскольку определение схемы может занимать некоторое время. Кроме того, это полезно, если у таблицы оптимизированная схема, поэтому преобразования между типами не потребуются.

Существует специальная настройка use_structure_from_insertion_table_in_table_functions, которая управляет этим поведением. У неё есть 3 возможных значения:

• 0 - табличная функция будет извлекать структуру из данных.
• 1 - табличная функция будет использовать структуру таблицы, в которую выполняется вставка.
• 2 - ClickHouse автоматически определит, можно ли использовать структуру таблицы, в которую выполняется вставка, или нужно определение схемы. Значение по умолчанию.

Пример 1:

Создадим таблицу hobbies1 со следующей структурой:

CREATE TABLE hobbies1
(
    `id` UInt64,
    `age` LowCardinality(UInt8),
    `name` String,
    `hobbies` Array(String)
)
ENGINE = MergeTree
ORDER BY id;

И вставьте данные из файла hobbies.jsonl:

INSERT INTO hobbies1 SELECT * FROM file(hobbies.jsonl)

В этом случае все столбцы из файла вставляются в таблицу без изменений, поэтому ClickHouse будет использовать структуру таблицы назначения вместо определения схемы.

Пример 2:

Создадим таблицу hobbies2 со следующей структурой:

CREATE TABLE hobbies2
(
  `id` UInt64,
  `age` LowCardinality(UInt8),
  `hobbies` Array(String)
)
  ENGINE = MergeTree
ORDER BY id;

И вставьте данные из файла hobbies.jsonl:

INSERT INTO hobbies2 SELECT id, age, hobbies FROM file(hobbies.jsonl)

В этом случае все столбцы из запроса SELECT присутствуют в таблице, поэтому ClickHouse будет использовать структуру таблицы, в которую выполняется вставка. Обратите внимание: это работает только для входных форматов, поддерживающих чтение подмножества столбцов, таких как JSONEachRow, TSKV, Parquet и т. д. (например, для формата TSV это не сработает).

Пример 3:

Давайте создадим таблицу hobbies3 со следующей структурой:

CREATE TABLE hobbies3
(
  `identifier` UInt64,
  `age` LowCardinality(UInt8),
  `hobbies` Array(String)
)
  ENGINE = MergeTree
ORDER BY identifier;

И вставьте данные из файла hobbies.jsonl:

INSERT INTO hobbies3 SELECT id, age, hobbies FROM file(hobbies.jsonl)

В этом случае столбец id используется в запросе SELECT, но в таблице такого столбца нет (в ней есть столбец с именем identifier), поэтому ClickHouse не может использовать структуру таблицы для вставки, и будет использовано автоматическое определение схемы.

Пример 4:

Давайте создадим таблицу hobbies4 со следующей структурой:

CREATE TABLE hobbies4
(
  `id` UInt64,
  `any_hobby` Nullable(String)
)
  ENGINE = MergeTree
ORDER BY id;

И вставьте данные из файла hobbies.jsonl:

INSERT INTO hobbies4 SELECT id, empty(hobbies) ? NULL : hobbies[1] FROM file(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 через табличную функцию 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 и посмотрим, как работает кэш определения схемы:

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022.ndjson.gz')

┌─name───────┬─type─────────────────────────────────────────┐
│ type       │ Nullable(String)                             │
│ actor      │ Tuple(                                      ↴│
│            │↳    avatar_url Nullable(String),            ↴│
│            │↳    display_login Nullable(String),         ↴│
│            │↳    id Nullable(Int64),                     ↴│
│            │↳    login Nullable(String),                 ↴│
│            │↳    url Nullable(String))                    │
│ repo       │ Tuple(                                      ↴│
│            │↳    id Nullable(Int64),                     ↴│
│            │↳    name Nullable(String),                  ↴│
│            │↳    url Nullable(String))                    │
│ created_at │ Nullable(String)                             │
│ payload    │ Tuple(                                      ↴│
│            │↳    action Nullable(String),                ↴│
│            │↳    distinct_size Nullable(Int64),          ↴│
│            │↳    pull_request Tuple(                     ↴│
│            │↳        author_association Nullable(String),↴│
│            │↳        base Tuple(                         ↴│
│            │↳            ref Nullable(String),           ↴│
│            │↳            sha Nullable(String)),          ↴│
│            │↳        head Tuple(                         ↴│
│            │↳            ref Nullable(String),           ↴│
│            │↳            sha Nullable(String)),          ↴│
│            │↳        number Nullable(Int64),             ↴│
│            │↳        state Nullable(String),             ↴│
│            │↳        title Nullable(String),             ↴│
│            │↳        updated_at Nullable(String),        ↴│
│            │↳        user Tuple(                         ↴│
│            │↳            login Nullable(String))),       ↴│
│            │↳    ref Nullable(String),                   ↴│
│            │↳    ref_type Nullable(String),              ↴│
│            │↳    size Nullable(Int64))                    │
└────────────┴──────────────────────────────────────────────┘
5 rows in set. Elapsed: 0.601 sec.

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022.ndjson.gz')

┌─name───────┬─type─────────────────────────────────────────┐
│ type       │ Nullable(String)                             │
│ actor      │ Tuple(                                      ↴│
│            │↳    avatar_url Nullable(String),            ↴│
│            │↳    display_login Nullable(String),         ↴│
│            │↳    id Nullable(Int64),                     ↴│
│            │↳    login Nullable(String),                 ↴│
│            │↳    url Nullable(String))                    │
│ repo       │ Tuple(                                      ↴│
│            │↳    id Nullable(Int64),                     ↴│
│            │↳    name Nullable(String),                  ↴│
│            │↳    url Nullable(String))                    │
│ created_at │ Nullable(String)                             │
│ payload    │ Tuple(                                      ↴│
│            │↳    action Nullable(String),                ↴│
│            │↳    distinct_size Nullable(Int64),          ↴│
│            │↳    pull_request Tuple(                     ↴│
│            │↳        author_association Nullable(String),↴│
│            │↳        base Tuple(                         ↴│
│            │↳            ref Nullable(String),           ↴│
│            │↳            sha Nullable(String)),          ↴│
│            │↳        head Tuple(                         ↴│
│            │↳            ref Nullable(String),           ↴│
│            │↳            sha Nullable(String)),          ↴│
│            │↳        number Nullable(Int64),             ↴│
│            │↳        state Nullable(String),             ↴│
│            │↳        title Nullable(String),             ↴│
│            │↳        updated_at Nullable(String),        ↴│
│            │↳        user Tuple(                         ↴│
│            │↳            login Nullable(String))),       ↴│
│            │↳    ref Nullable(String),                   ↴│
│            │↳    ref_type Nullable(String),              ↴│
│            │↳    size Nullable(Int64))                    │
└────────────┴──────────────────────────────────────────────┘

5 строк в наборе. Elapsed: 0.059 sec.

Как видите, второй запрос выполнился почти мгновенно.

Давайте попробуем изменить некоторые настройки, которые могут повлиять на определяемую схему:

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022.ndjson.gz')
SETTINGS input_format_json_try_infer_named_tuples_from_objects=0, input_format_json_read_objects_as_strings = 1

┌─name───────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ type       │ Nullable(String) │              │                    │         │                  │                │
│ actor      │ Nullable(String) │              │                    │         │                  │                │
│ repo       │ Nullable(String) │              │                    │         │                  │                │
│ created_at │ Nullable(String) │              │                    │         │                  │                │
│ payload    │ Nullable(String) │              │                    │         │                  │                │
└────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

5 rows in set. Elapsed: 0.611 sec

Как видите, кэшированная схема не была использована для того же файла, потому что был изменён параметр, который может влиять на автоматически определяемую схему.

Давайте проверим содержимое таблицы system.schema_inference_cache:

SELECT schema, format, source FROM system.schema_inference_cache WHERE storage='S3'

┌─schema──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─format─┬─source───────────────────────────────────────────────────────────────────────────────────────────────────┐
│ type Nullable(String), actor Tuple(avatar_url Nullable(String), display_login Nullable(String), id Nullable(Int64), login Nullable(String), url Nullable(String)), repo Tuple(id Nullable(Int64), name Nullable(String), url Nullable(String)), created_at Nullable(String), payload Tuple(action Nullable(String), distinct_size Nullable(Int64), pull_request Tuple(author_association Nullable(String), base Tuple(ref Nullable(String), sha Nullable(String)), head Tuple(ref Nullable(String), sha Nullable(String)), number Nullable(Int64), state Nullable(String), title Nullable(String), updated_at Nullable(String), user Tuple(login Nullable(String))), ref Nullable(String), ref_type Nullable(String), size Nullable(Int64)) │ NDJSON │ datasets-documentation.s3.eu-west-3.amazonaws.com443/datasets-documentation/github/github-2022.ndjson.gz │
│ type Nullable(String), actor Nullable(String), repo Nullable(String), created_at Nullable(String), payload Nullable(String)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 │ NDJSON │ datasets-documentation.s3.eu-west-3.amazonaws.com443/datasets-documentation/github/github-2022.ndjson.gz │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Как видите, для одного и того же файла есть две разные схемы.

Мы можем очистить кэш схемы с помощью системного запроса:

SYSTEM CLEAR SCHEMA CACHE FOR S3

Ok.

SELECT count() FROM system.schema_inference_cache WHERE storage='S3'

┌─count()─┐
│       0 │
└─────────┘

​

Текстовые форматы

Для текстовых форматов ClickHouse читает данные построчно, извлекает значения столбцов в соответствии с форматом, а затем использует рекурсивные парсеры и эвристики, чтобы определить тип каждого значения. Максимальное количество строк и байтов, считываемых из данных при определении схемы, задаётся настройками 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

В форматах JSON ClickHouse разбирает значения в соответствии со спецификацией JSON, а затем пытается подобрать для них наиболее подходящий тип данных.

Рассмотрим, как это работает, какие типы могут быть выведены автоматически и какие специальные настройки можно использовать в JSON formats.

Примеры

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

Integers, Floats, Bools, Strings:

DESC format(JSONEachRow, '{"int" : 42, "float" : 42.42, "string" : "Hello, World!"}');

┌─name───┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ int    │ Nullable(Int64)   │              │                    │         │                  │                │
│ float  │ Nullable(Float64) │              │                    │         │                  │                │
│ bool   │ Nullable(Bool)    │              │                    │         │                  │                │
│ string │ Nullable(String)  │              │                    │         │                  │                │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Даты, DateTimes:

DESC format(JSONEachRow, '{"date" : "2022-01-01", "datetime" : "2022-01-01 00:00:00", "datetime64" : "2022-01-01 00:00:00.000"}')

┌─name───────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ date       │ Nullable(Date)          │              │                    │         │                  │                │
│ datetime   │ Nullable(DateTime)      │              │                    │         │                  │                │
│ datetime64 │ Nullable(DateTime64(9)) │              │                    │         │                  │                │
└────────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Arrays:

DESC format(JSONEachRow, '{"arr" : [1, 2, 3], "nested_arrays" : [[1, 2, 3], [4, 5, 6], []]}')

┌─name──────────┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ arr           │ Array(Nullable(Int64))        │              │                    │         │                  │                │
│ nested_arrays │ Array(Array(Nullable(Int64))) │              │                    │         │                  │                │
└───────────────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Если массив содержит null, ClickHouse определит типы на основе остальных элементов массива:

DESC format(JSONEachRow, '{"arr" : [null, 42, null]}')

┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ arr  │ Array(Nullable(Int64)) │              │                    │         │                  │                │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Если массив содержит значения разных типов и параметр input_format_json_infer_array_of_dynamic_from_array_of_different_types включён (по умолчанию он включён), такой массив получит тип Array(Dynamic):

SET input_format_json_infer_array_of_dynamic_from_array_of_different_types=1;
DESC format(JSONEachRow, '{"arr" : [42, "hello", [1, 2, 3]]}');

┌─name─┬─type───────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ arr  │ Array(Dynamic) │              │                    │         │                  │                │
└──────┴────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Именованные Tuple:

Когда включен параметр input_format_json_try_infer_named_tuples_from_objects, при определении схемы ClickHouse попытается вывести именованный Tuple из объектов JSON. Полученный именованный Tuple будет содержать все элементы из всех соответствующих объектов JSON в выборке данных.

SET input_format_json_try_infer_named_tuples_from_objects = 1;
DESC format(JSONEachRow, '{"obj" : {"a" : 42, "b" : "Hello"}}, {"obj" : {"a" : 43, "c" : [1, 2, 3]}}, {"obj" : {"d" : {"e" : 42}}}')

┌─name─┬─type───────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj  │ Tuple(a Nullable(Int64), b Nullable(String), c Array(Nullable(Int64)), d Tuple(e Nullable(Int64))) │              │                    │         │                  │                │
└──────┴────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Безымянные Tuple:

Если настройка input_format_json_infer_array_of_dynamic_from_array_of_different_types отключена, в форматах JSON массивы Array с элементами разных типов рассматриваются как безымянные Tuple.

SET input_format_json_infer_array_of_dynamic_from_array_of_different_types = 0;
DESC format(JSONEachRow, '{"tuple" : [1, "Hello, World!", [1, 2, 3]]}')

┌─name──┬─type─────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ tuple │ Tuple(Nullable(Int64), Nullable(String), Array(Nullable(Int64))) │              │                    │         │                  │                │
└───────┴──────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Если некоторые значения равны null или пусты, мы используем типы соответствующих значений из других строк:

SET input_format_json_infer_array_of_dynamic_from_array_of_different_types=0;
DESC format(JSONEachRow, $$
                              {"tuple" : [1, null, null]}
                              {"tuple" : [null, "Hello, World!", []]}
                              {"tuple" : [null, null, [1, 2, 3]]}
                         $$)

┌─name──┬─type─────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ tuple │ Tuple(Nullable(Int64), Nullable(String), Array(Nullable(Int64))) │              │                    │         │                  │                │
└───────┴──────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Maps:

В JSON можно считывать объекты со значениями одного типа как значения типа Map. Примечание: это работает только при отключённых настройках input_format_json_read_objects_as_strings и input_format_json_try_infer_named_tuples_from_objects.

SET input_format_json_read_objects_as_strings = 0, input_format_json_try_infer_named_tuples_from_objects = 0;
DESC format(JSONEachRow, '{"map" : {"key1" : 42, "key2" : 24, "key3" : 4}}')

┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ map  │ Map(String, Nullable(Int64)) │              │                    │         │                  │                │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Вложенные сложные типы:

DESC format(JSONEachRow, '{"value" : [[[42, 24], []], {"key1" : 42, "key2" : 24}]}')

┌─name──┬─type─────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Tuple(Array(Array(Nullable(String))), Tuple(key1 Nullable(Int64), key2 Nullable(Int64))) │              │                    │         │                  │                │
└───────┴──────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Если ClickHouse не может определить тип для какого-либо ключа, поскольку данные содержат только null/пустые объекты/пустые массивы, будет использован тип String, если включена настройка input_format_json_infer_incomplete_types_as_strings; в противном случае будет сгенерировано исключение:

DESC format(JSONEachRow, '{"arr" : [null, null]}') SETTINGS input_format_json_infer_incomplete_types_as_strings = 1;

┌─name─┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ arr  │ Array(Nullable(String)) │              │                    │         │                  │                │
└──────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

DESC format(JSONEachRow, '{"arr" : [null, null]}') SETTINGS input_format_json_infer_incomplete_types_as_strings = 0;

Code: 652. DB::Exception: Received from localhost:9000. DB::Exception:
Cannot determine type for column 'arr' by first 1 rows of data,
most likely this column contains only Nulls or empty Arrays/Maps.
...

​

Настройки JSON

input_format_json_try_infer_numbers_from_strings

При включении этой настройки числа могут определяться по строковым значениям.

По умолчанию эта настройка отключена.

Пример:

SET input_format_json_try_infer_numbers_from_strings = 1;
DESC format(JSONEachRow, $$
                              {"value" : "42"}
                              {"value" : "424242424242"}
                         $$)

┌─name──┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Nullable(Int64) │              │                    │         │                  │                │
└───────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_json_try_infer_named_tuples_from_objects

Включение этой настройки позволяет определять именованные Tuple по объектам JSON. Получившийся именованный Tuple будет содержать все элементы из всех соответствующих объектов JSON в выборке данных. Это может быть полезно, когда данные JSON не являются разреженными, и выборка данных содержит все возможные ключи объектов.

Эта настройка включена по умолчанию.

Пример

Query

SET input_format_json_try_infer_named_tuples_from_objects = 1;
DESC format(JSONEachRow, '{"obj" : {"a" : 42, "b" : "Hello"}}, {"obj" : {"a" : 43, "c" : [1, 2, 3]}}, {"obj" : {"d" : {"e" : 42}}}')

Response

┌─name─┬─type───────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj  │ Tuple(a Nullable(Int64), b Nullable(String), c Array(Nullable(Int64)), d Tuple(e Nullable(Int64))) │              │                    │         │                  │                │
└──────┴────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Query

SET input_format_json_try_infer_named_tuples_from_objects = 1;
DESC format(JSONEachRow, '{"array" : [{"a" : 42, "b" : "Hello"}, {}, {"c" : [1,2,3]}, {"d" : "2020-01-01"}]}')

Response

┌─name──┬─type────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ array │ Array(Tuple(a Nullable(Int64), b Nullable(String), c Array(Nullable(Int64)), d Nullable(Date))) │              │                    │         │                  │                │
└───────┴─────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects

Включение этой настройки позволяет использовать тип String для неоднозначных путей при выводе именованных Tuple из объектов JSON (если включен параметр input_format_json_try_infer_named_tuples_from_objects) вместо генерации исключения. Это позволяет считывать объекты JSON как именованные Tuple даже при наличии неоднозначных путей.

По умолчанию отключено.

Примеры

Если настройка отключена:

Query

SET input_format_json_try_infer_named_tuples_from_objects = 1;
SET input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects = 0;
DESC format(JSONEachRow, '{"obj" : {"a" : 42}}, {"obj" : {"a" : {"b" : "Hello"}}}');

Response

Code: 636. DB::Exception: The table structure cannot be extracted from a JSONEachRow format file. Error:
Code: 117. DB::Exception: JSON objects have ambiguous data: in some objects path 'a' has type 'Int64' and in some - 'Tuple(b String)'. You can enable setting input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects to use String type for path 'a'. (INCORRECT_DATA) (version 24.3.1.1).
You can specify the structure manually. (CANNOT_EXTRACT_TABLE_STRUCTURE)

При включенной настройке:

Query

SET input_format_json_try_infer_named_tuples_from_objects = 1;
SET input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects = 1;
DESC format(JSONEachRow, '{"obj" : "a" : 42}, {"obj" : {"a" : {"b" : "Hello"}}}');
SELECT * FROM format(JSONEachRow, '{"obj" : {"a" : 42}}, {"obj" : {"a" : {"b" : "Hello"}}}');

Response

┌─name─┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj  │ Tuple(a Nullable(String))     │              │                    │         │                  │                │
└──────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
┌─obj─────────────────┐
│ ('42')              │
│ ('{"b" : "Hello"}') │
└─────────────────────┘

input_format_json_read_objects_as_strings

Включение этой настройки позволяет считывать вложенные JSON-объекты как строки. Эту настройку можно использовать для чтения вложенных JSON-объектов без использования типа объект JSON.

Эта настройка включена по умолчанию.

Примечание: эта настройка вступит в силу, только если отключена настройка input_format_json_try_infer_named_tuples_from_objects.

SET input_format_json_read_objects_as_strings = 1, input_format_json_try_infer_named_tuples_from_objects = 0;
DESC format(JSONEachRow, $$
                             {"obj" : {"key1" : 42, "key2" : [1,2,3,4]}}
                             {"obj" : {"key3" : {"nested_key" : 1}}}
                         $$)

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj  │ Nullable(String) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_json_read_numbers_as_strings

При включении этой настройки числовые значения можно считывать как строки.

Эта настройка включена по умолчанию.

Пример

SET input_format_json_read_numbers_as_strings = 1;
DESC format(JSONEachRow, $$
                                {"value" : 1055}
                                {"value" : "unknown"}
                         $$)

┌─name──┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Nullable(String) │              │                    │         │                  │                │
└───────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_json_read_bools_as_numbers

При включении этого параметра значения Bool можно читать как числа.

Этот параметр включен по умолчанию.

Пример:

SET input_format_json_read_bools_as_numbers = 1;
DESC format(JSONEachRow, $$
                                {"value" : true}
                                {"value" : 42}
                         $$)

┌─name──┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Nullable(Int64) │              │                    │         │                  │                │
└───────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_json_read_bools_as_strings

При включении этой настройки значения Bool можно считывать как строки.

Эта настройка включена по умолчанию.

Пример:

SET input_format_json_read_bools_as_strings = 1;
DESC format(JSONEachRow, $$
                                {"value" : true}
                                {"value" : "Hello, World"}
                         $$)

┌─name──┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Nullable(String) │              │                    │         │                  │                │
└───────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_json_read_arrays_as_strings

При включении этого параметра значения JSON-массива можно считывать как строки.

Этот параметр включен по умолчанию.

Пример

SET input_format_json_read_arrays_as_strings = 1;
SELECT arr, toTypeName(arr), JSONExtractArrayRaw(arr)[3] from format(JSONEachRow, 'arr String', '{"arr" : [1, "Hello", [1,2,3]]}');

┌─arr───────────────────┬─toTypeName(arr)─┬─arrayElement(JSONExtractArrayRaw(arr), 3)─┐
│ [1, "Hello", [1,2,3]] │ String          │ [1,2,3]                                   │
└───────────────────────┴─────────────────┴───────────────────────────────────────────┘

input_format_json_infer_incomplete_types_as_strings

Включение этой настройки позволяет использовать тип String для ключей JSON, которые в выборке данных при определении схемы содержат только 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

SET input_format_json_infer_incomplete_types_as_strings = 1, input_format_json_try_infer_named_tuples_from_objects = 1;
DESCRIBE format(JSONEachRow, '{"obj" : {"a" : [1,2,3], "b" : "hello", "c" : null, "d" : {}, "e" : []}}');
SELECT * FROM format(JSONEachRow, '{"obj" : {"a" : [1,2,3], "b" : "hello", "c" : null, "d" : {}, "e" : []}}');

Response

┌─name─┬─type───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj  │ Tuple(a Array(Nullable(Int64)), b Nullable(String), c Nullable(String), d Nullable(String), e Array(Nullable(String))) │              │                    │         │                  │                │
└──────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

┌─obj────────────────────────────┐
│ ([1,2,3],'hello',NULL,'{}',[]) │
└────────────────────────────────┘

​

CSV

В формате CSV ClickHouse извлекает значения столбцов из строки в соответствии с разделителями. ClickHouse ожидает, что все типы, кроме чисел и строк, будут заключены в двойные кавычки. Если значение заключено в двойные кавычки, ClickHouse пытается разобрать данные внутри кавычек с помощью рекурсивного парсера, а затем определить для них наиболее подходящий тип данных. Если значение не заключено в двойные кавычки, ClickHouse пытается разобрать его как число, а если значение не является числом, ClickHouse рассматривает его как строку.

Если вы не хотите, чтобы ClickHouse пытался определять сложные типы с помощью парсеров и эвристик, можно отключить настройку input_format_csv_use_best_effort_in_schema_inference, и тогда ClickHouse будет рассматривать все столбцы как String.

Если настройка input_format_csv_detect_header включена, ClickHouse попытается определить заголовок с именами столбцов (и, возможно, типами) при определении схемы. Эта настройка включена по умолчанию.

Примеры:

Целые числа, числа с плавающей точкой, логические значения, строки:

DESC format(CSV, '42,42.42,true,"Hello,World!"')

┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Int64)   │              │                    │         │                  │                │
│ c2   │ Nullable(Float64) │              │                    │         │                  │                │
│ c3   │ Nullable(Bool)    │              │                    │         │                  │                │
│ c4   │ Nullable(String)  │              │                    │         │                  │                │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Строки без кавычек:

DESC format(CSV, 'Hello world!,World hello!')

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(String) │              │                    │         │                  │                │
│ c2   │ Nullable(String) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Даты, DateTimes:

DESC format(CSV, '"2020-01-01","2020-01-01 00:00:00","2022-01-01 00:00:00.000"')

┌─name─┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Date)          │              │                    │         │                  │                │
│ c2   │ Nullable(DateTime)      │              │                    │         │                  │                │
│ c3   │ Nullable(DateTime64(9)) │              │                    │         │                  │                │
└──────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Массивы:

DESC format(CSV, '"[1,2,3]","[[1, 2], [], [3, 4]]"')

┌─name─┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Nullable(Int64))        │              │                    │         │                  │                │
│ c2   │ Array(Array(Nullable(Int64))) │              │                    │         │                  │                │
└──────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

DESC format(CSV, $$"['Hello', 'world']","[['Abc', 'Def'], []]"$$)

┌─name─┬─type───────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Nullable(String))        │              │                    │         │                  │                │
│ c2   │ Array(Array(Nullable(String))) │              │                    │         │                  │                │
└──────┴────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Если массив содержит NULL, ClickHouse будет использовать типы остальных элементов массива:

DESC format(CSV, '"[NULL, 42, NULL]"')

┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Nullable(Int64)) │              │                    │         │                  │                │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Map:

DESC format(CSV, $$"{'key1' : 42, 'key2' : 24}"$$)

┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Map(String, Nullable(Int64)) │              │                    │         │                  │                │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Вложенные массивы и Map:

DESC format(CSV, $$"[{'key1' : [[42, 42], []], 'key2' : [[null], [42]]}]"$$)

┌─name─┬─type──────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Map(String, Array(Array(Nullable(Int64))))) │              │                    │         │                  │                │
└──────┴───────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Если ClickHouse не может определить тип значения в кавычках, поскольку данные содержат только значения NULL, ClickHouse будет считать его String:

DESC format(CSV, '"[NULL, NULL]"')

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(String) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Пример с отключённой настройкой input_format_csv_use_best_effort_in_schema_inference:

SET input_format_csv_use_best_effort_in_schema_inference = 0
DESC format(CSV, '"[1,2,3]",42.42,Hello World!')

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(String) │              │                    │         │                  │                │
│ c2   │ Nullable(String) │              │                    │         │                  │                │
│ c3   │ Nullable(String) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Примеры автоматического определения заголовка (когда input_format_csv_detect_header включен):

Только имена:

SELECT * FROM format(CSV,
$$"number","string","array"
42,"Hello","[1, 2, 3]"
43,"World","[4, 5, 6]"
$$)

┌─number─┬─string─┬─array───┐
│     42 │ Hello  │ [1,2,3] │
│     43 │ World  │ [4,5,6] │
└────────┴────────┴─────────┘

Имена и типы:

DESC format(CSV,
$$"number","string","array"
"UInt32","String","Array(UInt16)"
42,"Hello","[1, 2, 3]"
43,"World","[4, 5, 6]"
$$)

┌─name───┬─type──────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ UInt32        │              │                    │         │                  │                │
│ string │ String        │              │                    │         │                  │                │
│ array  │ Array(UInt16) │              │                    │         │                  │                │
└────────┴───────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Обратите внимание: заголовок можно определить только если есть хотя бы один столбец с типом, отличным от String. Если все столбцы имеют тип String, заголовок не определяется:

SELECT * FROM format(CSV,
$$"first_column","second_column"
"Hello","World"
"World","Hello"
$$)

┌─c1───────────┬─c2────────────┐
│ first_column │ second_column │
│ Hello        │ World         │
│ World        │ Hello         │
└──────────────┴───────────────┘

​

Настройки CSV

input_format_csv_try_infer_numbers_from_strings

Включение этой настройки позволяет определять числа в строковых значениях.

По умолчанию эта настройка отключена.

Пример:

SET input_format_json_try_infer_numbers_from_strings = 1;
DESC format(CSV, '42,42.42');

┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Int64)   │              │                    │         │                  │                │
│ c2   │ Nullable(Float64) │              │                    │         │                  │                │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

TSV/TSKV

В форматах TSV/TSKV ClickHouse извлекает значение столбца из строки в соответствии с табличными разделителями, а затем разбирает извлечённое значение с помощью рекурсивного парсера, чтобы определить наиболее подходящий тип. Если тип определить не удаётся, ClickHouse обрабатывает это значение как String.

Если вы не хотите, чтобы ClickHouse пытался определять сложные типы с помощью парсеров и эвристик, можно отключить настройку input_format_tsv_use_best_effort_in_schema_inference, и ClickHouse будет обрабатывать все столбцы как Strings.

Если включена настройка input_format_tsv_detect_header, ClickHouse при определении схемы попытается определить заголовок с именами столбцов (и, возможно, типами). По умолчанию эта настройка включена.

Примеры:

Целые числа, числа с плавающей точкой, булевы значения, строки:

DESC format(TSV, '42    42.42    true    Hello,World!')

┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Int64)   │              │                    │         │                  │                │
│ c2   │ Nullable(Float64) │              │                    │         │                  │                │
│ c3   │ Nullable(Bool)    │              │                    │         │                  │                │
│ c4   │ Nullable(String)  │              │                    │         │                  │                │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

DESC format(TSKV, 'int=42    float=42.42    bool=true    string=Hello,World!\n')

┌─name───┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ int    │ Nullable(Int64)   │              │                    │         │                  │                │
│ float  │ Nullable(Float64) │              │                    │         │                  │                │
│ bool   │ Nullable(Bool)    │              │                    │         │                  │                │
│ string │ Nullable(String)  │              │                    │         │                  │                │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Даты, DateTimes:

DESC format(TSV, '2020-01-01    2020-01-01 00:00:00    2022-01-01 00:00:00.000')

┌─name─┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Date)          │              │                    │         │                  │                │
│ c2   │ Nullable(DateTime)      │              │                    │         │                  │                │
│ c3   │ Nullable(DateTime64(9)) │              │                    │         │                  │                │
└──────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Массивы:

DESC format(TSV, '[1,2,3]    [[1, 2], [], [3, 4]]')

┌─name─┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Nullable(Int64))        │              │                    │         │                  │                │
│ c2   │ Array(Array(Nullable(Int64))) │              │                    │         │                  │                │
└──────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

DESC format(TSV, '[''Hello'', ''world'']    [[''Abc'', ''Def''], []]')

┌─name─┬─type───────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Nullable(String))        │              │                    │         │                  │                │
│ c2   │ Array(Array(Nullable(String))) │              │                    │         │                  │                │
└──────┴────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Если массив содержит NULL, ClickHouse будет использовать типы остальных элементов массива:

DESC format(TSV, '[NULL, 42, NULL]')

┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Nullable(Int64)) │              │                    │         │                  │                │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Tuple:

DESC format(TSV, $$(42, 'Hello, world!')$$)

┌─name─┬─type─────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Tuple(Nullable(Int64), Nullable(String)) │              │                    │         │                  │                │
└──────┴──────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Map:

DESC format(TSV, $${'key1' : 42, 'key2' : 24}$$)

┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Map(String, Nullable(Int64)) │              │                    │         │                  │                │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Вложенные Array, Tuple и Map:

DESC format(TSV, $$[{'key1' : [(42, 'Hello'), (24, NULL)], 'key2' : [(NULL, ','), (42, 'world!')]}]$$)

┌─name─┬─type────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Map(String, Array(Tuple(Nullable(Int64), Nullable(String))))) │              │                    │         │                  │                │
└──────┴─────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Если ClickHouse не может определить тип, поскольку данные содержат только значения NULL, ClickHouse будет считать их типом String:

DESC format(TSV, '[NULL, NULL]')

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(String) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Пример с отключённой настройкой input_format_tsv_use_best_effort_in_schema_inference:

SET input_format_tsv_use_best_effort_in_schema_inference = 0
DESC format(TSV, '[1,2,3]    42.42    Hello World!')

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(String) │              │                    │         │                  │                │
│ c2   │ Nullable(String) │              │                    │         │                  │                │
│ c3   │ Nullable(String) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Примеры автоопределения строки заголовков (когда input_format_tsv_detect_header включен):

Только имена:

SELECT * FROM format(TSV,
$$number    string    array
42    Hello    [1, 2, 3]
43    World    [4, 5, 6]
$$);

┌─number─┬─string─┬─array───┐
│     42 │ Hello  │ [1,2,3] │
│     43 │ World  │ [4,5,6] │
└────────┴────────┴─────────┘

Имена и типы:

DESC format(TSV,
$$number    string    array
UInt32    String    Array(UInt16)
42    Hello    [1, 2, 3]
43    World    [4, 5, 6]
$$)

┌─name───┬─type──────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ UInt32        │              │                    │         │                  │                │
│ string │ String        │              │                    │         │                  │                │
│ array  │ Array(UInt16) │              │                    │         │                  │                │
└────────┴───────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Обратите внимание, что заголовок может быть определён только при наличии хотя бы одного столбца с типом, отличным от String. Если все столбцы имеют тип String, заголовок не определяется:

SELECT * FROM format(TSV,
$$first_column    second_column
Hello    World
World    Hello
$$)

┌─c1───────────┬─c2────────────┐
│ first_column │ second_column │
│ Hello        │ World         │
│ World        │ Hello         │
└──────────────┴───────────────┘

​

Значения

В формате Values ClickHouse извлекает значение столбца из строки и затем разбирает его с помощью рекурсивного парсера — аналогично тому, как разбираются литералы.

Примеры:

Целые числа, числа с плавающей точкой, булевы значения, строки:

DESC format(Values, $$(42, 42.42, true, 'Hello,World!')$$)

┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Int64)   │              │                    │         │                  │                │
│ c2   │ Nullable(Float64) │              │                    │         │                  │                │
│ c3   │ Nullable(Bool)    │              │                    │         │                  │                │
│ c4   │ Nullable(String)  │              │                    │         │                  │                │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Даты, DateTimes:

DESC format(Values, $$('2020-01-01', '2020-01-01 00:00:00', '2022-01-01 00:00:00.000')$$)

┌─name─┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Date)          │              │                    │         │                  │                │
│ c2   │ Nullable(DateTime)      │              │                    │         │                  │                │
│ c3   │ Nullable(DateTime64(9)) │              │                    │         │                  │                │
└──────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Массивы:

DESC format(Values, '([1,2,3], [[1, 2], [], [3, 4]])')

┌─name─┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Nullable(Int64))        │              │                    │         │                  │                │
│ c2   │ Array(Array(Nullable(Int64))) │              │                    │         │                  │                │
└──────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Если массив содержит null, ClickHouse определит типы на основе остальных элементов массива:

DESC format(Values, '([NULL, 42, NULL])')

┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Nullable(Int64)) │              │                    │         │                  │                │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Tuple:

DESC format(Values, $$((42, 'Hello, world!'))$$)

┌─name─┬─type─────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Tuple(Nullable(Int64), Nullable(String)) │              │                    │         │                  │                │
└──────┴──────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Map-значения:

DESC format(Values, $$({'key1' : 42, 'key2' : 24})$$)

┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Map(String, Nullable(Int64)) │              │                    │         │                  │                │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Вложенные Array, Tuple и Map:

DESC format(Values, $$([{'key1' : [(42, 'Hello'), (24, NULL)], 'key2' : [(NULL, ','), (42, 'world!')]}])$$)

┌─name─┬─type────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Map(String, Array(Tuple(Nullable(Int64), Nullable(String))))) │              │                    │         │                  │                │
└──────┴─────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Если ClickHouse не может определить тип, поскольку данные содержат только значения NULL, будет сгенерировано исключение:

DESC format(Values, '([NULL, NULL])')

Code: 652. DB::Exception: Received from localhost:9000. DB::Exception:
Cannot determine type for column 'c1' by first 1 rows of data,
most likely this column contains only Nulls or empty Arrays/Maps.
...

Пример при отключённой настройке input_format_tsv_use_best_effort_in_schema_inference:

SET input_format_tsv_use_best_effort_in_schema_inference = 0
DESC format(TSV, '[1,2,3]    42.42    Hello World!')

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(String) │              │                    │         │                  │                │
│ c2   │ Nullable(String) │              │                    │         │                  │                │
│ c3   │ Nullable(String) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

CustomSeparated

В формате CustomSeparated ClickHouse сначала извлекает из строки все значения столбцов в соответствии с указанными разделителями, а затем пытается определить тип данных для каждого значения на основе правила экранирования.

Если включена настройка input_format_custom_detect_header, ClickHouse при определении схемы попытается обнаружить заголовок с именами столбцов (и, возможно, типами). Эта настройка включена по умолчанию.

Пример

SET format_custom_row_before_delimiter = '<row_before_delimiter>',
       format_custom_row_after_delimiter = '<row_after_delimiter>\n',
       format_custom_row_between_delimiter = '<row_between_delimiter>\n',
       format_custom_result_before_delimiter = '<result_before_delimiter>\n',
       format_custom_result_after_delimiter = '<result_after_delimiter>\n',
       format_custom_field_delimiter = '<field_delimiter>',
       format_custom_escaping_rule = 'Quoted'

DESC format(CustomSeparated, $$<result_before_delimiter>
<row_before_delimiter>42.42<field_delimiter>'Some string 1'<field_delimiter>[1, NULL, 3]<row_after_delimiter>
<row_between_delimiter>
<row_before_delimiter>NULL<field_delimiter>'Some string 3'<field_delimiter>[1, 2, NULL]<row_after_delimiter>
<result_after_delimiter>
$$)

┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Float64)      │              │                    │         │                  │                │
│ c2   │ Nullable(String)       │              │                    │         │                  │                │
│ c3   │ Array(Nullable(Int64)) │              │                    │         │                  │                │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Пример автоматического определения заголовка (если input_format_custom_detect_header включен):

SET format_custom_row_before_delimiter = '<row_before_delimiter>',
       format_custom_row_after_delimiter = '<row_after_delimiter>\n',
       format_custom_row_between_delimiter = '<row_between_delimiter>\n',
       format_custom_result_before_delimiter = '<result_before_delimiter>\n',
       format_custom_result_after_delimiter = '<result_after_delimiter>\n',
       format_custom_field_delimiter = '<field_delimiter>',
       format_custom_escaping_rule = 'Quoted'

DESC format(CustomSeparated, $$<result_before_delimiter>
<row_before_delimiter>'number'<field_delimiter>'string'<field_delimiter>'array'<row_after_delimiter>
<row_between_delimiter>
<row_before_delimiter>42.42<field_delimiter>'Some string 1'<field_delimiter>[1, NULL, 3]<row_after_delimiter>
<row_between_delimiter>
<row_before_delimiter>NULL<field_delimiter>'Some string 3'<field_delimiter>[1, 2, NULL]<row_after_delimiter>
<result_after_delimiter>
$$)

┌─number─┬─string────────┬─array──────┐
│  42.42 │ Some string 1 │ [1,NULL,3] │
│   ᴺᵁᴸᴸ │ Some string 3 │ [1,2,NULL] │
└────────┴───────────────┴────────────┘

​

Template

В формате Template ClickHouse сначала извлекает из строки все значения столбцов в соответствии с указанным шаблоном, а затем пытается определить тип данных каждого значения по правилу его экранирования.

Пример

Допустим, у нас есть файл resultset со следующим содержимым:

<result_before_delimiter>
${data}<result_after_delimiter>

И файл row_format со следующим содержанием:

<row_before_delimiter>${column_1:CSV}<field_delimiter_1>${column_2:Quoted}<field_delimiter_2>${column_3:JSON}<row_after_delimiter>

Затем можно выполнить следующие запросы:

SET format_template_rows_between_delimiter = '<row_between_delimiter>\n',
       format_template_row = 'row_format',
       format_template_resultset = 'resultset_format'

DESC format(Template, $$<result_before_delimiter>
<row_before_delimiter>42.42<field_delimiter_1>'Some string 1'<field_delimiter_2>[1, null, 2]<row_after_delimiter>
<row_between_delimiter>
<row_before_delimiter>\N<field_delimiter_1>'Some string 3'<field_delimiter_2>[1, 2, null]<row_after_delimiter>
<result_after_delimiter>
$$)

┌─name─────┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ column_1 │ Nullable(Float64)      │              │                    │         │                  │                │
│ column_2 │ Nullable(String)       │              │                    │         │                  │                │
│ column_3 │ Array(Nullable(Int64)) │              │                    │         │                  │                │
└──────────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

Regexp

Как и в формате Template, в формате Regexp ClickHouse сначала извлекает все значения столбцов из строки в соответствии с указанным регулярным выражением, а затем пытается определить тип данных для каждого значения в соответствии с указанным правилом экранирования.

Пример

SET format_regexp = '^Line: value_1=(.+?), value_2=(.+?), value_3=(.+?)',
       format_regexp_escaping_rule = 'CSV'

DESC format(Regexp, $$Line: value_1=42, value_2="Some string 1", value_3="[1, NULL, 3]"
Line: value_1=2, value_2="Some string 2", value_3="[4, 5, NULL]"$$)

┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Int64)        │              │                    │         │                  │                │
│ c2   │ Nullable(String)       │              │                    │         │                  │                │
│ c3   │ Array(Nullable(Int64)) │              │                    │         │                  │                │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

Настройки текстовых форматов

​

input_format_max_rows_to_read_for_schema_inference/input_format_max_bytes_to_read_for_schema_inference

Эти настройки определяют объём данных, считываемых при определении схемы. Чем больше строк/байтов считывается, тем больше времени занимает определение схемы, но тем выше вероятность правильно определить типы (особенно если данные содержат много значений NULL).

Значения по умолчанию:

• 25000 для input_format_max_rows_to_read_for_schema_inference.
• 33554432 (32 Mb) для input_format_max_bytes_to_read_for_schema_inference.

​

column_names_for_schema_inference

Список имён столбцов, используемых при определении схемы для форматов без явных имён столбцов. Указанные имена будут использоваться вместо значений по умолчанию c1,c2,c3,.... Формат: column1,column2,column3,....

Пример

DESC format(TSV, 'Hello, World!    42    [1, 2, 3]') settings column_names_for_schema_inference = 'str,int,arr'

┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ str  │ Nullable(String)       │              │                    │         │                  │                │
│ int  │ Nullable(Int64)        │              │                    │         │                  │                │
│ arr  │ Array(Nullable(Int64)) │              │                    │         │                  │                │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

schema_inference_hints

Список имён столбцов и типов, используемых при определении схемы вместо автоматически определённых типов. Формат: ‘column_name1 column_type1, column_name2 column_type2, …’. Этот параметр можно использовать, чтобы указать типы столбцов, которые не удалось определить автоматически, или для оптимизации схемы.

Пример

DESC format(JSONEachRow, '{"id" : 1, "age" : 25, "name" : "Josh", "status" : null, "hobbies" : ["football", "cooking"]}') SETTINGS schema_inference_hints = 'age LowCardinality(UInt8), status Nullable(String)', allow_suspicious_low_cardinality_types=1

┌─name────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id      │ Nullable(Int64)         │              │                    │         │                  │                │
│ age     │ LowCardinality(UInt8)   │              │                    │         │                  │                │
│ name    │ Nullable(String)        │              │                    │         │                  │                │
│ status  │ Nullable(String)        │              │                    │         │                  │                │
│ hobbies │ Array(Nullable(String)) │              │                    │         │                  │                │
└─────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

schema_inference_make_columns_nullable $

Управляет тем, будут ли выведенные типы объявляться как Nullable при выводе схемы для форматов, не содержащих информации о допустимости NULL. Возможные значения:

• 0 - определённый тип никогда не будет Nullable,
• 1 - все автоматически определённые типы будут Nullable,
• 2 или ‘auto’ — для текстовых форматов тип будет выведен как Nullable только в том случае, если столбец содержит NULL в выборке, разбираемой при автоматическом определении схемы; для форматов со строгой типизацией (Parquet, ORC, Arrow) информация о допустимости NULL берётся из метаданных файла,
• 3 - для текстовых форматов используйте Nullable; для строго типизированных форматов используйте метаданные файла.

По умолчанию: 3.

Примеры

SET schema_inference_make_columns_nullable = 1;
DESC format(JSONEachRow, $$
                                {"id" :  1, "age" :  25, "name" : "Josh", "status" : null, "hobbies" : ["football", "cooking"]}
                                {"id" :  2, "age" :  19, "name" :  "Alan", "status" : "married", "hobbies" :  ["tennis", "art"]}
                         $$)

┌─name────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id      │ Nullable(Int64)         │              │                    │         │                  │                │
│ age     │ Nullable(Int64)         │              │                    │         │                  │                │
│ name    │ Nullable(String)        │              │                    │         │                  │                │
│ status  │ Nullable(String)        │              │                    │         │                  │                │
│ hobbies │ Array(Nullable(String)) │              │                    │         │                  │                │
└─────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

SET schema_inference_make_columns_nullable = 'auto';
DESC format(JSONEachRow, $$
                                {"id" :  1, "age" :  25, "name" : "Josh", "status" : null, "hobbies" : ["football", "cooking"]}
                                {"id" :  2, "age" :  19, "name" :  "Alan", "status" : "married", "hobbies" :  ["tennis", "art"]}
                         $$)

┌─name────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id      │ Int64            │              │                    │         │                  │                │
│ age     │ Int64            │              │                    │         │                  │                │
│ name    │ String           │              │                    │         │                  │                │
│ status  │ Nullable(String) │              │                    │         │                  │                │
│ hobbies │ Array(String)    │              │                    │         │                  │                │
└─────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

SET schema_inference_make_columns_nullable = 0;
DESC format(JSONEachRow, $$
                                {"id" :  1, "age" :  25, "name" : "Josh", "status" : null, "hobbies" : ["football", "cooking"]}
                                {"id" :  2, "age" :  19, "name" :  "Alan", "status" : "married", "hobbies" :  ["tennis", "art"]}
                         $$)

┌─name────┬─type──────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id      │ Int64         │              │                    │         │                  │                │
│ age     │ Int64         │              │                    │         │                  │                │
│ name    │ String        │              │                    │         │                  │                │
│ status  │ String        │              │                    │         │                  │                │
│ hobbies │ Array(String) │              │                    │         │                  │                │
└─────────┴───────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

input_format_try_infer_integers

Этот параметр не применяется к типу данных JSON.

Если параметр включен, ClickHouse будет пытаться определять целые числа вместо чисел с плавающей точкой при определении схемы для текстовых форматов. Если все числа в столбце из выборочных данных являются целыми, результирующим типом будет Int64; если хотя бы одно число является числом с плавающей точкой, результирующим типом будет Float64. Если выборочные данные содержат только целые числа и хотя бы одно из них является положительным и выходит за пределы Int64, ClickHouse определит тип UInt64.

Включен по умолчанию.

Примеры

SET input_format_try_infer_integers = 0
DESC format(JSONEachRow, $$
                                {"number" : 1}
                                {"number" : 2}
                         $$)

┌─name───┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ Nullable(Float64) │              │                    │         │                  │                │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

SET input_format_try_infer_integers = 1
DESC format(JSONEachRow, $$
                                {"number" : 1}
                                {"number" : 2}
                         $$)

┌─name───┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ Nullable(Int64) │              │                    │         │                  │                │
└────────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

DESC format(JSONEachRow, $$
                                {"number" : 1}
                                {"number" : 18446744073709551615}
                         $$)

┌─name───┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ Nullable(UInt64) │              │                    │         │                  │                │
└────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

DESC format(JSONEachRow, $$
                                {"number" : 1}
                                {"number" : 2.2}
                         $$)

┌─name───┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ Nullable(Float64) │              │                    │         │                  │                │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

input_format_try_infer_datetimes

Если включено, ClickHouse будет пытаться определять тип DateTime или DateTime64 по строковым полям при определении схемы для текстовых форматов. Если все поля столбца в выборочных данных были успешно разобраны как значения даты и времени, результирующим типом будет DateTime или DateTime64(9) (если у какого-либо значения даты и времени есть дробная часть), если хотя бы одно поле не удалось разобрать как значение даты и времени, результирующим типом будет String.

Включено по умолчанию.

Примеры

SET input_format_try_infer_datetimes = 0;
DESC format(JSONEachRow, $$
                                {"datetime" : "2021-01-01 00:00:00", "datetime64" : "2021-01-01 00:00:00.000"}
                                {"datetime" : "2022-01-01 00:00:00", "datetime64" : "2022-01-01 00:00:00.000"}
                         $$)

┌─name───────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ datetime   │ Nullable(String) │              │                    │         │                  │                │
│ datetime64 │ Nullable(String) │              │                    │         │                  │                │
└────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

SET input_format_try_infer_datetimes = 1;
DESC format(JSONEachRow, $$
                                {"datetime" : "2021-01-01 00:00:00", "datetime64" : "2021-01-01 00:00:00.000"}
                                {"datetime" : "2022-01-01 00:00:00", "datetime64" : "2022-01-01 00:00:00.000"}
                         $$)

┌─name───────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ datetime   │ Nullable(DateTime)      │              │                    │         │                  │                │
│ datetime64 │ Nullable(DateTime64(9)) │              │                    │         │                  │                │
└────────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

DESC format(JSONEachRow, $$
                                {"datetime" : "2021-01-01 00:00:00", "datetime64" : "2021-01-01 00:00:00.000"}
                                {"datetime" : "unknown", "datetime64" : "unknown"}
                         $$)

┌─name───────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ datetime   │ Nullable(String) │              │                    │         │                  │                │
│ datetime64 │ Nullable(String) │              │                    │         │                  │                │
└────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

input_format_try_infer_datetimes_only_datetime64

Если параметр включен, ClickHouse всегда будет определять DateTime64(9), когда включен input_format_try_infer_datetimes, даже если значения datetime не содержат дробной части.

По умолчанию отключено.

Примеры

SET input_format_try_infer_datetimes = 1;
SET input_format_try_infer_datetimes_only_datetime64 = 1;
DESC format(JSONEachRow, $$
                                {"datetime" : "2021-01-01 00:00:00", "datetime64" : "2021-01-01 00:00:00.000"}
                                {"datetime" : "2022-01-01 00:00:00", "datetime64" : "2022-01-01 00:00:00.000"}
                         $$)

┌─name───────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ datetime   │ Nullable(DateTime64(9)) │              │                    │         │                  │                │
│ datetime64 │ Nullable(DateTime64(9)) │              │                    │         │                  │                │
└────────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Примечание: при автоматическом определении схемы обработка значений даты и времени учитывает настройку date_time_input_format

​

input_format_try_infer_dates

Если включено, ClickHouse будет пытаться определять тип Date по строковым полям при определении схемы для текстовых форматов. Если все поля столбца в выборке данных были успешно разобраны как даты, результирующим типом будет Date, если хотя бы одно поле не удалось разобрать как дату, результирующим типом будет String.

Включено по умолчанию.

Примеры

SET input_format_try_infer_datetimes = 0, input_format_try_infer_dates = 0
DESC format(JSONEachRow, $$
                                {"date" : "2021-01-01"}
                                {"date" : "2022-01-01"}
                         $$)

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ date │ Nullable(String) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

SET input_format_try_infer_dates = 1
DESC format(JSONEachRow, $$
                                {"date" : "2021-01-01"}
                                {"date" : "2022-01-01"}
                         $$)

┌─name─┬─type───────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ date │ Nullable(Date) │              │                    │         │                  │                │
└──────┴────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

DESC format(JSONEachRow, $$
                                {"date" : "2021-01-01"}
                                {"date" : "unknown"}
                         $$)

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ date │ Nullable(String) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

input_format_try_infer_exponent_floats

Если включен, ClickHouse будет пытаться распознавать числа с плавающей точкой в экспоненциальной записи в текстовых форматах (кроме JSON, где числа в экспоненциальной записи распознаются всегда).

По умолчанию отключен.

Пример

SET input_format_try_infer_exponent_floats = 1;
DESC format(CSV,
$$1.1E10
2.3e-12
42E00
$$)

┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Float64) │              │                    │         │                  │                │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

Самоописывающие форматы

Самоописывающие форматы содержат информацию о структуре данных в самих данных: это может быть заголовок с описанием, бинарное дерево типов или какая-либо таблица. Чтобы автоматически вывести схему из файлов в таких форматах, ClickHouse считывает часть данных, содержащую информацию о типах, и преобразует её в схему таблицы ClickHouse.

​

Форматы с суффиксом -WithNamesAndTypes

ClickHouse поддерживает некоторые текстовые форматы с суффиксом -WithNamesAndTypes. Этот суффикс означает, что перед самими данными в них содержатся две дополнительные строки с именами столбцов и типами. При определении схемы для таких форматов ClickHouse считывает первые две строки и извлекает из них имена столбцов и типы.

Пример

DESC format(TSVWithNamesAndTypes,
$$num    str    arr
UInt8    String    Array(UInt8)
42    Hello, World!    [1,2,3]
$$)

┌─name─┬─type─────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ num  │ UInt8        │              │                    │         │                  │                │
│ str  │ String       │              │                    │         │                  │                │
│ arr  │ Array(UInt8) │              │                    │         │                  │                │
└──────┴──────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

JSON-форматы с метаданными

Некоторые JSON-форматы ввода (JSON, JSONCompact, JSONColumnsWithMetadata) содержат метаданные с именами и типами столбцов. При определении схемы для таких форматов ClickHouse читает эти метаданные.

Пример

DESC format(JSON, $$
{
    "meta":
    [
        {
            "name": "num",
            "type": "UInt8"
        },
        {
            "name": "str",
            "type": "String"
        },
        {
            "name": "arr",
            "type": "Array(UInt8)"
        }
    ],

    "data":
    [
        {
            "num": 42,
            "str": "Hello, World",
            "arr": [1,2,3]
        }
    ],

    "rows": 1,

    "statistics":
    {
        "elapsed": 0.005723915,
        "rows_read": 1,
        "bytes_read": 1
    }
}
$$)

┌─name─┬─type─────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ num  │ UInt8        │              │                    │         │                  │                │
│ str  │ String       │              │                    │         │                  │                │
│ arr  │ Array(UInt8) │              │                    │         │                  │                │
└──────┴──────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

Avro

В формате Avro ClickHouse считывает схему из данных и преобразует её в схему ClickHouse, используя следующие соответствия типов:

Тип данных Avro	Тип данных ClickHouse
boolean	Bool
int	Int32
int (date) *	Date32
long	Int64
float	Float32
double	Float64
bytes, string	String
fixed	FixedString(N)
enum	Enum
array(T)	Array(T)
union(null, T), union(T, null)	Nullable(T)
null	Nullable(Nothing)
string (uuid) *	UUID
binary (decimal) *	Decimal(P, S)

• Логические типы Avro

Другие типы Avro не поддерживаются.

​

Parquet

В формате Parquet ClickHouse считывает схему из данных и преобразует её в схему ClickHouse в соответствии со следующими соответствиями типов:

Тип данных Parquet	Тип данных ClickHouse
BOOL	Bool
UINT8	UInt8
INT8	Int8
UINT16	UInt16
INT16	Int16
UINT32	UInt32
INT32	Int32
UINT64	UInt64
INT64	Int64
FLOAT	Float32
DOUBLE	Float64
DATE	Date32
TIME (ms)	DateTime
TIMESTAMP, TIME (us, ns)	DateTime64
STRING, BINARY	String
DECIMAL	Decimal
LIST	Array
STRUCT	Tuple
MAP	Map

Другие типы Parquet не поддерживаются.

​

Arrow

В формате Arrow ClickHouse считывает схему из данных и преобразует её в схему ClickHouse в соответствии со следующими типами:

Тип данных Arrow	Тип данных ClickHouse
BOOL	Bool
UINT8	UInt8
INT8	Int8
UINT16	UInt16
INT16	Int16
UINT32	UInt32
INT32	Int32
UINT64	UInt64
INT64	Int64
FLOAT, HALF_FLOAT	Float32
DOUBLE	Float64
DATE32	Date32
DATE64	DateTime
TIMESTAMP, TIME32, TIME64	DateTime64
STRING, BINARY	String
DECIMAL128, DECIMAL256	Decimal
LIST	Array
STRUCT	Tuple
MAP	Map

Другие типы Arrow не поддерживаются.

​

ORC

В формате ORC ClickHouse считывает схему из данных и преобразует её в схему ClickHouse в соответствии со следующим соответствием типов:

Тип данных ORC	Тип данных ClickHouse
Boolean	Bool
Tinyint	Int8
Smallint	Int16
Int	Int32
Bigint	Int64
Float	Float32
Double	Float64
Date	Date32
Timestamp	DateTime64
String, Char, Varchar,BINARY	String
Decimal	Decimal
List	Array
Struct	Tuple
Map	Map

Другие типы ORC не поддерживаются.

​

Native

Формат Native используется внутри ClickHouse и содержит схему в самих данных. При автоматическом определении схемы ClickHouse считывает её из данных без каких-либо преобразований.

​

Форматы с внешней схемой

Для таких форматов требуется отдельный файл со схемой, описывающей данные на определённом языке схем. Чтобы автоматически определить схему по файлам в таких форматах, ClickHouse считывает внешнюю схему из отдельного файла и преобразует её в схему таблицы ClickHouse.

​

Protobuf

При автоматическом определении схемы для формата Protobuf ClickHouse использует следующие соответствия типов:

Тип данных Protobuf	Тип данных ClickHouse
bool	UInt8
float	Float32
double	Float64
int32, sint32, sfixed32	Int32
int64, sint64, sfixed64	Int64
uint32, fixed32	UInt32
uint64, fixed64	UInt64
string, bytes	String
enum	Enum
repeated T	Array(T)
message, group	Tuple

​

CapnProto

При автоматическом определении схемы для формата CapnProto ClickHouse использует следующие соответствия типов:

Тип данных CapnProto	Тип данных ClickHouse
Bool	UInt8
Int8	Int8
UInt8	UInt8
Int16	Int16
UInt16	UInt16
Int32	Int32
UInt32	UInt32
Int64	Int64
UInt64	UInt64
Float32	Float32
Float64	Float64
Text, Data	String
enum	Enum
List	Array
struct	Tuple
union(T, Void), union(Void, T)	Nullable(T)

​

Строго типизированные двоичные форматы

В таких форматах каждое сериализованное значение содержит информацию о своём типе (а возможно, и о своём имени), но информация обо всей таблице отсутствует. При автоматическом определении схемы для таких форматов ClickHouse читает данные строка за строкой (до input_format_max_rows_to_read_for_schema_inference строк или input_format_max_bytes_to_read_for_schema_inference байт) и извлекает тип (и, возможно, имя) каждого значения из данных, а затем преобразует эти типы в типы ClickHouse.

​

MsgPack

В формате MsgPack между строками нет разделителя, поэтому для использования автоматического определения схемы для этого формата следует указать количество столбцов в таблице с помощью настройки input_format_msgpack_number_of_columns. ClickHouse использует следующие соответствия типов:

Тип данных MessagePack (INSERT)	Тип данных ClickHouse
int N, uint N, negative fixint, positive fixint	Int64
bool	UInt8
fixstr, str 8, str 16, str 32, bin 8, bin 16, bin 32	String
float 32	Float32
float 64	Float64
uint 16	Date
uint 32	DateTime
uint 64	DateTime64
fixarray, array 16, array 32	Array
fixmap, map 16, map 32	Map

По умолчанию все выведенные типы имеют обёртку Nullable, но это можно изменить с помощью настройки schema_inference_make_columns_nullable.

​

BSONEachRow

В формате BSONEachRow каждая строка данных представлена BSON-документом. При автоматическом определении схемы ClickHouse читает BSON-документы по одному и извлекает из данных значения, имена и типы, а затем преобразует эти типы в типы ClickHouse, используя следующие соответствия типов:

Тип BSON	Тип ClickHouse
\x08 булево значение	Bool
\x10 int32	Int32
\x12 int64	Int64
\x01 double	Float64
\x09 datetime	DateTime64
\x05 бинарные данные с бинарным подтипом \x00, \x02 string, \x0E symbol, \x0D код JavaScript	String
\x07 ObjectId,	FixedString(12)
\x05 бинарные данные с UUID-подтипом \x04, размер = 16	UUID
\x04 array	Array/Tuple (если вложенные типы различаются)
\x03 document	Named Tuple/Map (со строковыми ключами)

По умолчанию все определённые типы оборачиваются в Nullable, но это можно изменить с помощью настройки schema_inference_make_columns_nullable.

​

Форматы с постоянной схемой

В таких форматах данные всегда имеют одну и ту же схему.

​

LineAsString

В этом формате ClickHouse считывает всю строку данных в один столбец с типом данных String. Для этого формата тип всегда определяется как String, а имя столбца — line.

Пример

DESC format(LineAsString, 'Hello\nworld!')

┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ line │ String │              │                    │         │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

JSONAsString

В этом формате ClickHouse считывает из данных весь объект JSON в один столбец с типом данных String. Определённый для этого формата тип всегда — String, а имя столбца — json.

Пример

DESC format(JSONAsString, '{"x" : 42, "y" : "Hello, World!"}')

┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ json │ String │              │                    │         │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

JSONAsObject

В этом формате ClickHouse считывает весь объект JSON из данных в один столбец с типом данных JSON. Определённый для этого формата тип всегда — JSON, а имя столбца — json.

Пример

DESC format(JSONAsObject, '{"x" : 42, "y" : "Hello, World!"}');

┌─name─┬─type─┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ json │ JSON │              │                    │         │                  │                │
└──────┴──────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

Режимы автоматического определения схемы

Автоматическое определение схемы по набору файлов данных может работать в 2 разных режимах: default и union. Режим задаётся настройкой schema_inference_mode.

​

Режим по умолчанию

В режиме по умолчанию ClickHouse считает, что у всех файлов одна и та же схема, и пытается определить её, читая файлы один за другим, пока это не удастся.

Пример:

Допустим, у нас есть 3 файла data1.jsonl, data2.jsonl и data3.jsonl со следующим содержимым:

data1.jsonl:

{"field1" :  1, "field2" :  null}
{"field1" :  2, "field2" :  null}
{"field1" :  3, "field2" :  null}

data2.jsonl:

{"field1" :  4, "field2" :  "Data4"}
{"field1" :  5, "field2" :  "Data5"}
{"field1" :  6, "field2" :  "Data5"}

data3.jsonl:

{"field1" :  7, "field2" :  "Data7", "field3" :  [1, 2, 3]}
{"field1" :  8, "field2" :  "Data8", "field3" :  [4, 5, 6]}
{"field1" :  9, "field2" :  "Data9", "field3" :  [7, 8, 9]}

Давайте попробуем применить автоматическое определение схемы к этим 3 файлам:

Query

:) DESCRIBE file('data{1,2,3}.jsonl') SETTINGS schema_inference_mode='default'

Response

┌─name───┬─type─────────────┐
│ field1 │ Nullable(Int64)  │
│ field2 │ Nullable(String) │
└────────┴──────────────────┘

Как видим, у нас нет field3 из файла data3.jsonl. Это происходит потому, что ClickHouse сначала попытался вывести схему по файлу data1.jsonl, но не смог из-за того, что в поле field2 были только значения NULL, а затем попытался вывести схему по data2.jsonl и успешно справился, поэтому данные из файла data3.jsonl не были прочитаны.

​

Режим union

В режиме union ClickHouse предполагает, что у файлов могут быть разные схемы, поэтому определяет схемы всех файлов, а затем объединяет их в одну общую схему.

Допустим, у нас есть 3 файла data1.jsonl, data2.jsonl и data3.jsonl со следующим содержимым:

data1.jsonl:

{"field1" :  1}
{"field1" :  2}
{"field1" :  3}

data2.jsonl:

{"field2" :  "Data4"}
{"field2" :  "Data5"}
{"field2" :  "Data5"}

data3.jsonl:

{"field3" :  [1, 2, 3]}
{"field3" :  [4, 5, 6]}
{"field3" :  [7, 8, 9]}

Давайте попробуем автоматически определить схему для этих 3 файлов:

Query

:) DESCRIBE file('data{1,2,3}.jsonl') SETTINGS schema_inference_mode='union'

Response

┌─name───┬─type───────────────────┐
│ field1 │ Nullable(Int64)        │
│ field2 │ Nullable(String)       │
│ field3 │ Array(Nullable(Int64)) │
└────────┴────────────────────────┘

Как видим, у нас есть все поля из всех файлов.

Примечание:

• Поскольку в некоторых файлах могут отсутствовать отдельные столбцы из итоговой схемы, режим union поддерживается только для форматов, которые позволяют читать подмножество столбцов (например, JSONEachRow, Parquet, TSVWithNames и т. д.), и не будет работать с другими форматами (например, CSV, TSV, JSONCompactEachRow и т. д.).
• Если ClickHouse не сможет вывести схему для одного из файлов, будет сгенерировано исключение.
• Если файлов много, чтение схемы из всех них может занять много времени.

​

Автоматическое определение формата

Если формат данных не указан и его нельзя определить по расширению файла, ClickHouse попытается определить формат файла по содержимому.

Примеры:

Допустим, у нас есть data со следующим содержимым:

"a","b"
1,"Data1"
2,"Data2"
3,"Data3"

Мы можем просмотреть этот файл и выполнить к нему запрос, не указывая формат или структуру:

:) desc file(data);

┌─name─┬─type─────────────┐
│ a    │ Nullable(Int64)  │
│ b    │ Nullable(String) │
└──────┴──────────────────┘

:) select * from file(data);

┌─a─┬─b─────┐
│ 1 │ Data1 │
│ 2 │ Data2 │
│ 3 │ Data3 │
└───┴───────┘

ClickHouse может автоматически определять лишь некоторые форматы, и это требует времени, поэтому формат всегда лучше указывать явно.