输入数据的自动模式推断
ClickHouse 可以自动确定几乎所有受支持的输入格式中输入数据的结构。本文档将描述何时使用模式推断，它如何与不同的输入格式一起工作，以及哪些设置可以控制它。
用法
当 ClickHouse 需要读取特定数据格式的数据且结构未知时，将使用模式推断。
表函数 file, s3, url, hdfs, azureBlobStorage。
这些表函数具有可选参数 structure，用于指定输入数据的结构。如果未指定此参数或将其设置为 auto，则将从数据中推断结构。
示例
假设我们在 user_files 目录中有一个 JSONEachRow 格式的文件 hobbies.jsonl，内容如下
{"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 表函数访问的某些文件可能不包含有关最后修改时间的信息；对于这种情况，有一个特殊的设置 schema_inference_cache_require_modification_time_for_url。禁用此设置允许对没有此类文件的最后修改时间的文件使用缓存中的模式。
还有一个系统表 schema_inference_cache，其中包含缓存中的所有当前模式，以及系统查询 SYSTEM DROP 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')
SETTINGS allow_experimental_object_type = 1
┌─name───────┬─type─────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ type │ Nullable(String) │ │ │ │ │ │
│ actor │ Object(Nullable('json')) │ │ │ │ │ │
│ repo │ Object(Nullable('json')) │ │ │ │ │ │
│ created_at │ Nullable(String) │ │ │ │ │ │
│ payload │ Object(Nullable('json')) │ │ │ │ │ │
└────────────┴──────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
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')
SETTINGS allow_experimental_object_type = 1
┌─name───────┬─type─────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ type │ Nullable(String) │ │ │ │ │ │
│ actor │ Object(Nullable('json')) │ │ │ │ │ │
│ repo │ Object(Nullable('json')) │ │ │ │ │ │
│ created_at │ Nullable(String) │ │ │ │ │ │
│ payload │ Object(Nullable('json')) │ │ │ │ │ │
└────────────┴──────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
5 rows in set. 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_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 Object(Nullable('json')), repo Object(Nullable('json')), created_at Nullable(String), payload Object(Nullable('json')) │ 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 DROP 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 格式中使用哪些特定设置。
示例
在以下示例中，将使用 format 表函数。
整数、浮点数、布尔值、字符串
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) │ │ │ │ │ │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
日期、日期时间
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)) │ │ │ │ │ │
└────────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
数组
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_try_infer_named_tuples_from_objects 时，在模式推断期间，ClickHouse 将尝试从 JSON 对象推断具名元组。生成的具名元组将包含来自示例数据中所有相应 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))) │ │ │ │ │ │
└──────┴────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
未命名元组
在 JSON 格式中，我们将具有不同类型元素的数组视为未命名元组。
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 或空，我们将使用来自其他行的相应值的类型
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))) │ │ │ │ │ │
└───────┴──────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
Map
在 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)) │ │ │ │ │ │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
JSON Object 类型（如果启用设置 allow_experimental_object_type）
SET allow_experimental_object_type = 1
DESC format(JSONEachRow, $$
{"obj" : {"key1" : 42}}
{"obj" : {"key2" : "Hello, World!"}}
{"obj" : {"key1" : 24, "key3" : {"a" : 42, "b" : null}}}
$$)
┌─name─┬─type─────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj │ Object(Nullable('json')) │ │ │ │ │ │
└──────┴──────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
嵌套复杂类型
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/空对象/空数组，则如果启用设置 input_format_json_infer_incomplete_types_as_strings，将使用类型 String，否则将抛出异常
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
启用此设置允许从 JSON 对象推断具名元组。生成的具名元组将包含来自示例数据中所有相应 JSON 对象的所有元素。当 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))) │ │ │ │ │ │
└──────┴────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
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"}]}')
结果
┌─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
启用此设置允许在从 JSON 对象推断具名元组期间（当启用 input_format_json_try_infer_named_tuples_from_objects 时），对不明确的路径使用 String 类型，而不是抛出异常。它允许将 JSON 对象读取为具名元组，即使存在不明确的路径。
默认禁用。
示例
禁用设置时
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"}}}');
结果
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)
启用设置时
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"}}}');
结果
┌─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 object 类型。
此设置默认启用。
注意：仅当禁用设置 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
启用此设置允许将布尔值读取为数字。
此设置默认启用。
示例
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
启用此设置允许将布尔值读取为字符串。
此设置默认启用。
示例
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
启用此设置允许在模式推断期间，对数据样本中仅包含 Null/{}/[] 的 JSON 键使用 String 类型。在 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 的错误。
示例
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" : []}}');
结果
┌─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 会将所有列视为字符串。
如果启用设置 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) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
日期、日期时间
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 会将所有列视为字符串。
如果启用设置 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) │ │ │ │ │ │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
日期、日期时间
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)) │ │ │ │ │ │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
元组
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)) │ │ │ │ │ │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
嵌套数组、元组和 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
在 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) │ │ │ │ │ │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
日期、日期时间
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)) │ │ │ │ │ │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
元组
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)) │ │ │ │ │ │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
嵌套数组、元组和 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 时）。
默认值
input_format_max_rows_to_read_for_schema_inference为25000。input_format_max_bytes_to_read_for_schema_inference为33554432(32 Mb)。
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。如果启用此设置，则所有推断的类型都将为 Nullable；如果禁用，则推断的类型永远不会为 Nullable；如果设置为 auto，则仅当列在模式推断期间解析的样本中包含 NULL 或文件元数据包含有关列可空性的信息时，推断的类型才为 Nullable。
默认启用。
示例
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
如果启用，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
如果启用，即使日期时间值不包含小数部分，当启用 input_format_try_infer_datetimes 时，ClickHouse 也将始终推断 DateTime64(9)。
默认禁用。
示例
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)) │ │ │ │ │ │
└────────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
注意：在 schema 推断期间解析 datetime 会遵循 date_time_input_format 设置。
input_format_try_infer_dates
如果启用，ClickHouse 将尝试在文本格式的 schema 推断中从字符串字段推断 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) │ │ │ │ │ │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
自描述格式
自描述格式在数据本身中包含关于数据结构的信息，它可以是带有描述的头部、二进制类型树或某种表格。为了从这种格式的文件中自动推断 schema，ClickHouse 会读取包含类型信息的数据部分，并将其转换为 ClickHouse 表的 schema。
带有 -WithNamesAndTypes 后缀的格式
ClickHouse 支持一些带有 -WithNamesAndTypes 后缀的文本格式。此后缀表示数据在实际数据之前包含两行额外的行，分别包含列名和类型。在对此类格式进行 schema 推断时，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）包含带有列名和类型的元数据。在对此类格式进行 schema 推断时，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 从数据中读取其 schema，并使用以下类型匹配将其转换为 ClickHouse schema
| 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 类型。
Parquet
在 Parquet 格式中，ClickHouse 从数据中读取其 schema，并使用以下类型匹配将其转换为 ClickHouse schema
| 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 类型。默认情况下，所有推断的类型都在 Nullable 内部，但可以使用 schema_inference_make_columns_nullable 设置更改此行为。
Arrow
在 Arrow 格式中，ClickHouse 从数据中读取其 schema，并使用以下类型匹配将其转换为 ClickHouse schema
| 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 类型。默认情况下，所有推断的类型都在 Nullable 内部，但可以使用 schema_inference_make_columns_nullable 设置更改此行为。
ORC
在 ORC 格式中，ClickHouse 从数据中读取其 schema，并使用以下类型匹配将其转换为 ClickHouse schema
| 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 类型。默认情况下，所有推断的类型都在 Nullable 内部，但可以使用 schema_inference_make_columns_nullable 设置更改此行为。
Native
Native 格式在 ClickHouse 内部使用，并在数据中包含 schema。在 schema 推断中，ClickHouse 从数据中读取 schema，无需任何转换。
带有外部 schema 的格式
此类格式需要使用特定的 schema 语言在单独的文件中描述数据的 schema。为了从这种格式的文件中自动推断 schema，ClickHouse 会从单独的文件中读取外部 schema，并将其转换为 ClickHouse 表 schema。
Protobuf
在 Protobuf 格式的 schema 推断中，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 格式的 schema 推断中，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) |
强类型二进制格式
在这种格式中，每个序列化的值都包含关于其类型（以及可能的名称）的信息，但没有关于整个表的信息。在对此类格式进行 schema 推断时，ClickHouse 会逐行读取数据（最多 input_format_max_rows_to_read_for_schema_inference 行或 input_format_max_bytes_to_read_for_schema_inference 字节），并从数据中提取每个值的类型（以及可能的名称），然后将这些类型转换为 ClickHouse 类型。
MsgPack
在 MsgPack 格式中，行之间没有分隔符，要在此格式中使用 schema 推断，您应使用 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 文档。在 schema 推断中，ClickHouse 逐个读取 BSON 文档，并从数据中提取值、名称和类型，然后使用以下类型匹配将这些类型转换为 ClickHouse 类型
| BSON 类型 | ClickHouse 类型 |
|---|---|
\x08 boolean | Bool |
\x10 int32 | Int32 |
\x12 int64 | Int64 |
\x01 double | Float64 |
\x09 datetime | DateTime64 |
\x05 binary with\x00 binary subtype, \x02 string, \x0E symbol, \x0D JavaScript code | String |
\x07 ObjectId, | FixedString(12) |
\x05 binary with \x04 uuid subtype, size = 16 | UUID |
\x04 array | Array/Tuple (如果嵌套类型不同) |
\x03 document | Named Tuple/Map (带有 String 键) |
默认情况下，所有推断的类型都在 Nullable 内部，但可以使用 schema_inference_make_columns_nullable 设置更改此行为。
具有常量 schema 的格式
这种格式的数据始终具有相同的 schema。
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 对象读取到具有 Object('json') 数据类型的单个列中。此格式的推断类型始终为 String，列名为 json。
注意：此格式仅在启用 allow_experimental_object_type 时有效。
示例
DESC format(JSONAsString, '{"x" : 42, "y" : "Hello, World!"}') SETTINGS allow_experimental_object_type=1
┌─name─┬─type───────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ json │ Object('json') │ │ │ │ │ │
└──────┴────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
Schema 推断模式
从一组数据文件进行 Schema 推断可以以 2 种不同的模式工作：default 和 union。模式由设置 schema_inference_mode 控制。
默认模式
在默认模式下，ClickHouse 假定所有文件都具有相同的 schema，并尝试通过逐个读取文件来推断 schema，直到成功为止。
示例
假设我们有 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 个文件上使用 schema 推断
:) DESCRIBE file('data{1,2,3}.jsonl') SETTINGS schema_inference_mode='default'
结果
┌─name───┬─type─────────────┐
│ field1 │ Nullable(Int64) │
│ field2 │ Nullable(String) │
└────────┴──────────────────┘
正如我们所见，我们没有来自文件 data3.jsonl 的 field3。发生这种情况是因为 ClickHouse 首先尝试从文件 data1.jsonl 推断 schema，由于字段 field2 只有 null 值而失败，然后尝试从 data2.jsonl 推断 schema 并成功，因此未读取文件 data3.jsonl 中的数据。
联合模式
在联合模式下，ClickHouse 假定文件可以具有不同的 schema，因此它会推断所有文件的 schema，然后将它们联合到公共 schema 中。
假设我们有 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 个文件上使用 schema 推断
:) DESCRIBE file('data{1,2,3}.jsonl') SETTINGS schema_inference_mode='union'
结果
┌─name───┬─type───────────────────┐
│ field1 │ Nullable(Int64) │
│ field2 │ Nullable(String) │
│ field3 │ Array(Nullable(Int64)) │
└────────┴────────────────────────┘
正如我们所见，我们拥有来自所有文件的所有字段。
注意
- 由于某些文件可能不包含结果 schema 中的某些列，因此联合模式仅支持支持读取列子集的格式（例如 JSONEachRow、Parquet、TSVWithNames 等），并且不适用于其他格式（例如 CSV、TSV、JSONCompactEachRow 等）。
- 如果 ClickHouse 无法从其中一个文件中推断 schema，则会抛出异常。
- 如果您有大量文件，则从所有文件中读取 schema 可能会花费大量时间。
自动格式检测
如果未指定数据格式，并且无法通过文件扩展名确定，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 只能检测一部分格式，并且此检测需要一些时间，最好始终显式指定格式。