TL;DR InfluxDB Tech Tips - Comparing Data With Kapacitor

By Regan Kuchan / Developer
Jun 09, 2016

Navigate to:

In this post we recap the week’s most interesting InfluxDB and TICK-stack related issues, workarounds in comparing data with Kapacitor, how-tos and Q&A from GitHub, IRC and the InfluxDB Google Group that you might have missed.

Querying data with identical tag keys and field keys

Q: I’m able to write data successfully, but I keep getting an empty response when I attempt to specify a tag in the WHERE clause. Any recommendations?

Write the data:

> INSERT candied,almonds=true almonds=50,half_almonds=51 1465317610000000000
> INSERT candied,almonds=true almonds=55,half_almonds=56 1465317620000000000

See that they’re all there:

> SELECT * FROM "candied"
name: candied
-------------
time                   almonds  almonds_1  half_almonds
2016-06-07T16:40:10Z   50       true       51
2016-06-07T16:40:20Z   55       true       56

Specify a tag in the WHERE clause:

> SELECT * FROM "candied" WHERE "almonds"='true'
> SELECT * FROM "candied" WHERE "almonds_1"='true'
>

A: You’re encountering issues because your schema includes a matching tag key and field key.

Use the syntax almonds::tag='true' to specify the almonds tag in the WHERE clause.

> SELECT * FROM "candied" WHERE "almonds"::tag='true'
name: candied
-------------
time                   almonds  almonds_1  half_almonds
2016-06-07T16:40:10Z   50       true       51
2016-06-07T16:40:20Z   55       true       56

To avoid this kind of query confusion, we recommend removing the identical tag key and field key from your schema. Depending on the rest of your data and how you plan on querying the data, something like the following schema would work:

> INSERT candied,status=full almonds=50 1465317610000000000
> INSERT candied,status=half almonds=51 1465317610000000000
> INSERT candied,status=full almonds=55 1465317620000000000
> INSERT candied,status=half almonds=56 1465317620000000000
> SELECT * FROM "candied"
name: candied
-------------
time                   almonds  status
2016-06-07T16:40:10Z   50       full
2016-06-07T16:40:10Z   51       half
2016-06-07T16:40:20Z   55       full
2016-06-07T16:40:20Z   56       half

Using Kapacitor to compare data in time

Q: I’m trying to use Kapacitor to compare data from the previous week to data from the current week. I do this by creating two vars (one for last week’s data and one for this week’s data), joining the vars, and writing the joined data back into InfluxDB.

The only problem is that join always uses time as the join key - and, of course, the data in the two vars have different timestamps. Is there a way to change the join key or otherwise combine the two sets of data?

Here’s my TICKscript:

var this_week = batch
  | query('SELECT mean("cups") FROM "caffeine"."default"."coffee_consumption"')
    .period(7d)
    .groupBy(time(1d))
    .every(30m)

var last_week = batch
  | query('SELECT mean("cups") FROM "caffeine"."default"."coffee_consumption"')
    .period(7d)
    .groupBy(time(1d))
    .every(30m)
    .offset(7d)

this_week
  |join(last_week)
   .as('this_week','last_week')
  |influxDBOut()
   .database('caffeine')
   .retentionPolicy('default')
   .measurement('joined_coffee_data')

A: Yes! Use shift to make last week’s timestamps match this week’s timestamps and then perform the join:

var this_week = batch
  | query('SELECT mean("cups") FROM "caffeine"."default"."coffee_consumption"')
    .period(7d)
    .groupBy(time(1d))
    .every(30m)

var last_week = batch
  | query('SELECT mean("cups") FROM "caffeine"."default"."coffee_consumption"')
    .period(7d)
    .groupBy(time(1d))
    .every(30m)
    .offset(7d)
  // Shift the data to match this week's data ?
  | shift(7d)

this_week
  |join(last_week)
   .as('this_week','last_week')
  |influxDBOut()
   .database('caffeine')
   .retentionPolicy('default')
   .measurement('joined_coffee_data')

Casting functionality

Q: I wrote all my data as floats but I’d like my queries to return integers. Is there any way to do this with InfluxQL?

A: Cast your floats to integers using the <field_key>::<type> syntax.

For example:

> INSERT blueberries value=20.890
> INSERT blueberries value=34.00812
> INSERT blueberries value=12.90

> SELECT "value"::integer FROM "blueberries"
name: blueberries
-----------------
time                             value
2016-06-07T18:43:52.875822901Z   20
2016-06-07T18:43:59.18983841Z    34
2016-06-07T18:44:07.778144875Z   12

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?