TL;DR InfluxDB Tech Tips - InfluxQL Support for Subqueries, CLI Support for Using a Retention Policy, and More
By Regan Kuchan / Jan 19, 2017 / InfluxDB, Tech Tips
In this post we preview some of the new features that will be released with InfluxDB 1.2. Check in next week for more on the most interesting InfluxDB and TICK-stack related issues, workarounds, how-tos and Q&A from GitHub, IRC and the InfluxDB Google Group.
InfluxQL support for subqueries
Starting with version 1.2, InfluxQL’s
SELECT statements support subqueries. A subquery is a query that is nested in the
FROM clause of another query. You can use a subquery to apply a query as a condition in the enclosing query.
The following query with a subquery calculates the average difference between the number of dogs and cats:
> SELECT MEAN("difference") FROM (SELECT "dogs" - "cats" AS "difference" FROM "pet_daycare") name: pet_daycare time mean ---- ---- 1970-01-01T00:00:00Z 9
Note that in previous versions of InfluxDB that calculation would have required you to execute two separate queries.
CLI support for using a retention policy
In prior versions of InfluxDB’s Command Line Interface (CLI), the only way query a non-
DEFAULT retention policy was to fully qualify the measurement name in the
FROM clause. Starting with version 1.2, the CLI supports specifying an alternative retention policy in the
USE command with the syntax:
USE <database_name>.<retention_policy_name>. Once you enter the alternative retention policy with the
USE command, there’s no need to include that retention policy in every
The example below tells the CLI to use the
life database and the non-
lemons retention policy:
> USE "life"."lemons" Using database life Using retention policy lemons > SELECT * FROM "glasses" LIMIT 1 name: glasses time lemonade peach_lemonade raspberry_lemonade ---- -------- -------------- ------------------ 2017-01-17T04:41:21.874459122Z 1 8 5
InfluxQL fix for SAMPLE(*)
In prior versions of InfluxDB,
SAMPLE(*,<N>) ignored fields with string values. That behavior has been fixed in version 1.2; now
SAMPLE(*,<N>) returns a random sample of
N points for every field in the measurement:
> SELECT SAMPLE(*,2) FROM "all_kinds" name: all_kinds time sample_bool sample_i_feel_heard sample_num ---- ----------- ------------------- ---------- 2017-01-17T05:17:34.843212275Z true yes 456 2017-01-17T05:18:14.298348463Z false me too! 4
- 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.