从输入数据自动推断模式
ClickHouse可以在几乎所有支持的 输入格式 中自动确定输入数据的结构。本文档将描述何时使用模式推断，它如何与不同的输入格式配合使用，以及可以控制它的哪些设置。
使用方法
当ClickHouse需要以特定的数据格式读取数据而结构未知时，会使用模式推断。
表函数 file, s3, url, hdfs, azureBlobStorage。
这些表函数有一个可选参数 structure，用于输入数据的结构。如果未指定此参数或设置为 auto，则将根据数据推断结构。
示例：
假设我们在 user_files 目录中有一个 hobbies.jsonl 文件，格式为 JSONEachRow，内容如下：
ClickHouse可以在您未指定其结构的情况下读取此数据：
注意：格式 JSONEachRow 是通过文件扩展名 .jsonl 自动确定的。
您可以使用 DESCRIBE 查询查看自动确定的结构：
表引擎 File, S3, URL, HDFS, azureBlobStorage
如果在 CREATE TABLE 查询中未指定列的列表，则表的结构将从数据中自动推断。
示例：
让我们使用文件 hobbies.jsonl。我们可以使用文件中的数据创建一个引擎为 File 的表：
clickhouse-local
clickhouse-local 有一个可选参数 -S/--structure，用于输入数据的结构。如果未指定此参数或设置为 auto，则将根据数据推断结构。
示例：
让我们使用文件 hobbies.jsonl。我们可以使用 clickhouse-local 查询该文件中的数据：
使用插入表的结构
当表函数 file/s3/url/hdfs 用于将数据插入表中时，可以选择使用插入表的结构，而不是从数据中提取。这可以提高插入性能，因为模式推断可能需要一些时间。此外，当表具有优化的结构时，它会很有用，因此不会在类型之间执行转换。
有一个特殊的设置 use_structure_from_insertion_table_in_table_functions 控制此行为。它有3种可能的值：
- 0 - 表函数将从数据中提取结构。
- 1 - 表函数将使用插入表的结构。
- 2 - ClickHouse 将自动确定是否可以使用插入表的结构或使用模式推断。默认值。
示例 1：
让我们以以下结构创建表 hobbies1：
并从文件 hobbies.jsonl 中插入数据：
在这种情况下，文件中的所有列都将插入到表中而不作更改，因此ClickHouse将使用插入表的结构，而不是模式推断。
示例 2：
让我们以以下结构创建表 hobbies2：
并从文件 hobbies.jsonl 中插入数据：
在这种情况下，SELECT 查询中的所有列都存在于表中，因此ClickHouse将使用插入表的结构。注意，它只适用于支持读取子集列的输入格式，如 JSONEachRow、TSKV、Parquet 等（因此它不适用于 TSV 格式）。
示例 3：
让我们以以下结构创建表 hobbies3：
并从文件 hobbies.jsonl 中插入数据：
在这种情况下，列 id 在 SELECT 查询中使用，但表中没有此列（它有一个名为 identifier 的列），因此ClickHouse无法使用插入表的结构，将使用模式推断。
示例 4：
让我们以以下结构创建表 hobbies4：
并从文件 hobbies.jsonl 中插入数据：
在这种情况下，在 SELECT 查询中对列 hobbies 执行了一些操作以将其插入到表中，因此ClickHouse无法使用插入表的结构，将使用模式推断。
模式推断缓存
对于大多数输入格式，模式推断读取一些数据以确定其结构，而此过程可能需要一些时间。为了防止ClickHouse在每次从同一文件读取数据时推断相同的模式，推断出的模式会被缓存，当再次访问同一文件时，ClickHouse将使用缓存中的模式。
有一些特殊设置控制此缓存：
schema_inference_cache_max_elements_for_{file/s3/hdfs/url/azure}- 对应表函数的最大缓存模式数。默认值为4096。这些设置应在服务器配置中进行设置。schema_inference_use_cache_for_{file,s3,hdfs,url,azure}- 允许开启/关闭用于模式推断的缓存。这些设置可以在查询中使用。
文件的模式可以通过修改数据或更改格式设置来更改。因此，模式推断缓存是通过文件来源、格式名称、所用格式设置和文件的最后修改时间来识别模式的。
注意：某些通过URL在 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 推断结构，并查看模式推断缓存是如何工作的：
如您所见，第二个查询几乎瞬间成功。
让我们尝试更改一些可能影响推断模式的设置：
如您所见，对于同一文件，未使用缓存中的模式，因为改变了可能影响推断模式的设置。
让我们检查一下 system.schema_inference_cache 表的内容：
如您所见，对于同一文件有两种不同的模式。
我们可以使用系统查询清除模式缓存：
文本格式
对于文本格式，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 表函数。
整数、浮点、布尔值、字符串：
日期、日期时间：
数组：
如果数组包含 null，ClickHouse 会使用其他数组元素的类型：
具名元组：
启用设置 input_format_json_try_infer_named_tuples_from_objects 后，在模式推断过程中，ClickHouse 将尝试从JSON对象中推断具名元组。结果的具名元组将包含来自示例数据的所有相应JSON对象的所有元素。
无名元组：
在JSON格式中，我们将具有不同类型元素的数组视为无名元组。
如果某些值为 null 或为空，我们会使用其他行对应值的类型：
映射：
在JSON中，我们可以读取具有相同类型值的对象作为Map类型。
注意：只有在禁用设置 input_format_json_read_objects_as_strings 和 input_format_json_try_infer_named_tuples_from_objects 时此设置才会生效。
JSON对象类型（如果启用设置 allow_experimental_object_type）：
嵌套复杂类型：
如果ClickHouse无法确定某些键的类型，因为数据仅包含null/空对象/空数组，如果启用设置 input_format_json_infer_incomplete_types_as_strings，将使用类型 String，否则将抛出异常：
JSON设置
input_format_json_try_infer_numbers_from_strings
启用此设置可允许从字符串值中推断数字。
此设置默认禁用。
示例：
input_format_json_try_infer_named_tuples_from_objects
启用此设置可允许从JSON对象中推断具名元组。结果的具名元组将包含来自示例数据的所有相应JSON对象的所有元素。 当JSON数据不稀疏时，这可能会很有用，因为数据样本将包含所有可能的对象键。
此设置默认启用。
示例
结果：
结果：
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 时）时，对模糊路径使用字符串类型，而不是抛出异常。
它允许将JSON对象读取为具名元组，即使存在模糊路径。
默认禁用。
示例
禁用设置：
结果：
启用设置：
结果：
input_format_json_read_objects_as_strings
启用此设置可允许将嵌套JSON对象作为字符串读取。 此设置可用于在不使用JSON对象类型的情况下读取嵌套JSON对象。
此设置默认启用。
注意：启用此设置仅在禁用设置 input_format_json_try_infer_named_tuples_from_objects 时生效。
input_format_json_read_numbers_as_strings
启用此设置可允许将数值作为字符串读取。
此设置默认启用。
示例
input_format_json_read_bools_as_numbers
启用此设置可允许将布尔值作为数字读取。
此设置默认启用。
示例：
input_format_json_read_bools_as_strings
启用此设置可允许将布尔值作为字符串读取。
此设置默认启用。
示例：
input_format_json_read_arrays_as_strings
启用此设置可允许将JSON数组值作为字符串读取。
此设置默认启用。
示例
input_format_json_infer_incomplete_types_as_strings
启用此设置可允许在模式推断期间使用字符串类型，用于数据样本中仅包含 Null/{}/[] 的JSON键。
在JSON格式中，如果启用（默认启用），任何值都可以读取为字符串，并且我们可以通过使用未知类型的键的字符串类型避免错误，例如“无法确定列 'column_name' 的类型，因为前25000行数据大多包含空值或空数组/映射”的错误。
示例：
结果：
CSV
在CSV格式中，ClickHouse根据分隔符从行中提取列值。ClickHouse期望除数字和字符串之外的所有类型都用双引号括起来。如果值在双引号中，ClickHouse尝试使用递归解析器解析引号内的数据，然后尝试为其找到最合适的数据类型。如果值不在双引号中，ClickHouse尝试将其解析为数字，如果值不是数字，ClickHouse将其视为字符串。
如果您不希望ClickHouse尝试使用一些解析器和启发式方法来确定复杂类型，可以禁用设置 input_format_csv_use_best_effort_in_schema_inference，ClickHouse将把所有列视为字符串。
如果启用设置 input_format_csv_detect_header，ClickHouse将在推断模式时尝试检测带有列名（可能还有类型）的头部。此设置默认启用。
示例：
整数、浮点、布尔值、字符串：
没有引号的字符串：
日期、日期时间：
数组：
如果数组包含null，ClickHouse将使用其他数组元素的类型：
映射：
嵌套数组和映射：
如果ClickHouse无法确定引号内的类型，因为数据仅包含空值，ClickHouse将其视为字符串：
禁用设置 input_format_csv_use_best_effort_in_schema_inference 的示例：
标题自动检测示例（当启用 input_format_csv_detect_header 时）：
仅名称：
名称和类型：
请注意，标题仅在至少有一列具有非字符串类型的情况下才能检测到。如果所有列都是字符串类型，则不会检测到标题：
CSV设置
input_format_csv_try_infer_numbers_from_strings
启用此设置可允许从字符串值中推断数字。
此设置默认禁用。
示例：
TSV/TSKV
在TSV/TSKV格式中，ClickHouse根据表格分隔符从行中提取列值，然后使用递归解析器解析提取的值，以确定最合适的类型。如果无法确定类型，ClickHouse将此值视为字符串。
如果您不希望ClickHouse尝试使用一些解析器和启发式方法来确定复杂类型，可以禁用设置 input_format_tsv_use_best_effort_in_schema_inference，ClickHouse将把所有列视为字符串。
如果启用设置 input_format_tsv_detect_header，ClickHouse将在推断模式时尝试检测带有列名（可能还有类型）的头部。此设置默认启用。
示例：
整数、浮点、布尔值、字符串：
日期、日期时间：
数组：
如果数组包含null，ClickHouse将使用其他数组元素的类型：
元组：
映射：
嵌套数组、元组和映射：
如果ClickHouse无法确定类型，因为数据仅包含空值，ClickHouse将其视为字符串：
禁用设置 input_format_tsv_use_best_effort_in_schema_inference 的示例：
标题自动检测示例（当启用 input_format_tsv_detect_header 时）：
仅名称：
名称和类型：
请注意，标题仅在至少有一列具有非字符串类型的情况下才能检测到。如果所有列都是字符串类型，则不会检测到标题：
值
在Values格式中，ClickHouse从行中提取列值，然后使用递归解析器进行解析，类似于字面值的解析。
示例：
整数、浮点、布尔值、字符串：
日期、日期时间：
数组：
如果数组包含null，ClickHouse将使用其他数组元素的类型：
元组：
映射：
嵌套数组、元组和映射：
如果ClickHouse无法确定类型，因为数据仅包含空值，将抛出异常：
禁用设置 input_format_tsv_use_best_effort_in_schema_inference 的示例：
自描述格式
自描述格式在数据本身中包含有关数据结构的信息，可以是带有描述的某个头部、二进制类型树或某种表。为了自动从这种格式的文件中推断模式，ClickHouse读取包含类型信息的数据部分并将其转换为ClickHouse表的模式。
带有 -WithNamesAndTypes 后缀的格式
ClickHouse支持一些带有后缀 -WithNamesAndTypes 的文本格式。此后缀表示数据在实际数据之前包含两行列名和类型。在此类格式的模式推断中，ClickHouse读取前两行，并提取列名和类型。
示例
带有元数据的JSON格式
某些JSON输入格式（JSON, JSONCompact, JSONColumnsWithMetadata）包含带有列名和类型的元数据。 在此类格式的模式推断中，ClickHouse会读取这些元数据。
示例
Avro
在Avro格式中，ClickHouse从数据中读取其模式，并使用以下类型匹配将其转换为ClickHouse模式：
| Avro数据类型 | ClickHouse数据类型 |
|---|---|
boolean | Bool |
int | Int32 |
int (date) * | Date32 |
long | Int64 |
float | Float32 |
double | Float64 |
bytes, string | String |
fixed | FixedString(N) |
enum | Enum |
array(T) | Array(T) |
union(null, T), union(T, null) | Nullable(T) |
null | Nullable(Nothing) |
string (uuid) * | UUID |
binary (decimal) * | Decimal(P, S) |
* Avro逻辑类型
其他Avro类型不支持。
Parquet
在 Parquet 格式中，ClickHouse 从数据中读取其模式并使用以下类型匹配将其转换为 ClickHouse 模式：
| Parquet 数据类型 | ClickHouse 数据类型 |
|---|---|
BOOL | Bool |
UINT8 | UInt8 |
INT8 | Int8 |
UINT16 | UInt16 |
INT16 | Int16 |
UINT32 | UInt32 |
INT32 | Int32 |
UINT64 | UInt64 |
INT64 | Int64 |
FLOAT | Float32 |
DOUBLE | Float64 |
DATE | Date32 |
TIME (ms) | DateTime |
TIMESTAMP, TIME (us, ns) | DateTime64 |
STRING, BINARY | String |
DECIMAL | Decimal |
LIST | Array |
STRUCT | Tuple |
MAP | Map |
其他 Parquet 类型不被支持。默认情况下，所有推断的类型都是 Nullable，但可以通过设置 schema_inference_make_columns_nullable 进行更改。
Arrow
在 Arrow 格式中，ClickHouse 从数据中读取其模式并使用以下类型匹配将其转换为 ClickHouse 模式：
| Arrow 数据类型 | ClickHouse 数据类型 |
|---|---|
BOOL | Bool |
UINT8 | UInt8 |
INT8 | Int8 |
UINT16 | UInt16 |
INT16 | Int16 |
UINT32 | UInt32 |
INT32 | Int32 |
UINT64 | UInt64 |
INT64 | Int64 |
FLOAT, HALF_FLOAT | Float32 |
DOUBLE | Float64 |
DATE32 | Date32 |
DATE64 | DateTime |
TIMESTAMP, TIME32, TIME64 | DateTime64 |
STRING, BINARY | String |
DECIMAL128, DECIMAL256 | Decimal |
LIST | Array |
STRUCT | Tuple |
MAP | Map |
其他 Arrow 类型不被支持。默认情况下，所有推断的类型都是 Nullable，但可以通过设置 schema_inference_make_columns_nullable 进行更改。
ORC
在 ORC 格式中，ClickHouse 从数据中读取其模式并使用以下类型匹配将其转换为 ClickHouse 模式：
| ORC 数据类型 | ClickHouse 数据类型 |
|---|---|
Boolean | Bool |
Tinyint | Int8 |
Smallint | Int16 |
Int | Int32 |
Bigint | Int64 |
Float | Float32 |
Double | Float64 |
Date | Date32 |
Timestamp | DateTime64 |
String, Char, Varchar,BINARY | String |
Decimal | Decimal |
List | Array |
Struct | Tuple |
Map | Map |
其他 ORC 类型不被支持。默认情况下，所有推断的类型都是 Nullable，但可以通过设置 schema_inference_make_columns_nullable 进行更改。
Native
Native 格式在 ClickHouse 内部使用，并在数据中包含模式。 在模式推断中，ClickHouse 从数据中读取模式而不进行任何转换。
Formats with external schema
这样的格式需要在特定模式语言的单独文件中描述数据的模式。 为了自动推断这些格式文件的模式，ClickHouse 从单独的文件中读取外部模式并将其转换为 ClickHouse 表模式。
Protobuf
在 Protobuf 格式的模式推断中，ClickHouse 使用以下类型匹配：
| Protobuf 数据类型 | ClickHouse 数据类型 |
|---|---|
bool | UInt8 |
float | Float32 |
double | Float64 |
int32, sint32, sfixed32 | Int32 |
int64, sint64, sfixed64 | Int64 |
uint32, fixed32 | UInt32 |
uint64, fixed64 | UInt64 |
string, bytes | String |
enum | Enum |
repeated T | Array(T) |
message, group | Tuple |
CapnProto
在 CapnProto 格式的模式推断中，ClickHouse 使用以下类型匹配：
| CapnProto 数据类型 | ClickHouse 数据类型 |
|---|---|
Bool | UInt8 |
Int8 | Int8 |
UInt8 | UInt8 |
Int16 | Int16 |
UInt16 | UInt16 |
Int32 | Int32 |
UInt32 | UInt32 |
Int64 | Int64 |
UInt64 | UInt64 |
Float32 | Float32 |
Float64 | Float64 |
Text, Data | String |
enum | Enum |
List | Array |
struct | Tuple |
union(T, Void), union(Void, T) | Nullable(T) |
Strong-typed binary formats
在这样的格式中，每个序列化值包含有关其类型（可能还有名称）的信息，但没有关于整个表的信息。
在这些格式的模式推断中，ClickHouse 按行读取数据（最多读取 input_format_max_rows_to_read_for_schema_inference 行或 input_format_max_bytes_to_read_for_schema_inference 字节），并从数据中提取每个值的类型（可能还有名称），然后将这些类型转换为 ClickHouse 类型。
MsgPack
在 MsgPack 格式中，各行之间没有分隔符，要对该格式使用模式推断，您应使用设置 input_format_msgpack_number_of_columns 指定表中的列数。ClickHouse 使用以下类型匹配：
MessagePack 数据类型 (INSERT) | ClickHouse 数据类型 |
|---|---|
int N, uint N, negative fixint, positive fixint | Int64 |
bool | UInt8 |
fixstr, str 8, str 16, str 32, bin 8, bin 16, bin 32 | String |
float 32 | Float32 |
float 64 | Float64 |
uint 16 | Date |
uint 32 | DateTime |
uint 64 | DateTime64 |
fixarray, array 16, array 32 | Array |
fixmap, map 16, map 32 | Map |
默认情况下，所有推断的类型都是 Nullable，但可以通过设置 schema_inference_make_columns_nullable 进行更改。
BSONEachRow
在 BSONEachRow 中，每行数据以 BSON 文档的形式呈现。在模式推断中，ClickHouse 逐一读取 BSON 文档并提取值、名称和数据类型，然后使用以下类型匹配将这些类型转换为 ClickHouse 类型：
| BSON 类型 | ClickHouse 类型 |
|---|---|
\x08 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 (带字符串键) |
默认情况下，所有推断的类型都是 Nullable，但可以通过设置 schema_inference_make_columns_nullable 进行更改。
Formats with constant schema
这些格式中的数据始终具有相同的模式。
LineAsString
在这种格式中，ClickHouse 将整个行从数据读取到具有 String 数据类型的单个列中。该格式的推断类型始终为 String，列名为 line。
示例
JSONAsString
在这种格式中，ClickHouse 将整个 JSON 对象从数据读取到具有 String 数据类型的单个列中。该格式的推断类型始终为 String，列名为 json。
示例
JSONAsObject
在这种格式中，ClickHouse 将整个 JSON 对象从数据读取到具有 Object('json') 数据类型的单个列中。该格式的推断类型始终为 String，列名为 json。
注意：此格式仅在启用 allow_experimental_object_type 时有效。
示例
Schema inference modes
从数据文件集推断模式可以在两种不同模式下工作：default 和 union。
模式由设置 schema_inference_mode 控制。
Default mode
在默认模式下，ClickHouse 假设所有文件具有相同的模式，并尝试通过逐个读取文件推断模式，直到成功为止。
示例：
假设我们有 3 个文件 data1.jsonl、data2.jsonl 和 data3.jsonl，内容如下：
data1.jsonl:
data2.jsonl:
data3.jsonl:
让我们尝试在这 3 个文件上使用模式推断：
结果：
如我们所见，文件 data3.jsonl 中没有 field3。
这是因为 ClickHouse 首先尝试从文件 data1.jsonl 推断模式，但由于 field2 仅为 null 而失败，
然后尝试从 data2.jsonl 推断模式并成功，所以没有读取文件 data3.jsonl 的数据。
Union mode
在联接模式下，ClickHouse 假设文件可以具有不同的模式，因此它推断所有文件的模式，然后将它们联接到公共模式。
假设我们有 3 个文件 data1.jsonl、data2.jsonl 和 data3.jsonl，内容如下：
data1.jsonl:
data2.jsonl:
data3.jsonl:
让我们尝试在这 3 个文件上使用模式推断：
结果：
如我们所见，我们有来自所有文件的所有字段。
注意：
- 由于某些文件可能不包含结果模式中的某些列，因此联接模式仅支持读取列子集的格式（如 JSONEachRow、Parquet、TSVWithNames 等），而不适用于其他格式（如 CSV、TSV、JSONCompactEachRow 等）。
- 如果 ClickHouse 无法从某个文件推断模式，则将抛出异常。
- 如果您有很多文件，从所有文件读取模式可能需要很长时间。
Automatic format detection
如果数据格式未指定且无法通过文件扩展名确定，ClickHouse 将尝试通过其内容检测文件格式。
示例：
假设我们有 data，其内容如下：
我们可以在不指定格式或结构的情况下检查和查询此文件：
ClickHouse 只能检测某些格式的子集，这种检测需要一些时间，最好明确指定格式。
