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

admin 发表于 2025-1-13 17:29

专栏技术分享

导出 JSON 数据和元信息

用于导入的几乎所有 JSON 格式都可以用于导出。最常用的导出格式是 JSONEachRow：

SELECT * FROM sometable FORMAT JSONEachRow

{"path":"Bob_Dolman","month":"2016-11-01","hits":245}{"path":"1-krona","month":"2017-01-01","hits":4}{"path":"Ahmadabad-e_Kalij-e_Sofla","month":"2017-01-01","hits":3}

此外，可以使用 JSONCompactEachRow 格式，通过省略列名来减少存储空间占用：

SELECT * FROM sometable FORMAT JSONCompactEachRow

["Bob_Dolman", "2016-11-01", 245]["1-krona", "2017-01-01", 4]["Ahmadabad-e_Kalij-e_Sofla", "2017-01-01", 3]

将数据类型导出为字符串

ClickHouse 会严格按照标准遵循数据类型导出 JSON。但在某些场景中，如果需要将所有值导出为字符串，可以使用 JSONStringsEachRow 格式：

SELECT * FROM sometable FORMAT JSONStringsEachRow

{"path":"Bob_Dolman","month":"2016-11-01","hits":"245"}{"path":"1-krona","month":"2017-01-01","hits":"4"}{"path":"Ahmadabad-e_Kalij-e_Sofla","month":"2017-01-01","hits":"3"}

在此示例中，数值列 hits 被编码为字符串。此功能适用于所有 JSON* 格式，用户可以使用 JSONStrings\* 或 JSONCompactStrings\* 格式：

SELECT * FROM sometable FORMAT JSONCompactStringsEachRow

["Bob_Dolman", "2016-11-01", "245"]["1-krona", "2017-01-01", "4"]["Ahmadabad-e_Kalij-e_Sofla", "2017-01-01", "3"]

导出数据及元信息

通用 JSON 格式（广泛应用于各种应用程序）不仅会导出结果数据，还包含列类型和查询统计信息：

SELECT * FROM sometable FORMAT JSON

{    "meta":    [        {            "name": "path",            "type": "String"        },        …    ],
    "data":    [        {            "path": "Bob_Dolman",            "month": "2016-11-01",            "hits": 245        },        …    ],
    "rows": 3,
    "statistics":    {        "elapsed": 0.000497457,        "rows_read": 3,        "bytes_read": 87    }}

JSONCompact 格式提供相同的元信息，但对数据部分采用紧凑格式：

SELECT * FROM sometable FORMAT JSONCompact

{    "meta":    [        {            "name": "path",            "type": "String"        },        …    ],
    "data":    [        ["Bob_Dolman", "2016-11-01", 245],        ["1-krona", "2017-01-01", 4],        ["Ahmadabad-e_Kalij-e_Sofla", "2017-01-01", 3]    ],
    "rows": 3,
    "statistics":    {        "elapsed": 0.00074981,        "rows_read": 3,        "bytes_read": 87    }}

如果需要将所有值导出为字符串，可以选择 JSONStrings 或 JSONCompactStrings 格式变体。

紧凑格式导出 JSON 数据

导出 JSON 数据及其结构的更高效方式是使用 JSONCompactEachRowWithNamesAndTypes 格式：

SELECT * FROM sometable FORMAT JSONCompactEachRowWithNamesAndTypes

["path", "month", "hits"]["String", "Date", "UInt32"]["Bob_Dolman", "2016-11-01", 245]["1-krona", "2017-01-01", 4]["Ahmadabad-e_Kalij-e_Sofla", "2017-01-01", 3]

该格式采用紧凑的 JSON 表示，并附加两行头部信息，分别包含列名和列类型。此格式非常适合导入到其他 ClickHouse 实例或应用程序。

将 JSON 数据导出到文件

可以使用 INTO OUTFILE 子句将 JSON 数据保存到文件中：

SELECT * FROM sometable INTO OUTFILE 'out.json' FORMAT JSONEachRow

36838935 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 子句启用实时压缩：

SELECT * FROM sometable INTO OUTFILE 'out.json.gz' FORMAT JSONEachRow

36838935 rows in set. Elapsed: 22.680 sec. Processed 36.84 million rows, 1.27 GB (1.62 million rows/s., 56.02 MB/s.)

尽管压缩过程耗时更长，但生成的文件大小显著减小：

2.2G    out.json576M    out.json.gz

处理其他 JSON 格式

在前面的示例中，我们使用 JSONEachRow（NDJSON）格式加载 JSON 数据。以下是其他常见 JSON 格式的加载方法。

JSON 对象数组

常见的一种 JSON 数据格式是数组形式的 JSON 对象列表，如以下示例：

> cat list.json[  {    "path": "Akiba_Hebrew_Academy",    "month": "2017-08-01",    "hits": 241  },  {    "path": "Aegithina_tiphia",    "month": "2018-02-01",    "hits": 34  },  ...]

可以为此类数据定义一个表：

CREATE TABLE sometable(    `path` String,    `month` Date,    `hits` UInt32)ENGINE = MergeTreeORDER BY tuple(month, path)

导入 JSON 对象列表时，可以使用 JSONEachRow 格式（从本地文件 list.json 加载数据）：

INSERT INTO sometableFROM INFILE 'list.json'FORMAT JSONEachRow

使用 FROM INFILE 子句从本地文件加载数据，导入成功：

SELECT *FROM sometable

