Understanding Apache IoTDB's Write and Merge Behavior

When working with Apache IoTDB (v2.0.8+) and its relational Table Model, you might expect that inserting a NULL value for an existing timestamp and TAG combination would overwrite or clear the previous value. However, as you observed, the old value (e.g., 'OPEN') persists even after you insert a NULL.

This behavior is not a bug; it is a fundamental design feature of time-series databases. Let's explore why this happens and how you can achieve your goal of clearing or updating a field to NULL.

---

Why Doesn't NULL Overwrite Existing Data?

Apache IoTDB uses an LSM-Tree (Log-Structured Merge-tree) storage architecture optimized for high-throughput writes. Under this architecture, writes are append-only. When you write data with the exact same timestamp and TAG (device ID) multiple times, IoTDB performs a field-by-field merge during queries and compaction.

In IoTDB, a NULL value in an INSERT statement is interpreted as "no data collected for this field at this timestamp" rather than "explicitly set this field to NULL."

This design is crucial for IoT use cases where different sensors on the same device upload data asynchronously:

  • Sensor A (Pressure) uploads at 10:00:00 -> (pressure=10.0, valve_state=null)
  • Sensor B (Valve State) uploads at 10:00:00 -> (pressure=null, valve_state='OPEN')

By merging these partial writes, IoTDB successfully reconstructs the complete state at 10:00:00: (pressure=10.0, valve_state='OPEN'). If NULL values overwrote existing data, Sensor B's write would have erased Sensor A's pressure reading, or vice versa.

---

How to Clear or Overwrite a Field to NULL

If your business logic requires you to explicitly clear a value or set it to NULL, you cannot do it via a standard INSERT statement with a null value. Instead, use one of the following approaches:

Approach 1: Delete and Re-insert (Recommended)

The most straightforward way to clear a timestamp's state and write fresh data is to delete the existing record for that timestamp and TAG before inserting the new data.

-- Step 1: Delete the existing data point for the specific time and TAG
DELETE FROM pump_snapshot 
WHERE pump_id = 'P-7' AND time = 1000;

-- Step 2: Insert the corrected data
INSERT INTO pump_snapshot(time, pump_id, pressure, valve_state) 
VALUES (1000, 'P-7', 20.0, null);

After running these two commands, a SELECT query will yield the expected result:

time                          | pump_id | pressure | valve_state
------------------------------+---------+----------+-------------
1970-01-01T08:00:01.000+08:00 | P-7     | 20.0     | null

Approach 2: Handle "Nullification" at the Application Layer

If deleting and re-inserting introduces too much overhead in your ingestion pipeline, you can use a sentinel value (like 'N/A', 'UNKNOWN', or 'CLEARED') to represent an explicitly cleared state instead of using database-level NULL.

-- Insert a placeholder string to represent a cleared state
INSERT INTO pump_snapshot(time, pump_id, pressure, valve_state) 
VALUES (1000, 'P-7', 20.0, 'N/A');

Then, map 'N/A' to null in your application code or downstream visualization tools (like Grafana).

---

Summary

  • IoTDB merges writes for the same timestamp and TAG field-by-field.
  • NULL means "absent", not "delete". It will not overwrite an existing value.
  • To explicitly clear a value, use a DELETE statement for that specific timestamp before writing your corrected backfill data.