TDengine SQL

This section explains the syntax of SQL to perform operations on databases, tables and STables, insert data, select data and use functions. We also provide some tips that can be used in TDengine SQL. If you have previous experience with SQL this section will be fairly easy to understand. If you do not have previous experience with SQL, you’ll come to appreciate the simplicity and power of SQL. TDengine SQL has been enhanced in version 3.0, and the query engine has been rearchitected. For information about how TDengine SQL has changed, see Changes in TDengine 3.0.

TDengine SQL is the major interface for users to write data into or query from TDengine. It uses standard SQL syntax and includes extensions and optimizations for time-series data and services. The maximum length of a TDengine SQL statement is 1 MB. Note that keyword abbreviations are not supported. For example, DELETE cannot be entered as DEL.

Syntax Specifications used in this chapter:

  • Keywords are given in uppercase, although SQL is not case-sensitive.
  • Information that you input is given in lowercase.
  • means optional input, excluding [] itself.
  • | means one of a few options, excluding | itself.
  • … means the item prior to it can be repeated multiple times.

To better demonstrate the syntax, usage and rules of TDengine SQL, hereinafter it’s assumed that there is a data set of data from electric meters. Each meter collects 3 data measurements: current, voltage, phase. The data model is shown below:

  1. taos> DESCRIBE meters;
  2.              Field              |        Type        |   Length    |    Note    |
  3. =================================================================================
  4.  ts                             | TIMESTAMP          |           8 |            |
  5.  current                        | FLOAT              |           4 |            |
  6.  voltage                        | INT                |           4 |            |
  7.  phase                          | FLOAT              |           4 |            |
  8.  location                       | BINARY             |          64 | TAG        |
  9.  groupid                        | INT                |           4 | TAG        |

The data set includes the data collected by 4 meters, the corresponding table name is d1001, d1002, d1003 and d1004 based on the data model of TDengine.

📄️ Data TypesTDengine supports a variety of data types including timestamp, float, JSON and many others.

📄️ Databasecreate and drop database, show or change database parameters

📄️ TableCreate Table

📄️ SupertableCreate a Supertable

📄️ InsertSyntax

📄️ SelectSyntax

📄️ Delete DataDelete data from table or Stable

📄️ FunctionsSingle Row Functions

📄️ Time-Series ExtensionsAs a purpose-built database for storing and processing time-series data, TDengine provides time-series-specific extensions to standard SQL.

📄️ Data SubscriptionThe information in this document is related to the TDengine data subscription feature.

📄️ Stream ProcessingRaw time-series data is often cleaned and preprocessed before being permanently stored in a database. Stream processing components like Kafka, Flink, and Spark are often deployed alongside a time-series database to handle these operations, increasing system complexity and maintenance costs.

📄️ OperatorsArithmetic Operators

📄️ JSON TypeSyntax

📄️ Escape CharactersEscape Characters

📄️ Name and Size LimitsNaming Rules

📄️ Reserved KeywordsKeyword List

📄️ ClusterThe physical entities that form TDengine clusters are known as data nodes (dnodes). Each dnode is a process running on the operating system of the physical machine. Dnodes can contain virtual nodes (vnodes), which store time-series data. Virtual nodes are formed into vgroups, which have 1 or 3 vnodes depending on the replica setting. If you want to enable replication on your cluster, it must contain at least three nodes. Dnodes can also contain management nodes (mnodes). Each cluster has up to three mnodes. Finally, dnodes can contain query nodes (qnodes), which compute time-series data, thus separating compute from storage. A single dnode can contain a vnode, qnode, and mnode.

📄️ MetadataTDengine includes a built-in database named INFORMATIONSCHEMA to provide access to database metadata, system information, and status information. This information includes database names, table names, and currently running SQL statements. All information related to TDengine maintenance is stored in this database. It contains several read-only tables. These tables are more accurately described as views, and they do not correspond to specific files. You can query these tables but cannot write data to them. The INFORMATIONSCHEMA database is intended to provide a unified method for SHOW commands to access data. However, using SELECT … FROM INFORMATION_SCHEMA.tablename offers several advantages over SHOW commands:

📄️ StatisticsTDengine includes a built-in database named PERFORMANCESCHEMA to provide access to database performance statistics. This document introduces the tables of PERFORMANCESCHEMA and their structure.

📄️ SHOW StatementSHOW command can be used to get brief system information. To get details about metatadata, information, and status in the system, please use select to query the tables in database INFORMATION_SCHEMA.

📄️ Access ControlManage user and user’s permission

📄️ User-Defined FunctionsYou can create user-defined functions and import them into TDengine.

📄️ Error RecoveryIn a complex environment, connections and query tasks may encounter errors or fail to return in a reasonable time. If this occurs, you can terminate the connection or task.

📄️ Changes in TDengine 3.0This document explains how TDengine SQL has changed in version 3.0.