ClickHouse 官方文档：处理 JSON 数据（1/2）

最新推荐文章于 2025-10-01 07:35:25 发布

原创

最新推荐文章于 2025-10-01 07:35:25 发布 · 1.3k 阅读

16 ·

CC 4.0 BY-SA版权

文章标签：

#clickhouse #json

本文字数：27902；估计阅读时间：70分钟

作者： ClickHouse team

本文在公众号【ClickHouseInc】首发

概述

ClickHouse 提供了多种处理 JSON 的方法，每种方法都有其优缺点和适用场景。在本指南中，我们将介绍如何加载 JSON 并优化架构设计。本指南包括以下内容：

加载 JSON：在 ClickHouse 中使用简单架构加载和查询 JSON（尤其是 NDJSON）。
JSON 架构推断：通过 JSON 架构推断查询 JSON 并生成表结构。
设计 JSON 架构：设计并优化 JSON 架构的步骤。
导出 JSON：JSON 的导出方法。
处理其他 JSON 格式：处理非 NDJSON 格式 JSON 的技巧。
其他 JSON 建模方法：高级 JSON 建模方法（不推荐使用）。

重要：全新 JSON 类型已进入 Beta 阶段

本指南涵盖现有的 JSON 处理技术。值得注意的是，ClickHouse 已推出一种新的 JSON 类型，目前处于 Beta 阶段。详情请参见这里。【https://clickhouse.com/docs/en/sql-reference/data-types/newjson】

加载 JSON 数据

本节假设 JSON 数据采用 NDJSON（换行分隔的 JSON，Newline delimited JSON）格式，在 ClickHouse 中称为 JSONEachRow。这种格式因其简洁性和高效的空间利用率而成为加载 JSON 的首选，但 ClickHouse 同样支持其他格式的输入和输出。

以下示例展示了一行来自 Python PyPI 数据集的 JSON 数据：

{
  "date": "2022-11-15",
  "country_code": "ES",
  "project": "clickhouse-connect",
  "type": "bdist_wheel",
  "installer": "pip",
  "python_minor": "3.9",
  "system": "Linux",
  "version": "0.3.0"
}

要将该 JSON 对象加载到 ClickHouse 中，需要先定义表架构。以下是一个简单的架构示例，其中 JSON 的键被映射为表的列名：

CREATE TABLE pypi (
  `date` Date,
  `country_code` String,
  `project` String,
  `type` String,
  `installer` String,
  `python_minor` String,
  `system` String,
  `version` String
)
ENGINE = MergeTree
ORDER BY (project, date)

排序键

我们使用 ORDER BY 子句定义了一个排序键。有关排序键的详细信息以及如何选择，请参考相关文档。【https://clickhouse.com/docs/en/data-modeling/schema-design#choosing-an-ordering-key】

ClickHouse 支持以多种格式加载 JSON 数据，并能根据文件扩展名和内容自动推断其类型。以下示例中，我们通过 S3 函数读取了与上述表对应的 JSON 文件：

SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz')
LIMIT 1
┌───────date─┬─country_code─┬─project────────────┬─type────────┬─installer────┬─python_minor─┬─system─┬─version─┐
│ 2022-11-15 │ CN           │ clickhouse-connect │ bdist_wheel │ bandersnatch │              │        │ 0.2.8   │
└────────────┴──────────────┴────────────────────┴─────────────┴──────────────┴──────────────┴────────┴─────────┘

1 row in set. Elapsed: 1.232 sec.

注意，这里无需显式指定文件格式。我们通过通配模式读取桶中的所有 *.json.gz 文件，ClickHouse 会根据文件扩展名和内容自动识别格式为 JSONEachRow（ndjson）。如果格式无法自动识别，可以通过参数函数手动指定。

SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz', JSONEachRow)

压缩文件

上述文件均已压缩，ClickHouse 能够自动检测并处理这些压缩文件。

要加载这些文件中的数据行，可以使用 INSERT INTO SELECT：

INSERT INTO pypi SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz')
Ok.

0 rows in set. Elapsed: 10.445 sec. Processed 19.49 million rows, 35.71 MB (1.87 million rows/s., 3.42 MB/s.)

SELECT * FROM pypi LIMIT 2

┌───────date─┬─country_code─┬─project────────────┐
│ 2022-05-26 │ CN       	│ clickhouse-connect │
│ 2022-05-26 │ CN       	│ clickhouse-connect │
└────────────┴──────────────┴────────────────────┘

2 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"}

以上示例假设使用 JSONEachRow 格式。ClickHouse 还支持其他常见的 JSON 格式，其加载方法请参考相关示例。【https://clickhouse.com/docs/en/integrations/data-formats/json/other-formats】

上述内容展示了加载 JSON 数据的基础方法。对于更复杂的 JSON 数据结构（如嵌套结构），请参考“设计 JSON 架构”指南。【https://clickhouse.com/docs/en/integrations/data-formats/json/schema】

JSON 架构自动推断

ClickHouse 支持自动推断 JSON 数据的结构。这一功能允许直接查询 JSON 数据，例如使用 clickhouse-local 查询磁盘数据，或查询存储在 S3 存储桶中的数据。此外，还可以在数据加载到 ClickHouse 之前自动生成表架构。

适用场景

结构一致：用于类型推断的数据需包含所有目标列。如果推断完成后数据增加了额外的列，这些列可能无法被查询。
类型一致：特定列的类型需要相互兼容。

注意事项

如果 JSON 数据具有动态结构，例如频繁新增键但未能及时更新架构（如日志中的 Kubernetes 标签），建议参考“设计 JSON 架构”指南。

类型检测

在之前的示例中，我们使用了 NDJSON 格式的 Python PyPI 数据集的简单版本。本节将探索一个更复杂的数据集——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    │ Null