TL;DR InfluxDB Tech Tips - Kapacitor Performing Queries
By Regan Kuchan / Jun 30, 2016 / Tech Tips
In this post we recap the week’s most interesting InfluxDB and TICK-stack related issues, workarounds Kapacitor performing queries, how-tos and Q&A from GitHub, IRC and the InfluxDB Google Group that you might have missed.
Automate successive aggregations with Kapacitor
Q: I want to automatically execute a query every minute that aggregates data for the past 24 hours and also increments the query’s 24 hour time range by a minute. I attempted to use a Continuous Query but I don’t think it can do what I want - the CQ executes every minute but it always queries the same 24 hour time period. Is what I want to do possible with a CQ? Is there another way to do this?
Here are a couple sample queries that I’d like to automate:
SELECT mean(water_pressure) INTO mean_pressures FROM sea WHERE time >= '2016-06-29T00:00:00Z' AND time <= '2016-06-30T00:00:00Z' SELECT mean(water_pressure) INTO mean_pressures FROM sea WHERE time >= '2016-06-29T00:01:00Z' AND time <= '2016-06-30T00:01:00Z'
A: You’re right, Continuous Queries currently can’t do what you want them to do. We recommend using Kapacitor to automatically perform the queries and write the results back into InfluxDB. Sample TICKscript:
batch |query('SELECT mean(water_pressure) FROM science."autogen".sea') .period(1d) .every(1m) .align() |influxDBOut() .database('science') .retentionPolicy('autogen') .measurement('mean_pressures') .precision('s')
Writing integers to InfluxDB
Q: I’m writing what I think are integers to the database, but the
SHOW FIELD KEYS query says they’re floats. How do I write integers to the database?
> INSERT cheese gouda=1 > SHOW FIELD KEYS name: cheese ------------ fieldKey fieldType gouda float
A: By default, InfluxDB assumes that all numerical field value are floats. To write an integer to the database, append an
i to the field value in your Line Protocol:
> INSERT cheese gouda=1i > SHOW FIELD KEYS name: cheese ------------ fieldKey fieldType gouda integer
Writing vs querying identifiers with double quotes
Q: I can successfully write data to InfluxDB but my queries return empty results. What am I doing wrong?
> INSERT "roses" "crepuscule"=8 > SHOW MEASUREMENTS name: measurements ------------------ name "roses" > SELECT crepuscule FROM roses > SELECT "crepuscule" FROM "roses" >
A: If you double quote an identifier in your Line Protocol, InfluxDB assumes that those quotes are part of the identifier name. To query an identifier that has double quotes in its name, you must both escape its double quotes and double quote the name:The following query should be faster:
> SELECT "\"crepuscule\"" FROM "\"roses\"" name: "roses" ------------- Time "crepuscule" 2016-06-29T20:54:46.926056779Z 8
In general, we recommend that you do not double quote identifiers in Line Protocol.
For more InfluxDB tips, check out our Frequently Asked Questions page and feel free to post your questions in the InfluxDB users group.
- Looking for InfluxDB clustering on your infrastructure? Get started with InfluxDB Enterprise Beta, now available for evaluation.
- Download and get started with InfluxDB 1.0 Beta 2
- Schedule a FREE 20 minute consultation with a Solutions Architect to review your InfluxDB project
- Attend one of our FREE virtual training seminars.