TL;DR InfluxDB Tech Tips - InfluxQL Support for Subqueries, CLI Support for Using a Retention Policy, and More

By Regan Kuchan / Product, Developer
Jan 19, 2017

Navigate to:

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

InfluxDB first executes the subquery and calculates the difference between the dogs and cats fields in the pet_daycare measurement. Next, InfluxDB calculates the average of those differences.

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 FROM clause!

The example below tells the CLI to use the life database and the non-DEFAULT 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

What's next:

  • 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.