0

I'd like some help to modelize my problem in Esper:

I create measurements multiple times a day, At the end of every day, I would like to aggregate all the measurements of this type, grouped by source created that day and make a count and a sum and re-inject this in a new measurements. I would do the same at the end of the week and at the end of the month.

Problem is I don't know how to combine a cron expression (to say every day at midnight for example), a time window (to say I want all the event of the past day) and an event stream (to select measurements of the type I want).

Thanks for your insight.

Gaetan L.
  • 649
  • 6
  • 20

1 Answers1

0

Best approach I think is defining a context for the durations like this:

create context DailyMeasurementAggregation
  context DailySourcePartition 
    partition by measurement.source.value from MeasurementCreated,
  context DailyTimerPartition 
    start (0,0,*,*,*,0) 
    end (59,23,*,*,*,59);


context DailyMeasurementAggregation
select
  count(m) as count,
  sum(getNumber(m, "myMeasurement.M.value")) as sum
from MeasurementCreated m
where getObject(m, "myMeasurement.M") is not null
output last when terminated;

The cron syntax for the other ranges would be like this: weekly: start (0,0,,,0,0) end (59,23,,,6,59) monthly: start (0,0,1,,,0) end (59,23,last,,,59)

The weekly is Sunday to Saturday so maybe you need to adjust the numbers. Here is the part in the esper documentation about the cron syntax: http://www.espertech.com/esper/release-5.2.0/esper-reference/html/event_patterns.html#pattern-timer-at

I tried to do something like end after 1 day but this seems not to work because then there is a context only every other day. That's why I ended the context 1 second earlier.

TyrManuZ
  • 2,039
  • 1
  • 14
  • 23
  • Hello TyrManuZ, thanks for your answer again. I thought about that, the only problem I have with the idea is that in the case of a restart of the server all data in the context would be lost, and since I would have a pretty long context (a month), I think the risk for it to happen may be too high. – Gaetan L. Jul 06 '16 at 11:37
  • Cumulocity will soon support esperha which will restore the state of such statements after a server restart. This would solve the issue. – TyrManuZ Jul 06 '16 at 13:17
  • Only other option is to trigger on `pattern[every timer:at(0,0,*,*,*,0)]` and use the functions to query the whole data from database and then calculate the values – TyrManuZ Jul 06 '16 at 13:19
  • Then I'm back to square one cause I don't know how to combine this with a time window and an event stream ^^ But I'm gonna try your solution if my issue is gonna be supported soon it should be okay! Anyway thank you very much for all the time you spend answering me here! – Gaetan L. Jul 07 '16 at 09:21
  • Since you can't say "last day of the month" in cron syntax, could I use context DailyTimerPartition start (0,0,1,*,*,0) end (0,0,1,*,*,0) or is the context gonna end as soon as it starts ? – Gaetan L. Jul 07 '16 at 09:59
  • You can actually say last day of the month like I wrote: monthly: start (0,0,1,,,0) end (59,23,last,,,59). If you follow the link to the docs you can see that there are certain keywords also allowed in cron syntax – TyrManuZ Jul 07 '16 at 12:05
  • As for using windows you have the same issue like with contexts. They have a state in cache that is removed on server restart without esperha. Only stateless option is something like `insert into AllMeasurementsOfDay select findAllMeasurementByFragmentTypeAndSourceAndTimeBetween(...) from pattern[every timer:at(0,0,*,*,*,0)];` I wouldn't recommend it because it could be a lot of data in return. – TyrManuZ Jul 07 '16 at 12:10