扩展数据类型

本文档主要介绍 YMatrix 支持的扩展数据类型。

在关系模型中，常用数据类型，每个列表示一个属性的值，如：姓名、身高、体重。但是对于时序场景而言，经常会出现以下情况：

无法预知指标集，即表模式可能要经常变。
指标集合过大，超过了 YMatrix 最多 1600 列的列数限制。

这时就需要使用扩展数据类型：支持在单个列中存储多个属性。

实现扩展数据类型的方式非常多样化，例如自定义序列化存储格式和编码方式，然后存储到字符串类型中等。 YMatrix 提供两种扩展数据类型：

JSON / jsonb
MXKV

1 JSON / jsonb

JSON / jsonb 类型继承自 PostgreSQL 数据库，和直接用字符串类型的列保存 JSON 串不同，JSON / jsonb 类型提供了常用的 JSON 操作函数，用于读取健值（Key），以及进行合并、删除等操作。

JSON 与 jsonb 使用方法基本一致，可以直接相互转换。区别是：

JSON 保存为文本，jsonb 保存为二进制数
JSON 写入时无需进行二进制转换，写入速度更快
jsonb 由于经过二进制转存，解析速度会更快

以下是常用的 JSON / jsonb 类型操作方法：

1.1 使用 -> 提取健值

SELECT '{"a":1, "b":2, "c":3}'::json->'a';
 ?column?
----------
 1
(1 row)

如果 JSON / jsonb 层级是嵌套的，多次使用 -> 即可：

SELECT '{"a":1, "b":2, "c":{"d":2}}'::json->'c'->'d';
 ?column?
----------
 2
(1 row)

1.2 删除键值

使用 - 号即可删除键值。在对 JSON 对象进行写操作时，必须将其转换为 jsonb 类型：

SELECT '{"a":1, "b":2, "c":3}'::jsonb - 'a';
     ?column?
------------------
 {"b": 2, "c": 3}
(1 row)

1.3 合并键值

使用 || 操作符将两个 jsonb 类型的键值合并到一起：

SELECT '{"a":1, "b":2}'::jsonb || '{"c":3}'::jsonb;
         ?column?
--------------------------
 {"a": 1, "b": 2, "c": 3}
(1 row)

1.4 更新键值

在键值相同的时候，使用 || 操作符更新数值：

SELECT '{"a":1, "b":2}'::jsonb || '{"b":3}'::jsonb;
     ?column?
------------------
 {"a": 1, "b": 3}
(1 row)

更多的 JSON 操作方法，请参考 PostgreSQL 文档。

2 MXKV

本课程教学视频已包含在 YMatrix 数据建模与时空分布模型

在使用 YMatrix 时序数据库采集数据的时候可能面临如下场景：

需采集的指标数量过多，超出 YMatrix 最多 1600 列的限制。
不同型号设备采集指标集合差别较大，导致在回传数据时有大量列值为空（NULL）。
无法预知指标集，数据表的模式（Schema）很可能会经常变。

针对如上问题传统解决方案如下：

将多个列序列化后存至变长字段中
使用 JSON 或 jsonb 数据类型

如上方案虽然可以解决操作复杂度，但性能并不理想。

MXKV 应运而生。MXKV 是 YMatrix 自研的高效可扩展存储类型，使用方法和 JSON 类似，优势在于：MXKV 利用二进制存储，配合 YMatrix 中的压缩算法，存储空间小，查询效率高。

注意！
该特性仅在企业版中提供。

2.1 使用方法

2.1.1 创建扩展并验证

MXKV 自定义数据类型包含在 MatrixTS 扩展中，首先要创建扩展：

CREATE EXTENSION matrixts;

验证 MXKV 是否已经开启：

mxadmin=# SELECT '{}'::mxkv_text;
 mxkv_text
-----------
 {}
(1 row)

如上所示，MXKV 已开启。

2.1.2 创建数据表并定义 MXKV 列

创建一张新表，并在新表中定义 MXKV 的整型值数据类型 mxkv_int4，来存储值为整型的 KV 数据：

CREATE TABLE data(
    time timestamp with time zone,
    tag_id int,
    kv mxkv_int4
)
DISTRIBUTED BY (tag_id);

2.1.3 定义键值

在使用 MXKV 类型前，为了压缩与查询性能优化，首先要做键值定义，来提前确定数据中包含的键值集合。

