TL;DR InfluxDB Tech Tips - InfluxQL Query Solutions on the SELECT Clause, Field Keys, and Selector Function

By Regan Kuchan / Developer
Dec 01, 2016

Navigate to:

In this weekly post we recap the most interesting InfluxQL query and TICK-stack related issues, workarounds, how-tos and Q&A from GitHub, IRC and the InfluxDB Google Group that you might have missed in the last week or so.

Regular expressions in the SELECT clause

Q: I’m specifying multiple specific field keys in the SELECT clause - is there any way to do this without having to list them all out?

My current query:

SELECT user_name,user_id,user_email[,...] FROM all_the_data

A: Starting with version 1.1, the SELECT clause supports regular expressions. Something like the following query might work, depending on your data:

SELECT /user/ FROM all_the_data

Check out the Data Exploration page or this InfluxQL short for more on the SELECT clause.

Workaround for dropping individual field keys

Q: Is there any way to drop specific field keys from a measurement? I see that there’s DROP SERIES, DELETE, and DROP MEASUREMENT , but I can’t find anything about dropping field keys.

A: Currently, InfluxQL doesn’t have a query for dropping specific field keys from a measurement. As a workaround, you can write the field keys that you want to keep to a different measurement, drop the original measurement, and then write the saved field keys back to the original measurement.

Raw data:

> SELECT * FROM "holidays"
name: holidays
time                  TOREMOVE  happiness_index self_worth user_id <--- this is a tag
----                  --------  --------------- ---------- -------
2016-11-30T20:05:48Z  0         20              10         1
2016-11-30T20:06:30Z  0         12              0.9        1
2016-11-30T20:07:13Z  0         13              4          1

Write the necessary field keys to a different measurement (holidays_copy):

> SELECT "happiness_index","self_worth" INTO "holidays_copy" FROM "holidays" GROUP BY *
name: result
time			written
----			-------
1970-01-01T00:00:00Z	3

> SELECT * FROM "holidays_copy"
name: holidays_copy
time                  happiness_index  self_worth  user_id
----                  ---------------  ----------  -------
2016-11-30T20:05:48Z  20               10          1
2016-11-30T20:06:30Z  12               0.9         1
2016-11-30T20:07:13Z  13               4           1

Drop the original measurement:

> DROP MEASUREMENT "holidays"
>

Write the saved field keys back to the original measurement:

> SELECT * INTO "holidays" FROM "holidays_copy" GROUP BY *
name: result
time                  written
----                  -------
1970-01-01T00:00:00Z  3

> SELECT * FROM "holidays"
name: holidays
time                   happiness_index  self_worth user_id
----                   ---------------  ---------- -------
2016-11-30T20:05:48Z   20               10         1
2016-11-30T20:06:30Z   12               0.9        1
2016-11-30T20:07:13Z   13               4          1

Be sure to include GROUP BY * in your INTO queries to preserve any tags as tags.

Selector functions in depth

Q: I’m having trouble understanding how a selector function, like LAST(), behaves when I run the two queries below. Will query 1 return the last value of fungi_index and the value of tree_age at that time? And for query 2, are the LAST calculations independent and what timestamp would that query return if the last values of fungi_index and tree_age occur at different times?

Query 1:

SELECT LAST("fungi_index"),"tree_age" FROM "forest"

Query 2:

SELECT LAST("fungi_index"),LAST("tree_age") FROM "forest"

A: You’re right about query 1; it does return the last value of fungi_index and the value of tree_age at that time. You can see that in the column headers of the returned data. The header for last value of fungi_index is last and the header for the value of tree_age is tree_age.

Query 2 returns a single point which includes the last value of fungi_index and the last value of tree_age, independent of the last value of fungi_index. The timestamp for that point is 1970-01-01T00:00:00Z (InfluxDB’s null timestamp equivalent) because InfluxDB can’t return more than one timestamp for a single point.

Raw data:

> SELECT * FROM forest
name: forest
time                  fungi_index   tree_age
----                  ------------  --------
2016-11-30T21:43:00Z  12            85
2016-11-30T21:44:00Z  34            172
2016-11-30T21:45:00Z  8

Query 1:

> SELECT LAST("fungi_index"),"tree_age" FROM "forest"
name: forest
time                  last  tree_age
----                  ----  --------
2016-11-30T21:44:00Z  34    172

Query 2:

> SELECT LAST("fungi_index"),LAST("tree_age") FROM "forest"
name: forest
time                  last  last_1
----                  ----  ------
1970-01-01T00:00:00Z  34    8

For more InfluxDB tips, see our Frequently Asked Questions page and feel free to post your questions in the InfluxDB users group!

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