跳至主要内容

从输入数据自动推断模式

ClickHouse 可以在几乎所有支持的 输入格式 中自动确定输入数据的结构。本文档将描述何时使用模式推断,它如何与不同的输入格式一起工作以及哪些设置可以控制它。

用法

当 ClickHouse 需要以特定数据格式读取数据并且结构未知时,将使用模式推断。

表函数 files3urlhdfsazureBlobStorage.

这些表函数具有可选参数 structure,其中包含输入数据的结构。如果未指定此参数或将其设置为 auto,则结构将从数据中推断。

示例

假设我们在 user_files 目录中有一个名为 hobbies.jsonl 的文件,它以 JSONEachRow 格式保存,内容如下:

{"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)) │ │ │ │ │ │
└─────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

表引擎 FileS3URLHDFSazureBlobStorage

如果在 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)

在这种情况下,SELECT 查询中使用了列 id,但表中没有此列(它有一个名为 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 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))) │ │ │ │ │ │
└───────┴──────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

映射

在 JSON 中,我们可以将具有相同类型值的對象读取为 Map 类型。注意:只有在设置 input_format_json_read_objects_as_stringsinput_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 对象类型(如果启用了 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 对象类型。

此设置默认情况下处于启用状态。

注意:启用此设置将仅在 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 类型,避免在模式推断期间出现类似 无法通过数据的最初 25000 行确定列 'column_name' 的类型,很可能此列仅包含 Null 或空数组/映射 的错误。

示例

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)) │ │ │ │ │ │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

映射

DESC format(CSV, $$"{'key1' : 42, 'key2' : 24}"$$)
┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Map(String, Nullable(Int64)) │ │ │ │ │ │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

嵌套数组和映射

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)) │ │ │ │ │ │
└──────┴──────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

映射

DESC format(TSV, $${'key1' : 42, 'key2' : 24}$$)
┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Map(String, Nullable(Int64)) │ │ │ │ │ │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

嵌套数组、元组和映射

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) │ │ │ │ │ │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

日期、日期时间

 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)) │ │ │ │ │ │
└──────┴──────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

映射

DESC format(Values, $$({'key1' : 42, 'key2' : 24})$$)
┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Map(String, Nullable(Int64)) │ │ │ │ │ │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

嵌套数组、元组和映射

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 格式中,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)) │ │ │ │ │ │
└──────────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

正则表达式

与 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

这些设置控制在模式推断过程中要读取的数据量。读取的行数/字节数越多,模式推断花费的时间就越多,但正确确定数据类型的可能性就越大(尤其是在数据包含大量空值时)。

