The concept of super tables is very important and useful in TDengine. I just don’t know what I’m doing when I’m studying. And when the information is less than the price. There are too few things to refer to, and the learning process is a hindrance to little white. However, most of the syntax is in SQL, so you just need to learn the features of TDengine. Supertables, for example, are better used with tags… Used to record the learning process, encountered problems. At present encounter the demand, need to achieve a few tables of a few fields query. Use If the super table is used, the default is to directly query all sub-table data, obviously does not meet the requirements. Later I learned that I needed to use TDengine’s TAG. Note: The total length of TAGS columns cannot exceed 512 bytes; TAGS columns cannot be of type TIMESTAMP and nCHAR; TAGS column names cannot be the same as other column names; TAGS column name cannot be a reserved keyword.
Create a new TAG for the super table. I want to use the TAG to specify the table name. This is the statement that creates the TAG for the super table
ALTER TABLE <stable_name> ADD TAG <new_tag_name> <TYPE>
Copy the code
Here to make fun of (of course also may be my own understanding ability problem) above statement I understand to write like this:
ALTER TABLE s_01020110 add tag tables_name binary;
Copy the code
After enter:
DB error: invalid SQL: invalid binary/nchar tag length
Copy the code
The length is not specified. The length is not specified.
ALTER TABLE s_01020110 add tag tables_name BINARY 40;
Copy the code
After enter:
DB error: syntax error near "40;"
Copy the code
I checked the document, but there was no specific demo. I searched on the Internet and found no demo written by others. Finally, I tried another writing method:
ALTER TABLE s_01020110 add tag tables_name BINARY(40);
Copy the code
This time enter found success… View the description of the supertable at this point;
describe s_01020110;
Copy the code
Drop supertable TAG
ALTER TABLE <stable_name> DROP TAG <tag_name>
Copy the code
No problem with that; Create a TAG for subtables of the supertable. Create a TAG ();
select tables_name from s_01020110;
Copy the code
Mysql > alter table subtable tag = null;
ALTER TABLE tb_name SET TAG tag_name=new_tag_value;
Copy the code
My requirement is to use a TAG to differentiate the table to be queried, so I set the TAG to the table name of the child table;
ALTER TABLE t_0102011001 SET TAG tables_name='t_0102011001';
Copy the code
I have 4 subtables under my super table, so I set up for each subtable (this is in the development SQL template stage, so I use it for testing). If a supertable has many sub-tables, it is not possible to set each table again. Create a STable statement by defining a STable table structure and using it to create subtables:
CREATE TABLE thermometer (ts timestamp, degree double)
TAGS(location binary(20), type int)
Copy the code
According to the thermometer (s timestamp, degree double), the TAGS(location,type) are binary(20) and int (20); If there are 4 collectors in Beijing, Tianjin and Shanghai, and there are 3 types of temperature collectors, we can create a table for each collector as follows:
CREATE TABLE therM1 USING Thermometer TAGS (' Beijing ', 1); CREATE TABLE Therm2 USING Thermometer TAGS (' Beijing ', 2); CREATE TABLE TherM3 USING Thermometer TAGS (' Tianjin ', 1); CREATE TABLE therm4 USING Thermometer TAGS (' Shanghai ', 3);Copy the code
Mysql > select * from supertable; mysql > select * from supertable;
select count(*) from s_01020110 group by tables_name;
Copy the code
For now…