Lindorm:Continuous query

Create a continuous query

Use CREATE CONTINUOUS QUERY to create a continuous query in a database.

Continuous queries are scoped to a database and are deleted automatically when the database is deleted. If you do not specify a database in the statement, the query is created in the current database set by the USE DATABASE statement.

Delete a continuous query

Use DROP CONTINUOUS QUERY to delete a continuous query from a database.

View continuous queries

Use SHOW CONTINUOUS QUERIES to list continuous queries and their metadata.

Downsample data for long-term storage

At a 1-second sampling interval, a database can only retain roughly one month of data. By downsampling to 1-minute averages and writing those results to a separate database with a longer TTL (time-to-live), you extend retention to a year while reducing storage costs.

The following example sets up two databases—one for raw data with a 30-day TTL and one for downsampled data with a 365-day TTL—and connects them with a continuous query.

Step 1. Create the source database to hold raw sensor data. The 30-day TTL ensures that high-frequency raw data does not accumulate indefinitely.

CREATE DATABASE db_sensor_month WITH (ttl=30);

Step 2. Create the source table in db_sensor_month.

USE db_sensor_month;

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

Step 3. Create the target database to store downsampled data. The 365-day TTL lets you retain long-term trends at a lower storage cost.

CREATE DATABASE db_sensor_year WITH (ttl=365);

Step 4. Create the target table in db_sensor_year with the same schema.

USE db_sensor_year;

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

Step 5. Create a continuous query that runs every minute, averages the raw readings, and writes the results to db_sensor_year.sensor. The sample by 60s clause groups data into 60-second buckets.

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;

Step 6. Write raw data to the source table to trigger the continuous query.

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);

Step 7. Query the downsampled results in the target table.

select * from db_sensor_year.sensor;

Expected output:

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

Pre-compute aggregations to speed up queries

Aggregate queries over large time windows can be slow. Run a continuous query to pre-compute the aggregations and write the results to a result table. Subsequent queries hit the pre-computed result table instead of scanning the raw data, improving query performance and throughput.