influxdata / influxdata/kapacitor

Joins not working on single row result set with large tolerance set

Open
#965 2 comments 0 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
2.4k
Forks
479
Avg merge
4d 16h
Merged PRs (30d)
4

Description

I am trying to setup a TICK script to store Holt-Winters prediction for the next 90 minutes, but I want to execute and store this value every 5 minutes. The end-goal is a real time graph of the current value and some percentage of the min/max of the holtWinters values.

Due to how the HW functions works, the time returned is always a factor of the predicted time range, so a continuous query will not work as my data points keep being overwritten.

The only way it seems to accomplish this is use the JOIN function, but there seems to be some issue joining two single rows.

1) Is there a better way to do this?
2) Is there any additional DEBUG options to see what is occurring in the JOIN section?

Thank-you.

What I am trying to get:

```
> select * from jointesting
name: jointesting
-----------------
time LAST.last HW.holtWinter
2016-10-11T21:11:16Z 12886 11443.49538959047
2016-10-11T21:12:18Z 12612 10443.123
```

```
[hw_join:query1] 2016/10/11 16:17:14 D! starting next batch query: SELECT last(value) FROM my_database.autogen."Session Counts" WHERE time >= '2016-10-11T20:17:14.870527724Z' AND time < '2016-10-11T21:17:14.870527724Z'
[hw_join:query3] 2016/10/11 16:17:14 D! starting next batch query: SELECT mean(value) FROM my_database.autogen."Session Counts" WHERE time >= '2016-09-06T21:17:14.870523151Z' AND time < '2016-10-11T21:17:14.870523151Z' GROUP BY time(90m, 0s)
[hw_join:log2] 2016/10/11 16:17:14 I! {"name":"Session Counts","tmax":"2016-10-11T16:17:14.870527724-05:00","points":[{"time":"2016-10-11T21:11:16Z","fields":{"last":12886},"tags":null}]}
[hw_join:log5] 2016/10/11 16:17:18 I! {"name":"Session Counts","tmax":"2016-10-11T22:30:00Z","points":[{"time":"2016-10-11T22:30:00Z","fields":{"holtWinters":11443.49538959047},"tags":null}]}
```

```
ID: hw_join
Error:
Template:
Type: batch
Status: enabled
Executing: true
Created: 11 Oct 16 10:10 CDT
Modified: 11 Oct 16 15:59 CDT
LastEnabled: 11 Oct 16 15:59 CDT
Databases Retention Policies: ["my_database"."autogen" "volumetric"."autogen"]
TICKscript:
var max_time = batch
|query('select last("value") from "my_database"."autogen"."Session Counts"')
.period(60m)
.every(1m)
|log()

var hw_calc = batch
|query('select mean("value") from "my_database"."autogen"."Session Counts"')
.groupBy(time(90m))
.period(35d)
.every(1m)
|holtWinters('mean', 1, 16, 90m)
|log()

max_time
|join(hw_calc)
.as('LAST', 'HW')
.tolerance(120m)
.fill(0.0)
|log()
|influxDBOut()
.database('volumetric')
.retentionPolicy('autogen')
.measurement('jointesting')

DOT:
digraph hw_join {
graph [throughput="0.00 batches/s"];

query3 [avg_exec_time_ns="15.547712ms" batches_queried="17" connect_errors="0" points_queried="9516" query_errors="0" ];
query3 -> holtWinters4 [processed="17"];

holtWinters4 [avg_exec_time_ns="3.627368367s" ];
holtWinters4 -> log5 [processed="17"];

log5 [avg_exec_time_ns="0" ];
log5 -> join7 [processed="17"];

query1 [avg_exec_time_ns="10.216113ms" batches_queried="17" connect_errors="0" points_queried="17" query_errors="0" ];
query1 -> log2 [processed="17"];

log2 [avg_exec_time_ns="0" ];
log2 -> join7 [processed="17"];

join7 [avg_exec_time_ns="6.757µs" ];
join7 -> log8 [processed="0"];

log8 [avg_exec_time_ns="0" ];
log8 -> influxdb_out9 [processed="0"];

influxdb_out9 [avg_exec_time_ns="0" points_written="0" write_errors="0" ];
}

```

Contributor guide

Open the contributing guide

Research direction

Start with the shown TICKscript's join() stage, its 120-minute tolerance, and the DOT output showing join7 processed zero batches. Reproduce the batch with the logged single-row query results and check whether the join emits the expected combined points; done means the join produces the requested LAST and HW fields and writes rows to jointesting.

Written by the indexing model from the issue text.

Assessment

Tech stack
go
Domain
data
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
28/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.