表函数 file, s3, url, hdfs, azureBlobStorage.
structure,用于指定输入数据的结构。如果未指定该参数,或将其设置为 auto,则会根据数据自动推断结构。
示例:
假设 user_files 目录中有一个采用 JSONEachRow 格式的文件 hobbies.jsonl,内容如下:
JSONEachRow 是根据文件扩展名 .jsonl 自动识别的。
你可以使用 DESCRIBE 查询来查看自动识别的结构:
表引擎 File, S3, URL, HDFS, azureBlobStorage
CREATE TABLE 查询中未指定列列表,系统会自动根据数据推断表结构。
示例:
我们使用文件 hobbies.jsonl。可以创建一个使用 File 引擎的表,并读取该文件中的数据:
clickhouse-local 提供可选参数 -S/--structure,用于指定输入数据的结构。如果未指定该参数,或将其设为 auto,则会根据数据自动推断其结构。
示例:
以文件 hobbies.jsonl 为例。我们可以使用 clickhouse-local 查询该文件中的数据:
file/s3/url/hdfs 向表中插入数据时,
可以选择使用插入表的结构,而不是从数据中提取结构。
这样可以提升插入性能,因为 schema 推断 可能会耗费一些时间。此外,当表具有优化后的 schema 时,这种方式也很有帮助,因此
无需执行类型转换。
有一个特殊设置 use_structure_from_insertion_table_in_table_functions
用于控制此行为。它有 3 个可能的值:
- 0 - 表函数将从数据中提取结构。
- 1 - 表函数将使用插入表的结构。
- 2 - ClickHouse 将自动判断是否可以使用插入表的结构,或使用 schema 推断。默认值。
hobbies1:
hobbies.jsonl 中插入数据:
hobbies2:
hobbies.jsonl 中插入数据:
SELECT 查询中的所有列都存在于该表中,因此 ClickHouse 将使用插入表的结构。
请注意,这仅适用于支持读取部分列的输入格式,例如 JSONEachRow、TSKV、Parquet 等 (因此例如 TSV 格式就不适用) 。
示例 3:
让我们创建表 hobbies3,其结构如下:
hobbies.jsonl 中插入数据:
SELECT 查询中使用了列 id,但该表并没有这一列 (而是有一个名为 identifier 的列) ,
因此 ClickHouse 无法使用插入表的结构,而会使用 schema 推断。
示例 4:
让我们创建表 hobbies4,其结构如下:
hobbies.jsonl 中插入数据:
SELECT 查询中对列 hobbies 做了一些处理后再将其插入表中,因此 ClickHouse 无法使用插入表的结构,而会使用 schema 推断。
对于大多数输入格式,schema 推断会读取部分数据来确定其结构,这个过程可能需要一些时间。
为了避免 ClickHouse 每次从同一文件读取数据时都重复推断相同的 schema,已推断出的 schema 会被缓存;当再次访问同一文件时,ClickHouse 会使用缓存中的 schema。
有一些专门用于控制此缓存的设置:
schema_inference_cache_max_elements_for_{file/s3/hdfs/url/azure}- 对应表函数可缓存的 schema 最大数量。默认值为4096。这些设置应在 server 配置中设置。schema_inference_use_cache_for_{file,s3,hdfs,url,azure}- 用于开启/关闭 schema 推断缓存。这些设置可以在查询中使用。
url 表函数中通过 url 访问的某些文件可能不包含最后修改时间信息;针对这种情况,有一个专门的设置
schema_inference_cache_require_modification_time_for_url。禁用此设置后,对于这类文件,即使没有最后修改时间,也允许使用缓存中的 schema。
此外,还有一个系统表 schema_inference_cache,其中包含当前缓存中的所有 schema,以及系统查询 SYSTEM CLEAR SCHEMA CACHE [FOR File/S3/URL/HDFS]
它可用于清理所有来源的 schema 缓存,或某个特定来源的 schema 缓存。
示例:
让我们尝试从 s3 中的样本数据集 github-2022.ndjson.gz 推断其结构,并看看 schema 推断缓存是如何工作的:
system.schema_inference_cache 表中的内容:
input_format_max_rows_to_read_for_schema_inference (默认值为 25000) 和 input_format_max_bytes_to_read_for_schema_inference (默认值为 32Mb) 控制。
默认情况下,所有推断出的类型均为 Nullable,但你可以通过设置 schema_inference_make_columns_nullable 更改这一行为 (请参见设置部分中的示例) 。
在 JSON 格式中,ClickHouse 会按照 JSON 规范解析值,并尝试为其推断最合适的数据类型。
下面介绍其工作原理、可推断的类型,以及在 JSON formats 中可使用的特定设置。
示例
在本节及后续内容中,示例将使用 format 表函数。
整数、浮点数、Bool、String:
null,ClickHouse 将根据其他数组元素来推断类型:
input_format_json_infer_array_of_dynamic_from_array_of_different_types 已启用 (默认启用) ,则该数组的类型将为 Array(Dynamic):
input_format_json_try_infer_named_tuples_from_objects 时,ClickHouse 会在进行 schema 推断 期间尝试从 JSON 对象中推断命名元组。
生成的命名元组将包含样本数据中所有对应 JSON 对象里的全部元素。
input_format_json_infer_array_of_dynamic_from_array_of_different_types,我们会在 JSON 格式中将元素类型不同的 Array 视为未命名元组。
null 或为空,则使用其他行中对应值的类型:
input_format_json_read_objects_as_strings 和 input_format_json_try_infer_named_tuples_from_objects 时,此功能才会生效。
input_format_json_infer_incomplete_types_as_strings 时将使用 String 类型;否则会抛出异常:
input_format_json_try_infer_numbers_from_strings
input_format_json_try_infer_named_tuples_from_objects
Query
Response
Query
Response
input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects
input_format_json_try_infer_named_tuples_from_objects 的情况下) ,对于存在歧义的路径将使用 String 类型,而不是抛出异常。
即使存在歧义路径,也可以将 JSON 对象读取为命名元组。
默认禁用。
示例
在该设置禁用时:
Query
Response
Query
Response
input_format_json_read_objects_as_strings
input_format_json_try_infer_named_tuples_from_objects 设置时,启用此设置才会生效。
input_format_json_read_numbers_as_strings
input_format_json_read_bools_as_numbers
input_format_json_read_bools_as_strings
input_format_json_read_arrays_as_strings
input_format_json_infer_incomplete_types_as_strings
Null/{}/[] 的 JSON 键,可使用 String 类型。
在 JSON formats 中,如果启用了所有相应设置 (这些设置默认均为启用) ,则任何值都可以读取为 String;这样一来,通过对类型未知的键使用 String 类型,就可以在 schema 推断期间避免出现类似 Cannot determine type for column 'column_name' by first 25000 rows of data, most likely this column contains only Nulls or empty Arrays/Maps 的错误。
Example:
Query
Response
input_format_csv_use_best_effort_in_schema_inference,
这样 ClickHouse 会将所有列都视为 String。
如果启用了设置 input_format_csv_detect_header,ClickHouse 会在推断 schema 时尝试检测包含列名 (以及可能包含类型) 的表头。该设置默认启用。
示例:
整数、浮点数、布尔值、字符串:
input_format_csv_use_best_effort_in_schema_inference 设置的示例:
input_format_csv_detect_header 时) :
仅列名:
input_format_csv_try_infer_numbers_from_strings
input_format_tsv_use_best_effort_in_schema_inference,
这样 ClickHouse 会将所有列都视为 String。
如果启用了设置 input_format_tsv_detect_header,ClickHouse 会在推断 schema 时尝试检测包含列名 (以及可能还有类型) 的表头。此设置默认启用。
示例:
整数、浮点数、布尔值、字符串:
input_format_tsv_use_best_effort_in_schema_inference 设置的示例:
input_format_tsv_detect_header 时) :
仅列名:
input_format_tsv_use_best_effort_in_schema_inference 设置的示例:
input_format_custom_detect_header,ClickHouse 会在推断 schema 时尝试检测包含列名 (以及可能还包含类型) 的表头。此设置默认启用。
示例
input_format_custom_detect_header 时) :
resultset 的文件,内容如下:
row_format:
input_format_max_rows_to_read_for_schema_inference/input_format_max_bytes_to_read_for_schema_inference
25000对应input_format_max_rows_to_read_for_schema_inference。33554432(32 Mb) 对应input_format_max_bytes_to_read_for_schema_inference。
c1,c2,c3,...。格式:column1,column2,column3,...。
示例
Nullable。可能的值:
- 0 - 推断出的类型永远不会是
Nullable, - 1 - 所有推断类型都将为
Nullable, - 2 或 ‘auto’ - 对于文本格式,仅当列在 schema 推断期间解析的样本中包含
NULL时,推断出的类型才会是Nullable;对于强类型格式 (Parquet、ORC、Arrow) ,可空性信息会从文件元数据中获取, - 3 - 对于文本格式,使用
Nullable;对于强类型格式,则使用文件元数据。
此设置不适用于
JSON 数据类型。Int64;如果至少有一个数字是浮点数,则结果类型为 Float64。
如果样本数据只包含整数,且至少有一个正整数超出 Int64 的表示范围,ClickHouse 将推断为 UInt64。
默认启用。
示例
DateTime 或 DateTime64 类型。
如果样本数据中某一列的所有字段都成功解析为日期时间,结果类型将为 DateTime 或 DateTime64(9) (如果存在带小数部分的日期时间) ;
如果至少有一个字段未能解析为日期时间,结果类型将为 String。
默认启用。
示例
input_format_try_infer_datetimes,ClickHouse 也始终会推断为 DateTime64(9)。
默认处于禁用状态。
示例
Date 类型。
如果样本数据中某一列的所有字段都成功解析为日期,则结果类型为 Date;
如果至少有一个字段未解析为日期,则结果类型为 String。
默认启用。
示例
不支持其他 Avro 类型。
在 Parquet 格式中,ClickHouse 会从数据中读取其 schema,并根据以下类型对应关系将其转换为 ClickHouse schema:
不支持其他 Parquet 类型。
在 Arrow 格式中,ClickHouse 会从数据中读取 schema,并根据以下类型对应关系将其转换为 ClickHouse schema:
不支持其他 Arrow 类型。
在 ORC 格式中,ClickHouse 会从数据中读取 schema,并按照以下类型映射将其转换为 ClickHouse schema:
不支持其他 ORC 类型。
Native 格式用于 ClickHouse 内部,并在数据中包含 schema。
在 schema 推断 中,ClickHouse 会直接从数据中读取 schema,无需任何转换。
这类格式需要在单独的文件中使用特定的 schema 语言来定义数据的 schema。
为了自动从这类格式的文件中推断 schema,ClickHouse 会从单独的文件中读取外部 schema,并将其转换为 ClickHouse 表的 schema。
在 Protobuf 格式的 schema 推断中,ClickHouse 使用以下类型映射:
在 CapnProto 格式的 schema 推断 中,ClickHouse 使用以下类型映射:
在这类格式中,每个序列化值都包含其类型信息 (以及可能包含其名称信息) ,但不包含整个表的信息。
对此类格式进行 schema 推断时,ClickHouse 会逐行读取数据 (最多读取
input_format_max_rows_to_read_for_schema_inference 行或 input_format_max_bytes_to_read_for_schema_inference 字节) ,并从数据中提取
每个值的类型 (以及可能的名称) ,然后将这些类型转换为 ClickHouse 类型。
在 MsgPack 格式中,行与行之间没有分隔符,因此如果要对此格式使用 schema 推断,您需要通过设置 input_format_msgpack_number_of_columns 指定表中的列数。ClickHouse 使用以下类型映射:
默认情况下,所有推断出的类型都包含在
Nullable 中,但可以通过设置 schema_inference_make_columns_nullable 进行更改。
在 BSONEachRow 中,每一行数据都表示为一个 BSON 文档。在进行 schema 推断时,ClickHouse 会逐个读取 BSON 文档,并从数据中提取
值、名称和类型,然后按照以下类型映射将这些类型转换为 ClickHouse 类型:
默认情况下,所有推断出的类型都会包裹在
Nullable 中,但可以通过设置 schema_inference_make_columns_nullable 进行更改。
这类格式中的数据始终采用相同的 schema。
在这种格式下,ClickHouse 会将数据中的整行读入一个 String 数据类型的单列中。该格式推断出的类型始终为 String,列名为 line。
示例
String 数据类型的列中。该格式推断出的类型始终为 String,列名为 json。
示例
JSON 数据类型的单个列中。此格式推断出的类型始终为 JSON,列名为 json。
示例
default 和 union。
该模式由设置 schema_inference_mode 控制。
在默认模式下,ClickHouse 假设所有文件都具有相同的 schema,并尝试逐个读取文件来推断 schema,直到成功为止。
示例:
假设我们有 3 个文件 data1.jsonl、data2.jsonl 和 data3.jsonl,内容如下:
data1.jsonl:
data2.jsonl:
data3.jsonl:
Query
Response
data3.jsonl 的 field3 并没有被推断出来。
这是因为 ClickHouse 首先尝试从文件 data1.jsonl 推断 schema,但由于字段 field2 的值全都是 NULL 而失败;
随后又尝试从 data2.jsonl 推断 schema 并成功,因此没有读取文件 data3.jsonl 中的数据。
在 Union 模式下,ClickHouse 假设各个文件的 schema 可能不同,因此会推断所有文件的 schema,再将它们合并成一个统一的 schema。
假设我们有 3 个文件 data1.jsonl、data2.jsonl 和 data3.jsonl,内容如下:
data1.jsonl:
data2.jsonl:
data3.jsonl:
Query
Response
- 由于某些文件可能不包含结果 schema 中的某些列,union mode 仅支持能够读取部分列的格式 (如 JSONEachRow、Parquet、TSVWithNames 等) ,对于其他格式 (如 CSV、TSV、JSONCompactEachRow 等) 则不起作用。
- 如果 ClickHouse 无法从某个文件中推断出 schema,则会抛出异常。
- 如果文件很多,从所有文件中读取 schema 可能会耗费较长时间。
data 文件,其内容如下:
ClickHouse 只能识别部分格式,而且这种识别需要一定时间,因此最好始终明确指定格式。