CnosDB Data Update and Deletion -

In time series data, data errors or anomalies may occur. At this time, the data needs to be cleaned and repaired. If the update operation is not supported, it will become very difficult. In addition, some business scenarios may require adjustments to existing data, such as changes in device information. Supporting data updates provides greater flexibility to meet these needs.

CnosDB Data Update and Deletion

Features

Support updating Tag value.
Supports updating Field values.
Support deleting data.

update clause :=
UPDATE <table name> SET <assignment clause> [, ...] [<WHERE clause>]

assignment clause :=
<column name> = <column value>

delete clause :=
DELETE FROM <table name> <WHERE clause>

Fundamental

Since Tag values and Field values have different storage forms and locations in the storage engine, the update logic is also different.

Update Tag Value

Tag values are stored separately as indexes in the storage engine. Therefore, for Tag update operations, the index needs to be updated.

Proceed as follows:

In the query layer, based on the tag filtering conditions in SQL, all series that meet the conditions are queried.
Send a pre-update request to all matching nodes to check whether there will be a conflict, that is, the original different series will become the same series after being updated. If there is a conflict, this SQL request fails.
After the check passes, the actual update task will be submitted. This task will be retried after a failure to ensure successful task execution.
After receiving the update request, the storage engine node will first record the WAL of type UpdateSeriesKeys, and then update the index.

Execution plan (simplified version)

UpdateTagValue
  Filter
    TagScan

Update Field Value

Query the data in the table according to the WHERE condition in SQL, update the Field value and reinsert it into the table, and use the deduplication feature of the tskv table to achieve the update purpose.

Execution plan (simplified version)

TableWriter
  Projection
    Filter
      TableScan

Delete Data

Filter and delete data in the table by WHERE condition. Only expressions targeting Tag or Time columns are supported.

Execution process (simplified version)

DeleteFromTableTask
  Coordinate DML task on vnodes
    Vnode delete execution

Sample Demonstration

Prepare Data

ALTER DATABASE public SET ttl '1000000d';
DROP TABLE IF EXISTS dml_tbl;
CREATE TABLE IF NOT EXISTS dml_tbl(f0 BIGINT , f1 BIGINT , TAGS(t0, t1) );

INSERT dml_tbl(TIME, f0, f1, t0, t1)
VALUES
    ('1999-12-31 00:00:00.000', 111, 444, 'tag11', 'tag21'),
    ('1999-12-31 00:00:00.005', 222, 444, 'tag12', 'tag22'),
    ('1999-12-31 00:00:00.010', null, 222, 'tag12', 'tag23'),
    ('1999-12-31 00:00:10.015', 444, 111, 'tag14', 'tag24'),
    ('1999-12-31 00:00:10.020', 222, 555, 'tag14', 'tag21'),
    ('1999-12-31 00:10:00.025', 333, 555, 'tag11', 'tag22'),
    ('1999-12-31 00:10:00.030', 444, 333, 'tag11', 'tag23'),
    ('1999-12-31 01:00:00.035', 555, 222, 'tag14', 'tag24');

Update Tag value

UPDATE dml_tbl SET t0 = 'tag_new11', t1 = 'tag_new21' WHERE t0 = 'tag11' AND t1 = 'tag21';

  +-------------------------+-----------+-----------+-----+-----+
  | time                    | t0        | t1        | f0  | f1  |
  +-------------------------+-----------+-----------+-----+-----+
  | 1999-12-31T00:00:00     | tag_new11 | tag_new21 | 111 | 444 |
  | 1999-12-31T00:00:00.005 | tag12     | tag22     | 222 | 444 |
  | 1999-12-31T00:00:00.010 | tag12     | tag23     |     | 222 |
  | 1999-12-31T00:00:10.015 | tag14     | tag24     | 444 | 111 |
  | 1999-12-31T00:00:10.020 | tag14     | tag21     | 222 | 555 |
  | 1999-12-31T00:10:00.025 | tag11     | tag22     | 333 | 555 |
  | 1999-12-31T00:10:00.030 | tag11     | tag23     | 444 | 333 |
  | 1999-12-31T01:00:00.035 | tag14     | tag24     | 555 | 222 |
  +-------------------------+-----------+-----------+-----+-----+

Update Field value

UPDATE dml_tbl SET f1 = 999 WHERE t0 = 'tag14' AND time < '1999-12-31T00:00:10.020';

  +-------------------------+-----------+-----------+-----+-----+
  | time                    | t0        | t1        | f0  | f1  |
  +-------------------------+-----------+-----------+-----+-----+
  | 1999-12-31T00:00:00     | tag_new11 | tag_new21 | 111 | 444 |
  | 1999-12-31T00:00:00.005 | tag12     | tag22     | 222 | 444 |
  | 1999-12-31T00:00:00.010 | tag12     | tag23     |     | 222 |
  | 1999-12-31T00:00:10.015 | tag14     | tag24     | 444 | 999 |
  | 1999-12-31T00:00:10.020 | tag14     | tag21     | 222 | 555 |
  | 1999-12-31T00:10:00.025 | tag11     | tag22     | 333 | 555 |
  | 1999-12-31T00:10:00.030 | tag11     | tag23     | 444 | 333 |
  | 1999-12-31T01:00:00.035 | tag14     | tag24     | 555 | 222 |
  +-------------------------+-----------+-----------+-----+-----+

