JSON 模式推断
ClickHouse 可以自动确定 JSON 数据的结构。这可以用于直接查询 JSON 数据，例如磁盘上的 clickhouse-local 或 S3 存储桶，以及/或者在将数据加载到 ClickHouse 之前自动创建模式。
何时使用类型推断
- 一致的结构 - 您要从中推断类型的数 据包含您感兴趣的所有列。类型推断后添加的其他列的数据将被忽略，并且无法查询。
- 一致的类型 - 特定列的数据类型需要兼容。
如果您有更多动态的 JSON，其中新键在没有足够警告的情况下添加以修改模式（例如日志中的 Kubernetes 标签），我们建议阅读 设计 JSON 模式。
检测类型
我们之前的示例使用了 Python PyPI 数据集 的简单版本，采用 NDJSON 格式。在本节中，我们探讨了一个具有嵌套结构的更复杂的数据集 - 包含 250 万篇学术论文的 arXiv 数据集。此数据集中每一行（作为 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。
使用带有 DESCRIBE 命令的 s3 函数 显示将推断的类型。
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 来允许忽略一定数量的行，如果数据触发插入错误。此外，可以提供 提示 来辅助推断。
进一步阅读
要了解有关数据类型推断的更多信息，您可以参考 此 文档页面。