关于json:Databend-JSON-复杂数据类型的设计与使用-Databend-特性系列

JSON 是一种罕用的半结构化数据，通过自描述的 Schema 构造，能够示意任何类型的数据，包含多层嵌套的数据类型，例如 Array、Object 等。与必须严格遵循表构造字段的结构化数据相比，具备灵活性高，易于动静扩大的长处。近年来，随着各平台数据量的迅速减少，JSON 等半结构化数据的应用越来越风行，例如，平台通过凋谢接口对外提供 JSON 格局的数据，以 JSON 格局存储公开的数据集，采纳 JSON 格局存储利用日志等。对这些数据进行剖析能够取得很多十分有价值的信息。因而，Databend 在反对结构化数据处理的同时，也提供了对 JSON 数据的反对。本文将具体介绍 JSON 数据的设计和应用。

Databend 应用 VARIANT 类型来存储半结构化数据，通常也能够应用 JSON 作为别名。例如，能够应用如下的 SQL 创立蕴含 JSON 数据的表：

CREATE TABLE test (
  id Int32,
  v1 VARIANT,
  v2 JSON
);

JSON 类型的数据须要通过调用 parse_json 或 try_parse_json 函数生成，函数输出的字符串为规范的 JSON 格局，包含 Null、Boolean、Number、String、Array、Object 6 种类型的数据。如果字符串不非法导致解析失败，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 对外部元素进行拜访。有三种模式的语法作为分隔符：

冒号 : 用于按 key 获取 Object 中的元素

<!—->

点号 . 用于按 key 获取 Object 中的元素，为了与表名和列名间接的分隔符辨别，不能作为第一个分隔符

<!—->

括号 [] 用于按 key 获取 Object 或按 index 获取 Array 中的元素

这三种类型的分隔符能够混合应用。例如：

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                |
+--------------------------+---------------+------------------+

很多公开的数据集是用 JSON 格局存储的，咱们能够将这些数据导入到 Databend 进行剖析，上面以 Github 的公开事件数据为例进行介绍。

GH Archive 提供了 Github 数据的下载，事件记录具备如下的 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":"support@teeckyar.ir",
          "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 命令导入 2022-09-10-10 的 github 数据

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       |
+----------------------------------------------------------+-----------+

统计 fork 次数最多的 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 数据的读取性能，咱们采纳如下的形式进行优化：

数据存储为二进制格局的 JSONB。通过内置的 j_entry 构造存储各个元素的类型和偏移地位，能够放慢解析速度，缩小内存占用。
应用虚构列放慢查问速度。利用 JSON 数据通常有类似构造的特点，将用户常常查问并且数据类型雷同的字段抽取进去存储为独自的虚构列，在查问的时候间接从虚构列进行读取，这样能够取得与其余数据类型雷同的查问性能。

对于性能优化的具体设计，咱们将会在后续的文章中进行介绍。

Databend 是一款开源、弹性、低成本，基于对象存储也能够做实时剖析的旧式数仓。期待您的关注，一起摸索云原生数仓解决方案，打造新一代开源 Data Cloud。

Databend 文档：https://databend.rs/
Twitter：https://twitter.com/Datafuse_…
Slack：https://datafusecloud.slack.com/
Wechat：Databend
GitHub：https://github.com/datafusela…

文章首发于公众号：Databend

关于json:Databend-JSON-复杂数据类型的设计与使用-Databend-特性系列

引言

JSON 数据类型

剖析 Github 的 JSON 数据

性能优化

对于 Databend

Just My Socks（注册教程内含优惠码）

关于json:Databend-JSON-复杂数据类型的设计与使用-Databend-特性系列

引言

JSON 数据类型

剖析 Github 的 JSON 数据

性能优化

对于 Databend

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）