Lindorm SQL create continuous query-Lindorm(Lindorm)-阿里云帮助中心

Manage LindormTSDB continuous queries to auto-aggregate time series data, list existing queries, and delete them.

Applicable engines and versions

Applies to LindormTSDB only. Supported by all versions.

CREATE CONTINUOUS QUERY

A continuous query (CQ) runs automatically on time series data in LindormTSDB at a fixed interval, aggregating recent data and writing the results to a destination table. Use CQs to downsample high-frequency data for long-term storage without manual intervention.

Syntax

create_cq_statement ::= CREATE CONTINUOUS QUERY [database_identifier.] cq_identifier
                         WITH ( cq_attribute_statement )
                         AS insert_select_statement

cq_attribute_statement ::= attribute_definition (',' attribute_definition)*
attribute_definition   ::= attr_identifier '=' attr_val

How it works

Each CQ runs on a fixed schedule controlled by three attributes:

`interval` — how often the CQ runs (required)
`window` — the time range of source data each run covers (defaults to interval)
`offset` — shifts the execution time relative to the UTC epoch (defaults to no shift)

At each scheduled execution, LindormTSDB determines the time range automatically based on these attributes and writes the aggregated results to the destination table. The write statement does not require an explicit time filter.

Parameters

database_identifier

The name of the database that owns the CQ. If omitted, the current database is used. Enclose the name in backticks. Example: ` db_sensor `.

cq_identifier

The name of the CQ. Enclose the name in backticks. Example: ` my_cq `.

cq_attribute_statement

Attributes that control how the CQ runs:

Attribute	Type	Required	Default	Description
`interval`	STRING	Yes	—	How often the CQ runs. The interval value can be accurate to seconds. Format: `%d%h%m%s` (d=days, h=hours, m=minutes, s=seconds). Example: `1h30s` = 1 hour 30 seconds.
`window`	STRING	No	Same as `interval`	The time range of source data each run covers. The window is inclusive of the start time and exclusive of the end time. Example: `window='20m'` with `interval='10m'` means each run covers the past 20 minutes of data.
`offset`	STRING	No	No offset (aligned to `1970-01-01 00:00:00 UTC`)	Shifts the execution time relative to the UTC epoch. Use this to align runs with a specific local time. Example: `interval='1d'` with `offset='16h'` runs the daily CQ at 00:00 UTC+8 (Beijing time).

insert_select_statement

The write statement that defines the aggregation logic and destination. Key constraints:

Do not include a time filter in the SELECT clause. LindormTSDB derives the time range from the CQ attributes automatically.
The number of columns in the SELECT clause must match the destination table, including all fields and tags.
For the full syntax, see Write data.

Example

The following CQ runs every hour and aggregates the past two hours of sensor data into hourly averages.

CREATE CONTINUOUS QUERY `default`.`my_cq`
WITH (`INTERVAL`='1h', `WINDOW`='2h')
AS
INSERT INTO `default`.`sensor`
SELECT AVG(`temperature`) AS `temperature`,
       AVG(`humidity`)    AS `humidity`,
       `device_id`,
       `region`
FROM   `default`.`sensor`
SAMPLE BY 1h;

What this CQ does at each run:

Calculates the average temperature and humidity, grouped by device_id and region.
Writes the results back to the sensor table in the default database.

SHOW CONTINUOUS QUERIES

Lists all continuous queries in the current database. To view continuous queries in a different database, use the USE statement to switch databases first.

Syntax

show_cq_statement ::= SHOW CONTINUOUS QUERIES

Returned results

Column name	Type	Description
name	VARCHAR	The name of the continuous query.
interval	VARCHAR	The interval at which the continuous query runs.
window	VARCHAR	The time window of the data. Only data updated within the latest time window is included in the continuous query's calculations.
offset	VARCHAR	The offset of the data time window.
query	VARCHAR	The statement executed in the continuous query.

Examples

List all continuous queries in the current database.

SHOW CONTINUOUS QUERIES;

The following result is returned:

+-------+----------+--------------------------------+
| name  | interval |             query              |
+-------+----------+--------------------------------+
| my_cq | 1d       | INSERT INTO                    |
|       |          | `db_sensor_year`.`sensor`      |
|       |          | (SELECT MEAN(`temperature`)  |
|       |          | AS `temperature`,              |
|       |          | MEAN(`humidity`) AS          |
|       |          | `humidity`, `device_id`,       |
|       |          | `region` FROM                  |
|       |          | `db_sensor_month`.`sensor`)    |
+-------+----------+--------------------------------+

DROP CONTINUOUS QUERY

Deletes a continuous query from a LindormTSDB database.

Syntax

DROP CONTINUOUS QUERY [database_identifier.cq_identifier]

Parameters

Parameter	Description
`database_identifier`	The name of the database that contains the continuous query. If omitted, defaults to the current database. Enclose the name in backticks. Example: ` db_sensor `
`cq_identifier`	The name of the continuous query to delete. Enclose the name in backticks. Example: ` my_cq `

Examples

Delete the continuous query my_cq from the db_sensor database:

DROP CONTINUOUS QUERY `db_sensor`.`my_cq`;