智能家居场景下的数据建模示例

本文档为“时序数据建模”章节的第四篇。YMatrix 认为，数据模型的设计会直接影响到数据消费与使用的价值。因此，除技术介绍外，我们尝试通过整个章节使你对时序数据模型（Time-series Data Model）的概念、应用及发展都有清晰的理解。

首篇为“时序数据模型是什么？”，通过回答几个层层深入的问题，最终使你对时序数据模型概念本身有清晰的理解。
第二篇“时序建模思路”将尝试从理论指导的角度给出 YMatrix 关系模型设计思路参考。
第三、四篇为车联网场景及智能家居场景（即此文档）下的数据建模示例，以“时序建模思路”为指导，给出 YMatrix 中不同时序场景的建模最佳实践。

注意！
此篇仅为参考示例，正式设计建模前建议至少完整详读 YMatrix 架构及组件原理。组件原理见“参考指南”章节。

1 什么是智能家居？

智能家居是物联网（IoT，Internet of Things）的典型应用场景之一。智能家居（Smart Home, Home Automation）是以住宅为平台，利用综合布线技术、网络通信技术、安全防范技术、自动控制技术、音视频技术将家居生活有关的设施集成，构建高效的住宅设施与家庭日程事务的管理系统，提升家居安全性、便利性、舒适性、艺术性，并实现环保节能的居住环境。

2 智能家居建模最佳实践

可以设想一下，假如你是一个智能空调指标平台 B 的开发/运维人员。现在，你想要就此平台的业务在 YMatrix 中进行建模设计，可能的思路如下：

序号	步骤
1	需求调研
2	建模设计与实施
3	模型测试

2.1 需求调研

智能空调指标平台 B 的数据结构：

其中品类、产品、设备及指标，组成每个指标值数据点的唯一标识。品类、产品、设备是空调指标表的静态标签集（Tagset），指标即为动态指标名。
智能空调指标平台 B 的数据特征：
- 数据规模：产品数量 10w 级别，每个产品出产数量不等的出货设备，一般为 100w ～ 1000w 级别，指标数量基本在 100 个以内。
- 产品指标：同一品类产品的指标类似但不完全一样。
- 指标类型：不确定。
智能空调指标平台 B 的查询特点：
- 最新值查询：某一个产品的某个设备的某一个指标的最新值。
- 单产品聚集查询：某一个产品对应的所有设备在某一天的指标和。
完整数据流：产品（传感器）端预计算、明细数据 -> 云端 -> YMatrix -> 智能家居应用程序

结论：经过仔细、全面的前期调研，我们认为在 YMatrix 中，此智能家居场景数据指标前期设计阶段难以确定，数据规模、设备数量大，应使用窄表模型，以常见的 text，float 为基础类型，并选择压缩性能良好的 MARS2 存储引擎为基础建表。

2.2 建模设计与实施

2.2.1 表结构

按不同的指标数据类型来构建多张窄表。

=# CREATE TABLE public.iot_float (
    product_type text, 
    product text,
    device text,
    metric text,
    value float4,
    ts timestamp with time zone
)
USING MARS2 
DISTRIBUTED BY (product_type,product,device) 
PARTITION BY range(ts)
(
 START ('2023-01-15') INCLUSIVE
 END ('2023-01-22') EXCLUSIVE
 EVERY (interval '1 hour'),
 DEFAULT PARTITION default_p
);
=# CREATE INDEX ON public.iot_float USING mars2_btree (device,ts,product_type,product);
=# CREATE TABLE public.iot_text (
    product_type text, 
    product text,
    device text, 
    metric text,
    value text,
    ts timestamp with time zone
)
USING MARS2 
DISTRIBUTED BY (product_type,product,device) 
PARTITION BY range(ts)
(
 START ('2023-01-15') INCLUSIVE
 END ('2023-01-22') EXCLUSIVE
 EVERY (interval '1 hour'),
 DEFAULT PARTITION default_p
);
=# CREATE INDEX ON public.iot_text USING mars2_btree (device,ts,product_type,product);

此示例中：

标签为 product_type，product，device 三列；
指标为 metric 列，指标值为 value 列；
指标类型为 float，text 两种；
以 product_type，product，device 作为分布键；
以 device，ts，product_type，product 作为排序键；
以 ts 作为分区键；
从 2023-01-15 至 2023-01-22 每小时为一个分区。

2.2 模型测试

序号	测试计划	SQL 语句
1	查看最新 10 条数据	`SELECT * FROM <表名> ORDER BY ts DESC LIMIT 10`
2	查询总行数	`SELECT COUNT(*) FROM <表名>`
3	查询某一设备最新上报的全部指标	`SELECT * FROM <表名> WHERE <设备标签列名> = '<设备标签值>' ORDER BY <时间戳列名> DESC LIMIT 1`
...

向 iot_float 表中插入 100 条测试语句。

=# INSERT INTO public.iot_float (product_type, product, device, metric, value, ts)
SELECT
    CASE (random() * 3)::int
        WHEN 0 THEN 'sensor'
        WHEN 1 THEN 'actuator'
        WHEN 2 THEN 'gateway'
        ELSE 'others'
    END AS product_type,
    'product_' || (random() * 10 + 1)::int AS product,
    'device_' || (random() * 100 + 1)::int AS device,
    CASE (random() * 5)::int
        WHEN 0 THEN 'temperature'
        WHEN 1 THEN 'humidity'
        WHEN 2 THEN 'pressure'
        WHEN 3 THEN 'voltage'
        WHEN 4 THEN 'current'
        ELSE 'others'
    END AS metric,
    random() * 100 AS value,
    timestamp '2023-01-15 00:00:00+00' + (random() * (timestamp '2023-01-22 00:00:00+00' - timestamp '2023-01-15 00:00:00+00')) AS ts
