加载 JSON

以下示例提供了加载结构化和半结构化 JSON 数据的非常简单的示例。有关更复杂的 JSON，包括嵌套结构，请参阅指南 设计 JSON 模式。

加载结构化 JSON

在本节中，我们假设 JSON 数据采用 NDJSON（换行分隔 JSON）格式，在 ClickHouse 中称为 JSONEachRow，并且结构良好，即列名和类型是固定的。由于其简洁性和高效的空间使用，NDJSON 是加载 JSON 的首选格式，但其他格式也受支持，包括输入和输出。

考虑以下 JSON 示例，表示来自 Python PyPI 数据集的一行数据。

{
  "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 中，必须定义表模式。

在这个简单的例子中，我们的结构是静态的，我们的列名是已知的，类型也是明确定义的。

尽管 ClickHouse 通过 JSON 类型支持半结构化数据，其中键名及其类型可以是动态的，但在这里这是不必要的。

尽可能选择静态模式

在您的列具有固定名称和类型且不期望新列的情况下，在生产环境中始终优先选择静态定义的模式。

对于高度动态的数据，JSON 类型是首选，由于列的名称和类型可能会改变。此类型在原型设计和数据探索中也很有用。

下面显示了一个简单的模式，其中 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 子句选择了一个排序键。有关排序键及如何选择它们的详细信息，请参见此处。

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）。如果 ClickHouse 无法检测格式，可以通过参数函数手动指定格式。

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────────────┬─type──┬─installer────┬─python_minor─┬─system─┬─version─┐
│ 2022-05-26 │ CN           │ clickhouse-connect │ sdist │ bandersnatch │              │        │ 0.0.7 │
│ 2022-05-26 │ CN           │ clickhouse-connect │ sdist │ bandersnatch │              │        │ 0.0.7 │
└────────────┴──────────────┴────────────────────┴───────┴──────────────┴──────────────┴────────┴─────────┘

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 格式。支持其他常见的 JSON 格式，这些格式的加载示例在此处提供。

加载半结构化 JSON

我们之前的示例加载了具有静态且已知键名和类型的 JSON。这种情况通常不是这样 - 密钥可以被添加或其类型可以改变。这在可观察性数据等用例中是很常见的。

ClickHouse 通过专用的 JSON 类型处理这种情况。

考虑以下示例，来自上述 Python PyPI 数据集的扩展版本。在这里，我们添加了一个任意的 tags 列，包含随机键值对。

{
  "date": "2022-09-22",
  "country_code": "IN",
  "project": "clickhouse-connect",
  "type": "bdist_wheel",
  "installer": "bandersnatch",
  "python_minor": "",
  "system": "",
  "version": "0.2.8",
  "tags": {
    "5gTux": "f3to*PMvaTYZsz!*rtzX1",
    "nD8CV": "value"
  }
}

这里的 tags 列是不可预测的，因此我们无法为其建模。要加载这些数据，我们可以使用我们之前的模式，但提供一个额外的 tags 列，其类型为 JSON：

SET enable_json_type = 1;

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

我们使用与原始数据集相同的方法填充表格：

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

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

Ok.

0 rows in set. Elapsed: 255.679 sec. Processed 1.00 million rows, 29.00 MB (3.91 thousand rows/s., 113.43 KB/s.)
Peak memory usage: 2.00 GiB.

SELECT *
FROM pypi_with_tags
LIMIT 2

┌───────date─┬─country_code─┬─project────────────┬─type──┬─installer────┬─python_minor─┬─system─┬─version─┬─tags─────────────────────────────────────────────────────┐
│ 2022-05-26 │ CN           │ clickhouse-connect │ sdist │ bandersnatch │              │        │ 0.0.7 │ {"nsBM":"5194603446944555691"}                           │
│ 2022-05-26 │ CN           │ clickhouse-connect │ sdist │ bandersnatch │              │        │ 0.0.7 │ {"4zD5MYQz4JkP1QqsJIS":"0","name":"8881321089124243208"} │
└────────────┴──────────────┴────────────────────┴───────┴──────────────┴──────────────┴────────┴─────────┴──────────────────────────────────────────────────────────┘

2 rows in set. Elapsed: 0.149 sec.

注意这里加载数据时的性能差异。JSON 列在插入时需要进行类型推断，如果存在多个类型的列，则还需要额外存储。尽管可以配置 JSON 类型（参见设计 JSON 模式）以实现与显式声明列的等效性能，但它的灵活性是在开箱即用的情况下设计的。然而，这种灵活性是有代价的。

何时使用 JSON 类型

当您的数据：

具有 不可预测的键，这些键可以随时间变化。
包含 具有不同类型的值（例如，一个路径有时可能包含一个字符串，有时是一个数字）。
需要模式灵活性，而严格类型不切实际。

如果您的数据结构是已知且一致的，则几乎不需要使用 JSON 类型，即使您的数据是 JSON 格式。具体来说，如果您的数据具有：

具有已知键的扁平结构：使用标准列类型，例如 String。
可预测的嵌套：使用 Tuple、Array 或 Nested 类型来表示这些结构。
具有不同类型的可预测结构：可以考虑使用 Dynamic 或 Variant 类型。

您还可以像我们在上述示例中所做的那样混合使用方法，使用静态列来表示可预测的顶级键，并使用单个 JSON 列来表示有效负载的动态部分。

加载结构化 JSON​

加载半结构化 JSON​

何时使用 JSON 类型​

加载结构化 JSON

加载半结构化 JSON

何时使用 JSON 类型