MXKV 提供了 UDF mxkv_import_keys 来完成键值定义。

有两种定义方式：

手动定义

手动提供 JSON 形式的 KV 样例，MXKV 会自动提取键值并完成定义。

如下 SQL 会提取 JSON 中的键值 'a' 和 'b' 并定义：

mxadmin=# SELECT mxkv_import_keys('{"a": 1, "b": 2}');
 mxkv_import_keys
------------------
 a
 b
(2 rows)

基于已有表的数据定义

现有 t_json 表，包括 JSON 类型的字段 c2：

mxadmin=# SELECT c2 FROM t_json ;
    c2
----------
 {"k1":1}
 {"k2":2}
(2 rows)

从结果可以看到，c2 中共包括 k1 和 k2 两个键值。

使用 mxkv_import_keys 通过表数据定义：

mxadmin=# SELECT mxkv_import_keys('t_json'::regclass, 'c2');
 mxkv_import_keys
------------------
 k1
 k2
(2 rows)

如上所示，在参数中提供表名和列名。
相比手动定义，这种方式更方便、更快捷，不过要以样例数据已存储在其他表中作为前提条件。

2.1.4 插入数据

键值确定后，下面开始插入 KV 数据：

mxadmin=# INSERT INTO data VALUES(now(), 1, '{"a":1, "b":2}');
INSERT 0 1

如上所示，插入的 KV 数据中，包含了刚刚定义的键值 'a' 和 'b'。

注意！
KV 中的键值必须定义后才能正确插入，否则会出现如下报错：
DETAIL: The key is not imported yet HINT: Import the keys with the mxkv_import_keys() function

2.1.5 读取键值内容

MXKV 键值内容的读取方式与 JSON 一样，都是使用 -> 符号：

mxadmin=# SELECT time, tag_id, kv->'a' as a, kv->'b' as b FROM data;
             time              | tag_id | a | b
-------------------------------+--------+---+---
 2021-07-02 13:23:30.405473+00 |      1 | 1 | 2
(1 row)

取出的键值和普通列一样，也可以做比较、与其他表做连接。例如，建一张与 data 表结构相同的表 data1 并插入相同的数据，使用如下 SQL 可以做连接：

mxadmin=# SELECT * FROM data, data1 WHERE data.kv->'a' = data1.kv->'a';
             time              | tag_id |        kv        |             time              | tag_id |        kv
-------------------------------+--------+------------------+-------------------------------+--------+------------------
 2021-07-02 13:23:30.405473+00 |      1 | {"a": 1, "b": 2} | 2021-07-02 13:26:31.828685+00 |      1 | {"a": 1, "b": 2}
(1 row)

注意！
MXKV 中 -> 与 ->> 效果等同。

2.2 MXKV 数据类型

上面创建的表中使用了 mxkv_int4 类型，这部分对于 MXKV 支持的几种数据类型做具体介绍：

mxkv_int4：存储 int4 / int 类型的 32 位整数值。
mxkv_float4：存储 float4 / real 类型的 32 位浮点数值。
mxkv_float8：存储 float8 / float / double precision 类型的 64 位浮点数值。
mxkv_text：存储 text 类型的字符串值。

其中 mxkv_float4 和 mxkv_float8 可以指定小数点后位数，如：

CREATE TABLE data(
    time timestamp with time zone,
    tag_id int,
    kv mxkv_float4(2)
)
DISTRIBUTED BY (tag_id);

这样定义后，小数点两位以后的将会做四舍五入。指定小数位数后，内部存储类型将转换为整型，更有利于优化和压缩。但数值范围也会相应缩小。

mxkv_float4(scale), scale >= 0
- mxkv_float4(0): [-2147483500, 2147483500]
- mxkv_float4(2): [-21474835.00, 21474835.00]
- mxkv_float4(4): [-214748.3500, 214748.3500]
- ......
mxkv_float8(scale), scale >= 0
- mxkv_float8(0): [-9223372036854775000, 9223372036854775000]
- mxkv_float4(2): [-92233720368547750.00, 92233720368547750.00]
- mxkv_float8(4): [-922337203685477.5000, 922337203685477.5000]
- ......

注意！
MXKV 只支持一级 KV 结构，不允许嵌套和数组。
更多深度技术信息请见博客 MXKV。