[feature request] backreference for measurement/series when making CQs

[liyichao]

I try to create downsampling series:

> create continuous query "1_mins_rollup" on collectd begin select mean(value) into "1_week".:series_name from /^[a-z]+.*/ group by time(1m) end
ERR: error parsing query: found :, expected identifier at line 1, char 92

It seems regex is not supported in influxdb 0.9 now. Then I have to create a continuous query for every measurement or series. It is too tedious.

[beckettsean]

The regex is supported but the :series_name backreference is not currently wired up.

[pauldix]

Yeah, we should do this. But it should be :measurement instead.

[srikara]

+1

[vptheron]

@beckettsean when you say "The regex is supported but the :series_name backreference is not currently wired up." what does it mean exactly? That you can query an existing series using a regex or that you can also reference regex groups to dynamically generate the downsampled series (as mentioned in the linked issue #297 ) ?

Is there any way today to dynamically create continuous queries and avoid creating one CQ per existing measurement?

[beckettsean]

@vptheron You can use regex. You cannot use any form of regex backreference like :measurement_name

I am aware of no current way to create CQs dynamically. That's why the backreference is needed.

[torkelo]

One thing I am not sure about is how to do a roll up of my 10s data into 1m series, keep all tags for each series (series = measurement and unique tag set).

CREATE CONTINUOUS QUERY myquery ON testdb 
BEGIN 
   SELECT mean(value) INTO "policy1".:measurement FROM /^[a-z]+.*/ GROUP BY time(1m) 
END

So this looks nice, the measurement back reference will allow for a generic rollup rule. But wont the query above (taken from your tests) loose all tag data? Since there are no group by tag it looks like it will be no tags in the target measurement.

And if the group by tag needs to be added to the select or group by clause in order to keep tag data in the target series of the CQ then this feature is incomplete or pointless. At least it does not seem to solve the case of creating generic rollup rules. Or am I missing something for how tags are preserved in the target series of a CQ?

I really would like a way for InfluxDB users to create generic rollup rules, rules that then Grafana can use to pick the right retention policy. Right now InfluxDB users use no rollups and get a horrible and slow experience, would be awesome to solve this :)

ping @pauldix any ideas?

[beckettsean]

Use GROUP BY * to group by all tags without having to reference them
specifically.

Precision thumbed for accuracy

[torkelo]

@beckettsean that is great, need to try 0.9.4-rc1 !!

[jeanpralo]

Hi guys,

@beckettsean, I am currently using 0.9.4.1 was wondering if those sorts of query are suppose to work in that version since I have a panic for this query:

CREATE CONTINUOUS QUERY "5mn_avg"
ON chv2
BEGIN
 SELECT mean(value) as value
 INTO "5mn_avg".:measurement
 FROM /load-load/
 GROUP BY time(5m), host
END

[continuous_querier] 2015/09/22 03:49:03 executing continuous query 5mn_avg
[query] 2015/09/22 03:49:03 SELECT mean(value) AS "value" INTO "chv2"."5mn_avg".:MEASUREMENT FROM "chv2"."default"./load-load/ WHERE time >= '2015-09-22T03:45:00Z' AND time < '2015-09-22T03:50:00Z' GROUP BY time(5m), host
panic: runtime error: invalid memory address or nil pointer dereference
[signal 0xb code=0x1 addr=0x20 pc=0x624233]

goroutine 64 [running]:
github.com/influxdb/influxdb/cluster.(_PointsWriter).MapShards(0xc82010cd00, 0xc8205bda40, 0x9, 0x0, 0x0)
/tmp/build/src/github.com/influxdb/influxdb/cluster/points_writer.go:179 +0x1f3
github.com/influxdb/influxdb/cluster.(_PointsWriter).WritePoints(0xc82010cd00, 0xc8205bda40, 0x0, 0x0)
/tmp/build/src/github.com/influxdb/influxdb/cluster/points_writer.go:215 +0x1d9
github.com/influxdb/influxdb/services/continuous_querier.(_Service).runContinuousQueryAndWriteResult(0xc820130240, 0xc820352400, 0x0, 0x0)
/tmp/build/src/github.com/influxdb/influxdb/services/continuous_querier/service.go:385 +0x93e
github.com/influxdb/influxdb/services/continuous_querier.(_Service).ExecuteContinuousQuery(0xc820130240, 0xc82066fce8, 0xc8201cfc40, 0xecd92c6af, 0x218089f9, 0xffb6e0, 0x0, 0x0)
/tmp/build/src/github.com/influxdb/influxdb/services/continuous_querier/service.go:292 +0x8a2
github.com/influxdb/influxdb/services/continuous_querier.(_Service).runContinuousQueries(0xc820130240, 0xc82066fe88)
/tmp/build/src/github.com/influxdb/influxdb/services/continuous_querier/service.go:224 +0x3c0
github.com/influxdb/influxdb/services/continuous_querier.(_Service).backgroundLoop(0xc820130240)
/tmp/build/src/github.com/influxdb/influxdb/services/continuous_querier/service.go:203 +0x543
created by github.com/influxdb/influxdb/services/continuous_querier.(*Service).Open
/tmp/build/src/github.com/influxdb/influxdb/services/continuous_querier/service.go:126 +0x2a4

