Example of data modeling in smart home scenarios

This document is the fourth chapter of the "Time Series Data Modeling". YMatrix believes that the design of data models will directly affect the value of data consumption and use. Therefore, in addition to the technical introduction, we try to use the entire chapter to give you a clear understanding of the concept, application and development of Time-series Data Model.

The first article is "What is a time series data model?" By answering several in-depth questions, you will eventually have a clear understanding of the concept of the time series data model itself.
The second article "Timing Sequence Modeling Ideas" will try to give references to the design ideas of YMatrix from the perspective of theoretical guidance.
Articles 3 and 4 are examples of data modeling under Internet of Vehicles and smart home scenarios (i.e. this document). Guided by "time sequence modeling ideas", they give best practices for modeling different temporal scenarios in YMatrix.

Notes!
This article is only a reference example. It is recommended to read at least in full detail YMatrix Architecture and component principles before formal design modeling. See the "Reference Guide" chapter for component principles.

1 What is smart home?

Smart home is one of the typical application scenarios of [Internet of Things (IoT, Internet of Things)](https://baike.baidu.com/item/Internet of Things/7306589?fromtitle=IoT&fromid=552548&fr=aladdin#reference-[1]-1136308-wrap). Smart Home (Home Automation) is based on the residential platform, using integrated wiring technology, network communication technology, security prevention technology, automatic control technology, audio and video technology to integrate facilities related to home life, build an efficient management system for residential facilities and family schedule affairs, improve home safety, convenience, comfort, and artistry, and realize an environmentally friendly and energy-saving living environment.

2 Smart Home Modeling Best Practices

It can be imagined that if you are a developer/operation and maintenance personnel of an intelligent air conditioner index platform B. Now, you want to model and design the business of this platform in YMatrix, the possible ideas are as follows:

Serial number	Step
1	Demand Research
2	Modeling Design and Implementation
3	Model Test

2.1 Demand Research

Data structure of smart air conditioning index platform B:

![](https://img.ymatrix.cn/ymatrix_home/Smart Home Data Structure (screenshot)_1681725819.png)

Among them, categories, products, equipment and indicators form a unique identifier for each indicator value data point. Category, product, and equipment are static tag sets (tagsets) of the air conditioner indicator table, and the indicator is the dynamic indicator name.

Data characteristics of smart air conditioning index platform B:
- Data scale: Product quantity is 10w level, each product produces different quantities of shipment equipment, generally 100w to 1000w level, and the number of indicators is basically within 100.
- Product indicators: The indicators of products of the same category are similar but not exactly the same.
- Metric type: Uncertain.
Query features of smart air conditioning index platform B:
- Latest value query: The latest value of a certain indicator of a certain device of a certain product.
- Single product cluster query: the indicators and the corresponding indicators of all equipment of a certain product on a certain day.
Complete data flow:

Pre-calculation and detailed data on the product (sensor) side -> Cloud -> YMatrix -> Smart home application

![](https://img.ymatrix.cn/ymatrix_home/Smart Home Data Stream (screenshot)_1681726007.png)

Conclusion: After careful and comprehensive preliminary research, we believe that in YMatrix, the preliminary design stage of this smart home scenario data indicator is difficult to determine. The data scale and number of devices are large. A narrow table model should be used, and the common text and float are the basic types, and the MARS3 storage engine with good compression performance should be selected as the basic table construction.

2.2 Modeling Design and Implementation

2.2.1 Table Structure

Build multiple narrow tables by different metric data types.

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

In this example:

Three columns labeled product_type, product, device;
The metric is metric column, and the metric value is value column;
The indicator types are float and text;
Use product_type, product, device as the distribution key;
Use device, ts, product_type, product as sort keys;
Use ts as partition key;
From 2023-01-15 to 2023-01-22 is one partition per hour.

2.2 Model Testing

| Serial number | Test plan | SQL statement |--|--|--|--| | 1 | View the latest 10 pieces of data | SELECT * FROM <Table Name> ORDER BY ts DESC LIMIT 10 | | 2 | Query the total number of rows | SELECT COUNT(*) FROM <table name> | | 3 | Query all the latest metrics reported by a certain device | SELECT * FROM <Table name> WHERE <Device tag column name> = '<Device tag value>' ORDER BY <Timestamp column name> DESC LIMIT 1 | | ... | | |

Insert 100 test statements into the iot_float table.

=# 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);

Insert 100 test statements into the iot_text table.

=# 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);

Test results:

Check out the latest 10 pieces of data from the iot_float table

=# 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)

View the latest 10 pieces of data from the iot_text table

=# 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)

Query the total number of rows of the iot_float table

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

Query the total number of rows of the iot_text table

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

Query all the latest metrics reported by a device in the iot_float table

=# 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)

Query all the latest metrics reported by a device in the iot_text table

=# 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)

Notes!
Only simple test examples are given here. For more scenario test examples, see the graphical interface [Easy to get started] (/doc/5.1/get_started/easy_to_get_started) section. In the actual environment, please design specific test statements based on the specific query requirements.