可扩展数据类型:mxkv

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

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

  1. 要采集的指标过多,超过了PostgreSQL的最多1600列限制
  2. 不同型号设备采集指标集合差别较大,导致在回传数据时有大量列值为NULL
  3. 无法预知指标集,即表schema可能要经常变

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

  1. 将多个列序列化后存在变长字段中
  2. 使用PostgreSQL自带的json或jsonb类型

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

mxkv应运而生,为MatrixDB自研的自定义kv数据类型,和json类似,将列名作为mxkv键值。既方便扩展,提升查询性能,又减少存储空间。

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

使用方法

创建扩展并验证

mxkv自定义数据类型包含在MatrixTS扩展中,首先要创建扩展:

create extension matrixts;

验证mxkv是否已经开启:

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

如上所示,mxkv已开启。

创建数据表并定义mxkv列

创建一张新表,并在新表中定义mxkv的整型value数据类型mxkv_int4,来存储value为整型的kv数据:

CREATE TABLE data(
    time timestamp with time zone,
    tag_id int,
    kv mxkv_int4
)
Distributed by (tag_id);

定义键值

在使用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)

如上所示,在参数中提供表名和列名。相比手动定义,这种方式更方便、更快捷。前提是样例数据已存储在其他表中。

插入数据

键值确定后,下面开始插入kv数据:

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

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

注意:kv中的键值必须定义后才能正确插入,否则会出现如下错误:

mxadmin=# insert into data values(now(), 1, '{"c":1}');
psql: ERROR:  unknown key "c"
LINE 1: insert into data values(now(), 1, '{"c":1}');
                                          ^
DETAIL:  The key is not imported yet
HINT:  Import the keys with the mxkv_import_keys() function

读取键值内容

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中->与->>效果等同。

数据类型

上面创建的表中使用了mxkv_int4类型,mxkv共支持如下几种类型

  1. mxkv_int4:存储int4/int类型的32位整数值
  2. mxkv_float4:存储float4/real类型的32位浮点数值
  3. mxkv_float8:存储float8/float/double precision 类型的64位浮点数值
  4. 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结构,不允许嵌套和数组。