Obviously I would like to avoid to create one CQ per measurement.

[otoolep]

This looks like the trace fixed by
#4136

This fix would be available in a nightly build, since it went in after
0.9.4.1.

https://influxdb.com/download/index.html

On Mon, Sep 21, 2015 at 9:04 PM, Jean Praloran notifications@github.com
wrote:

Hi guys,

@beckettsean https://github.com/beckettsean, I am currently using
0.9.4.1 was wondering if those sorts of query are suppose to work in that
version since I have a panic for this query:

CREATE CONTINUOUS QUERY "5mn_avg"
ON chv2
BEGIN
SELECT mean(value) as value
INTO "5mn_avg".:measurement
FROM /load-load/
GROUP BY time(5m), host
END

[continuous_querier] 2015/09/22 03:49:03 executing continuous query 5mn_avg
[query] 2015/09/22 03:49:03 SELECT mean(value) AS "value" INTO
"chv2"."5mn_avg".:MEASUREMENT FROM "chv2"."default"./load-load/ WHERE time

= '2015-09-22T03:45:00Z' AND time < '2015-09-22T03:50:00Z' GROUP BY
time(5m), host
panic: runtime error: invalid memory address or nil pointer dereference
[signal 0xb code=0x1 addr=0x20 pc=0x624233]

goroutine 64 [running]:
github.com/influxdb/influxdb/cluster.(

_PointsWriter).MapShards(0xc82010cd00, 0xc8205bda40, 0x9, 0x0, 0x0)
/tmp/build/src/github.com/influxdb/influxdb/cluster/points_writer.go:179
http://github.com/influxdb/influxdb/cluster/points_writer.go:179 +0x1f3
github.com/influxdb/influxdb/cluster.(
http://github.com/influxdb/influxdb/cluster.(_PointsWriter).WritePoints(0xc82010cd00,
0xc8205bda40, 0x0, 0x0)
/tmp/build/src/github.com/influxdb/influxdb/cluster/points_writer.go:215
+0x1d9
github.com/influxdb/influxdb/services/continuous_querier.(

_Service).runContinuousQueryAndWriteResult(0xc820130240, 0xc820352400,
0x0, 0x0)
/tmp/build/src/github.com/influxdb/influxdb/services/continuous_querier/service.go:385
http://github.com/influxdb/influxdb/services/continuous_querier/service.go:385
+0x93e github.com/influxdb/influxdb/services/continuous_querier.(
http://github.com/influxdb/influxdb/services/continuous_querier.(_Service).ExecuteContinuousQuery(0xc820130240,
0xc82066fce8, 0xc8201cfc40, 0xecd92c6af, 0x218089f9, 0xffb6e0, 0x0, 0x0)
/tmp/build/src/
github.com/influxdb/influxdb/services/continuous_querier/service.go:292
+0x8a2
github.com/influxdb/influxdb/services/continuous_querier.(

Service).runContinuousQueries(0xc820130240, 0xc82066fe88)
/tmp/build/src/github.com/influxdb/influxdb/services/continuous_querier/service.go:224
http://github.com/influxdb/influxdb/services/continuous_querier/service.go:224
+0x3c0 github.com/influxdb/influxdb/services/continuous_querier.(
http://github.com/influxdb/influxdb/services/continuous_querier.(
Service).backgroundLoop(0xc820130240)
/tmp/build/src/
github.com/influxdb/influxdb/services/continuous_querier/service.go:203
+0x543
created by
github.com/influxdb/influxdb/services/continuous_querier.(*Service).Open
/tmp/build/src/
github.com/influxdb/influxdb/services/continuous_querier/service.go:126
+0x2a4

Obviously I would like to avoid to create one CQ per measurement.

—
Reply to this email directly or view it on GitHub
#2555 (comment).

[jeanpralo]

@otoolep thanks for that ! it was the problem and it is working on the nightly build !