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:
|
---|
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.