扩展数据类型

本文档主要介绍 YMatrix 支持的扩展数据类型,可用于宽表/窄表变式模型的构建。

YMatrix 是一种模式化的关系型数据库,其所有表都需要通过 DDL 语句预定义一个明确的模式,即 Schema。模式中的每列表示一个属性的值,如:姓名、身高、体重。但是对于一些业务场景而言,经常会出现以下情况:

  1. 无法预知指标集,即表模式可能要经常变。
  2. 指标集合过大,超过了 YMatrix 最多 1600 列的列数限制。
  3. 不同指标集合间差别大,导致在回传数据时有大量列值为空(NULL)。

将多个列序列化后存至变长字段中

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

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

对比如下:

数据类型 存储方式 支持的存储格式 支持的数据格式
JSON 文本 所有格式 INT/NUMBERIC/BOOLEAN/NULL等
支持不同数据格式以数组的形式存储到一个 JSON 列
JSONB 二进制数 所有格式 INT/NUMBERIC/BOOLEAN/NULL等
支持不同数据格式以数组的形式存储到一个 JSON 列
MXKV 二进制数 所有格式 MXKV_INT4/MXKV_FLOAT4/MXKV_FLOAT8/MXKV_TEXT
只支持一级 KV 结构,不允许嵌套和数组,如要存储不同数据格式的指标,需创建多个列
MXKV2 二进制数 MARS2/3 MXKV_INT8/MXKV_FLOAT8/MXKV_TEXT/MXKV_BOOL
只支持一级 KV 结构,不允许嵌套和数组,如要存储不同数据格式的指标,需创建多个列


1 JSON/JSONB

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

JSON 与 JSONB 使用方法基本一致,可以直接相互转换。区别是:

  1. JSON 保存为文本,JSONB 保存为二进制数
  2. JSON 写入时无需进行二进制转换,写入速度更快
  3. JSONB 由于经过二进制转存,解析速度会更快
  4. 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 / MXKV2

MXKV 与 MXKV2 是 YMatrix 自研的高效可扩展存储类型,使用方法和 JSON/JSONB 类似。其优势在于利用二进制存储,配合 YMatrix 中的编码链压缩算法,可以实现更小的存储空间及更高的查询效率。

除本文档首 4 种数据类型对比表格中列出的项外,二者还有如下区别:

  • 在使用步骤上,MXKV 需要手工导入 key;MXKV2 不需要显式手工导入
  • MXKV2 的压缩率最高,查询性能最佳,MXKV 其次,而后是 JSONB,最后 JSON
  • MXKV2 需配合向量化执行引擎才会获得其特有的性能收益;MXKV 不能从向量化收益
  • MXKV2 建表时必须指定压缩方式;MXKV 非必须

此部分教学视频已包含在 YMatrix 数据建模与时空分布模型

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

2.1 使用方法

2.1.1 创建扩展并验证

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

=# CREATE EXTENSION matrixts;

验证 MXKV/MXKV2 是否已经开启:

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

// MXKV2
=# SELECT '{}'::mxkv2_text;
 mxkv2_text
-----------
 {}
(1 row)

如上所示,MXKV 已开启。

2.1.2 创建数据表并定义 MXKV 列

创建一张新表,并在新表中定义整型值数据类型,来存储值为整型的 KV 数据:

// MXKV
=# CREATE TABLE data_1(
    time timestamp with time zone,
    tag_id int,
    kv mxkv_int4
   )
   USING MARS3
   DISTRIBUTED BY (tag_id)
   ORDER BY (time,tag_id);

// MXKV2
=# CREATE TABLE data_2(
    time timestamp with time zone,
    tag_id int,
    kv mxkv2_int8 encoding(compresstype=mxcustom,encodechain='auto')
   )
   USING MARS3
   DISTRIBUTED BY (tag_id)
   ORDER BY (time,tag_id);

2.1.3 定义键值(MXKV2 跳过此步骤)

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

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

有两种定义方式:

  • 手动定义

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

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

=# SELECT mxkv_import_keys('{"a": 1, "b": 2}');
 mxkv_import_keys
------------------
 a
 b
(2 rows)
  • 基于已有表的数据定义

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

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

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

使用 mxkv_import_keys 通过表数据定义:

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

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

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

2.1.4 插入数据

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

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

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

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

2.1.5 读取键值内容

MXKV 键值内容的读取方式与 JSON 一致,都是使用 -> 符号:

// MXKV
=# SELECT time, tag_id, kv->'a' as a, kv->'b' as b FROM data_1;
             time              | tag_id | a | b
-------------------------------+--------+---+---
 2023-10-19 10:20:05.334169+00 |      1 | 1 | 2
(1 row)

// MXKV2
=# SELECT time, tag_id, kv->'a' as a, kv->'b' as b FROM data_2;
             time              | tag_id | a | b
-------------------------------+--------+---+---
 2023-10-19 10:20:05.334169+00 |      1 | 1 | 2
(1 row)

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

// MXKV
=# SELECT * FROM data_1, data_3 WHERE data_1.kv->'a' = data_3.kv->'a';
             time              | tag_id |        kv        |             time              | tag_id |        kv
-------------------------------+--------+------------------+-------------------------------+--------+------------------
 2023-10-19 10:20:05.334169+00 |      1 | {"a": 1, "b": 2} | 2023-10-19 10:20:05.334169+00 |      1 | {"a": 1, "b": 2}
(1 row)

// MXKV2
=# SELECT * FROM data_2, data_3 WHERE data_2.kv->'a' = data_3.kv->'a';
             time              | tag_id |        kv        |             time              | tag_id |        kv
-------------------------------+--------+------------------+-------------------------------+--------+------------------
 2023-10-19 10:20:05.334169+00 |      1 | {"a": 1, "b": 2} | 2023-10-19 10:20:05.334169+00 |      1 | {"a": 1, "b": 2}
(1 row)

注意!
MXKV/MXKV2 中 ->->> 效果等同。

2.2 MXKV/MXKV2 支持的数据格式

数据类型 支持的数据格式
MXKV MXKV_INT4:存储 INT4/INT 类型的 32 位整数值
MXKV_FLOAT4:存储 FLOAT4 / REAL 类型的 32 位浮点数值
MXKV_FLOAT8:存储 FLOAT8/FLOAT/DOUBLE PRECISION 类型的 64 位浮点数值
MXKV_TEXT:存储 TEXT 类型的字符串值
MXKV2 MXKV_INT8:存储 INT8/INT 类型的 64 位整数值
MXKV_FLOAT8:存储 FLOAT8/FLOAT/DOUBLE PRECISION 类型的 64 位浮点数值
MXKV_TEXT:存储 TEXT 类型的字符串值
MXKV_BOOL:存储 BOOLEAN 类型的布尔值

其中 MXKV_FLOAT4 和 MXKV_FLOAT8 可以指定小数点后位数,例如:

=# CREATE TABLE data_4(
    time timestamp with time zone,
    tag_id int,
    kv mxkv_float4(2)
   )
   USING MARS3
   DISTRIBUTED BY (tag_id)
   ORDER BY (time,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/MXKV2 均只支持一级 KV 结构,不允许嵌套和数组。 更多深度技术信息请见博客 MXKV