background

That’s right! TDengine does not provide statements for modifying and deleting operations at the SQL level, that is, there are no UPDATE and DELETE statements.

  • Data cannot be updated in TDengine default configuration!

  • TDengine stores data for 10 years with default configuration!

When we experienced the basic functions of TDengine previously, we only involved the creation and deletion of database, creation and deletion of data table and various query functions of data table. Therefore, the data table CRUD (Create, Retrieve, Update, Delete), This is just about creating and querying, but what about updating and deleting? We look at it from two perspectives.

  1. TDengine is specifically designed for time series data of the Internet of Things, and has a timestamp as the primary key. Internet of things data can be written once and read many times; The value of individual data points is low, and users focus on the characteristics of trends over time. Modifying or deleting a single data point is either pointless or ill-intentioned.

  2. On the other hand, it is necessary to study the TDengine library statements. Or if we’re careful enough, we’ll do show databases; Update, delete (keep0, keep1, keep(D)). That said, TDengine supports modification and deletion, just configuration.

Build library statement

CREATE DATABASE [IF NOT EXISTS] db_name [KEEP keep] [DAYS days] [UPDATE 1];
Copy the code

Notes:

  1. The default value is 3650 days (10 years). The database automatically deletes the data that exceeds the retention period.
  2. UPDATE flag The database supports updating the same timestamp data.

Unmodifiable?

If the update parameter is not specified during database creation, the default value of update is 0, indicating that data cannot be modified (data with the same timestamp is discarded).

The update parameter can be configured to be modifiable, but the timestamp must be the same before the other fields can be modified (obviously, the timestamp cannot be modified ~).

taos> drop database if exists open_update;
Query OK, 0 of 0 row(s) inDatabase (0.018665s) taOS > create database open_update update 1; Query OK, 0 of 0 row(s)inDatabase (0.003878s) taOS > create tableif not exists open_update.weather(ts timestamp, temperature float, humidity float) tags(location nchar(64), groupId int);
Query OK, 0 of 0 row(s) inDatabase (0.001556s) taOS > create tableif not exists open_update.t1 using open_update.weather tags("taiyuan", 1);
Query OK, 0 of 0 row(s) inDatabase (0.064076s) taOS > create tableif not exists open_update.t2 using open_update.weather tags("xian", 1);
Query OK, 0 of 0 row(s) inDatabase (0.003091s) taOS > insert into open_update.t2 (ts, temperature, humidity) values ("The 2021-07-26 13:55:09. 237", 99.9, 999);
Query OK, 1 of 1 row(s) inDatabase (0.004368s) taOS > select * from open_update.t2; ts | temperature | humidity | ======================================================================== 2021-07-26 13:55:09. 237 | | | 999.00000 99.90000 Query OK, 1 row (s)in setTaos > insert into open_update.t2 (ts, temperature, humidity) values ("The 2021-07-26 13:55:09. 237", 1.0, 111);
Query OK, 1 of 1 row(s) inDatabase (0.001265s) taOS > select * from open_update.t2; ts | temperature | humidity | ======================================================================== 2021-07-26 13:55:09. 237 | | | 111.00000 1.00000 Query OK, 1 row (s)in set(0.004360 s)Copy the code

In this case, you can run show databases; The update configuration of the corresponding Open_UPDATE database is 1, indicating that data can be overwritten by timestamp.

  • Attached SQL:
drop database if exists open_update;
create database open_update update 1;

create table if not exists open_update.weather(ts timestamp, temperature float, humidity float) tags(location nchar(64), groupId int);

create table if not exists open_update.t1 using open_update.weather tags("taiyuan", 1);
create table if not exists open_update.t2 using open_update.weather tags("xian", 1);

insert into open_update.t2 (ts, temperature, humidity) values(" the 2021-07-26 13:55:09. 237 ",99.9.999);
select * from open_update.t2;

insert into open_update.t2 (ts, temperature, humidity) values(" the 2021-07-26 13:55:09. 237 ",1.0.111);
select * from open_update.t2;
Copy the code

Undeletable?

If the keep parameter is not specified during database construction, the default keep parameter is 3650, indicating that data will be stored for 10 years. That is, TDengine has the automatic data clearing mechanism.

Note:

  1. The value of the keep parameter is 3650. The value ranges from days to 365000. The value of days must be greater than or equal to the value of days.
  2. You can use the vi /etc/taos/taos. CFG configuration file and the keep and days parameters of the build library option.
taos> create database auto_delete keep 1 days 1;
Query OK, 0 of 0 row(s) inThe database (0.001604 s)# display content followed by... The end indicates that the content has been truncated
taos> show create database auto_delete;
            Database            |        Create Database         |
==================================================================
 auto_delete                    | CREATE DATABASE auto_delete... |
Query OK, 1 row(s) in set(0.002352 s)# display content followed by... The end indicates that the content has been truncatedtaos> show create table auto_delete.weather; Table | Create Table | ================================================================== weather | create table weather  (ts TI... | Query OK, 1 row(s)in set(0.001046 s)Use this command to modify the display character width to display the full content. Note: This configuration command is temporary and will become invalid after the taosd service is restarted.
taos> SET MAX_BINARY_DISPLAY_WIDTH 125;

taos> show create database auto_delete;
             Database             |                        Create Database                         |
====================================================================================================
 auto_delete                      | CREATE DATABASE auto_delete REPLICA 1 QUORUM 1 DAYS 1 BLOCKS 6 |
Query OK, 1 row(s) in set(0.001756s) taos> show create table auto_delete.weather; Table | Create Table | = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = ===================== weather | create table weather (ts TIMESTAMP,temperature FLOAT,humidity FLOAT) TAGS (location NCHAR(64),groupid INT) | Query OK, 1 row(s)in set(0.000232 s)Copy the code

  • Attached SQL:
create database auto_delete keep 1 days 1;

create table if not exists auto_delete.weather(ts timestamp, temperature float, humidity float) tags(location nchar(64), groupId int);

create table if not exists auto_delete.t1 using auto_delete.weather tags("taiyuan", 1);
create table if not exists auto_delete.t2 using auto_delete.weather tags("yuncheng", 1);
create table if not exists auto_delete.t3 using auto_delete.weather tags("xian", 2);
create table if not exists auto_delete.t4 using auto_delete.weather tags("xianyang", 2);

insert into auto_delete.t1 (ts, temperature, humidity) values (now, 23.0.50.1);
insert into auto_delete.t2 (ts, temperature, humidity) values (now, 23.0.50.1);
insert into auto_delete.t3 (ts, temperature, humidity) values (now, 23.0.50.1);
insert into auto_delete.t4 (ts, temperature, humidity) values (now, 23.0.50.1);
Copy the code

Notes:

  1. I had a problem with the data not being wiped when the time was up, but I shut down the virtual machine at night and the data was actually wiped the next day. Specific principles refer to the official website: blog.csdn.net/taos_data/a…

  2. When the test configuration is updatable, the initial library name is: Can_update: show create database can_update: show create database can_update: show create database can_update: show create database can_update: show create database can_update

drop database if exists can_update;
create database can_update update 1;
show create database can_update;
Copy the code

Reference

  • www.taosdata.com/cn/document…

If you have any questions or any bugs are found, please feel free to contact me.

Your comments and suggestions are welcome!

This article has participated in the activity of “New person creation Ceremony”, and started the road of digging gold creation together.