Delete Data

public ❯ DROP TABLE IF EXISTS dml_tbl_for_delete;

public ❯ CREATE TABLE IF NOT EXISTS dml_tbl_for_delete(f0 BIGINT , f1 BIGINT , TAGS(t0, t1) );

public ❯ INSERT dml_tbl_for_delete(TIME, f0, f1, t0, t1)
VALUES
    ('1999-12-31 00:00:00.000', 111, 444, 'tag11', 'tag21'),
    ('1999-12-31 00:00:00.005', 222, 444, 'tag12', 'tag22'),
    ('1999-12-31 00:00:00.010', null, 222, 'tag12', 'tag23'),
    ('1999-12-31 00:00:10.015', 444, 111, 'tag14', 'tag24'),
    ('1999-12-31 00:00:10.020', 222, 555, 'tag14', 'tag21'),
    ('1999-12-31 00:10:00.025', 333, 555, 'tag11', 'tag22'),
    ('1999-12-31 00:10:00.030', 444, 333, 'tag11', 'tag23'),
    ('1999-12-31 01:00:00.035', 555, 222, 'tag14', 'tag24');

+-------------------------+-------+-------+-----+-----+
| time                    | t0    | t1    | f0  | f1  |
+-------------------------+-------+-------+-----+-----+
| 1999-12-31T00:00:00     | tag11 | tag21 | 111 | 444 |
| 1999-12-31T00:00:00.005 | tag12 | tag22 | 222 | 444 |
| 1999-12-31T00:00:00.010 | tag12 | tag23 |     | 222 |
| 1999-12-31T00:00:10.015 | tag14 | tag24 | 444 | 111 |
| 1999-12-31T00:00:10.020 | tag14 | tag21 | 222 | 555 |
| 1999-12-31T00:10:00.025 | tag11 | tag22 | 333 | 555 |
| 1999-12-31T00:10:00.030 | tag11 | tag23 | 444 | 333 |
| 1999-12-31T01:00:00.035 | tag14 | tag24 | 555 | 222 |
+-------------------------+-------+-------+-----+-----+

public ❯ DELETE FROM dml_tbl_for_delete WHERE t0 = 'tag14' AND time < '1999-12-31T00:00:10.020';

+-------------------------+-------+-------+-----+-----+
| time                    | t0    | t1    | f0  | f1  |
+-------------------------+-------+-------+-----+-----+
| 1999-12-31T00:00:00     | tag11 | tag21 | 111 | 444 |
| 1999-12-31T00:00:00.005 | tag12 | tag22 | 222 | 444 |
| 1999-12-31T00:00:00.010 | tag12 | tag23 |     | 222 |
| 1999-12-31T00:00:10.020 | tag14 | tag21 | 222 | 555 |
| 1999-12-31T00:10:00.025 | tag11 | tag22 | 333 | 555 |
| 1999-12-31T00:10:00.030 | tag11 | tag23 | 444 | 333 |
| 1999-12-31T01:00:00.035 | tag14 | tag24 | 555 | 222 |
+-------------------------+-------+-------+-----+-----+

Use Scenarios and Cases

In time series databases, there are usually some specific scenarios that require updating historical data. The following are some situations where UPDATE and DELETE operations may be needed to update historical data:

Data Correction : Sometimes the initially recorded data may be wrong due to sensor errors, communication issues, or other reasons. In this case, you may need to correct historical data through UPDATE and DELETE operations to ensure that the stored data is accurate.
Data Completion : Sometimes data may be lost during certain time periods due to equipment failure or communication issues. When the issue is resolved, you may receive supplemental data and wish to update the history.
Handling abnormal situations : In some specific business scenarios, there may be situations where historical data needs to be updated or deleted, such as handling data changes after device upgrades.

Notes and limitations

When updating the Tag value, try to reduce the amount of data selected by WHERE. A large number of index updates will cause performance problems.
When updating the Tag value, avoid data writing and deletion operations because CnosDB does not provide transaction guarantees.
When deleting data, complex conditional expressions are not supported for the time being: filtering on the time column only supports interval judgment, and does not support complex filtering ( WHERE time < ‘2023-01-01T00:01:00' is supported , but WHERE time is not supported) % 2 = 0 ). In addition, query conditions can only use the time column and Tag column but not the Field column.

Conclusion

This article mainly introduces the data update and delete function in CnosDB. It includes how to update Tag values and Field values, and how to delete data. It also introduces the basic principles and provides corresponding example demonstrations and usage scenarios.

If you have any questions or comments, please feel free to ask them so we can further discuss and improve the corresponding features. Thanks for reading this article!