JSON
更新时间：2025-01-20
加载 JSON
在本节中，我们假设 JSON 数据为NDJSON（换行符分隔的JSON）格式，在ClickHouse中称为JSONEachRow。这是加载 JSON 的首选格式，因为它简洁且能有效利用空间，但其他格式也支持输入和输出。
考虑以下JSON示例，它表示来自Python PyPI 数据集的一行：
Plain Text
1{
2 "date": "2022-11-15",
3 "country_code": "ES",
4 "project": "clickhouse-connect",
5 "type": "bdist_wheel",
6 "installer": "pip",
7 "python_minor": "3.9",
8 "system": "Linux",
9 "version": "0.3.0"
10}为了将此JSON对象加载到 ClickHouse，必须定义表模式。下面显示了一个简单的模式，其中JSON 键映射到列名：
Plain Text
1CREATE TABLE pypi (
2 `date` Date,
3 `country_code` String,
4 `project` String,
5 `type` String,
6 `installer` String,
7 `python_minor` String,
8 `system` String,
9 `version` String
10)
11ENGINE = MergeTree
12ORDER BY (project, date)ClickHouse 可以加载多种格式的 JSON 数据，并自动根据扩展名和内容推断类型。我们可以使用S3 函数读取上表的 JSON 文件：
Plain Text
1SELECT *
2FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz')
3LIMIT 1
4┌───────date─┬─country_code─┬─project────────────┬─type────────┬─installer────┬─python_minor─┬─system─┬─version─┐
5│ 2022-11-15 │ CN │ clickhouse-connect │ bdist_wheel │ bandersnatch │ │ │ 0.2.8 │
6└────────────┴──────────────┴────────────────────┴─────────────┴──────────────┴──────────────┴────────┴─────────┘
7
81 row in set. Elapsed: 1.232 sec.请注意，我们不需要指定文件格式。相反，我们使用glob模式来读取*.json.gz存储桶中的所有文件。ClickHouse 会自动JSONEachRow根据文件扩展名和内容推断格式为 (ndjson)。如果 ClickHouse 无法检测到格式，可以通过参数函数手动指定格式。
Plain Text
1SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz', JSONEachRow)要加载这些文件中的行，可以使用INSERT INTO SELECT：
Plain Text
1INSERT INTO pypi SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz')
2Ok.
3
40 rows in set. Elapsed: 10.445 sec. Processed 19.49 million rows, 35.71 MB (1.87 million rows/s., 3.42 MB/s.)
5
6SELECT * FROM pypi LIMIT 2
7
8┌───────date─┬─country_code─┬─project────────────┐
9│ 2022-05-26 │ CN │ clickhouse-connect │
10│ 2022-05-26 │ CN │ clickhouse-connect │
11└────────────┴──────────────┴────────────────────┘
12
132 rows in set. Elapsed: 0.005 sec. Processed 8.19 thousand rows, 908.03 KB (1.63 million rows/s., 180.38 MB/s.)FORMAT也可以使用子句以内联方式加载行，例如：
INSERT INTO pypi FORMAT JSONEachRow {"date":"2022-11-15","country_code":"CN","project":"clickhouse-connect","type":"bdist_wheel","installer":"bandersnatch","python_minor":"","system":"","version":"0.2.8"}
JSON架构推断
ClickHouse 可以自动确定 JSON 数据的结构。这可用于直接查询 JSON 数据（例如使用磁盘clickhouse-local或 S3 存储桶），和/或在将数据加载到 ClickHouse 之前自动创建模式。
何时使用类型推断
- 一致的结构- 您要从中推断类型的数据包含您感兴趣的所有列。类型推断后添加的附加列的数据将被忽略并且无法查询。
- 一致的类型- 特定列的数据类型需要兼容。
检测类型
之前的示例使用了 NDJSON 格式的Python PyPI 数据集的简单版本。在本节中，我们将探索具有嵌套结构的更复杂的数据集 -包含 250 万篇学术论文的arXiv数据集。此数据集中的每一行都以 NDJSON 格式分发，代表一篇已发表的学术论文。示例行如下所示：
Plain Text
1{
2 "id": "2101.11408",
3 "submitter": "Daniel Lemire",
4 "authors": "Daniel Lemire",
5 "title": "Number Parsing at a Gigabyte per Second",
6 "comments": "Software at https://github.com/fastfloat/fast_float and\n https://github.com/lemire/simple_fastfloat_benchmark/",
7 "journal-ref": "Software: Practice and Experience 51 (8), 2021",
8 "doi": "10.1002/spe.2984",
9 "report-no": null,
10 "categories": "cs.DS cs.MS",
11 "license": "http://creativecommons.org/licenses/by/4.0/",
12 "abstract": "With disks and networks providing gigabytes per second ....\n",
13 "versions": [
14 {
15 "created": "Mon, 11 Jan 2021 20:31:27 GMT",
16 "version": "v1"
17 },
18 {
19 "created": "Sat, 30 Jan 2021 23:57:29 GMT",
20 "version": "v2"
21 }
22 ],
23 "update_date": "2022-11-07",
24 "authors_parsed": [
25 [
26 "Lemire",
27 "Daniel",
28 ""
29 ]
30 ]
31}这些数据需要比前面的示例复杂得多的架构。我们在下面概述了定义此架构的过程，并引入了诸如和Tuple之类的复杂类型Array。
该数据集存储在公共 S3 存储桶中s3://datasets-documentation/arxiv/arxiv.json.gz。
您可以看到上面的数据集包含嵌套的 JSON 对象。虽然用户应该起草并版本化他们的架构，但推断允许从数据中推断类型。这允许自动生成架构 DDL，避免手动构建它并加速开发过程。
使用命令中的s3函数DESCRIBE显示将被推断的类型。
Plain Text
1DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
2SETTINGS describe_compact_output = 1
Plain Text
1┌─name───────────┬─type────────────────────────────────────────────────────────────────────┐
2│ id │ Nullable(String) │
3│ submitter │ Nullable(String) │
4│ authors │ Nullable(String) │
5│ title │ Nullable(String) │
6│ comments │ Nullable(String) │
7│ journal-ref │ Nullable(String) │
8│ doi │ Nullable(String) │
9│ report-no │ Nullable(String) │
10│ categories │ Nullable(String) │
11│ license │ Nullable(String) │
12│ abstract │ Nullable(String) │
13│ versions │ Array(Tuple(created Nullable(String),version Nullable(String))) │
14│ update_date │ Nullable(Date) │
15│ authors_parsed │ Array(Array(Nullable(String))) │
16└────────────────┴─────────────────────────────────────────────────────────────────────────┘避免空值
您可以看到很多列被检测为 Nullable。我们不建议在不是绝对需要时使用 Nullable 类型。您可以使用schema_inference_make_columns_nullable来控制应用 Nullable 时的行为。
可以看到，大多数列已自动检测为String，update_date列正确检测为Date。versions列已创建为用于Array(Tuple(created String, version String))存储对象列表，authors_parsed定义为Array(Array(String))用于嵌套数组。
查询 JSON
可以依靠模式推理来就地查询JSON数据。下面,我们找到了每年的顶级作者，利用了日期和数组被自动检测的事实。
Plain Text
1SELECT
2 toYear(update_date) AS year,
3 authors,
4 count() AS c
5FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
6GROUP BY
7 year,
8 authors
9ORDER BY
10 year ASC,
11 c DESC
12LIMIT 1 BY year
13
14┌─year─┬─authors────────────────────────────────────┬───c─┐
15│ 2007 │ The BABAR Collaboration, B. Aubert, et al │ 98 │
16│ 2008 │ The OPAL collaboration, G. Abbiendi, et al │ 59 │
17│ 2009 │ Ashoke Sen │ 77 │
18│ 2010 │ The BABAR Collaboration, B. Aubert, et al │ 117 │
19│ 2011 │ Amelia Carolina Sparavigna │ 21 │
20│ 2012 │ ZEUS Collaboration │ 140 │
21│ 2013 │ CMS Collaboration │ 125 │
22│ 2014 │ CMS Collaboration │ 87 │
23│ 2015 │ ATLAS Collaboration │ 118 │
24│ 2016 │ ATLAS Collaboration │ 126 │
25│ 2017 │ CMS Collaboration │ 122 │
26│ 2018 │ CMS Collaboration │ 138 │
27│ 2019 │ CMS Collaboration │ 113 │
28│ 2020 │ CMS Collaboration │ 94 │
29│ 2021 │ CMS Collaboration │ 69 │
30│ 2022 │ CMS Collaboration │ 62 │
31│ 2023 │ ATLAS Collaboration │ 128 │
32│ 2024 │ ATLAS Collaboration │ 120 │
33└──────┴────────────────────────────────────────────┴─────┘
34
3518 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 并创建表。这不会加载任何数据，以下示例使用的文件来创建表。
Plain Text
1CREATE TABLE arxiv
2ENGINE = MergeTree
3ORDER BY update_date EMPTY
4AS SELECT *
5FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
6SETTINGS schema_inference_make_columns_nullable = 02.为了确认表模式，我们使用以下SHOW CREATE TABLE命令：
Plain Text
1SHOW CREATE TABLE arxiv
2
3CREATE TABLE arxiv
4(
5 `id` String,
6 `submitter` String,
7 `authors` String,
8 `title` String,
9 `comments` String,
10 `journal-ref` String,
11 `doi` String,
12 `report-no` String,
13 `categories` String,
14 `license` String,
15 `abstract` String,
16 `versions` Array(Tuple(created String, version String)),
17 `update_date` Date,
18 `authors_parsed` Array(Array(String))
19)
20ENGINE = MergeTree
21ORDER BY update_date
22SETTINGS index_granularity = 8192以上是此数据的正确架构。架构推断基于对数据进行采样并逐行读取数据。根据格式提取列值，使用递归解析器和启发式方法确定每个值的类型。架构推断中从数据中读取的最大行数和字节数由设置input_format_max_rows_to_read_for_schema_inference(默认为 25000)和input_format_max_bytes_to_read_for_schema_inference(默认为 32MB) 控制。
使用函数创建表
使用函数创建表，如下所示：
Plain Text
1CREATE TABLE arxiv
2ENGINE = MergeTree
3ORDER BY update_date EMPTY
4AS SELECT *
5FROM 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
6
7SHOW CREATE TABLE arxiv
8
9CREATE TABLE arxiv
10(
11 `id` String,
12 `submitter` String,
13 `authors` String,
14 `title` String,
15 `comments` String,
16 `doi` String,
17 `report-no` String,
18 `categories` String,
19 `license` String,
20 `abstract` String,
21 `versions` Array(Tuple(created String, version String)),
22 `update_date` Date,
23 `authors_parsed` Array(Array(String))
24)
25ENGINE = MergeTree
26ORDER BY update_date加载JSON数据
- 前面的命令创建了一个可以加载数据的表。现在，您可以使用以下命令将数据插入表中
INSERT INTO SELECT：
Plain Text
1INSERT INTO arxiv SELECT *
2FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
3
40 rows in set. Elapsed: 38.498 sec. Processed 2.52 million rows, 1.39 GB (65.35 thousand rows/s., 36.03 MB/s.)
5Peak memory usage: 870.67 MiB.2.加载后，我们可以查询数据，可选择使用格式PrettyJSONEachRow以显示其原始结构中的行：
Plain Text
1SELECT *
2FROM arxiv
3LIMIT 1
4FORMAT PrettyJSONEachRow
5
6{
7 "id": "0704.0004",
8 "submitter": "David Callan",
9 "authors": "David Callan",
10 "title": "A determinant of Stirling cycle numbers counts unlabeled acyclic",
11 "comments": "11 pages",
12 "journal-ref": "",
13 "doi": "",
14 "report-no": "",
15 "categories": "math.CO",
16 "license": "",
17 "abstract": " We show that a determinant of Stirling cycle numbers counts unlabeled acyclic\nsingle-source automata.",
18 "versions": [
19 {
20 "created": "Sat, 31 Mar 2007 03:16:14 GMT",
21 "version": "v1"
22 }
23 ],
24 "update_date": "2007-05-23",
25 "authors_parsed": [
26 [
27 "Callan",
28 "David"
29 ]
30 ]
31}
32
331 row in set. Elapsed: 0.009 sec.设计 JSON 架构
虽然可以使用架构推断来为JSON数据建立初始架构并就地查询 JSON 数据文件（例如在 S3 中），但用户应该致力于为其数据建立优化的版本化架构。我们在下面讨论用于建模 JSON 结构的选项。
静态 JSON 与动态 JSON
定义 JSON 架构的主要任务是确定每个键值的适当类型。我们建议用户对 JSON 层次结构中的每个键递归应用以下规则，以确定每个键的适当类型。
- 原始类型-如果键的值是原始类型，无论它是子对象的一部分还是位于根上，请确保根据通用架构设计最佳实践和类型优化规则选择其类型。原始数组（例如，
phone_numbers如下所示）可以建模为Array(<type>)。Array(String)。 - 静态与动态- 如果键的值是一个复杂对象，即一个对象或一个对象数组，则确定它是否会发生变化。很少有新键的对象，可以通过模式更改来预测和处理新键的添加
和处理新键的添加 ALTER TABLE ADD COLUMN，可以被视为静态的。这包括在某些 JSON 文档中可能只提供部分键的对象。经常添加新键和/或不可预测的新键的对象应被视为动态的。
重要提示：上述规则应递归应用。如果确定某个键的值是动态的，则无需进一步评估，并可遵循处理动态对象中的指导原则。如果对象是静态的，则继续评估子键，直到遇到键值是原始键或动态键为止。
- 为了说明这些规则，使用以下代表一个 JSON 示例：
Plain Text
1{
2 "id": 1,
3 "name": "Clicky McCliickHouse",
4 "username": "Clicky",
5 "email": "clicky@clickhouse.com",
6 "address": [
7 {
8 "street": "Victor Plains",
9 "suite": "Suite 879",
10 "city": "Wisokyburgh",
11 "zipcode": "90566-7771",
12 "geo": {
13 "lat": -43.9509,
14 "lng": -34.4618
15 }
16 }
17 ],
18 "phone_numbers": ["010-692-6593", "020-192-3333"],
19 "website": "clickhouse.com",
20 "company": {
21 "name": "ClickHouse",
22 "catchPhrase": "The real-time data warehouse for analytics",
23 "labels": {
24 "type": "database systems",
25 "founded": "2021"
26 }
27 },
28 "dob": "2007-03-31",
29 "tags": {
30 "hobby": "Databases",
31 "holidays": [
32 {
33 "year": 2024,
34 "location": "Azores, Portugal"
35 }
36 ],
37 "car": {
38 "model": "Tesla",
39 "year": 2023
40 }
41 }
42}4.应用这些规则：
*根键name、username、email、website可表示为 类型String。列phone_numbers是 类型的数组原语Array(String)，分别具有dob和id类型Date和UInt32。
- 不会向address对象添加新键（只有新地址对象），因此可以将其视为静态的。如果我们递归，则除 之外的所有子列都可以被视为基元（和类型String）geo。这也是一个具有两列的静态结构Float32，lat和lon。
- 该tags列是动态的。我们假设可以向任何类型和结构的此对象添加新的任意标签。
- 该company对象是静态的，并且始终最多包含指定的 3 个键。子键name和catchPhrase的类型为String。键labels是动态的。我们假设可以向此对象添加新的任意标签。值始终是字符串类型的键值对。
处理静态对象
- 建议使用命名元组来处理静态对象
Tuple。可以使用元组数组Array(Tuple)来保存对象数组。在元组本身中，应使用相同的规则定义列及其各自的类型。这可以导致嵌套元组来表示嵌套对象，如下所示。
为了说明这一点，我们使用前面的 JSON 人员示例，省略动态对象：
Plain Text
1{
2 "id": 1,
3 "name": "Clicky McCliickHouse",
4 "username": "Clicky",
5 "email": "clicky@clickhouse.com",
6 "address": [
7 {
8 "street": "Victor Plains",
9 "suite": "Suite 879",
10 "city": "Wisokyburgh",
11 "zipcode": "90566-7771",
12 "geo": {
13 "lat": -43.9509,
14 "lng": -34.4618
15 }
16 }
17 ],
18 "phone_numbers": ["010-692-6593", "020-192-3333"],
19 "website": "clickhouse.com",
20 "company": {
21 "name": "ClickHouse",
22 "catchPhrase": "The real-time data warehouse for analytics"
23 },
24 "dob": "2007-03-31"
25}- 该表的架构如下所示：
Plain Text
1CREATE TABLE people
2(
3 `id` Int64,
4 `name` String,
5 `username` String,
6 `email` String,
7 `address` Array(Tuple(city String, geo Tuple(lat Float32, lng Float32), street String, suite String, zipcode String)),
8 `phone_numbers` Array(String),
9 `website` String,
10 `company` Tuple(catchPhrase String, name String),
11 `dob` Date
12)
13ENGINE = MergeTree
14ORDER BY username注意company列是如何定义为的Tuple(catchPhrase String, name String)。该address字段使用Array(Tuple)带有嵌套的Tuple来表示geo列。
3.可以将JSON按照其当前结构插入到此表中：
Plain Text
1INSERT INTO people FORMAT JSONEachRow
2{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}4.在上面的例子中，有最少的数据。但如下所示，可以通过句点分隔的名称来查询元组字段：
Plain Text
1SELECT
2 address.street,
3 company.name
4FROM people
5
6┌─address.street────┬─company.name─┐
7│ ['Victor Plains'] │ ClickHouse │
8└───────────────────┴──────────────┘5.请注意address.street列是如何作为 来返回的Array。要按位置查询数组内的特定对象，应在列名后指定数组偏移量。例如，要从第一个地址访问street：
Plain Text
1SELECT address.street[1] AS street
2FROM people
3
4┌─street────────┐
5│ Victor Plains │
6└───────────────┘
7
81 row in set. Elapsed: 0.001 sec.元组的主要缺点是子列不能用于排序键。因此，以下操作将失败：
Plain Text
1CREATE TABLE people
2(
3 `id` Int64,
4 `name` String,
5 `username` String,
6 `email` String,
7 `address` Array(Tuple(city String, geo Tuple(lat Float32, lng Float32), street String, suite String, zipcode String)),
8 `phone_numbers` Array(String),
9 `website` String,
10 `company` Tuple(catchPhrase String, name String),
11 `dob` Date
12)
13ENGINE = MergeTree
14ORDER BY company.name
15
16Code: 47. DB::Exception: Missing columns: 'company.name' while processing query: 'company.name', required columns: 'company.name' 'company.name'. (UNKNOWN_IDENTIFIER)排序键中的元组
虽然元组列不能用于排序键，但可以使用整个元组。虽然可能，但这很少有意义。
处理默认值
即使JSON对象是结构化的，它们通常也很稀疏，只提供了已知键的子集。幸运的是，该Tuple类型不需要 JSON 有效负载中的所有列。如果未提供，则将使用默认值。
- 考虑之前的表和下面的
people稀疏 JSON，缺少键suite、geo和phone_numberscatchPhrase:
Plain Text
1{
2 "id": 1,
3 "name": "Clicky McCliickHouse",
4 "username": "Clicky",
5 "email": "clicky@clickhouse.com",
6 "address": [
7 {
8 "street": "Victor Plains",
9 "city": "Wisokyburgh",
10 "zipcode": "90566-7771"
11 }
12 ],
13 "website": "clickhouse.com",
14 "company": {
15 "name": "ClickHouse"
16 },
17 "dob": "2007-03-31"
18}2.可以从下面看到这一行可以成功插入：
Plain Text
1INSERT INTO people FORMAT JSONEachRow
2{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","city":"Wisokyburgh","zipcode":"90566-7771"}],"website":"clickhouse.com","company":{"name":"ClickHouse"},"dob":"2007-03-31"}
3
4Ok.
5
61 row in set. Elapsed: 0.002 sec.3.查询此单行，可以看到省略的列（包括子对象）使用了默认值：
Plain Text
1SELECT *
2FROM people
3FORMAT PrettyJSONEachRow
4
5{
6 "id": "1",
7 "name": "Clicky McCliickHouse",
8 "username": "Clicky",
9 "email": "clicky@clickhouse.com",
10 "address": [
11 {
12 "city": "Wisokyburgh",
13 "geo": {
14 "lat": 0,
15 "lng": 0
16 },
17 "street": "Victor Plains",
18 "suite": "",
19 "zipcode": "90566-7771"
20 }
21 ],
22 "phone_numbers": [],
23 "website": "clickhouse.com",
24 "company": {
25 "catchPhrase": "",
26 "name": "ClickHouse"
27 },
28 "dob": "2007-03-31"
29}
30
311 row in set. Elapsed: 0.001 sec.区分empty和null
如果用户需要区分值为空和未提供，则可以使用Nullable类型。除非绝对必要，否则应避免这样做，因为这会对此类列的存储和查询性能产生负面影响。
处理新列
虽然当JSON键是静态的时候，结构化方法是最简单的，但如果可以规划对模式的更改，即提前知道新键，并且可以相应地修改模式，则仍然可以使用此方法。
请注意，ClickHouse 默认会忽略有效负载中提供但架构中不存在的 JSON 键。考虑以下修改后的 JSON 有效负载并添加了一个nickname键：
Plain Text
1{
2 "id": 1,
3 "name": "Clicky McCliickHouse",
4 "nickname": "Clicky",
5 "username": "Clicky",
6 "email": "clicky@clickhouse.com",
7 "address": [
8 {
9 "street": "Victor Plains",
10 "suite": "Suite 879",
11 "city": "Wisokyburgh",
12 "zipcode": "90566-7771",
13 "geo": {
14 "lat": -43.9509,
15 "lng": -34.4618
16 }
17 }
18 ],
19 "phone_numbers": ["010-692-6593", "020-192-3333"],
20 "website": "clickhouse.com",
21 "company": {
22 "name": "ClickHouse",
23 "catchPhrase": "The real-time data warehouse for analytics"
24 },
25 "dob": "2007-03-31"
26}忽略键即可成功插入此JSONnickname：
Plain Text
1INSERT INTO people FORMAT JSONEachRow
2{"id":1,"name":"Clicky McCliickHouse","nickname":"Clicky","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}
3
4Ok.
5
61 row in set. Elapsed: 0.002 sec.可以使用命令将列添加到架构中ALTER TABLE ADD COLUMN。可以通过子句指定默认值DEFAULT，如果在后续插入期间未指定默认值，则将使用该默认值。不存在此值的行（因为它们是在创建之前插入的）也将返回此默认值。如果没有DEFAULT指定值，则将使用该类型的默认值。
例如：
Plain Text
1-- insert initial row (nickname will be ignored)
2INSERT INTO people FORMAT JSONEachRow
3{"id":1,"name":"Clicky McCliickHouse","nickname":"Clicky","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}
4
5-- add column
6ALTER TABLE people
7 (ADD COLUMN `nickname` String DEFAULT 'no_nickname')
8
9-- insert new row (same data different id)
10INSERT INTO people FORMAT JSONEachRow
11{"id":2,"name":"Clicky McCliickHouse","nickname":"Clicky","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}
12
13-- select 2 rows
14SELECT id, nickname FROM people
15
16┌─id─┬─nickname────┐
17│ 2 │ Clicky │
18│ 1 │ no_nickname │
19└────┴─────────────┘
20
212 rows in set. Elapsed: 0.001 sec.处理动态对象
- 有两种推荐的方法来处理动态对象：
- Map(String,V)类型
- 带有 JSON 函数的字符串
- 可以应用以下规则来确定最合适的。
- 如果对象高度动态，没有可预测的结构并且包含任意嵌套对象，则用户应使用该
String类型。可以使用 JSON 函数在查询时提取值，如下所示。 - 如果对象用于存储任意键，且大多为一种类型，请考虑使用该
Map类型。理想情况下，唯一键的数量不应超过几百个。Map对于具有子对象的对象，也可以考虑使用该类型，前提是后者的类型一致。通常，我们建议Map将该类型用于标签和标记，例如日志数据中的 Kubernetes pod 标签。
应用对象级方法
不同的技术可以应用于同一架构中的不同对象。有些对象可以用String和其他来最好地解决Map。请注意，一旦String使用类型，就不需要做出进一步的架构决策。相反，可以将子对象嵌套在键中Map，如下所示 - 包括String表示JSON。
使用字符串
对于使用动态 JSON 的用户来说，使用上述结构化方法处理数据通常不可行，因为动态 JSON 可能会发生变化，或者其架构不太为人所理解。为了获得绝对的灵活性，用户可以简单地将 JSON 存储为Strings，然后根据需要使用函数提取字段。这与将 JSON 作为结构化对象处理完全相反。这种灵活性会产生成本，并带来重大缺点 - 主要是查询语法复杂性增加以及性能下降。
如前所述，对于原始person对象，我们无法确保列的结构tags。我们插入原始行（还包括company.labels，暂时忽略），将Tags列声明为String：
Plain Text
1CREATE TABLE people
2(
3 `id` Int64,
4 `name` String,
5 `username` String,
6 `email` String,
7 `address` Array(Tuple(city String, geo Tuple(lat Float32, lng Float32), street String, suite String, zipcode String)),
8 `phone_numbers` Array(String),
9 `website` String,
10 `company` Tuple(catchPhrase String, name String),
11 `dob` Date,
12 `tags` String
13)
14ENGINE = MergeTree
15ORDER BY username
16
17INSERT INTO people FORMAT JSONEachRow
18{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics","labels":{"type":"database systems","founded":"2021"}},"dob":"2007-03-31","tags":{"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}}}
19
20Ok.
211 row in set. Elapsed: 0.002 sec.可以选择该tags列并看到JSON已作为字符串插入：
Plain Text
1SELECT tags
2FROM people
3
4┌─tags───────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
5│ {"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}} │
6└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
7
81 row in set. Elapsed: 0.001 sec.JSONExtract函数可用于从此 JSON 中检索值。请考虑以下简单示例：
Plain Text
1SELECT JSONExtractString(tags, 'holidays') as holidays FROM people
2
3┌─holidays──────────────────────────────────────┐
4│ [{"year":2024,"location":"Azores, Portugal"}] │
5└───────────────────────────────────────────────┘
6
71 row in set. Elapsed: 0.002 sec.请注意，函数既需要对String列的引用tags，也需要提取 JSON 中的路径。嵌套路径需要嵌套函数，例如JSONExtractUInt(JSONExtractString(tags, 'car'), 'year')提取列。可以通过函数JSON_QUERY和JSON_VALUEtags.car.year简化嵌套路径的提取。
考虑数据集中的极端情况arxiv，我们将整个body视为一个String。
Plain Text
1CREATE TABLE arxiv (
2 body String
3)
4ENGINE = MergeTree ORDER BY ()要插入此模式，我们需要使用以下JSONAsString格式：
Plain Text
1INSERT INTO arxiv SELECT *
2FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz', 'JSONAsString')
3
40 rows in set. Elapsed: 25.186 sec. Processed 2.52 million rows, 1.38 GB (99.89 thousand rows/s., 54.79 MB/s.)假设我们希望统计按年份发布的论文数量。将查询与结构化版本的架构进行对比，并与仅使用字符串的情况进行比较：
Plain Text
1-- using structured schema
2SELECT
3 toYear(parseDateTimeBestEffort(versions.created[1])) AS published_year,
4 count() AS c
5FROM arxiv_v2
6GROUP BY published_year
7ORDER BY c ASC
8LIMIT 10
9
10┌─published_year─┬─────c─┐
11│ 1986 │ 1 │
12│ 1988 │ 1 │
13│ 1989 │ 6 │
14│ 1990 │ 26 │
15│ 1991 │ 353 │
16│ 1992 │ 3190 │
17│ 1993 │ 6729 │
18│ 1994 │ 10078 │
19│ 1995 │ 13006 │
20│ 1996 │ 15872 │
21└────────────────┴───────┘
22
2310 rows in set. Elapsed: 0.264 sec. Processed 2.31 million rows, 153.57 MB (8.75 million rows/s., 582.58 MB/s.)
24
25-- using unstructured String
26
27SELECT
28 toYear(parseDateTimeBestEffort(JSON_VALUE(body, '$.versions[0].created'))) AS published_year,
29 count() AS c
30FROM arxiv
31GROUP BY published_year
32ORDER BY published_year ASC
33LIMIT 10
34
35┌─published_year─┬─────c─┐
36│ 1986 │ 1 │
37│ 1988 │ 1 │
38│ 1989 │ 6 │
39│ 1990 │ 26 │
40│ 1991 │ 353 │
41│ 1992 │ 3190 │
42│ 1993 │ 6729 │
43│ 1994 │ 10078 │
44│ 1995 │ 13006 │
45│ 1996 │ 15872 │
46└────────────────┴───────┘
47
4810 rows in set. Elapsed: 1.281 sec. Processed 2.49 million rows, 4.22 GB (1.94 million rows/s., 3.29 GB/s.)
49Peak memory usage: 205.98 MiB.注意这里使用 xpath 表达式来按方法过滤 JSON JSON_VALUE(body, '$.versions[0].created')。
字符串函数比使用索引的显式类型转换慢得多（> 10 倍）。上述查询始终需要全表扫描并处理每一行。虽然这些查询在像这样的小型数据集上仍然很快，但在较大的数据集上性能会下降。
这种方法的灵活性显然是以性能和语法为代价的，因此它只应该用于模式中高度动态的对象。
简单的JSON的函数
上述示例使用了 JSON* 系列函数。这些函数利用了基于simdjson 的完整 JSON 解析器，该解析器解析严谨，并能区分嵌套在不同级别的相同字段。这些函数能够处理语法正确但格式不正确的 JSON，例如键之间的双空格。
有一组更快、更严格的函数可用。这些simpleJSON*函数主要通过对 JSON 的结构和格式做出严格的假设来提供潜在的卓越性能。具体来说：
- 字段名称必须是常量
- 字段名称的一致编码，例如
simpleJSONHas('{"abc":"def"}', 'abc') = 1，但是visitParamHas('{"\\u0061\\u0062\\u0063":"def"}', 'abc') = 0 - 字段名称在所有嵌套结构中都是唯一的。嵌套级别之间没有区别，匹配也是无差别的。如果有多个匹配字段，则使用第一个匹配的字段。
- 字符串文字之外不得有特殊字符。这包括空格。以下内容无效，无法解析。
Plain Text
1{"@timestamp": 893964617, "clientip": "40.135.0.0", "request": {"method": "GET",
2"path": "/images/hm_bg.jpg", "version": "HTTP/1.0"}, "status": 200, "size": 24736}而以下内容将正确解析：
Plain Text
1{"@timestamp":893964617,"clientip":"40.135.0.0","request":{"method":"GET",
2"path":"/images/hm_bg.jpg","version":"HTTP/1.0"},"status":200,"size":24736}simpleJSON*在某些情况下，如果性能至关重要并且您的 JSON 满足上述要求，这些可能是合适的。下面显示了使用函数重写的早期查询的示例：
Plain Text
1SELECT
2 toYear(parseDateTimeBestEffort(simpleJSONExtractString(simpleJSONExtractRaw(body, 'versions'), 'created'))) AS published_year,
3 count() AS c
4FROM arxiv
5GROUP BY published_year
6ORDER BY published_year ASC
7LIMIT 10
8
9┌─published_year─┬─────c─┐
10│ 1986 │ 1 │
11│ 1988 │ 1 │
12│ 1989 │ 6 │
13│ 1990 │ 26 │
14│ 1991 │ 353 │
15│ 1992 │ 3190 │
16│ 1993 │ 6729 │
17│ 1994 │ 10078 │
18│ 1995 │ 13006 │
19│ 1996 │ 15872 │
20└────────────────┴───────┘
21
2210 rows in set. Elapsed: 0.964 sec. Processed 2.48 million rows, 4.21 GB (2.58 million rows/s., 4.36 GB/s.)
23Peak memory usage: 211.49 MiB.上述代码使用simpleJSONExtractString来提取created密钥，利用了我们只需要发布日期的第一个值这一事实。在这种情况下，函数的限制simpleJSON*对于性能的提升是可以接受的。
使用 Map
如果对象用于存储主要为一种类型的任意键，请考虑使用该Map类型。理想情况下，唯一键的数量不应超过几百个。我们建议Map将该类型用于标签和标记，例如日志数据中的 Kubernetes pod 标签。虽然这是一种表示嵌套结构的简单方法，Map但它有一些明显的局限性：
- 所有字段必须属于同一类型。
- 访问子列需要特殊的映射语法，因为字段不作为列存在；整个对象是一列。
- 访问子列会加载整个
Map值，即所有同级值及其各自的值。对于较大的地图，这可能会导致严重的性能损失。
字符串键
当将对象建模为Map时，String使用 key 来存储JSON 键名称。因此映射将始终为Map(String, T)，其中T取决于数据。
原始值
最简单的应用Map是当对象包含与值相同的原始类型时。在大多数情况下，这涉及使用String值的类型T。
- 考虑我们之前的Person JSON，其中
company.labels对象被确定为动态的。重要的是，我们只希望将String类型的键值对添加到此对象。因此，我们可以将其声明为Map(String, String)：
Plain Text
1CREATE TABLE people
2(
3 `id` Int64,
4 `name` String,
5 `username` String,
6 `email` String,
7 `address` Array(Tuple(city String, geo Tuple(lat Float32, lng Float32), street String, suite String, zipcode String)),
8 `phone_numbers` Array(String),
9 `website` String,
10 `company` Tuple(catchPhrase String, name String, labels Map(String,String)),
11 `dob` Date,
12 `tags` String
13)
14ENGINE = MergeTree
15ORDER BY username- 可以插入原始的完整 JSON 对象：
Plain Text
1INSERT INTO people FORMAT JSONEachRow
2{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics","labels":{"type":"database systems","founded":"2021"}},"dob":"2007-03-31","tags":{"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}}}
3
4Ok.
5
61 row in set. Elapsed: 0.002 sec.- 在请求对象中查询这些字段需要使用映射语法，例如：
`SELECT company.labels FROM people
┌─company.labels───────────────────────────────┐ │ {'type':'database systems','founded':'2021'} │ └──────────────────────────────────────────────┘
1 row in set. Elapsed: 0.001 sec.
SELECT company.labels['type'] AS type FROM people
┌─type─────────────┐ │ database systems │ └──────────────────┘
1 row in set. Elapsed: 0.001 sec.`
对象值
该Map类型也可以考虑用于具有子对象的对象，前提是后者的类型具有一致性。
- 假设
tags我们persons对象的键需要一致的结构，其中每个子对象tag都有一个name和time列。此类JSON文档的简化示例可能如下所示：
Plain Text
1{
2 "id": 1,
3 "name": "Clicky McCliickHouse",
4 "username": "Clicky",
5 "email": "clicky@clickhouse.com",
6 "tags": {
7 "hobby": {
8 "name": "Diving",
9 "time": "2024-07-11 14:18:01"
10 },
11 "car": {
12 "name": "Tesla",
13 "time": "2024-07-11 15:18:23"
14 }
15 }
16}- Map(String, Tuple(name String, time DateTime))可以用如下所示的模型来建模：
Plain Text
1CREATE TABLE people
2(
3 `id` Int64,
4 `name` String,
5 `username` String,
6 `email` String,
7 `tags` Map(String, Tuple(name String, time DateTime))
8)
9ENGINE = MergeTree
10ORDER BY username
11
12INSERT INTO people FORMAT JSONEachRow
13{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"clicky@clickhouse.com","tags":{"hobby":{"name":"Diving","time":"2024-07-11 14:18:01"},"car":{"name":"Tesla","time":"2024-07-11 15:18:23"}}}
14
15Ok.
16
171 row in set. Elapsed: 0.002 sec.
18
19SELECT tags['hobby'] AS hobby
20FROM people
21FORMAT JSONEachRow
22
23{"hobby":{"name":"Diving","time":"2024-07-11 14:18:01"}}
24
251 row in set. Elapsed: 0.001 sec.- 在这种情况下，映射的应用通常很少见，并且建议对数据进行重构，以使动态键名不包含子对象。例如，可以将上述内容重构如下，以允许使用
Array(Tuple(key String, name String, time DateTime)。
Plain Text
1{
2 "id": 1,
3 "name": "Clicky McCliickHouse",
4 "username": "Clicky",
5 "email": "clicky@clickhouse.com",
6 "tags": [
7 {
8 "key": "hobby",
9 "name": "Diving",
10 "time": "2024-07-11 14:18:01"
11 },
12 {
13 "key": "car",
14 "name": "Tesla",
15 "time": "2024-07-11 15:18:23"
16 }
17 ]
18}导出 JSON
- 几乎任何用于导入的 JSON 格式都可以用于导出。最流行的是
JSONEachRow：
Plain Text
1SELECT * FROM sometable FORMAT JSONEachRow
Plain Text
1{"path":"Bob_Dolman","month":"2016-11-01","hits":245}
2{"path":"1-krona","month":"2017-01-01","hits":4}
3{"path":"Ahmadabad-e_Kalij-e_Sofla","month":"2017-01-01","hits":3}- 或者可以
JSONCompactEachRow通过跳过列名来节省磁盘空间：
Plain Text
1SELECT * FROM sometable FORMAT JSONCompactEachRow
Plain Text
1["Bob_Dolman", "2016-11-01", 245]
2["1-krona", "2017-01-01", 4]
3["Ahmadabad-e_Kalij-e_Sofla", "2017-01-01", 3]将数据类型覆盖为字符串
ClickHouse 尊重数据类型并将根据标准导出 JSON。但如果我们需要将所有值编码为字符串，则可以使用JSONStringsEachRow格式：
Plain Text
1SELECT * FROM sometable FORMAT JSONStringsEachRow
Plain Text
1{"path":"Bob_Dolman","month":"2016-11-01","hits":"245"}
2{"path":"1-krona","month":"2017-01-01","hits":"4"}
3{"path":"Ahmadabad-e_Kalij-e_Sofla","month":"2017-01-01","hits":"3"}现在，hits数字列被编码为字符串。所有 JSON* 格式都支持导出为字符串，只需探索JSONStrings\*并JSONCompactStrings\*格式化：
Plain Text
1SELECT * FROM sometable FORMAT JSONCompactStringsEachRow
Plain Text
1["Bob_Dolman", "2016-11-01", "245"]
2["1-krona", "2017-01-01", "4"]
3["Ahmadabad-e_Kalij-e_Sofla", "2017-01-01", "3"]导出元数据和数据
- 应用程序中流行的通用JSON格式不仅会导出结果数据，还会导出列类型和查询统计信息：
Plain Text
1SELECT * FROM sometable FORMAT JSON
Plain Text
1{
2 "meta":
3 [
4 {
5 "name": "path",
6 "type": "String"
7 },
8 …
9 ],
10
11 "data":
12 [
13 {
14 "path": "Bob_Dolman",
15 "month": "2016-11-01",
16 "hits": 245
17 },
18 …
19 ],
20
21 "rows": 3,
22
23 "statistics":
24 {
25 "elapsed": 0.000497457,
26 "rows_read": 3,
27 "bytes_read": 87
28 }
29}- JSONCompact格式将打印相同的元数据，但对数据本身使用压缩形式：
Plain Text
1SELECT * FROM sometable FORMAT JSONCompact
Plain Text
1{
2 "meta":
3 [
4 {
5 "name": "path",
6 "type": "String"
7 },
8 …
9 ],
10
11 "data":
12 [
13 ["Bob_Dolman", "2016-11-01", 245],
14 ["1-krona", "2017-01-01", 4],
15 ["Ahmadabad-e_Kalij-e_Sofla", "2017-01-01", 3]
16 ],
17
18 "rows": 3,
19
20 "statistics":
21 {
22 "elapsed": 0.00074981,
23 "rows_read": 3,
24 "bytes_read": 87
25 }
26}考虑JSONStrings或JSONCompactStrings变体将所有值编码为字符串。
导出JSON数据和结构的压缩方法
- 获取数据及其结构的更有效的方法是使用
JSONCompactEachRowWithNamesAndTypes格式：
Plain Text
1SELECT * FROM sometable FORMAT JSONCompactEachRowWithNamesAndTypes
Plain Text
1["path", "month", "hits"]
2["String", "Date", "UInt32"]
3["Bob_Dolman", "2016-11-01", 245]
4["1-krona", "2017-01-01", 4]
5["Ahmadabad-e_Kalij-e_Sofla", "2017-01-01", 3]- 这将使用紧凑的 JSON 格式，前面有两个标题行，其中包含列名和类型。然后可以使用此格式将数据导入另一个 ClickHouse 实例（或其他应用程序）。
将JSON导出到文件
- 要将导出的 JSON 数据保存到文件，可以使用INTO OUTFILE子句：
Plain Text
1SELECT * FROM sometable INTO OUTFILE 'out.json' FORMAT JSONEachRow
Plain Text
136838935 rows in set. Elapsed: 2.220 sec. Processed 36.84 million rows, 1.27 GB (16.60 million rows/s., 572.47 MB/s.)- ClickHouse 仅用了 2 秒就将近 3700 万条记录导出到 JSON 文件。我们还可以使用
COMPRESSION子句导出以动态启用压缩：
Plain Text
1SELECT * FROM sometable INTO OUTFILE 'out.json.gz' FORMAT JSONEachRow
Plain Text
136838935 rows in set. Elapsed: 22.680 sec. Processed 36.84 million rows, 1.27 GB (1.62 million rows/s., 56.02 MB/s.)- 需要更多时间来完成，但会生成更小的压缩文件：
Plain Text
12.2G out.json
2576M out.json.gz处理其他JSON格式
前面加载JSON数据的示例假设使用JSONEachRow(ndjson)。我们在下面提供了加载其他常见格式的JSON的示例。
JSON 对象数组
- JSON 数据最流行的形式之一是在 JSON 数组中拥有 JSON 对象列表，如下例所示：
Plain Text
1> cat list.json
2[
3 {
4 "path": "Akiba_Hebrew_Academy",
5 "month": "2017-08-01",
6 "hits": 241
7 },
8 {
9 "path": "Aegithina_tiphia",
10 "month": "2018-02-01",
11 "hits": 34
12 },
13 ...
14]2.为这种数据创建一个表：
Plain Text
1CREATE TABLE sometable
2(
3 `path` String,
4 `month` Date,
5 `hits` UInt32
6)
7ENGINE = MergeTree
8ORDER BY tuple(month, path)- 要导入 JSON 对象列表，我们可以使用一种格式（从list.
jsonJSONEachRow文件插入数据）：
Plain Text
1INSERT INTO sometable
2FROM INFILE 'list.json'
3FORMAT JSONEachRow- 使用FROM INFILE子句从本地文件加载数据，并且可以看到导入成功：
Plain Text
1SELECT *
2FROM sometable
Plain Text
1┌─path──────────────────────┬──────month─┬─hits─┐
2│ 1971-72_Utah_Stars_season │ 2016-10-01 │ 1 │
3│ Akiba_Hebrew_Academy │ 2017-08-01 │ 241 │
4│ Aegithina_tiphia │ 2018-02-01 │ 34 │
5└───────────────────────────┴────────────┴──────┘处理NDJSON（行分隔的JSON）
- 许多应用程序可以以 JSON 格式记录数据，以便每个日志行都是一个单独的 JSON 对象，就像在此文件中一样：
Plain Text
1cat object-per-line.json
Plain Text
1{"path":"1-krona","month":"2017-01-01","hits":4}
2{"path":"Ahmadabad-e_Kalij-e_Sofla","month":"2017-01-01","hits":3}
3{"path":"Bob_Dolman","month":"2016-11-01","hits":245}- 相同的
JSONEachRow格式可以处理这样的文件：
Plain Text
1INSERT INTO sometable FROM INFILE 'object-per-line.json' FORMAT JSONEachRow;
2SELECT * FROM sometable;
Plain Text
1┌─path──────────────────────┬──────month─┬─hits─┐
2│ Bob_Dolman │ 2016-11-01 │ 245 │
3│ 1-krona │ 2017-01-01 │ 4 │
4│ Ahmadabad-e_Kalij-e_Sofla │ 2017-01-01 │ 3 │
5└───────────────────────────┴────────────┴──────┘JSON对象键
- 在某些情况下，JSON对象列表可以编码为对象属性而不是数组元素：
Plain Text
1cat objects.json
Plain Text
1{
2 "a": {
3 "path":"April_25,_2017",
4 "month":"2018-01-01",
5 "hits":2
6 },
7 "b": {
8 "path":"Akahori_Station",
9 "month":"2016-06-01",
10 "hits":11
11 },
12 ...
13}- ClickHouse 可以使用以下格式从此类数据中加载数据
JSONObjectEachRow：
Plain Text
1INSERT INTO sometable FROM INFILE 'objects.json' FORMAT JSONObjectEachRow;
2SELECT * FROM sometable;
Plain Text
1┌─path────────────┬──────month─┬─hits─┐
2│ Abducens_palsy │ 2016-05-01 │ 28 │
3│ Akahori_Station │ 2016-06-01 │ 11 │
4│ April_25,_2017 │ 2018-01-01 │ 2 │
5└─────────────────┴────────────┴──────┘指定父对象键值
- 假设还想将父对象键中的值保存到表中。 在这种情况下，可以使用以下选项来定义要保存键值的列的名称：
Plain Text
1SET format_json_object_each_row_column_for_object_name = 'id'- 现在，可以使用函数检查要从原始JSON文件中加载哪些数据
file()：
Plain Text
1SELECT * FROM file('objects.json', JSONObjectEachRow)
Plain Text
1┌─id─┬─path────────────┬──────month─┬─hits─┐
2│ a │ April_25,_2017 │ 2018-01-01 │ 2 │
3│ b │ Akahori_Station │ 2016-06-01 │ 11 │
4│ c │ Abducens_palsy │ 2016-05-01 │ 28 │
5└────┴─────────────────┴────────────┴──────┘请注意该id列是如何正确地填充键值的。
JSON数组
- 有时，为了节省空间，JSON文件会以数组而不是对象的形式进行编码。在这种情况下，我们处理JSON 数组列表：
Plain Text
1cat arrays.json
Plain Text
1["Akiba_Hebrew_Academy", "2017-08-01", 241],
2["Aegithina_tiphia", "2018-02-01", 34],
3["1971-72_Utah_Stars_season", "2016-10-01", 1]- 在这种情况下，ClickHouse 将加载此数据并根据数组中的顺序将每个值归属于相应的列。我们
JSONCompactEachRow为此使用格式：
Plain Text
1SELECT * FROM sometable
Plain Text
1┌─c1────────────────────────┬─────────c2─┬──c3─┐
2│ Akiba_Hebrew_Academy │ 2017-08-01 │ 241 │
3│ Aegithina_tiphia │ 2018-02-01 │ 34 │
4│ 1971-72_Utah_Stars_season │ 2016-10-01 │ 1 │
5└───────────────────────────┴────────────┴─────┘从JSON数组导入单个列
- 在某些情况下，数据可以按列而不是按行进行编码。在这种情况下，父 JSON 对象包含具有值的列。查看以下文件：
Plain Text
1cat columns.json
Plain Text
1{
2 "path": ["2007_Copa_America", "Car_dealerships_in_the_USA", "Dihydromyricetin_reductase"],
3 "month": ["2016-07-01", "2015-07-01", "2015-07-01"],
4 "hits": [178, 11, 1]
5}- ClickHouse使用
JSONColumns以下格式来解析数据：
Plain Text
1SELECT * FROM file('columns.json', JSONColumns)
Plain Text
1┌─path───────────────────────┬──────month─┬─hits─┐
2│ 2007_Copa_America │ 2016-07-01 │ 178 │
3│ Car_dealerships_in_the_USA │ 2015-07-01 │ 11 │
4│ Dihydromyricetin_reductase │ 2015-07-01 │ 1 │
5└────────────────────────────┴────────────┴──────┘- 当处理列数组而不是使用格式的对象时，也支持更紧凑的格式
JSONCompactColumns：
Plain Text
1SELECT * FROM file('columns-array.json', JSONCompactColumns)
Plain Text
1┌─c1──────────────┬─────────c2─┬─c3─┐
2│ Heidenrod │ 2017-01-01 │ 10 │
3│ Arthur_Henrique │ 2016-11-01 │ 12 │
4│ Alan_Ebnother │ 2015-11-01 │ 66 │
5└─────────────────┴────────────┴────┘保存JSON对象
- 在某些情况下，您可能希望将JSON对象保存到单个String（或 JSON）列中，而不是对其进行解析。这在处理不同结构的JSON对象列表时很有用。让我们以这个文件为例，其中父列表中有多个不同JSON对象：
Plain Text
1cat custom.json
Plain Text
1[
2 {"name": "Joe", "age": 99, "type": "person"},
3 {"url": "/my.post.MD", "hits": 1263, "type": "post"},
4 {"message": "Warning on disk usage", "type": "log"}
5]2.我们希望将原始 JSON 对象保存到下表中：
Plain Text
1CREATE TABLE events
2(
3 `data` String
4)
5ENGINE = MergeTree
6ORDER BY ()- 现在可以使用格式将文件中的数据加载到该表中
JSONAsString以保留JSON对象而不是解析它们：
Plain Text
1INSERT INTO events (data)
2FROM INFILE 'custom.json'
3FORMAT JSONAsString- 可以使用JSON函数来查询已保存的对象：
Plain Text
1SELECT
2 JSONExtractString(data, 'type') AS type,
3 data
4FROM events
Plain Text
1┌─type───┬─data─────────────────────────────────────────────────┐
2│ person │ {"name": "Joe", "age": 99, "type": "person"} │
3│ post │ {"url": "/my.post.MD", "hits": 1263, "type": "post"} │
4│ log │ {"message": "Warning on disk usage", "type": "log"} │
5└────────┴──────────────────────────────────────────────────────┘
JSONAsString请注意，如果我们有JSON对象每行格式的文件（通常与JSONEachRow格式一起使用），那么它可以完美地工作。
嵌套对象的架构
在处理嵌套 JSON 对象的情况下，我们可以另外定义模式并使用复杂类型（Array、Object Data Type或Tuple）来加载数据：
Plain Text
1SELECT *
2FROM file('list-nested.json', JSONEachRow, 'page Tuple(path String, title String, owner_id UInt16), month Date, hits UInt32')
3LIMIT 1
Plain Text
1┌─page───────────────────────────────────────────────┬──────month─┬─hits─┐
2│ ('Akiba_Hebrew_Academy','Akiba Hebrew Academy',12) │ 2017-08-01 │ 241 │
3└────────────────────────────────────────────────────┴────────────┴──────┘访问嵌套的JSON对象
- 可以通过启用以下设置选项来引用嵌套的JSON键：
Plain Text
1SET input_format_import_nested_json = 1- 这能够使用点符号引用嵌套的JSON对象键（记住用反引号符号包装它们才能起作用）：
Plain Text
1SELECT *
2FROM file('list-nested.json', JSONEachRow, '`page.owner_id` UInt32, `page.title` String, month Date, hits UInt32')
3LIMIT 1
Plain Text
1┌─page.owner_id─┬─page.title───────────┬──────month─┬─hits─┐
2│ 12 │ Akiba Hebrew Academy │ 2017-08-01 │ 241 │
3└───────────────┴──────────────────────┴────────────┴──────┘这样，可以展平嵌套的JSON对象或使用一些嵌套的值将它们保存为单独的列。
跳过未知列
- 默认情况下，ClickHouse在导入JSON数据时会忽略未知列。我们来尝试将原始文件导入到没有该列的表中
month：
Plain Text
1CREATE TABLE shorttable
2(
3 `path` String,
4 `hits` UInt32
5)
6ENGINE = MergeTree
7ORDER BY path- 这样仍然可以将原始的包含3列的JSON数据插入到该表中：
Plain Text
1INSERT INTO shorttable FROM INFILE 'list.json' FORMAT JSONEachRow;
2SELECT * FROM shorttable
Plain Text
1┌─path──────────────────────┬─hits─┐
2│ 1971-72_Utah_Stars_season │ 1 │
3│ Aegithina_tiphia │ 34 │
4│ Akiba_Hebrew_Academy │ 241 │
5└───────────────────────────┴──────┘- ClickHouse将在导入时忽略未知列。可以使用input_format_skip_unknown_fields设置选项禁用此功能：
Plain Text
1SET input_format_skip_unknown_fields = 0;
2INSERT INTO shorttable FROM INFILE 'list.json' FORMAT JSONEachRow;
Plain Text
1Ok.
2Exception on client:
3Code: 117. DB::Exception: Unknown field found while parsing JSONEachRow format: month: (in file/uri /data/clickhouse/user_files/list.json): (at row 1)- 如果 JSON 和表列结构不一致，ClickHouse将会抛出异常。
BSON
ClickHouse 允许导出和导入BSON编码文件的数据。一些DBMS使用此格式，例如MongoDB数据库。
- 要导入BSON数据，使用BSONEachRow格式。从这个BSON文件导入数据：
Plain Text
1SELECT * FROM file('data.bson', BSONEachRow)
Plain Text
1┌─path──────────────────────┬─month─┬─hits─┐
2│ Bob_Dolman │ 17106 │ 245 │
3│ 1-krona │ 17167 │ 4 │
4│ Ahmadabad-e_Kalij-e_Sofla │ 17167 │ 3 │
5└───────────────────────────┴───────┴──────┘- 还可以使用相同的格式导出到BSON文件：
Plain Text
1SELECT *
2FROM sometable
3INTO OUTFILE 'out.bson'
4FORMAT BSONEachRow此后，我们将数据导出到out.bson文件。
对JSON进行建模的其他方法
使用嵌套
Nested类型可用于对很少发生变化的静态对象进行建模，从而为Tuple和提供替代方案Array(Tuple)。我们通常建议避免对JSON使用此类型，因为它的行为通常令人困惑。主要好处Nested是子列可用于对键进行排序。
下面提供了一个使用Nested类型对静态对象进行建模的示例。请考虑以下JSON中的简单日志条目：
Plain Text
1{
2 "timestamp": 897819077,
3 "clientip": "45.212.12.0",
4 "request": {
5 "method": "GET",
6 "path": "/french/images/hm_nav_bar.gif",
7 "version": "HTTP/1.0"
8 },
9 "status": 200,
10 "size": 3305
11}
12``
13
14We can declare the `request` key as `Nested`. Similar to `Tuple`, we are required to specify the sub columns.
15
16```sql
17-- default
18SET flatten_nested=1
19CREATE table http
20(
21 timestamp Int32,
22 clientip IPv4,
23 request Nested(method LowCardinality(String), path String, version LowCardinality(String)),
24 status UInt16,
25 size UInt32,
26) ENGINE = MergeTree() ORDER BY (status, timestamp);flatten_nested
该设置flatten_nested控制嵌套的行为。
- flatten_nested=1
值1（默认值）不支持任意嵌套级别。使用此值，最容易将嵌套数据结构视为长度相同的多个Array列。字段“方法”、“路径”和“版本”都是单独的“数组（类型）”列，它们有一个关键约束：方法、路径和版本字段的长度必须相同。如果我们使用SHOW CREATE TABLE，如下所示：
Plain Text
1SHOW CREATE TABLE http
2
3CREATE TABLE http
4(
5 `timestamp` Int32,
6 `clientip` IPv4,
7 `request.method` Array(LowCardinality(String)),
8 `request.path` Array(String),
9 `request.version` Array(LowCardinality(String)),
10 `status` UInt16,
11 `size` UInt32
12)
13ENGINE = MergeTree
14ORDER BY (status, timestamp)下面，插入到这个表中：
Plain Text
1SET input_format_import_nested_json = 1;
2INSERT INTO http
3FORMAT JSONEachRow
4{"timestamp":897819077,"clientip":"45.212.12.0","request":[{"method":"GET","path":"/french/images/hm_nav_bar.gif","version":"HTTP/1.0"}],"status":200,"size":3305}这里需要注意几个要点：
- 我们需要使用设置
input_format_import_nested_json将JSON作为嵌套结构插入。如果没有这个，我们需要将JSON展平，即
Plain Text
1INSERT INTO http FORMAT JSONEachRow
2{"timestamp":897819077,"clientip":"45.212.12.0","request":{"method":["GET"],"path":["/french/images/hm_nav_bar.gif"],"version":["HTTP/1.0"]},"status":200,"size":3305}- 嵌套字段
method、path和version需要作为 JSON 数组传递，即
Plain Text
1{
2 "@timestamp": 897819077,
3 "clientip": "45.212.12.0",
4 "request": {
5 "method": [
6 "GET"
7 ],
8 "path": [
9 "/french/images/hm_nav_bar.gif"
10 ],
11 "version": [
12 "HTTP/1.0"
13 ]
14 },
15 "status": 200,
16 "size": 3305
17}可以使用点符号来查询列：
Plain Text
1SELECT clientip, status, size, `request.method` FROM http WHERE has(request.method, 'GET');
2
3┌─clientip────┬─status─┬─size─┬─request.method─┐
4│ 45.212.12.0 │ 200 │ 3305 │ ['GET'] │
5└─────────────┴────────┴──────┴────────────────┘
61 row in set. Elapsed: 0.002 sec.Array请注意，子列的使用意味着可以利用完整的呼吸数组函数ARRAY JOIN，包括子句 - 如果您的列有多个值，则很有用。
- flatten_nested=0
这允许任意级别的嵌套，并且意味着嵌套的列保留为单个Tuples 数组 - 实际上它们变得相同Array(Tuple)。
这是使用 JSON 的首选方法，通常也是最简单的方法Nested。如下所示，它仅要求所有对象都是列表。
下面，我们重新创建表格并重新插入一行：
Plain Text
1CREATE TABLE http
2(
3 `timestamp` Int32,
4 `clientip` IPv4,
5 `request` Nested(method LowCardinality(String), path String, version LowCardinality(String)),
6 `status` UInt16,
7 `size` UInt32
8)
9ENGINE = MergeTree
10ORDER BY (status, timestamp)
11
12SHOW CREATE TABLE http
13
14-- note Nested type is preserved.
15CREATE TABLE default.http
16(
17 `timestamp` Int32,
18 `clientip` IPv4,
19 `request` Nested(method LowCardinality(String), path String, version LowCardinality(String)),
20 `status` UInt16,
21 `size` UInt32
22)
23ENGINE = MergeTree
24ORDER BY (status, timestamp)
25
26INSERT INTO http
27FORMAT JSONEachRow
28{"timestamp":897819077,"clientip":"45.212.12.0","request":[{"method":"GET","path":"/french/images/hm_nav_bar.gif","version":"HTTP/1.0"}],"status":200,"size":3305}这里需要注意几个要点：
input_format_import_nested_json无需插入。- 类型
Nested保存在 中SHOW CREATE TABLE。此列下面实际上是Array(Tuple(Nested(method LowCardinality(String), path String, version LowCardinality(String)))) - 因此，我们需要将其插入
request为数组，即：
Plain Text
1{
2 "timestamp": 897819077,
3 "clientip": "45.212.12.0",
4 "request": [
5 {
6 "method": "GET",
7 "path": "/french/images/hm_nav_bar.gif",
8 "version": "HTTP/1.0"
9 }
10 ],
11 "status": 200,
12 "size": 3305
13}可以再次使用点符号来查询列：
Plain Text
1SELECT clientip, status, size, `request.method` FROM http WHERE has(request.method, 'GET');
2
3┌─clientip────┬─status─┬─size─┬─request.method─┐
4│ 45.212.12.0 │ 200 │ 3305 │ ['GET'] │
5└─────────────┴────────┴──────┴────────────────┘
61 row in set. Elapsed: 0.002 sec.使用成对数组
- 成对数组在将JSON表示为字符串的灵活性与更结构化方法的性能之间实现了平衡。该架构非常灵活，因为任何新字段都可能添加到根中。然而，这需要更复杂的查询语法，并且与嵌套结构不兼容。
例如，请参考下表：
Plain Text
1CREATE TABLE http_with_arrays (
2 keys Array(String),
3 values Array(String)
4)
5ENGINE = MergeTree ORDER BY tuple();- 要插入此表，需要将JSON构造为键和值的列表。以下查询说明了如何使用
JSONExtractKeysAndValues来实现此目的：
Plain Text
1SELECT
2 arrayMap(x -> (x.1), JSONExtractKeysAndValues(json, 'String')) AS keys,
3 arrayMap(x -> (x.2), JSONExtractKeysAndValues(json, 'String')) AS values
4FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/http/documents-01.ndjson.gz', 'JSONAsString')
5LIMIT 1
6FORMAT Vertical
7
8Row 1:
9──────
10keys: ['@timestamp','clientip','request','status','size']
11values: ['893964617','40.135.0.0','{"method":"GET","path":"/images/hm_bg.jpg","version":"HTTP/1.0"}','200','24736']
12
131 row in set. Elapsed: 0.416 sec.- 请注意，请求列仍然是一个以字符串表示的嵌套结构。我们可以向根插入任何新键。我们还可以在JSON本身中存在任意差异。要插入到我们的本地表中，请执行以下操作：
Plain Text
1INSERT INTO http_with_arrays
2SELECT
3 arrayMap(x -> (x.1), JSONExtractKeysAndValues(json, 'String')) AS keys,
4 arrayMap(x -> (x.2), JSONExtractKeysAndValues(json, 'String')) AS values
5FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/http/documents-01.ndjson.gz', 'JSONAsString')
6
70 rows in set. Elapsed: 12.121 sec. Processed 10.00 million rows, 107.30 MB (825.01 thousand rows/s., 8.85 MB/s.)- 查询此结构需要使用
indexOf函数来识别所需键的索引（应与值的顺序一致）。这可用于访问值数组列，即values[indexOf(keys, 'status')]。我们仍然需要请求列的JSON解析方法 - 在本例中为simpleJSONExtractString。
Plain Text
1SELECT toUInt16(values[indexOf(keys, 'status')]) as status,
2 simpleJSONExtractString(values[indexOf(keys, 'request')], 'method') as method,
3 count() as c
4FROM http_with_arrays
5WHERE status >= 400
6 AND toDateTime(values[indexOf(keys, '@timestamp')]) BETWEEN '1998-01-01 00:00:00' AND '1998-06-01 00:00:00'
7GROUP by method, status ORDER BY c DESC LIMIT 5;
8
9┌─status─┬─method─┬─────c─┐
10│ 404 │ GET │ 11267 │
11│ 404 │ HEAD │ 276 │
12│ 500 │ GET │ 160 │
13│ 500 │ POST │ 115 │
14│ 400 │ GET │ 81 │
15└────────┴────────┴───────┘
16
175 rows in set. Elapsed: 0.383 sec. Processed 8.22 million rows, 1.97 GB (21.45 million rows/s., 5.15 GB/s.)
18Peak memory usage: 51.35 MiB.