The supertable is a concept introduced by TDengine that enables aggregation operations across tables and makes your time-series database (TSDB) easier to manage.
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:
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
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
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 –
groupId – this
CREATE TABLE statement indicates that the location of the data collection point
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.