What are continuous queries, how do I use continuous queries, what are the application scenarios of continuous queries - Lindorm

Continuous query is a simplified stream computing capability that can periodically execute SQL queries and store the query results in the specified data table. You can use continuous queries in pre-downsampling and pre-computing scenarios to downsample data for long-term storage and better query performance. This topic describes the concept, usage, and application scenarios of continuous queries in LindormTSDB.

Background information

In scenarios that involves real-time time series data that is written to the database in chronological order, you may want to process the time series data at a scheduled interval based on the specified query conditions and store the results. For example, you can aggregate data that is written to the database within a specific window at a scheduled interval. Continuous queries are suitable for these scenarios.

The following figure shows a sample scenario in which time series data is aggregated at a scheduled interval.

Overview

Continuous queries provide simplified stream computing capability that can be used to periodically execute SQL queries on time series data and store the query results in the specified data table.

A continuous query calculates time series within a time window at a scheduled interval.

For example, if the interval and time window of a continuous query are set to 1 hour, the continuous query calculate the data of the previous hour at the beginning of each hour. If the calculation is triggered at 20:00, time series data that is written to the database within the following time range is calculated: [19:00:00, 20:00:00).

Important

A continuous query triggers calculation at the scheduled interval. However, the calculation results may be stored to the specified after a period of time based on the size of the data to be calculated and the real-time workload of the instance.
The accuracy of the results of a continuous query depends on whether the data is written in chronological order. If data is not written to the original time series table in chronological order, data within the previous time window may be still written sporadically to the table after the start of the next time window, the continuous query does not recalculate the historical data before the current time window.
You can perform a continuous query without specifying a time window. In this case, the time window is the same as the interval specified for the continuous query.

Use continuous queries

Manage continuous queries

LindormTSDB allows you to use SQL to manage continuous queries in your databases.

Create a continuous query
You can create a continuous query in the specified database. For more information about the SQL syntax, see CREATE CONTINUOUS QUERY.
Note
- Continuous queries are associated with specific databases. When you delete a database, continuous queries created in the database are automatically deleted.
- If you do not specify a database when you create a continuous query, the query is created in the current database specified by the USE DATABASE syntax.
Delete a continuous query
You can delete an existing continuous query from the specified database. For more information about the SQL syntax, see DROP CONTINUOUS QUERY.
View the information about a continuous query
You can query the metadata of a continuous query For more information about the SQL syntax, see SHOW CONTINUOUS QUERIES.

Common scenarios and examples

Data downsampling and long-term storage

In scenarios that involve large amounts of data, the storage costs of the data become a critical issue. LindormTSDB allows you to configure a data time-to-live (TTL) for each database. You can use continuous queries to downsample data and write the result to a database with a longer TTL. The following examples show how to use continuous queries and database TTLs to implement data downsampling and long-term storage.

Examples

In the following example, the original data is sampled at an interval of 1 second. In this case, large amounts of data is sampled and stored. Only the data generated within the latest month can be stored. If you set the interval to a minute, data sampled within the latest year can be stored.

Create a database named db_sensor_month to store original time series data and set the TTL of the database to 30 days.
```
CREATE DATABASE db_sensor_month WITH (ttl=30);
```

Create a table named sensor in the db_sensor_month database.

USE db_sensor_month;

CREATE TABLE sensor (
    device_id VARCHAR TAG,
    region VARCHAR TAG,
    time TIMESTAMP,
    temperature DOUBLE,
    humidity BIGINT);

Create another database named db_sensor_year to store downsampled data and set the TTL of the database to 365 days.
```
CREATE DATABASE db_sensor_year WITH (ttl=365);
```

Create a table named db_sensor_year.sensor to store the downsampled data.

USE db_sensor_year;

CREATE TABLE sensor (
    device_id VARCHAR TAG,
    region VARCHAR TAG,
    time TIMESTAMP,
    temperature DOUBLE,
    humidity BIGINT);

Create a continuous query to execute downsamling queries and write the downsampled data to the db_sensor_year.sensor table.

CREATE CONTINUOUS QUERY db_sensor_year.my_cq WITH(`interval`='1m')
AS
  INSERT into db_sensor_year.sensor(time, temperature, humidity, device_id,region)
  SELECT time, avg(temperature) as temperature, avg(humidity) humidity, device_id, region
  FROM db_sensor_month.sensor
  sample by 60s;

Write data to the original time series data table db_sensor_month.sensor.

insert into db_sensor_month.sensor(region,device_id,time,temperature,humidity) values ('hz', 'id123', current_timestamp, 37, 70);
insert into db_sensor_month.sensor(region,device_id,time,temperature,humidity) values ('hz', 'id123', current_timestamp, 38, 67);

Query the downsampled data in the db_sensor_year.sensor table.

select * from db_sensor_year.sensor;

The following results is returned:

+-----------+--------+---------------------------+-------------+----------+
| device_id | region |           time            | temperature | humidity |
+-----------+--------+---------------------------+-------------+----------+
| id123     | hz     | 2023-10-08T19:54:00+08:00 | 37.500000   | 68.5     |
+-----------+--------+---------------------------+-------------+----------+

Implement pre-computing to improve query performance

If you perform downsampling queries or aggregate queries on data within a long time window, the query results may take a long period of time to be returned. In this case, you can use continuous query to implement pre-computing on the original data and directly query the calculated data in the result table. This help you improve the performance and throughput of queries.