FROM generate_series(1, 100);

向 iot_text 表中插入 100 条测试语句。

=# INSERT INTO public.iot_text (product_type, product, device, metric, value, ts)
SELECT
    CASE (random() * 3)::int
        WHEN 0 THEN 'sensor'
        WHEN 1 THEN 'actuator'
        WHEN 2 THEN 'gateway'
        ELSE 'others'
    END AS product_type,
    'product_' || (random() * 10 + 1)::int AS product,
    'device_' || (random() * 100 + 1)::int AS device,
    CASE (random() * 5)::int
        WHEN 0 THEN 'status'
        WHEN 1 THEN 'message'
        WHEN 2 THEN 'error'
        WHEN 3 THEN 'warning'
        WHEN 4 THEN 'log'
        ELSE 'others'
    END AS metric,
    'value_' || (random() * 1000 + 1)::int AS value,
    timestamp '2023-01-15 00:00:00+00' + (random() * (timestamp '2023-01-22 00:00:00+00' - timestamp '2023-01-15 00:00:00+00')) AS ts
FROM generate_series(1, 100);

测试结果：

查看 iot_float 表的最新 10 条数据

=# SELECT * FROM iot_float ORDER BY ts DESC LIMIT 10;
product_type |  product   |  device   |   metric    |   value    |              ts
--------------+------------+-----------+-------------+------------+-------------------------------
actuator     | product_3  | device_24 | others     |  38.504875 | 2023-01-21 18:38:55.188439+08
others      | product_10 | device_59 | voltage     |  35.519894 | 2023-01-21 18:11:00.799357+08
sensor       | product_11 | device_15 | temperature |  74.955025 | 2023-01-21 17:04:56.706237+08
others      | product_7  | device_93 | pressure    |  62.177837 | 2023-01-21 14:52:45.494772+08
gateway      | product_5  | device_1  | humidity    | 0.23014386 | 2023-01-21 14:51:14.290224+08
others      | product_6  | device_61 | voltage     |  46.473114 | 2023-01-21 13:55:51.427582+08
gateway      | product_4  | device_50 | pressure    |  53.413925 | 2023-01-21 10:11:41.670681+08
actuator     | product_9  | device_13 | pressure    |  24.377035 | 2023-01-21 09:09:19.720012+08
gateway      | product_3  | device_26 | current     |  58.887447 | 2023-01-21 07:38:12.482037+08
gateway      | product_8  | device_82 | pressure    |  76.386024 | 2023-01-21 06:14:41.306756+08
(10 rows)

查看 iot_text 表的最新 10 条数据

=# SELECT * FROM iot_text ORDER BY ts DESC LIMIT 10;
product_type |  product   |  device   | metric  |   value   |              ts
--------------+------------+-----------+---------+-----------+-------------------------------
gateway      | product_10 | device_70 | status  | value_933 | 2023-01-21 22:03:51.85526+08
others      | product_10 | device_46 | status  | value_471 | 2023-01-21 21:27:29.235879+08
actuator     | product_4  | device_65 | log     | value_72  | 2023-01-21 21:20:27.515338+08
gateway      | product_1  | device_34 | error   | value_864 | 2023-01-21 20:17:05.031651+08
gateway      | product_6  | device_79 | warning | value_681 | 2023-01-21 18:03:51.699731+08
actuator     | product_6  | device_92 | message | value_463 | 2023-01-21 17:51:20.218522+08
gateway      | product_8  | device_44 | log     | value_526 | 2023-01-21 17:28:13.374733+08
actuator     | product_4  | device_2  | warning | value_78  | 2023-01-21 16:52:34.770392+08
sensor       | product_7  | device_49 | log     | value_258 | 2023-01-21 10:40:49.467672+08
actuator     | product_7  | device_36 | error   | value_981 | 2023-01-21 08:05:55.809313+08
(10 rows)

查询 iot_float 表的总行数

=# SELECT COUNT(*) FROM iot_float;
count
-------
100
(1 row)

查询 iot_text 表的总行数

=# SELECT COUNT(*) FROM iot_text;
count
-------
100
(1 row)

查询 iot_float 表中某一设备最新上报的全部指标

=# SELECT * FROM iot_float WHERE device = 'device_1' ORDER BY ts DESC LIMIT 1;
product_type |  product  |  device  |  metric  |   value    |              ts
--------------+-----------+----------+----------+------------+-------------------------------
gateway      | product_5 | device_1 | humidity | 0.23014386 | 2023-01-21 14:51:14.290224+08
(1 row)

查询 iot_text 表中某一设备最新上报的全部指标

=# SELECT * FROM iot_text WHERE device = 'device_66' ORDER BY ts DESC LIMIT 1;
product_type |  product  |  device   | metric |  value   |              ts
--------------+-----------+-----------+--------+----------+-------------------------------
others      | product_5 | device_66 | error  | value_94 | 2023-01-20 20:00:48.991428+08
(1 row)

注意！
在此只给出了简单的测试示例，更多场景测试示例见图形化界面轻松上手板块。在实际环境中，请根据具体的查询需求设计具体的测试语句。