持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第24天，点击查看活动详情
各种平台通过开放接口共享 JSON 数据，我们将在这篇文章中深入探讨 Databend 中的 JSON 数据类型。
JSON（JavaScript Object Notation）是一种常用的半结构化数据类型。凭借自描述的模式结构，JSON可以容纳所有数据类型，包括多层嵌套数据类型，如Array、 Object 等。JSON 和必须严格执行的结构化数据类型相比，具有高度的灵活性和易于动态扩展的优点遵循表格数据结构中的字段。
随着近年来数据量的快速增长，许多平台已经开始使用并充分利用半结构化数据类型（例如 JSON）。例如，JSON 数据通过开放接口被各个平台共享，公共数据集和应用程序日志以 JSON 格式存储。Databend 支持结构化数据类型以及 JSON。
在 Databend 中使用 JSON
Databend 将半结构化数据存储为 VARIANT（也称为 JSON）数据类型：
CREATE TABLE test
(
id INT32,
v1 VARIANT,
v2 JSON
);
JSON数据需要调用“parse_json”或“try_parse_json”函数生成。输入字符串必须为标准 JSON 格式，包Null、Boolean、Number、String、Array 和 Object。如果由于无效字符串导致解析失败，“parse_json”函数将返回错误，而“try_parse_json”函数将返回 NULL 值。
INSERT INTO test VALUES
(1, parse_json('{"a":{"b":1,"c":[1,2]}}'), parse_json('[["a","b"],{"k":"a"}]')),
(2, parse_json('{"a":{"b":2,"c":[3,4]}}'), parse_json('[["c","d"],{"k":"b"}]'));SELECT * FROM test;
+----+-------------------------+-----------------------+
| id | v1 | v2 |
+----+-------------------------+-----------------------+
| 1 | {"a":{"b":1,"c":[1,2]}} | [["a","b"],{"k":"a"}] |
| 2 | {"a":{"b":2,"c":[3,4]}} | [["c","d"],{"k":"b"}] |
+----+-------------------------+-----------------------+
JSON 通常保存 Array 或 Object 类型的数据。由于嵌套的层次结构，可以通过 JSON PATH 访问内部元素。该语法支持以下分隔符：
- “:”：冒号可用于通过键获取对象中的元素。
- “.”：点可用于通过键获取对象中的元素。不要在语句中使用点作为第一个分隔符，否则 Databend 会将点作为分隔表名和列名的分隔符。
- “[]”：括号可用于通过键获取对象中的元素或通过索引获取数组中的元素。
可以混合使用上述三种类型的分隔符。
SELECT v1:a.c, v1:a['b'], v1['a']:c, v2[0][1], v2[1].k FROM test;+--------+-----------+-----------+----------+---------+
| v1:a.c | v1:a['b'] | v1['a']:c | v2[0][1] | v2[1].k |
+--------+-----------+-----------+----------+---------+
| [1,2] | 1 | [1,2] | "b" | "a" |
| [3,4] | 2 | [3,4] | "d" | "b" |
+--------+-----------+-----------+----------+---------+
通过 JSON PATH 提取的内部元素也是 JSON 类型，可以通过 cast 函数或使用转换运算符“::”将其转换为基本类型。
SELECT cast(v1:a.c[0], int64), v1:a.b::int32, v2[0][1]::string FROM test;+--------------------------+---------------+------------------+
| cast(v1:a.c[0] as int64) | v1:a.b::int32 | v2[0][1]::string |
+--------------------------+---------------+------------------+
| 1 | 1 | b |
| 3 | 2 | d |
+--------------------------+---------------+------------------+
从 GitHub 解析 JSON
许多公共数据集以 JSON 格式存储。我们可以将这些数据导入Databend进行解析。以下介绍以 GitHub 事件数据集为例。
GitHub 事件数据集（从GH Archive下载）使用以下 JSON 格式：
{
"id":"23929425917",
"type":"PushEvent",
"actor":{
"id":109853386,
"login":"teeckyar-bot",
"display_login":"teeckyar-bot",
"gravatar_id":"",
"url":"https://api.github.com/users/teeckyar-bot",
"avatar_url":"https://avatars.githubusercontent.com/u/109853386?"
},
"repo":{
"id":531248561,
"name":"teeckyar/Times",
"url":"https://api.github.com/repos/teeckyar/Times"
},
"payload":{
"push_id":10982315959,
"size":1,
"distinct_size":1,
"ref":"refs/heads/main",
"head":"670e7ca4085e5faa75c8856ece0f362e56f55f09",
"before":"0a2871cb7e61ce47a6790adaf09facb6e1ef56ba",
"commits":[
{
"sha":"670e7ca4085e5faa75c8856ece0f362e56f55f09",
"author":{
"email":"",
"name":"teeckyar-bot"
},
"message":"1662804002 Timehash!",
"distinct":true,
"url":"https://api.github.com/repos/teeckyar/Times/commits/670e7ca4085e5faa75c8856ece0f362e56f55f09"
}
]
},
"public":true,
"created_at":"2022-09-10T10:00:00Z",
"org":{
"id":106163581,
"login":"teeckyar",
"gravatar_id":"",
"url":"https://api.github.com/orgs/teeckyar",
"avatar_url":"https://avatars.githubusercontent.com/u/106163581?"
}
}
从上面的数据可以看出，“actor”、“repo”、“payload”、“org”字段是嵌套结构，可以存储为JSON。其他可以存储为基本数据类型。所以我们可以像这样创建一个表：
CREATE TABLE `github_data`
(
`id` VARCHAR,
`type` VARCHAR,
`actor` JSON,
`repo` JSON,
`payload` JSON,
`public` BOOLEAN,
`created_at` timestamp(0),
`org` json
);
使用 COPY INTO 命令加载数据：
COPY INTO github_data
FROM 'https://data.gharchive.org/2022-09-10-10.json.gz'
FILE_FORMAT = (
compression = auto
type = NDJSON
);
以下代码返回提交次数最多的前 10 个项目：
SELECT repo:name,
count(id)
FROM github_data
WHERE type = 'PushEvent'
GROUP BY repo:name
ORDER BY count(id) DESC
LIMIT 10;+----------------------------------------------------------+-----------+
| repo:name | count(id) |
+----------------------------------------------------------+-----------+
| "Lombiq/Orchard" | 1384 |
| "maique/microdotblog" | 970 |
| "Vladikasik/statistic" | 738 |
| "brokjad/got_config" | 592 |
| "yanonono/booth-update" | 537 |
| "networkoperator/demo-cluster-manifests" | 433 |
| "kn469/web-clipper-bed" | 312 |
| "ufapg/jojo" | 306 |
| "bj5nj7oh/bj5nj7oh" | 291 |
| "appseed-projects2/500f32d3-8019-43ee-8f2a-a273163233fb" | 247 |
+----------------------------------------------------------+-----------+
以下代码返回分叉最多的前 10 个用户：
SELECT actor:login,
count(id)
FROM github_data
WHERE type='ForkEvent'
GROUP BY actor:login
ORDER BY count(id) DESC
LIMIT 10;+-----------------------------------+-----------+
| actor:login | count(id) |
+-----------------------------------+-----------+
| "actions-marketplace-validations" | 191 |
| "alveraboquet" | 59 |
| "ajunlonglive" | 50 |
| "Shutch420" | 13 |
| "JusticeNX" | 13 |
| "RyK-eR" | 12 |
| "DroneMad" | 10 |
| "UnqulifiedEngineer" | 9 |
| "PeterZs" | 8 |
| "lgq2015" | 8 |
+-----------------------------------+-----------+
性能优化
JSON数据一般以明文形式保存，每次读取数据都需要解析生成serde_json::Value的枚举值。与其他基本数据类型相比，处理 JSON 数据需要更多的解析时间和更多的内存空间。
Databend 使用以下方法提高了 JSON 数据的读取性能：
- 为了加快解析速度并减少内存使用，Databend 将 JSON 数据以二进制格式存储为 JSONB，并使用内置的 j_entry 结构来保存每个元素的数据类型和偏移位置。
- 添加虚拟列以加快查询速度。Databend 将经常查询的字段和相同数据类型的字段提取出来，并将它们存储为单独的虚拟列。查询时会直接从虚拟列中读取数据，这使得Databend达到了与查询其他基本数据类型相同的性能。
