Clickhouse新版本JSON字段数据写入方式

在Clickhouse版本22.3.1版本以上，提供了针对JSON格式数据的新的数据类型：JSON，从而实现了存储此类数据由原先的结构化表结构，更新为现在的半结构化表存储。对于新增字段，某些同学刚接触可能会比较陌生，这里介绍一些基本的原理和操作相关的介绍。

更多的关于clickhouse新版本的JSON字段类型介绍，可以参考下面这篇博文：

Clickhouse JSON表字段方法详解（处理半结构化/结构化数据方法）

本文主要介绍当表字段类型定义为JSON格式后，数据插入的多种方法

准备工作：

创建一张数据表json_test，其中建表语句如下所示：

CREATE TABLE test.mail_ods.json_test
(
    `repo_name` String,
    `event` String,
    `data` Object('json'),
    `accounts` Array(String)
)
ENGINE = MergeTree
ORDER BY tuple()
1
2
3
4
5
6
7
8
9

一、以INSERT VALUES方式插入

利用Clickhouse的HTTP请求方式进行数据插入：

echo "INSERT INTO test.json_test values ('pioug/yield-data', 'PushEvent', '{\"os\": \"android\", \"carrier\": \"5G\"}', ['{\"account\":\"lisi\", \"active\":false}'])" | curl -u default:123 http://localhost:8123/ --data-binary @-
1

这里需要注意的是，以values方式插入时，要记得在对应JSON字段的值前后加上单引号，否则会报错：

cho "INSERT INTO mail_ods.json_test values ('pioug/yield-data', 'PushEvent', {\"os\": \"android\", \"carrier\": \"5G\"}, ['{\"account\":\"lisi\", \"active\":false}'])" | curl -u default:123 http://localhost:8123/ --data-binary @-
Code: 62. DB::Exception: Cannot parse expression of type Object('json') here: {"os": "android", "carrier": "5G"}, ['{"account":"lisi", "active":false}'])
: While executing ValuesBlockInputFormat. (SYNTAX_ERROR) (version 22.4.6.53 (official build))

1
2
3
4

同时，要记得对字段内的key-value值加反斜杠进行转义。

二、指定Format格式插入

Clickhouse支持的多种Format输入输出格式包括：

Formats for Input and Output Data

对于JSON类型格式的输入输出可以参考

Importing and exporting JSON data in ClickHouse

这里我们给出示例，对于JSON数据，如果想要实现每个key对应表中的字段，value值作为表中的字段值存入的话，需要使用Format JSONEachRow格式来插入数据：

JSON数据

{
	"repo_name": "pioug/yield-data",
	"event": "PushEvent",
	"data": {
		"os": "android",
		"carrier": "5G"
	},
	"accounts": ["{\"account\":\"lisi\", \"active\":false}"]
}
1
2
3
4
5
6
7
8
9

使用Format JSONEachRow格式插入方式如下所示：

echo 'INSERT INTO mail_ods.json_test Format JSONEachRow {"repo_name": "pioug/yield-data", "event":"PushEvent", "data": {"os": "android", "carrier": "5G"}, "accounts": ["{\"account\":\"lisi\", \"active\":false}"]}' | curl -u default:123 http://localhost:8123/ --data-binary @-
1

此时需要注意的是，JSONEachRow后面紧跟着的是一个完整的JSON结构的数据，不需要再像insert values时那样加单引号。比如，对于accounts，中括号[]里为了表示String，用的是双引号，而不是之前的单引号。

插入时，会根据key对应表中的字段，value写入表中对应字段的值。