┌─path──────────────────────┬──────month─┬─hits─┐│ 1971-72_Utah_Stars_season │ 2016-10-01 │    1 ││ Akiba_Hebrew_Academy      │ 2017-08-01 │  241 ││ Aegithina_tiphia          │ 2018-02-01 │   34 │└───────────────────────────┴────────────┴──────┘

处理 NDJSON（行分隔 JSON）

许多应用会将日志记录为 JSON 格式，每行表示一个独立的 JSON 对象，如以下文件所示：

cat object-per-line.json

{"path":"1-krona","month":"2017-01-01","hits":4}{"path":"Ahmadabad-e_Kalij-e_Sofla","month":"2017-01-01","hits":3}{"path":"Bob_Dolman","month":"2016-11-01","hits":245}

JSONEachRow 格式也适用于此类文件：

INSERT INTO sometable FROM INFILE 'object-per-line.json' FORMAT JSONEachRow;SELECT * FROM sometable;

┌─path──────────────────────┬──────month─┬─hits─┐│ Bob_Dolman                │ 2016-11-01 │  245 ││ 1-krona                   │ 2017-01-01 │    4 ││ Ahmadabad-e_Kalij-e_Sofla │ 2017-01-01 │    3 │└───────────────────────────┴────────────┴──────┘

JSON 对象键

在某些场景下，JSON 对象列表可能以对象属性的形式编码，而非数组元素（例如 objects.json 文件）：

cat objects.json

{  "a": {    "path":"April_25,_2017",    "month":"2018-01-01",    "hits":2  },  "b": {    "path":"Akahori_Station",    "month":"2016-06-01",    "hits":11  },  ...}

ClickHouse 支持使用 JSONObjectEachRow 格式加载此类数据：

INSERT INTO sometable FROM INFILE 'objects.json' FORMAT JSONObjectEachRow;SELECT * FROM sometable;

┌─path────────────┬──────month─┬─hits─┐│ Abducens_palsy  │ 2016-05-01 │   28 ││ Akahori_Station │ 2016-06-01 │   11 ││ April_25,_2017  │ 2018-01-01 │    2 │└─────────────────┴────────────┴──────┘

指定父对象键值

如果需要将父对象的键值保存到表中，可以使用以下选项定义目标列名：

SET format_json_object_each_row_column_for_object_name = 'id'

随后，可以通过 file() 函数检查从原始 JSON 文件加载的数据：

SELECT * FROM file('objects.json', JSONObjectEachRow)

┌─id─┬─path────────────┬──────month─┬─hits─┐│ a  │ April_25,_2017  │ 2018-01-01 │    2 ││ b  │ Akahori_Station │ 2016-06-01 │   11 ││ c  │ Abducens_palsy  │ 2016-05-01 │   28 │└────┴─────────────────┴────────────┴──────┘

可以看到，id 列已成功填充对应的键值。

JSON 数组

为了节省空间，JSON 文件有时会以数组而非对象的形式编码。在这种情况下，处理的是 JSON 数组列表：

cat arrays.json

["Akiba_Hebrew_Academy", "2017-08-01", 241],["Aegithina_tiphia", "2018-02-01", 34],["1971-72_Utah_Stars_season", "2016-10-01", 1]

ClickHouse 会按数组值的顺序映射到列，使用 JSONCompactEachRow 格式加载此类数据：

SELECT * FROM sometable

┌─c1────────────────────────┬─────────c2─┬──c3─┐│ Akiba_Hebrew_Academy      │ 2017-08-01 │ 241 ││ Aegithina_tiphia          │ 2018-02-01 │  34 ││ 1971-72_Utah_Stars_season │ 2016-10-01 │   1 │└───────────────────────────┴────────────┴─────┘

从 JSON 数组中导入单独的列

在某些情况下，数据按列而非按行编码，此时父 JSON 对象包含列及其值。以下是一个示例文件：

cat columns.json

{  "path": ["2007_Copa_America", "Car_dealerships_in_the_USA", "Dihydromyricetin_reductase"],  "month": ["2016-07-01", "2015-07-01", "2015-07-01"],  "hits": [178, 11, 1]}

ClickHouse 使用 JSONColumns 格式解析此类数据：

SELECT * FROM file('columns.json', JSONColumns)

┌─path───────────────────────┬──────month─┬─hits─┐│ 2007_Copa_America          │ 2016-07-01 │  178 ││ Car_dealerships_in_the_USA │ 2015-07-01 │   11 ││ Dihydromyricetin_reductase │ 2015-07-01 │    1 │└────────────────────────────┴────────────┴──────┘

对于列数组而非对象的场景，还可以使用更紧凑的 JSONCompactColumns 格式：

SELECT * FROM file('columns-array.json', JSONCompactColumns)

┌─c1──────────────┬─────────c2─┬─c3─┐│ Heidenrod       │ 2017-01-01 │ 10 ││ Arthur_Henrique │ 2016-11-01 │ 12 ││ Alan_Ebnother   │ 2015-11-01 │ 66 │└─────────────────┴────────────┴────┘

将 JSON 对象保存为字符串而不解析

在处理结构不同的 JSON 列表时，您可能希望将 JSON 对象以字符串形式保存，而不对其进行解

路过

雷人

握手

鲜花

鸡蛋

收藏邀请

上一篇：利用高级监控仪表板解决常见数据库问题下一篇：ClickHouse 官方文档：处理 JSON 数据（1/2）

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

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