默认值

  • input_format_max_rows_to_read_for_schema_inference25000
  • input_format_max_bytes_to_read_for_schema_inference33554432(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 将尝试在文本格式的模式推断中从字符串字段推断类型 DateTimeDateTime64。如果样本数据中列中的所有字段都成功解析为日期时间,结果类型将为 DateTimeDateTime64(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 已启用。

默认情况下处于禁用状态。

示例

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 除外,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 输入格式(JSONJSONCompactJSONColumnsWithMetadata)包含带有列名和类型的元数据。在对这些格式进行模式推断时,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 数据类型
booleanBool
intInt32
int (date) *Date32
longInt64
floatFloat32
doubleFloat64
bytes, stringString
fixedFixedString(N)
enumEnum
array(T)Array(T)
union(null, T), union(T, null)Nullable(T)
nullNullable(Nothing)
string (uuid) *UUID
binary (decimal) *Decimal(P, S)

* Avro 逻辑类型

不支持其他 Avro 类型。

Parquet

在 Parquet 格式中,ClickHouse 从数据中读取其模式,并使用以下类型匹配将其转换为 ClickHouse 模式

Parquet 数据类型ClickHouse 数据类型
BOOLBool
UINT8UInt8
INT8Int8
UINT16UInt16
INT16Int16
UINT32UInt32
INT32Int32
UINT64UInt64
INT64Int64
FLOATFloat32
DOUBLEFloat64
DATEDate32
TIME (ms)DateTime
TIMESTAMP, TIME (us, ns)DateTime64
STRING, BINARYString
DECIMALDecimal
LISTArray
STRUCTTuple
MAPMap

不支持其他 Parquet 类型。默认情况下,所有推断的类型都在 Nullable 中,但可以使用设置 schema_inference_make_columns_nullable 进行更改。

Arrow

在 Arrow 格式中,ClickHouse 从数据中读取其模式,并使用以下类型匹配将其转换为 ClickHouse 模式

Arrow 数据类型ClickHouse 数据类型
BOOLBool
UINT8UInt8
INT8Int8
UINT16UInt16
INT16Int16
UINT32UInt32
INT32Int32
UINT64UInt64
INT64Int64
FLOAT, HALF_FLOATFloat32
DOUBLEFloat64
DATE32Date32
DATE64DateTime
TIMESTAMP, TIME32, TIME64DateTime64
STRING, BINARYString
DECIMAL128, DECIMAL256Decimal
LISTArray
STRUCTTuple
MAPMap

不支持其他 Arrow 类型。默认情况下,所有推断的类型都在 Nullable 中,但可以使用设置 schema_inference_make_columns_nullable 进行更改。

ORC

在 ORC 格式中,ClickHouse 从数据中读取其模式,并使用以下类型匹配将其转换为 ClickHouse 模式

ORC 数据类型ClickHouse 数据类型
BooleanBool
TinyintInt8
SmallintInt16
IntInt32
BigintInt64
FloatFloat32
DoubleFloat64
DateDate32
TimestampDateTime64
String, Char, Varchar,BINARYString
DecimalDecimal
ListArray
StructTuple
MapMap

不支持其他 ORC 类型。默认情况下,所有推断的类型都在 Nullable 中,但可以使用设置 schema_inference_make_columns_nullable 进行更改。

Native

Native 格式在 ClickHouse 内部使用,并在数据中包含模式。在模式推断中,ClickHouse 从数据中读取模式,无需任何转换。

带有外部模式的格式

此类格式需要一个模式,该模式使用特定的模式语言在单独的文件中描述数据。为了从此类格式的文件中自动推断模式,ClickHouse 从单独的文件中读取外部模式,并将其转换为 ClickHouse 表格模式。

Protobuf

在 Protobuf 格式的模式推断中,ClickHouse 使用以下类型匹配

Protobuf 数据类型ClickHouse 数据类型
boolUInt8
floatFloat32
doubleFloat64
int32, sint32, sfixed32Int32
int64, sint64, sfixed64Int64
uint32, fixed32UInt32
uint64, fixed64UInt64
string, bytesString
enumEnum
repeated TArray(T)
message, groupTuple

CapnProto

在 CapnProto 格式的模式推断中,ClickHouse 使用以下类型匹配

CapnProto 数据类型ClickHouse 数据类型
BoolUInt8
Int8Int8
UInt8UInt8
Int16Int16
UInt16UInt16
Int32Int32
UInt32UInt32
Int64Int64
UInt64UInt64
Float32Float32
Float64Float64
Text, DataString
enumEnum
ListArray
structTuple
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 数据类型(INSERTClickHouse 数据类型
int N, uint N, negative fixint, positive fixintInt64
boolUInt8
fixstr, str 8, str 16, str 32, bin 8, bin 16, bin 32String
float 32Float32
float 64Float64
uint 16Date
uint 32DateTime
uint 64DateTime64
fixarray, array 16, array 32Array
fixmap, map 16, map 32Map

默认情况下,所有推断的类型都在 Nullable 中,但可以使用设置 schema_inference_make_columns_nullable 进行更改。

BSONEachRow

在 BSONEachRow 中,每行数据都表示为一个 BSON 文档。在模式推断中,ClickHouse 一次读取一个 BSON 文档,并从数据中提取值、名称和类型,然后使用以下类型匹配将这些类型转换为 ClickHouse 类型

BSON 类型ClickHouse 类型
\x08 booleanBool
\x10 int32Int32
\x12 int64Int64
\x01 doubleFloat64
\x09 datetimeDateTime64
\x05 二进制数据,\x00 二进制子类型,\x02 字符串,\x0E 符号,\x0D JavaScript 代码String
\x07 ObjectId,FixedString(12)
\x05 二进制数据,\x04 uuid 子类型,大小为 16UUID
\x04 数组数组/元组 (如果嵌套类型不同)
\x03 文档命名元组/映射 (使用字符串键)

默认情况下,所有推断的类型都在 Nullable 中,但可以使用设置 schema_inference_make_columns_nullable 进行更改。

具有固定模式的格式

此类格式的数据始终具有相同的模式。

LineAsString

在此格式中,ClickHouse 将数据中的整行读入一个名为 line 的单列,数据类型为 String。此格式推断出的类型始终为 String

示例

DESC format(LineAsString, 'Hello\nworld!')
┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ line │ String │ │ │ │ │ │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

JSONAsString

在此格式中,ClickHouse 将数据中的整个 JSON 对象读入一个名为 json 的单列,数据类型为 String。此格式推断出的类型始终为 String

示例

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 的单列,数据类型为 Object('json')。此格式推断出的类型始终为 String

注意:此格式仅在启用 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') │ │ │ │ │ │
└──────┴────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

模式推断模式

从数据文件集中推断模式可以使用两种不同的模式:defaultunion。模式由设置 schema_inference_mode 控制。

默认模式

在默认模式下,ClickHouse 假设所有文件都具有相同的模式,并尝试通过逐个读取文件来推断模式,直到成功。

示例

假设我们有 3 个文件 data1.jsonldata2.jsonldata3.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 个文件上使用模式推断

:) 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 文件推断模式,但由于 field2 只有 null 值而失败,然后尝试从 data2.jsonl 文件推断模式并成功,因此没有读取 data3.jsonl 文件中的数据。

联合模式

在联合模式下,ClickHouse 假设文件可以具有不同的模式,因此它推断所有文件的模式,然后将它们联合到通用模式中。

假设我们有 3 个文件 data1.jsonldata2.jsonldata3.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 个文件上使用模式推断

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

结果

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

正如我们所见,我们拥有所有文件中所有字段。

注意

  • 由于某些文件可能不包含结果模式中的某些列,因此联合模式仅支持支持读取列子集的格式(例如 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 只能检测到一部分格式,并且这种检测需要一些时间,因此最好始终明确指定格式。