加载 JSON
在本节中,我们假设 JSON 数据采用 NDJSON(换行符分隔的 JSON)格式,在 ClickHouse 中称为 JSONEachRow
。这是加载 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 中,必须定义一个表 schema。下面显示了一个简单的 schema 示例,其中 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.
请注意,我们不需要指定文件格式。相反,我们使用 glob 模式读取 bucket 中的所有 *.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────────────┐
│ 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 格式。支持其他常见的 JSON 格式,此处提供了加载这些格式的示例。
上面提供了一个非常简单的加载 JSON 数据的示例。对于更复杂的 JSON,包括嵌套结构,请参阅指南 设计 JSON schema。