Using regular expressions (regex) in continuous query (and standard queries)

Telegraf
,
1

I’m trying to downsample all my data (in a specific database) with a continuous query including back-referencing as explained in https://docs.influxdata.com/influxdb/v1.7/query_language/continuous_queries/ . However, it does’nt work, because the regular expression /.*/ in the FROM clause seems to not find most measurements.

cq_oneyear      CREATE CONTINUOUS QUERY cq_oneyear ON openhab_db BEGIN SELECT mean(*), min(*), max(*) INTO openhab_d
b.oneyear.:MEASUREMENT FROM openhab_db.sevendays./.*/ GROUP BY time(15m), * END

So, first question: Am I right that /.*/ should match ALL measurements in openhab_db.sevendays ?

So, to play around, I’m using this query on CLI:

> SELECT mean(*), min(*), max(*) INTO openhab_db.oneyear.:MEASUREMENT FROM openhab_db.sevendays./.*/ WHERE time>=now
() - 6h GROUP BY time(5m), *
name: result
time    written
----    -------
0       0

Then without any INTO clause - and LAST as aggregator:

SELECT LAST(*) FROM openhab_db.sevendays./a.*/ WHERE time >= '2020-04-04T21:30:00Z' AND time < '2020-04-04T21:55:0
0Z' GROUP BY time(60m)

→ some measurements are shown:

name: aldoni_thled_State
time                    last_value
----                    ----------
1586034000000000000     lost

name: hzgctrl_State
time                    last_value
----                    ----------
1586034000000000000     ready

name: ian_tray_State
time                    last_value
----                    ----------
1586034000000000000     ready

[... some few more ..]

Then, I changed the regex to /a.*/ - from my understanding of regular expressions this should match all measurements starting with a.
However - it shows now more measurements - maybe all containing an a?:

The matched measurements now can be aggregated with MEAN, MIN, or MAX, so I use them for my example query (with LAST as aggregator it also shows the text states):

> SELECT MEAN(*),MAX(*),MIN(*) FROM openhab_db.sevendays./a.*/ WHERE time >= '2020-04-04T21:30:00Z' AND time < '2020
-04-04T21:55:00Z' GROUP BY time(60m)
name: Balkon_dewPoint
time                    mean_value              max_value       min_value
----                    ----------              ---------       ---------
1586034000000000000     0.9583999999999999      1.18            0.63

name: Balkon_humidity
time                    mean_value              max_value       min_value
----                    ----------              ---------       ---------
1586034000000000000     47.565599999999996      48.96           46.63

name: Balkon_pressure
time                    mean_value      max_value       min_value
----                    ----------      ---------       ---------
1586034000000000000     1027.282        1027.43         1027.2

name: Balkon_temperature
time                    mean_value              max_value       min_value
----                    ----------              ---------       ---------
1586034000000000000     11.719999999999995      11.97           11.53

[...many more ...]

What I’m doing wrong?

I’m using continuous queries with wildcards and back-referencing on another server without problems?

2

Ok, I found the answer:

SELECT only returns values of same datatype for same field name. If the first value “selected” is of type string, then only other values of type string are returned.

This is neither mentioned in documention of the SELECT statement nor in other, related documentation like the FAQ.

So, basically, the problem is the very (too) simple scheme OpenHAB uses for it’s “influxdb persistance” where all values are put into an extra measurement with one field named “value” and the datatype of the corresponding openhab item.

So, my solution is to select the field name “value” instead of * and to explicitly state the value’s datetype:

cq_sevendays    CREATE CONTINUOUS QUERY cq_sevendays ON openhab_db BEGIN SELECT mean(value::float) AS value, min(val
ue::float), max(value::float) INTO openhab_db.oneyear.:MEASUREMENT FROM openhab_db.sevendays./.*/ GROUP BY time(15m)
, * END
cq_oneyear      CREATE CONTINUOUS QUERY cq_oneyear ON openhab_db BEGIN SELECT mean(value::float) AS value, min(min::
float), max(max::float) INTO openhab_db.forever.:MEASUREMENT FROM openhab_db.oneyear./.*/ GROUP BY time(1d), * END

This leaves out the string (that represnents an enum) values - however, that’s exactly what I wanted anyhow - but originally planned to take care in a later step.