TL;DR InfluxDB Tech Tips - Kapacitor Performing Queries

By Regan Kuchan / Developer
Jun 30, 2016

Navigate to:

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.

What's next?