TL;DR InfluxDB Tech Tips - Customizing GROUP by Time() Windows & Series Cardinality
By Regan Kuchan / May 13, 2016 / Tech Tips
In this post we recap the week’s most interesting InfluxDB and TICK-stack related issues, workarounds with customizing GROUP by time() windows & series cardinality, how-tos and Q&A from GitHub, IRC and the InfluxDB Google Group that you might have missed.
Regular expressions in the WHERE clause
Q: I’m trying to write a query with a wildcard in the
WHERE clause and I can’t seem to get it to work.
I’m able to use a regex in the
> SELECT mean("species") FROM /rainforest*/ WHERE "location" = 'q01'
But this query returns an error:
> SELECT mean("species") FROM /rainforest*/ WHERE "location" = /q*/ ERR: error parsing query: found /, expected identifier, string, number, bool at line 1, char 62
A: InfluxQL requires regular expressions in the
WHERE clause. You need to use the proper operator for regex, which is
=~ for positive matches, and
!~ for negative matches.
> SELECT mean("species") FROM /rainforest*/ WHERE "location" =~ /q*/
Series cardinality and retention policies
Q: Do retention policies affect series cardinality?
I’m trying to limit my series cardinality for performance purposes. I’m planning on having several retention policies and I’d like to know if that has an effect on series cardinality.
A: Retention policies do not multiply series cardinality as the series index only applies to unique measurement and tagset combinations. Whether a measurement-tagset combination is in every retention policy or just one, it’s still only a single series for the index, and thus for the cardinality concerns.
Customizing GROUP BY time() windows
Q: Is there any way to change the default time windows returned by a
GROUP BY time() query?
A: Starting with InfluxDB version 0.13, you can alter
GROUP BY time()’s default rounded calendar time boundaries by including an offset interval.
The syntax is:
SELECT [...] GROUP BY time(time_interval,offset_interval)
See Data Exploration for a more detailed description and examples.
- Download and get started with InfluxDB v0.13
- Schedule a FREE 20 minute consultation with a Solutions Architect to review your InfluxDB project
- Attend one of our FREE virtual training seminars.
- Looking for InfluxDB Clustering on your infrastructure? Contact Sales to get a demo of InfluxDB Enterprise plus pricing information.