The supertable is a concept introduced by TDengine that enables aggregation operations across tables and makes your time-series database (TSDB) easier to manage.
Introduction
A supertable is essentially a kind of template that you use to create the tables for your data collection points, known as subtables. Because TDengine creates one table per data collection point, the total number of tables in a database can become extremely high. By using supertables, you can easily manage the creation of new tables and perform queries across all subtables within a supertable.
In TDengine, instead of creating tables from scratch, you first create one supertable for each type of data collection point in your environment. Then you create the subtable for each specific data collection point using the template defined by the supertable. Data is not stored in the supertable; it is only a template that defines the schema for its subtables. Queries that you run on the supertable return results for all of the subtables under it.
When you create a supertable, you define metric and tag fields that apply to all subtables under the supertable:
- Metrics are the measurements that the data collection point collects – for example, the temperature in a weather monitoring scenario or the CPU usage in a DevOps scenario.
- Tags are static attributes that describe a data collection point – for example, its location, device manufacturer, or serial number.
Note that you can add, remove, and modify tags after a supertable has been created. Your changes take effect on all subtables within the supertable.
The subtables in a supertable all have the same data schema, but each has its own specific tag values. The following figure shows an example of a supertable for a smart meters scenario.
In the figure, the supertable meters was created with four metric fields (timestamp, current, voltage, and phase) – and two tag fields (location and groupID). This represents a type of data collection point – in this case, a smart meter. Under the meters supertable, three subtables have been created: d1001, d1002, and d1004. Each subtable represents a specific smart meter.
You can query the supertable using tag values as filtering conditions – for example, in the supertable shown in the figure, you could query the average current of all meters whose location is California.SanFrancisco. This provides a significant performance boost because TDengine can filter out any subtables that do not meet the specified conditions without scanning the data in those tables.
Supertable SQL Statements
TDengine supports standard SQL with extensions for time-series applications. You can create, delete, and modify your supertables with SQL statements. For example, the following statement creates the meters supertable shown in the preceding figure:
CREATE STABLE meters (ts TIMESTAMP, current FLOAT, voltage INT, phase FLOAT) TAGS (location BINARY(64), groupId INT);In this statement, CREATE STABLE meters creates a supertable named meters. The following parameter defines its metric fields – a column named ts of type TIMESTAMP, a column named current of type FLOAT, a column named voltage of type INT, and a column named phase of type FLOAT. The TAGS keyword defines the tag fields for the supertable – in this case a column named location of type BINARY (with a maximum length of 64 bits) and a column named groupId of type INT.
Note that the first column of every table in TDengine must use the TIMESTAMP data type. TDengine uses this column as the primary key. For more information about the data types that TDengine supports, see Data Types.
Now that you have created a supertable, you can use the supertable to create the subtables for your data collection points. For example, the following statement created the d1001 subtable shown under meters in the preceding figure.
CREATE TABLE d1001 USING meters TAGS ("California.SanFrancisco",2);In this statement, the USING keyword specifies a supertable to use as a template for the newly created table – in this case, the meters supertable. The TAGS keyword specifies the value of each tag field for the subtable. As the meters supertable contains two tag fields – location and groupId – this CREATE TABLE statement indicates that the location of the data collection point d1001 is California.SanFrancisco and the group ID is 2. When you execute this statement, the subtable is created with the same data schema – four metric columns and two tag columns – defined in the supertable.
With each of your data collection points storing data in a separate subtable contained within the same supertable, you can easily perform all kinds of queries on your data. You can filter by tag to obtain exactly the results you need without experiencing any performance deterioration.
For technical information about supertables, see Supertable in the TDengine documentation.
