Unable to join data in TICK script

BhanuKiran · January 29, 2019, 10:15pm

I have below TICK script

// Parameters
var period = 10s
var every = 10s
var tolerance = 10d

// Dataframe
// check - http://192.168.0.126:9092/kapacitor/v1/tasks/voc_stats/step1
// check - http://192.168.0.5:9092/kapacitor/v1/tasks/voc_stats/step1
var hourlyAverage = batch
|query('''SELECT mean(usage_user) AS usage_user FROM "telegraf"."autogen"."cpu"  WHERE cpu = 'cpu-total' ''')
    .period(1h)
    .every(every)
|httpOut('step1')

var weeklyAverage = batch
|query('''SELECT mean(usage_user) AS usage_user FROM "telegraf"."autogen"."cpu"  WHERE cpu = 'cpu-total' ''')
    .period(7d)
    .every(every)
|httpOut('step2')

var weeklyMinimum = batch
|query('''SELECT min(usage_user) AS usage_user FROM "telegraf"."autogen"."cpu"  WHERE cpu = 'cpu-total' ''')
    .period(7d)
    .every(every)
|httpOut('step3')

var data = hourlyAverage
|join(weeklyAverage, weeklyMinimum)
    .as('hourlyAverage', 'weeklyAverage', 'weeklyMinimum')
    .tolerance(8d)
|httpOut('step4')

I can observer the http ouput from step1,2&3 but step 4 is always outputting null series.
httpOut looks like this
step1 :
{"series":[{"name":"cpu","columns":["time","usage_user"],"values":[["2019-01-29T21:10:26.908059043Z",0.784185539493217]]}]}
Step2:
{"series":[{"name":"cpu","columns":["time","usage_user"],"values":[["2019-01-22T22:10:46.908007082Z",3.631224120920862]]}]}
Step3:
{"series":[{"name":"cpu","columns":["time","usage_user"],"values":[["2019-01-28T00:21:15Z",0.1752190237792004]]}]}
step4:
{"series":[{"name":"cpu"}]}

BhanuKiran · February 1, 2019, 3:15pm

@rawkode, @katy, @Anaisdg @philb Any comments on this ?

rawkode · February 1, 2019, 9:42pm

Should step 4 be:

var hourlyAverage | batch
|join(weeklyAverage, weeklyMinimum)
    .as('hourlyAverage', 'weeklyAverage', 'weeklyMinimum')
    .tolerance(8d)
|httpOut('step4')

BhanuKiran · February 1, 2019, 9:45pm

Hi @rawkode,

I just copy paste the script it says
invalid TICKscript: parser: unexpected | line 36 char 19 in "lyAverage | batch". expected: "identifier"

line 36 ishourlyAverage | batch

rawkode · February 1, 2019, 9:47pm

You’re right, that was sloppy copy-pasting on my part.

var hourlyAverage = join(weeklyAverage, weeklyMinimum)
    .as('hourlyAverage', 'weeklyAverage', 'weeklyMinimum')
    .tolerance(8d)
|httpOut('step4')

rawkode · February 1, 2019, 9:49pm

Wait, are you using TICK or Flux?

BhanuKiran · February 1, 2019, 9:50pm

Hi @rawkode
I am using TICK script editor from chronograf GUI.

Getting this errorinvalid TICKscript: line 28 char 6: cannot get properties of non pointer value

BhanuKiran · February 4, 2019, 7:55pm

@rawkode Any possible answer for this ? or Do need any extra information on this ? I am running docker from influxdb sandbox. Tried both./sandbox up and./sandbox up - nightly as well
I have tried almost the same script as this today and it seems not working as well

var every = 15s
var custom_period = 3h

// Dataframe
// check - http://192.168.0.123:9092/kapacitor/v1/tasks/Rule-1-cpu/step1
var hourlyAverage = batch
    |query('''SELECT mean(usage_user) AS usage_user FROM "telegraf"."autogen"."cpu" WHERE cpu = 'cpu-total' ''')
        .period(1h)
        .every(every)
        .fill('none')
        .align()
    |httpOut('step1')

// Custom hours ago average
var customAverage = batch
    |query('''SELECT mean(usage_user) AS usage_user FROM "telegraf"."autogen"."cpu" WHERE cpu = 'cpu-total' ''')
        .period(1h)
        .every(every)
        .fill('none')
        .align()
        // Query data from custom time ago
        .offset(custom_period)
    |shift(custom_period)
    |httpOut('step2')

var weeklyAverage = batch
    |query('''SELECT mean(usage_user) AS usage_user FROM "telegraf"."autogen"."cpu" WHERE cpu = 'cpu-total' ''')
        .period(7d)
        .every(every)
        .fill('none')
        .align()
    |httpOut('step3')

var weeklyMinimum = batch
    |query('''SELECT min(usage_user) AS usage_user FROM "telegraf"."autogen"."cpu" WHERE cpu = 'cpu-total' ''')
        .period(7d)
        .every(every)
        .fill('none')
        .align()
    |httpOut('step4')

