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.
ποΈ Data Types
Timestamp
ποΈ Databases
Create Database
ποΈ Tables
Create Table
ποΈ Supertables
Create Supertable
ποΈ Data Ingestion
Writing Syntax
ποΈ Data Querying
Query Syntax
ποΈ 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.
ποΈ Data Deletion
Deleting data is a feature provided by TDengine that allows users to delete data records from specified tables or supertables within a specified time period, facilitating the cleanup of abnormal data caused by device failures and other reasons.
ποΈ Functions
Single Row Functions
ποΈ Time-Series Extensions
TDengine, in addition to supporting standard SQL, also offers a series of specialized query syntaxes tailored for time-series business scenarios, which greatly facilitate the development of applications in time-series contexts.
ποΈ Data Subscription
Starting from TDengine 3.0.0.0, significant optimizations and enhancements have been made to the message queue to simplify user solutions.
ποΈ Stream Processing
Creating Stream Computing
ποΈ Operators
Arithmetic Operators
ποΈ JSON Data Type
Syntax Explanation
ποΈ Escape Characters
Escape Character Table
ποΈ Names & Limits
Naming Rules
ποΈ Reserved Keywords
Reserved Keywords
ποΈ 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:
ποΈ Performance Data
Starting from TDengine 3.0, a built-in database performance_schema is provided, which stores performance-related statistical data. This section details the tables and table structures within it.
ποΈ SHOW Commands
SHOW commands can be used to obtain brief system information. To get detailed metadata, system information, and status within the system, use the select statement to query tables in the INFORMATION_SCHEMA database.
ποΈ Users
User and permission management is a feature of TDengine Enterprise Edition. This section only discusses the basic user management part. To learn about and obtain comprehensive permission management features, please contact the TDengine sales team.
ποΈ Permissions
In TDengine, permission management is divided into user management, database authorization management, and message subscription authorization management. This section focuses on database authorization and subscription authorization.
ποΈ UDFs
In addition to the built-in functions of TDengine, users can also write their own function logic and integrate it into the TDengine system.
ποΈ TSMAs
To improve the performance of aggregate function queries with large data volumes, window pre-aggregation (TSMA Time-Range Small Materialized Aggregates) objects are created. By using fixed time windows to pre-calculate specified aggregate functions and storing the results, query performance is enhanced by querying these pre-calculated results.
ποΈ Troubleshooting
How to terminate problematic connections, queries, and transactions to restore the system to normal
ποΈ Syntax Changes in TDengine 3.0
Changes to SQL Basic Elements
ποΈ Join Queries
Join Concepts
ποΈ Data Compression
Starting from version 3.3.0.0, TDengine provides more advanced compression features, allowing users to configure whether to compress each column at the time of table creation, as well as the compression algorithm and compression level used.
ποΈ Views
Starting from TDengine 3.2.1.0, TDengine Enterprise Edition provides the functionality of views, which simplifies operations and enhances sharing capabilities among users.