Skip to main content

SQL Manual

This document explains the syntax rules, main query functions, supported SQL query functions, and common techniques supported by TDengine SQL. Readers are expected to have a basic understanding of SQL language. TDengine version 3.0 has made significant improvements and optimizations compared to version 2.x, especially with a complete overhaul of the query engine, thus there are many changes in SQL syntax compared to version 2.x. For detailed changes, please see the 3.0 Syntax Changes section.

TDengine SQL is the primary tool for users to write data and perform queries on TDengine. TDengine SQL provides standard SQL syntax and has optimized and added many syntax features and functions specific to time-series data and business needs. The maximum length of a TDengine SQL statement is 1M. TDengine SQL does not support abbreviations of keywords, for example, DELETE cannot be abbreviated as DEL.

This section follows the conventions below for SQL syntax:

  • Keywords are represented in uppercase letters, but SQL itself does not distinguish between the case of keywords and identifiers
  • Lowercase letters indicate content that needs to be entered by the user
  • [ ] indicates optional content, but you cannot enter [] itself
  • | indicates a choice among multiple options, choose one, but you cannot enter | itself
  • … indicates that the previous item can be repeated multiple times

To better illustrate the rules and characteristics of SQL syntax, this document assumes the existence of a dataset. Taking smart meters as an example, assume each smart meter collects three quantities: current, voltage, and phase. Its modeling is as follows:

taos> DESCRIBE meters;
Field | Type | Length | Note |
=================================================================================
ts | TIMESTAMP | 8 | |
current | FLOAT | 4 | |
voltage | INT | 4 | |
phase | FLOAT | 4 | |
location | BINARY | 64 | tag |
groupid | INT | 4 | tag |

The dataset includes data from 4 smart meters, according to TDengine's modeling rules, corresponding to 4 subtables, named d1001, d1002, d1003, d1004.

πŸ“„οΈ Tag Indices

This section explains the indexing mechanism of TDengine. Prior to version 3.0.3.0 (exclusive), an index is created by default on the first column tag, but it does not support dynamically adding indexes to other columns. Starting from version 3.0.3.0, indexes can be dynamically added to other tag columns. The index automatically created on the first tag column is enabled by default in queries, and users cannot intervene in any way. Proper use of indexes can effectively improve query performance.

πŸ“„οΈ Manage Nodes

The physical entities that make up a TDengine cluster are dnodes (short for data nodes), which are processes running on top of the operating system. Within a dnode, vnodes (virtual nodes) can be established for storing time-series data. In a multi-node cluster environment, when the replica of a database is 3, each vgroup in that database consists of 3 vnodes; when the replica is 1, each vgroup consists of 1 vnode. To configure a database with multiple replicas, there must be at least 3 dnodes in the cluster. In a dnode, an mnode (management node) can also be created, with a maximum of three mnodes in a single cluster. In TDengine 3.0.0.0, to support separation of storage and computation, a new logical node called qnode (query node) was introduced, which can either coexist with a vnode in the same dnode or be completely separated on different dnodes.

πŸ“„οΈ Metadata

TDengine has a built-in database called INFORMATIONSCHEMA, which provides access to database metadata, system information, and status, such as the names of databases or tables, the SQL statements currently being executed, etc. This database stores information about all other databases maintained by TDengine. It contains multiple read-only tables. In fact, these tables are views, not base tables, so there are no files associated with them. Therefore, you can only query these tables, and cannot perform write operations like INSERT. The INFORMATIONSCHEMA database is designed to provide access to the information provided by various SHOW statements supported by TDengine (such as SHOW TABLES, SHOW DATABASES) in a more consistent way. Compared to SHOW statements, using SELECT ... FROM INFORMATION_SCHEMA.tablename has the following advantages: