Innovative IoT Point Management with DolphinDB IOTDB Engine

In IoT applications, "points" refer to the various sensors and actuators on devices that collect, monitor, and control data; managing and analyzing these point data is crucial for real‑time monitoring, alerts, and intelligent decision‑making across domains such as connected vehicles, smart manufacturing, and environmental monitoring.

As the number of points and data types grow, efficiently managing point data becomes a common challenge for enterprises.

Wide‑table vs. narrow‑table modeling

For example, in a connected‑vehicle scenario a car may generate battery level, speed, and engine‑status data. Sample JSON records are shown below:

{"time":"2024.01.01 00:00:10.000","carID":"0001","elec":90}
{"time":"2024.01.01 00:00:20.000","carID":"0001","elec":89}
{"time":"2024.01.01 00:00:00.000","carID":"0001","speed":80.17}
{"time":"2024.01.01 00:00:01.000","carID":"0001","speed":80.29}
{"time":"2024.01.01 00:02:00.000","carID":"0001","engineStatus":"level1"}
{"time":"2024.01.01 00:04:00.000","carID":"0001","engineStatus":"level2"}

These records differ in type, sampling frequency, and timestamps. To manage them one can adopt either a wide‑table or a narrow‑table schema.

Using a wide‑table schema results in a single table with many sparse columns; as the number of points increases, the table becomes increasingly sparse and difficult to manage.

Using a narrow‑table schema creates a separate table for each point type (e.g., INT, DOUBLE, STRING). While this eliminates sparsity, the number of tables and maintenance effort grow with the number of point types.

Converting all point values to strings allows storage in a single table, but this approach raises storage costs and degrades query and computation efficiency.

The article then asks whether a solution exists that avoids data sparsity, multi‑table management, and performance loss.

DolphinDB, a high‑performance distributed time‑series database, offers the IOTDB engine to address these issues. The engine supports a flexible IOTANY column type, enabling all point data—regardless of original type—to be stored efficiently in one table.

The IOTDB engine consists of three components: the TSDB engine (stores historical point data), a latest‑value cache table (keeps the most recent value of each point for fast queries), and a static‑info table (records the true data type of each point and validates type consistency on writes).

A performance comparison shows the IOTDB engine outperforms traditional TSDB storage for mixed‑type point data.

An application example demonstrates managing five common water‑pipeline sensor points (pressure, flow, turbidity, valve state, etc.) using the IOTDB engine.

Step 1: Create an IOTDB engine database

create database "dfs://IOTDB" partitioned by HASH([SYMBOL,10]), VALUE([today()]), engine = "IOTDB";

Step 2: Create a point data table – enable the latest‑value cache with latestKeyCache="true" and define a column of type IOTANY to store all point samples.

Step 3: Write data – assemble data of different types into separate in‑memory tables and use append! to insert them, resulting in a single table where all point values reside in the IOTANY column.

Step 4: Query the latest state – use the syntax context by+csort+limit -1 to retrieve the most recent value of each point.

The article concludes with a link to the full tutorial and an invitation to a live session where DolphinDB experts will further discuss the IOTDB engine and its advanced IoT use cases.