influxdata / influxdata/kapacitor
Joins not working on single row result set with large tolerance set
- 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
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