Lindorm SQL create continuous query - Lindorm - Alibaba Cloud Documentation Center

Continuous queries (CQs) are automatically executed on time series data in LindormTSDB at the specified interval to efficiently store data for a long period of time. You can use the CREATE CONTINUOUS QUERY syntax to create a continuous query by configuring data writing rules.

Applicable engines and versions

The CREATE CONTINUOUS QUERY syntax is applicable only to all versions of LindormTSDB.

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

Usage notes

Database name (database_identifier)

The name of the database for the continuous query. If you do not specify a database, the current database is used. Enclose the database name in backticks (`). For example, `db_sensor`.

Continuous query name (cq_identifier)

The name of the continuous query. Enclose the continuous query name in backticks (`). For example, `my_cq`.

Continuous query configurations (cq_attribute_statement)

The cq_attribute_statement parameter specifies the attributes of the continuous query that you want to create. The following table describes the attributes that you can configure for the continuous query.

Attribute	Type	Required	Description
interval	STRING	Yes	The interval at which the calculation tasks in the continuous query are performed. The interval value can be accurate to seconds The format is `%d%h%m%s`. For example, 1h30s indicates 1 hour and 30 seconds. 1d indicates one day. Where: d: days h: hours m: minutes s: seconds
window	STRING	No	Specifies the time window for the calculation. This is the time range of data covered by each calculation. If not specified, the time window is the same as the interval. For example, `interval`='10m', `window`='20m' means the task runs every 10 minutes and each calculation is based on data from the last 20 minutes. The calculation window is a time interval that is inclusive of the start time and exclusive of the end time. The format is `%d%h%m%s`. For example, 1h30s indicates 1 hour and 30 seconds. 1d indicates one day. The characters in the format indicate different units of time: d: days h: hours m: minutes s: seconds
offset	STRING	No	Specifies the window offset. By default, time windows are aligned with `1970-01-01 00:00:00 UTC`. Use the offset parameter to change this alignment. For example, `interval`='1d', `offset`='16h' means the daily task runs at 00:00 Beijing time (UTC+8). The format is `%d%h%m%s`. For example, 1h30s indicates 1 hour and 30 seconds. 1d indicates one day. Where: d: days h: hours m: minutes s: seconds

Statement used to write data (insert_select_statement)

For more information about the syntax of insert_select_statement, see Write data.
When you specify insert_select_statement, you do not need to specify a time range for your query. In this case, LindormTSDB performs calculation tasks during the time interval when you run the query.
The number of columns in the SELECT clause must match the destination table, including both fields and tags.

Examples

The following example creates a continuous query. This query runs every hour to calculate the hourly average temperature and humidity for the past two hours from the source table sensor. The results are written to the sensor table in the default database.

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;