TL;DR InfluxDB Tech Tips - Moving data & Getting Un-queryable Tags, Querying Data Based on Function Results & More
By Regan Kuchan / Jan 05, 2017 / 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 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_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.
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
> SELECT ELAPSED("green",1w) FROM "tea" name: tea time elapsed ---- ------- 2017-01-05T00:14:00Z 0
- 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.