TL;DR InfluxDB Tech Tips - Moving data & Getting Un-queryable Tags, Querying Data Based on Function Results & More

By Regan Kuchan / Product, Developer
Jan 05, 2017

Navigate to:

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 InfluxDB Google Group that you might have missed in the last week or so.

Moving data and getting un-queryable tags

Q: I’m using an INTO query to move data to a different retention policy (RP) in the same measurement. The data appear to be in the new RP but: 1) I’m seeing what seems like a duplicate tag key with an _1 appended to it and 2) I can no longer group query results by tag in the new RP.

Any idea what’s going on here?

# Original data

> SELECT * FROM "trees"."autogen"."concentration"
name: concentration
time                  chlorophyll  id              <---- id is a tag
----                  -----------  --
2017-01-04T21:35:48Z  10           a
2017-01-04T21:36:48Z  6            b

# INTO query

> SELECT * INTO "trees"."one_day"."concentration" FROM "trees"."autogen"."concentration"
name: result
time                  written
----                  -------
1970-01-01T00:00:00Z  2                           <---- seems to work!

# New data

> SELECT * FROM "trees"."one_day"."concentration"
name: concentration
time                  chlorophyll  id  id_1       <---- empty duplicate tag?      
----                  -----------  --  ----
2017-01-04T21:35:48Z  10           a
2017-01-04T21:36:48Z  6            b

# GROUP BY tag on the new data

> SELECT * FROM "trees"."one_day"."concentration" GROUP BY "id"
name: concentration
tags: id=                                         <---- doesn't seem to recognize id as a tag
time                  chlorophyll  id
----                  -----------  --
2017-01-04T21:35:48Z  10           a
2017-01-04T21:36:48Z  6            b

A: While it may seem odd, what you’re seeing is the expected behavior. By default, INTO queries turn any tags in the original data to fields in the destination data. When you run your INTO query, the concentration measurement ends up with a tag key id and a field key id (that’s why you’re seeing id and id_1 in the query results). In addition, you can’t GROUP BY id on the newly-written data because id is a field and InfluxQL doesn’t support GROUPing by fields.

Just include GROUP BY id in your INTO query to keep id as a tag in the new RP. In general, we recommend that you always include GROUP BY * in your INTO queries as that clause preserves all tags in the original data as tags in the destination data.

Querying data based on function results

Q: I’m trying to identify points that are written to my database at longer-than-expected intervals. More specifically, I want to find points that are written more than seven minutes after the previous point. I’ve tried the query below but it’s not supported in InfluxQL; do you know of a way to do this?

My dream query:

> SELECT "blinks" FROM "reactions" WHERE ELAPSED("blinks") > 7m

A: You’re right, as of version 1.1 you can’t perform that query. As a workaround, you can get the results you want in two steps. First, use an INTO query to write the results of the ELAPSED() function to your database. Second, query those ELAPSED() results and include your seven-minute condition.

# Step 1

> SELECT ELAPSED("blinks",1m) AS "elapsed_blinks" INTO "elapsed_reactions" FROM "reactions"
name: result
time                   written
----                   -------
1970-01-01T00:00:00Z   3

# Step 2

> SELECT "elapsed_blinks" FROM "elapsed_reactions" WHERE "elapsed_blinks" > 7
name: elapsed_reactions
time                  elapsed_blinks
----                  --------------
2017-01-04T21:18:00Z  8

Using ELAPSED() when unit > elapsed time

Q: I’ve started working with the ELAPSED() function, and I’m wondering what happens if I specify a unit that’s greater than the difference between the timestamps. So what does InfluxDB do if I specify 1w in the function and the points are only one minute apart? Will I get an error?

A: InfluxDB returns 0 if the unit option is greater than the difference between the timestamps.

The timestamps in the tea measurement occur at one-minute intervals. If the query asks for the number of weeks between the timestamps, InfluxDB returns 0:

> SELECT ELAPSED("green",1w) FROM "tea"
name: tea
time                  elapsed
----                  -------
2017-01-05T00:14:00Z  0

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.