TL;DR InfluxDB Tech Tips - Advanced Conditionals in InfluxQL, Non-SELECT Queries in Chronograf, and Queries Across Measurements
By Regan Kuchan / Mar 30, 2017 / Chronograf, InfluxDB, Tech Tips
In this weekly post we recap the most interesting InfluxDB and TICK-stack related issues, workarounds, how-tos and Q&A from GitHub, IRC and the InfluxData Community that you might have missed in the last week or so.
Advanced conditionals in InfluxQL
Q: I want to calculate minimum values across distinct time intervals and only see results where the minimum value is greater than
0. Is there a way to do that with InfluxQL?
GROUP BY time() query:
> SELECT MIN("new-customers") FROM "coffee" WHERE time >= '2017-03-29T14:00:00Z' AND time < '2017-03-29T17:00:00Z' GROUP BY time(1h),"location" name: coffee tags: location=a time min ---- --- 2017-03-29T14:00:00Z 0 <--- I'd love to suppress that result 2017-03-29T15:00:00Z 1 2017-03-29T16:00:00Z 1 name: coffee tags: location=b time min ---- --- 2017-03-29T14:00:00Z 14 2017-03-29T15:00:00Z 15 2017-03-29T16:00:00Z 4
It’s invalid syntax but this is essentially what I’m looking for:
> SELECT MIN("new-customers") FROM "coffee" WHERE time >= '2017-03-29T14:00:00Z' AND time < '2017-03-29T17:00:00Z' AND MIN("new-customers") > 0 GROUP BY time(1h),"location" ERR: invalid expression: min("new-customers"::float) > 0
A: Yes! InfluxQL’s subqueries offer that functionality:
> SELECT "min" FROM (SELECT MIN("new-customers") FROM "coffee" WHERE time >= '2017-03-29T14:00:00Z' AND time < '2017-03-29T17:00:00Z' GROUP BY time(1h),"location") WHERE "min" > 0 GROUP BY "location" name: coffee tags: location=a time min ---- --- 2017-03-29T15:00:00Z 1 <--- No more 0! ???? 2017-03-29T16:00:00Z 1 name: coffee tags: location=b time min ---- --- 2017-03-29T14:00:00Z 14 2017-03-29T15:00:00Z 15 2017-03-29T16:00:00Z 4
The subquery (the bolded query that’s in the
FROM clause of the main query) is your original query. The main query places an additional condition on the results of that subquery. Check out the Data Exploration page for more information about subqueries and their use cases.
Non-SELECT queries and user management in Chronograf
Q: I’ve been using the web admin console to run
SHOW queries on my data and manage my users in InfluxDB. I’ve noticed that the web admin console has been deprecated and is expected to be removed in a future release. Are there any substitutions for the web admin console? If at all possible, I’d prefer not to use the HTTP API or the Command Line Interface.
A: Chronograf, InfluxData’s user interface for querying, visualizing, and monitoring your data, supports both
SHOW queries and user management. Check out the installation guide for how to get up and running with Chronograf.
An example of running a
SHOW query in Chronograf:
User management in Chronograf:
Chronograf is currently in beta and doesn’t support all of the functionality available in the web admin interface (yet!). If you have any issues or requests please feel free to open an issue. We’d love to get your feedback!
Queries across measurements
Q: I know I can query field keys with the same name across measurements; what happens if I query field keys that appear only in some of the measurements in the
FROM clause? Will the query just return an error?
A: Your query won’t return an error. InfluxDB doesn’t return results for a measurement if the field key doesn’t exist in that measurement. In the example below, every measurement except for
winter has the
flower-index field key. Notice that the query doesn’t return any results for
winter even though that measurement appears in the
> SELECT MEAN("flower-index") FROM "summer","fall","winter","spring" name: fall time mean ---- ---- 1970-01-01T00:00:00Z 4 name: spring time mean ---- ---- 1970-01-01T00:00:00Z 10 name: summer time mean ---- ---- 1970-01-01T00:00:00Z 8
- Downloads for the TICK-stack are live on our "downloads" page.
- Deploy on the Cloud: Get started with a FREE trial of InfluxDB Cloud featuring fully-managed clusters, Kapacitor and Grafana.
- Deploy on Your Servers: Want to run InfluxDB clusters on your servers? Try a FREE 14-day trial of InfluxDB Enterprise featuring an intuitive UI for deploying, monitoring, and rebalancing clusters, plus managing backups and restores.
- Tell Your Story: Over 300 companies have shared their story on how InfluxDB is helping them succeed. Submit your testimonial and get a limited edition hoodie as a thank you.