可观测性-Metrics-存储-InfluxDb连续查询（CQ）Continuous Queries

连续查询 (CQ) 是 InfluxQL 查询，可在实时数据上 自动并定期运行，并 将查询结果存储在指定的度量指标中。

基本语法

CREATE CONTINUOUS QUERY <cq_name> ON <database_name>
BEGIN
  <cq_query>
END

cq_query：需要一个函数、一个INTO子句和一个GROUP BY time()子句.

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

• 注意：cq_query中不需要时间范围。InfluxDB 自动生成cq_query执行 CQ 的时间范围。系统将忽略cq_query子句中任何用户指定的时间范围。
• cq_query以间隔相同的间隔执行，并且它们在 InfluxDB 数据库预设时间边界的开始处运行。如果GROUP BY time()间隔为一小时，则 CQ 在每小时开始时执行。
• 当 CQ 执行时，它会运行单个查询以获取介于now()和now()减去 GROUP BY time()间隔之间的时间范围。如果GROUP BY time()时间间隔为一小时，当前时间为 17:00，则查询的时间范围为 16:00 到 16:59.999999999。

示例

CREATE CONTINUOUS QUERY "cq_basic" ON "transportation"
BEGIN
  SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(1h)
END

可以选择存储在非默认保留策略中。

CREATE CONTINUOUS QUERY "cq_basic_rp" ON "transportation"
BEGIN
  SELECT mean("passengers") INTO "transportation"."three_weeks"."average_passengers" FROM "bus_data" GROUP BY time(1h)
END

原指标数据

name: bus_data
--------------
time                   passengers   complaints
2016-08-28T07:00:00Z   5            9
2016-08-28T07:15:00Z   8            9
2016-08-28T07:30:00Z   8            9
2016-08-28T07:45:00Z   7            9
2016-08-28T08:00:00Z   8            9
2016-08-28T08:15:00Z   15           7
2016-08-28T08:30:00Z   15           7
2016-08-28T08:45:00Z   17           7
2016-08-28T09:00:00Z   20           7

结果如下

name: average_passengers
------------------------
time                   mean
2016-08-28T07:00:00Z   7
2016-08-28T08:00:00Z   13.75

高级语法

CREATE CONTINUOUS QUERY <cq_name> ON <database_name>
RESAMPLE EVERY <interval> FOR <interval>
BEGIN
  <cq_query>
END

• EVERY：以与子句中的时间间隔相同的时间间隔执行RESAMPLE 。

如果EVERY间隔是两小时，InfluxDB 每隔一小时执行一次 CQ。

• FOR：指定 CQ 的时间范围的长度。

示例

示例一

CREATE CONTINUOUS QUERY "cq_advanced_every" ON "transportation"
RESAMPLE EVERY 30m
BEGIN
  SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(1h)
END

cq_advanced_every以 30 分钟的间隔执行。每 30 分钟cq_advanced_every运行一次查询，该查询涵盖当前时间段的时间范围，即与 相交的一小时时间段now()。

8:00  `cq_advanced_every` executes a query with the time range `WHERE time >= '7:00' AND time < '8:00'`.
8:30  `cq_advanced_every` executes a query with the time range `WHERE time >= '8:00' AND time < '9:00'`.
9:00  `cq_advanced_every` executes a query with the time range `WHERE time >= '8:00' AND time < '9:00'`.

name: average_passengers
------------------------
time                   mean
2016-08-28T07:00:00Z   7
2016-08-28T08:00:00Z   13.75

示例二

CREATE CONTINUOUS QUERY "cq_advanced_for" ON "transportation"
RESAMPLE FOR 1h
BEGIN
  SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(30m)
END

8:00 `cq_advanced_for` executes a query with the time range `WHERE time >= '7:00' AND time < '8:00'`.
8:30 `cq_advanced_for` executes a query with the time range  `WHERE time >= '7:30' AND time < '8:30'`.
9:00 `cq_advanced_for` executes a query with the time range `WHERE time >= '8:00' AND time < '9:00'`.

name: average_passengers
------------------------
time                   mean
2016-08-28T07:00:00Z   6.5
2016-08-28T07:30:00Z   7.5
2016-08-28T08:00:00Z   11.5
2016-08-28T08:30:00Z   16

示例三

CREATE CONTINUOUS QUERY "cq_advanced_every_for" ON "transportation"
RESAMPLE EVERY 1h FOR 90m
BEGIN
  SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(30m)
END

8:00 `cq_advanced_every_for` executes a query with the time range `WHERE time >= '6:30' AND time < '8:00'`.
9:00 `cq_advanced_every_for` executes a query with the time range `WHERE time >= '7:30' AND time < '9:00'`.

name: average_passengers
------------------------
time                   mean
2016-08-28T06:30:00Z   3
2016-08-28T07:00:00Z   6.5
2016-08-28T07:30:00Z   7.5
2016-08-28T08:00:00Z   11.5
2016-08-28T08:30:00Z   16

总结

EVERY 1h、FOR 90m、GROUP BY time(30m)的解释如下：

• EVERY 1h： 每60分钟执行一次CQ。
• FOR 90m：查询now() - now() - 90m的范围数据。
• GROUP BY time(30m): 按照30分钟聚合数据。