Continuous Query is a very useful feature for users to query time series data automatically and periodically on realtime data and store query results in a specified measurement according to the time interval and time range.

For example, using the continuous query described below, the IoTDB downsamples the CPU load metric, and keeps a single average datapoint per 15 minutes grouped by server region:

CREATE CONTINUOUS QUERY "downsampled_cpu_load"
ON database_name
BEGIN
  SELECT mean(value) as value,
  INTO "downsampled.cpu_load"
  FROM cpu_load
  GROUP BY time(15m), region
END

Syntax

1. Basic syntax

Description of basic syntax

1.1 The cq_query

The cq_query requires a function, an INTO clause, and a GROUP BY time() clause:

SELECT <function[s]> 
INTO <destination_measurement> 
FROM <measurement> 
[WHERE <stuff>] GROUP BY time(<interval>)[,<tag_key[s]>]

1.2 Schedule and coverage

Continuous queries operate on real-time data. They use the local server’s timestamp, the GROUP BY time() interval, and IoTDB database’s preset time boundaries to determine when to execute and what time range to cover in the query.

CQs execute at the same interval as the cq_query’s GROUP BY time() interval, and they run at the start of the IoTDB database’s preset time boundaries. If the GROUP BY time() interval is one hour, the CQ executes at the start of every hour.

When the CQ executes, it runs a single query for the time range between now() and now() minus the GROUP BY time() interval.  If the GROUP BY time() interval is one hour and the current time is 17:00, the query’s time range is between 16:00 and 16:59.999999999.

1.3 Common issues with basic syntax

1) Handling time intervals with no data

CQs do not write any results for a time interval if no data fall within that time range.

Note that the basic syntax does not support using fill() to change the value reported for intervals with no data. Basic syntax CQs ignore fill() if it’s included in the CQ query. A possible workaround is to use the advanced CQ syntax.

2) Resampling previous time intervals

The basic CQ runs a single query that covers the time range between now() and now() minus the GROUP BY time() interval. See the advanced syntax for how to configure the query’s time range.

3) Backfilling results for older data

CQs operate on realtime data, that is, data with timestamps that occur relative to now(). Use a basic INTO query to backfill results for data with older timestamps.

4) Missing tags in the CQ results

By default, all INTO queries convert any tags in the source measurement to fields in the destination measurement.

Include GROUP BY * in the CQ to preserve tags in the destination measurement.

2. Advanced syntax

Scheduling and coverage

CQs operate on real-time data. With the advanced syntax, CQs use the local server’s timestamp, the information in the RESAMPLE clause, and the IoTDB server’s preset time boundaries to determine when to execute and what time range to cover in the query.

CQs execute at the same interval as the EVERY interval in the RESAMPLE clause, and they run at the start of IoTDB’s preset time boundaries. If the EVERY interval is two hours, IoTDB executes the CQ at the top of every other hour.

When the CQ executes, it runs a single query for the time range between now() and now() minus the FOR interval in the RESAMPLE clause. If the FOR interval is two hours and the current time is 17:00, the query’s time range is between 15:00 and 16:59.999999999.

Both the EVERY interval and the FOR interval accept duration literals. The RESAMPLE clause works with either or both of the EVERY and FOR intervals configured. CQs default to the relevant basic syntax behavior if the EVERY interval or FOR interval is not provided.