JSON 模式推断
ClickHouse 可以自动确定 JSON 数据的结构。这可以用于直接查询磁盘上（使用 clickhouse-local）或 S3 存储桶中的 JSON 数据，和/或在将数据加载到 ClickHouse 之前自动创建模式。
何时使用类型推断
- 结构一致 - 您将从中推断类型的数据包含您感兴趣的所有列。在类型推断之后添加了额外列的数据将被忽略，并且无法查询。
- 类型一致 - 特定列的数据类型需要兼容。
如果您有更动态的 JSON，其中在没有充分警告修改模式的情况下添加了新键（例如日志中的 Kubernetes 标签），我们建议阅读 设计 JSON 模式。
检测类型
我们之前的示例使用了 Python PyPI 数据集 的简单版本，格式为 NDJSON。在本节中，我们将探索一个更复杂的数据集，其中包含嵌套结构 - arXiv 数据集，其中包含 250 万篇学术论文。此数据集以 NDJSON 格式分发，每一行代表一篇已发表的学术论文。下面显示了一个示例行
{
"id": "2101.11408",
"submitter": "Daniel Lemire",
"authors": "Daniel Lemire",
"title": "Number Parsing at a Gigabyte per Second",
"comments": "Software at https://github.com/fastfloat/fast_float and\n https://github.com/lemire/simple_fastfloat_benchmark/",
"journal-ref": "Software: Practice and Experience 51 (8), 2021",
"doi": "10.1002/spe.2984",
"report-no": null,
"categories": "cs.DS cs.MS",
"license": "http://creativecommons.org/licenses/by/4.0/",
"abstract": "With disks and networks providing gigabytes per second ....\n",
"versions": [
{
"created": "Mon, 11 Jan 2021 20:31:27 GMT",
"version": "v1"
},
{
"created": "Sat, 30 Jan 2021 23:57:29 GMT",
"version": "v2"
}
],
"update_date": "2022-11-07",
"authors_parsed": [
[
"Lemire",
"Daniel",
""
]
]
}
此数据比之前的示例需要更复杂的模式。我们在下面概述了定义此模式的过程，介绍了诸如 Tuple 和 Array 等复杂类型。
此数据集存储在公共 S3 存储桶 s3://datasets-documentation/arxiv/arxiv.json.gz 中。
您可以看到上面的数据集包含嵌套的 JSON 对象。虽然用户应该起草和版本化他们的模式，但推断允许从数据中推断类型。这允许自动生成模式 DDL，避免了手动构建它的需要，并加速了开发过程。
除了检测模式之外，JSON 模式推断还会自动从文件扩展名和内容中推断数据格式。上面的文件被自动检测为 NDJSON。
将 s3 函数 与 DESCRIBE 命令一起使用，可以显示将要推断的类型。
DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
SETTINGS describe_compact_output = 1
┌─name───────────┬─type────────────────────────────────────────────────────────────────────┐
│ id │ Nullable(String) │
│ submitter │ Nullable(String) │
│ authors │ Nullable(String) │
│ title │ Nullable(String) │
│ comments │ Nullable(String) │
│ journal-ref │ Nullable(String) │
│ doi │ Nullable(String) │
│ report-no │ Nullable(String) │
│ categories │ Nullable(String) │
│ license │ Nullable(String) │
│ abstract │ Nullable(String) │
│ versions │ Array(Tuple(created Nullable(String),version Nullable(String))) │
│ update_date │ Nullable(Date) │
│ authors_parsed │ Array(Array(Nullable(String))) │
└────────────────┴─────────────────────────────────────────────────────────────────────────┘
您可以看到很多列都被检测为 Nullable。我们不建议在不需要时使用 Nullable 类型。您可以使用 schema_inference_make_columns_nullable 来控制何时应用 Nullable 的行为。
我们可以看到，大多数列都已自动检测为 String，而 update_date 列已正确检测为 Date。 versions 列已创建为 Array(Tuple(created String, version String)) 以存储对象列表，而 authors_parsed 被定义为 Array(Array(String)) 用于嵌套数组。
日期和日期时间的自动检测可以通过设置 input_format_try_infer_dates 和 input_format_try_infer_datetimes 分别控制（默认情况下都启用）。对象作为元组的推断由设置 input_format_json_try_infer_named_tuples_from_objects 控制。控制 JSON 模式推断的其他设置（例如数字的自动检测）可以在此处找到。
查询 JSON
我们可以依靠模式推断来就地查询 JSON 数据。下面，我们找到每年的顶级作者，利用了日期和数组被自动检测到的事实。
SELECT
toYear(update_date) AS year,
authors,
count() AS c
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
GROUP BY
year,
authors
ORDER BY
year ASC,
c DESC
LIMIT 1 BY year
┌─year─┬─authors────────────────────────────────────┬───c─┐
│ 2007 │ The BABAR Collaboration, B. Aubert, et al │ 98 │
│ 2008 │ The OPAL collaboration, G. Abbiendi, et al │ 59 │
│ 2009 │ Ashoke Sen │ 77 │
│ 2010 │ The BABAR Collaboration, B. Aubert, et al │ 117 │
│ 2011 │ Amelia Carolina Sparavigna │ 21 │
│ 2012 │ ZEUS Collaboration │ 140 │
│ 2013 │ CMS Collaboration │ 125 │
│ 2014 │ CMS Collaboration │ 87 │
│ 2015 │ ATLAS Collaboration │ 118 │
│ 2016 │ ATLAS Collaboration │ 126 │
│ 2017 │ CMS Collaboration │ 122 │
│ 2018 │ CMS Collaboration │ 138 │
│ 2019 │ CMS Collaboration │ 113 │
│ 2020 │ CMS Collaboration │ 94 │
│ 2021 │ CMS Collaboration │ 69 │
│ 2022 │ CMS Collaboration │ 62 │
│ 2023 │ ATLAS Collaboration │ 128 │
│ 2024 │ ATLAS Collaboration │ 120 │
└──────┴────────────────────────────────────────────┴─────┘
18 rows in set. Elapsed: 20.172 sec. Processed 2.52 million rows, 1.39 GB (124.72 thousand rows/s., 68.76 MB/s.)
模式推断允许我们查询 JSON 文件，而无需指定模式，从而加速了即席数据分析任务。
创建表
我们可以依靠模式推断来创建表的模式。以下 CREATE AS EMPTY 命令导致表的 DDL 被推断出来，并创建表。这不会加载任何数据
CREATE TABLE arxiv
ENGINE = MergeTree
ORDER BY update_date EMPTY
AS SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
SETTINGS schema_inference_make_columns_nullable = 0
为了确认表模式，我们使用 SHOW CREATE TABLE 命令
SHOW CREATE TABLE arxiv
CREATE TABLE arxiv
(
`id` String,
`submitter` String,
`authors` String,
`title` String,
`comments` String,
`journal-ref` String,
`doi` String,
`report-no` String,
`categories` String,
`license` String,
`abstract` String,
`versions` Array(Tuple(created String, version String)),
`update_date` Date,
`authors_parsed` Array(Array(String))
)
ENGINE = MergeTree
ORDER BY update_date
SETTINGS index_granularity = 8192
以上是此数据的正确模式。模式推断基于采样数据并逐行读取数据。列值根据格式提取，使用递归解析器和启发式方法来确定每个值的类型。从模式推断中的数据读取的最大行数和字节数由设置 input_format_max_rows_to_read_for_schema_inference（默认为 25000）和 input_format_max_bytes_to_read_for_schema_inference（默认为 32MB）控制。如果检测不正确，用户可以按照此处所述提供提示。
从代码片段创建表
上面的示例使用 S3 上的文件来创建表模式。用户可能希望从单行代码片段创建模式。这可以使用 format 函数来实现，如下所示
CREATE TABLE arxiv
ENGINE = MergeTree
ORDER BY update_date EMPTY
AS SELECT *
FROM format(JSONEachRow, '{"id":"2101.11408","submitter":"Daniel Lemire","authors":"Daniel Lemire","title":"Number Parsing at a Gigabyte per Second","comments":"Software at https://github.com/fastfloat/fast_float and","doi":"10.1002/spe.2984","report-no":null,"categories":"cs.DS cs.MS","license":"http://creativecommons.org/licenses/by/4.0/","abstract":"Withdisks and networks providing gigabytes per second ","versions":[{"created":"Mon, 11 Jan 2021 20:31:27 GMT","version":"v1"},{"created":"Sat, 30 Jan 2021 23:57:29 GMT","version":"v2"}],"update_date":"2022-11-07","authors_parsed":[["Lemire","Daniel",""]]}') SETTINGS schema_inference_make_columns_nullable = 0
SHOW CREATE TABLE arxiv
CREATE TABLE arxiv
(
`id` String,
`submitter` String,
`authors` String,
`title` String,
`comments` String,
`doi` String,
`report-no` String,
`categories` String,
`license` String,
`abstract` String,
`versions` Array(Tuple(created String, version String)),
`update_date` Date,
`authors_parsed` Array(Array(String))
)
ENGINE = MergeTree
ORDER BY update_date
加载 JSON 数据
之前的命令创建了一个可以加载数据的表。您现在可以使用以下 INSERT INTO SELECT 将数据插入到您的表中
INSERT INTO arxiv SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
0 rows in set. Elapsed: 38.498 sec. Processed 2.52 million rows, 1.39 GB (65.35 thousand rows/s., 36.03 MB/s.)
Peak memory usage: 870.67 MiB.
有关从其他来源（例如文件）加载数据的示例，请参阅此处。
加载后，我们可以查询我们的数据，可以选择使用格式 PrettyJSONEachRow 以原始结构显示行
SELECT *
FROM arxiv
LIMIT 1
FORMAT PrettyJSONEachRow
{
"id": "0704.0004",
"submitter": "David Callan",
"authors": "David Callan",
"title": "A determinant of Stirling cycle numbers counts unlabeled acyclic",
"comments": "11 pages",
"journal-ref": "",
"doi": "",
"report-no": "",
"categories": "math.CO",
"license": "",
"abstract": " We show that a determinant of Stirling cycle numbers counts unlabeled acyclic\nsingle-source automata.",
"versions": [
{
"created": "Sat, 31 Mar 2007 03:16:14 GMT",
"version": "v1"
}
],
"update_date": "2007-05-23",
"authors_parsed": [
[
"Callan",
"David"
]
]
}
1 row in set. Elapsed: 0.009 sec.
处理错误
有时，您可能会遇到错误的数据。例如，特定列不具有正确的类型或 JSON 格式不正确。为此，您可以使用设置 input_format_allow_errors_ratio 以允许在数据触发插入错误时忽略一定数量的行。此外，可以提供提示以帮助推断。
进一步阅读
要了解有关数据类型推断的更多信息，您可以参考此文档页面。