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.
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.
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.
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 |
Data structure of smart air conditioning index platform B: _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:
Query features of smart air conditioning index platform B:
Complete data flow: Pre-calculation and detailed data on the product (sensor) side -> Cloud -> YMatrix -> Smart home application _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 a MARS2 storage engine with good compression performance should be selected as the basic table construction.
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 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);In this example:
2023-01-15 to 2023-01-22 is one partition per hour.| 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:
=# 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)=# 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)=# 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.0/get_started/easy_to_get_started) section. In the actual environment, please design specific test statements based on the specific query requirements.