How do I use Lindorm SQL to create a continuous query - Lindorm

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

Parameters

Database name (database_identifier)

The database_identifier parameter specifies the database in which data is queried. If you do not specify this parameter, data in the current database is queried by default. The database name must be enclosed by a pair of backticks (`). Example: `db_sensor`.

Continuous query name (cq_identifier)

The continuous query name must be enclosed by a pair of backticks (`). 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 and must be in the following format: `%d%h%m%s`. For example, the value 1h30s specifies a time window of 1 hour and 30 seconds, and the value 1d specifies a time window of 1 hour. The characters in the format indicate different units of time: d: days h: hours m: minutes s: seconds
window	STRING	No	The time window of the data. Data that is updated within the latest specified time window is used by the calculation tasks in the continuous query. If you do not specify this attribute, its value is the same as the value of the interval attribute. For example, if you set the `interval` attribute to 10m and the window attribute to 20m, calculation tasks in the continuous query are performed on a 10-minute basis and each calculation task uses data within the latest 20 minutes. Data that is updated at the last point in time of the window is not used by the current calculation task. The value of this attribute must be in the following format: `%d%h%m%s`. For example, the value 1h30s specifies a time window of 1 hour and 30 seconds, and the value 1d specifies a time window of 1 hour. The characters in the format indicate different units of time: d: days h: hours m: minutes s: seconds
offset	STRING	No	The offset of the data time window. By default, the data time window is specified based on the UNIX epoch `1970-01-01 00:00:00 UTC`. You can specify the offset attribute to change the point in time based on which the data time window is specified. For example, if you set interval to 1d and offset to 16h, the calculation tasks in the continuous query are performed at 00:00 (UTC+8) on a daily basis. The value of this attribute must be in the following format: `%d%h%m%s`. For example, the value 1h30s specifies an offset of 1 hour and 30 seconds, and the value 1d specifies an offset of 1 hour. The characters in the format indicate different units of time: 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.

Examples

The following example shows how to create a continuous query. In this example, a continuous query is created to calculate the hourly average temperature and humidity within the latest two hours based on the data in the original table named sensor. The calculation results are inserted to another table named sensor in the default database.

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