var data = hourlyAverage
    |join(customAverage, weeklyAverage, weeklyMinimum)
        .as('hourlyAverage', 'customAverage', 'weeklyAverage', 'weeklyMinimum')
        .tolerance(2w)
    |httpOut('step5')

Here i have similar outputs
step 1:
{"series":[{"name":"cpu","columns":["time","usage_user"],"values":[["2019-02-04T18:51:30Z",5.510724480730352]]}]}
step 2:
{"series":[{"name":"cpu","columns":["time","usage_user"],"values":[["2019-02-04T18:51:30Z",3.297008825595237]]}]}
step 3:
{"series":[{"name":"cpu","columns":["time","usage_user"],"values":[["2019-01-28T19:51:45Z",4.021326806086728]]}]}
step 4:
{"series":[{"name":"cpu","columns":["time","usage_user"],"values":[["2019-02-04T07:04:30Z",0.17513134851138412]]}]}
step5:
{"series":[{"name":"cpu"}]}

I have tried plenty of permutation and combination but nothing giving me the result, i really appreciate if can provide me a way around.

Thanks

rawkode · February 5, 2019, 6:50pm

Hi @BhanuKiran,

Let me run some tests and I’ll get back to you as soon as I can.

Sorry for the delay

rawkode · February 6, 2019, 7:03pm

Hi @BhanuKiran,

This appears to be a way the join handles the batched points, but this can be fixed by utilising last().

var period = 10s

var every = 10s

var tolerance = 10d

var hourlyAverage = batch
    |query('''SELECT mean(usage_user) AS usage_user FROM "telegraf"."autogen"."cpu"  WHERE cpu = 'cpu-total' ''')
        .period(1h)
        .every(every)
    |last('usage_user')
        .as('usage_user')

var weeklyAverage = batch
    |query('''SELECT mean(usage_user) AS usage_user FROM "telegraf"."autogen"."cpu"  WHERE cpu = 'cpu-total' ''')
        .period(7d)
        .every(every)
    |last('usage_user')
        .as('usage_user')

hourlyAverage
    |join(weeklyAverage)
        .as('hourlyAverage', 'weeklyAverage')
        .tolerance(1s)

Thanks for your patience, hope this helps!

BhanuKiran · February 6, 2019, 8:08pm

Thanks @rawkode .
It’s an amazing work around. I have gone through many post but none of them explained this trick. I am also happy that this also solved my another issue of time stamping. In below example, although join node is succeeded but it has an issue with the time stamp.

// Parameters
var every = 10s

// Dataframe
// check - http://ip/kapacitor/v1/tasks/test/step1

// Past one week average
var weeklyAverage = batch
    |query('''SELECT mean(voc) AS voc_0015BC0036000018 FROM "sensordata"."autogen"."voc"  WHERE eui = '0015BC0036000018' ''')
        .period(7d)
        .every(every)
        .groupBy('eui')
        .align()
    |httpOut('step1')

// Past one week minimum
var weeklyMinimum = batch
    |query('''SELECT mean(voc) AS voc_0015BC0036000018 FROM "sensordata"."autogen"."voc"  WHERE eui = '0015BC0036000018' ''')
        .period(7d)
        .every(every)
        .groupBy('eui')
        .align()
    |httpOut('step2')

var data = weeklyAverage
    |join(weeklyMinimum)
        .as('weeklyAverage', 'weeklyMinimum')
        .tolerance(2w)
    |httpOut('step3')

data
	|influxDBOut()
    	.database('telegraf')
        .retentionPolicy('autogen')
        .measurement('cpu_stats')

If see the http output
step#1
{"series":[{"name":"voc","tags":{"eui":"0015BC0036000018"},"columns":["time","voc_0015BC0036000018"],"values":[["2019-01-30T19:54:10Z",107.27860696517413]]}]}
step#2
{"series":[{"name":"voc","tags":{"eui":"0015BC0036000018"},"columns":["time","voc_0015BC0036000018"],"values":[["2019-01-30T19:54:10Z",107.27860696517413]]}]}
step#3
{"series":[{"name":"voc","tags":{"eui":"0015BC0036000018"},"columns":["time","weeklyAverage.voc_0015BC0036000018","weeklyMinimum.voc_0015BC0036000018"],"values":[["2019-01-28T00:00:00Z",107.27860696517413,107.27860696517413]]}]}

I observe the time stamp in step 3 its always “2019-01-28T00:00:00Z” in every single execution. Effectively Influxdb out node is overwriting the same data point every single time of its execution.

But now i think its will form time series, rather than over writing the same data point again and again.

Thanks, really appreciated.

nated099 · October 17, 2019, 7:38pm

I’ve been searching for this solution for weeks. This should really be put on influxdata joinnode page as a sidenote or something. Thank you!

Topic		Replies	Views
Help Debugging Join in tick sctript Kapacitor	3	757	March 12, 2019
Tick script unable to output data into the influxdb and raise alert Kapacitor kapacitor	1	958	January 27, 2019
Problem with evaluation node - TICK script kapacitor	1	731	February 3, 2019
Kapacitor: ShiftNode and Join in Stream	3	1435	June 26, 2017
Kapacitor Join is not working with Group clause	2	2071	June 19, 2018

Unable to join data in TICK script

Related topics