Cases for IoT: Query the Time-series Data

The Internet of things (IoT) describes the network of physical objects (or groups of such objects) with sensors, processing ability, software and other technologies that connect and exchange data with other devices and systems over the Internet or other communications networks.

The query performance is measured by two main indicators, throughput and latency. This tutorial demonstrates DolphinDB's query performance in IIoT (Industrial Internet of Things) applications and compares the latency of its TSDB engine, OLAP engine, and ClickHouse MergeTree engine in querying the IIoT data. Specifically,

• the latest record of a device;
• the latest record for all devices of a tenant;
• the statistics of a device for the last 5 minutes;
• data (in seconds) of a device for the last day.

The results show that the DolphinDB TSDB engine outperforms the DolphinDB OLAP engine and the ClickHouse MergeTree engine.

• Case 1: Query last 100 records of a device.

  noise = loadTable(database("dfs://NoiseDB"),"noise")
  select * from noise 
  where date=2022.01.01 and tenantId=1055 and deviceId=10067
  order by ts desc
  limit 100
  
  # timer(10) select ...
  Time elapsed: 24.33 ms

  We specify the partitioning columns "date" and "tenantId" as the filtering conditions in the where clause, with which the corresponding partition can be located quickly. The sort key (deviceId) is also specified as the filtering condition, with which the data block can be located based on the index and the latest 100 records can be retrieved in chronological order. On average, a query takes 2 ms, and the first query takes 14ms.

Note: The first query refers to the query that do not cause cache hits on database and operating system.

• Case 2: Query latest records for all devices of a tenant.

  noise = loadTable(database("dfs://NoiseDB"),"noise")
  select * from noise 
  where date=2022.01.01 and tenantId=1055
  context by deviceId
  csort ts desc
  limit 1
  
  # timer(10) select ...
  Time elapsed: 246.619 ms

  Similar to the case 1, the partitioning columns "date" and "tenantId" are also specified as the filtering conditions in the where clause to quickly locate the partitions. The data is grouped by device ID using context by, and each group is sorted in reverse chronological order with csort (considering that the IoT data can be out-of-order). Use limit 1 to get the latest record in each group to track the latest status of all devices for that tenant on that day. On average, a query takes 25 ms, and the first query takes 121 ms.

• Case 3: Query the sound pressure of a device for the last 5 minutes.

  noise = loadTable(database("dfs://NoiseDB"),"noise")
  select
       min(ts) as startTs,
       max(ts) as endTs,
       max(soundPressureLevel),
       avg(soundPressureLevel),
       max(soundPowerLevel), 
       avg(soundPowerLevel) 
  from noise
  where date=2022.01.01 and tenantId=1055 and deviceId=10067 and ts between 2022.01.01T00:50:15.518:2022.01.01T00:55:15.518
  group by tenantId, deviceId
  
  # timer(10) select ...
  Time elapsed: 22.168 ms

  The data from device 10067 of tenant 1055 on date 2022.01.01 (from 00:50:15.518 to 00:55:15.518) is filtered by the where clause. Then, group records by "tenentId" and "deviceId" and perform aggregations over each group to obtain the sound pressure metrics. The average query time is 2 ms, and the first query time is 13 ms.

• Case 4: Query the data of a device for the last day.

  noise = loadTable(database("dfs://NoiseDB"),"noise")
  select *
  from noise
  where date=2022.01.01 and tenantId=1055 and deviceId=10067
  order by ts
  
  # timer(10) select ...
  Time elapsed: 23.261 ms

  The filltered data by the where clause is sorted by "ts" with order by. The average query time is 2 ms, and the first query time is 16 ms.

This chapter compares the query performance of DolphinDB TSDB engine, the OLAP engine, and the ClickHouse MergeTree engine on the above dataset. As much as possible, the environment variables were kept the same during testing to ensure validity. See "Appendices" for detailed test scripts.

DolphinDB TSDB engine has excellent performance on time-series data queries in IoT scenarios, which can track device status with millisecond latency. From the comparison, we can see that the DolphinDB TSDB engine outperforms the DolphinDB OLAP engine and the ClickHouse MergeTree engine.

• DolphinDB script
• ClickHouse script