Run a scheduler to check if batch is finished and apply calculations for every finished batch in Kusto?

user_17429072 0
Viewed 58

I have the following data which I randomally generated in kusto.

 let data = datatable(BatchNumber: int,Timestamp:datetime, Power1:int, Power2: int, Speed1: int, Speed2: int, Enabled1: bool, Enabled2: bool)
        [
         1, datetime(2022-02-18 10:00:00 AM), 100, 200, 50, 80, false, true,
         1, datetime(2022-02-18 10:01:00 AM), 100, 200, 50, 80, true, true,
         1, datetime(2022-02-18 10:02:00 AM), 100, 200, 50, 80, false, true,
         1, datetime(2022-02-18 10:03:00 AM), 100, 200, 50, 80, true, true,
         1, datetime(2022-02-18 10:04:00 AM), 100, 200, 50, 80, false, true,
         1, datetime(2022-02-18 10:05:00 AM), 100, 200, 50, 80, true, true,
         1, datetime(2022-02-18 10:06:00 AM), 100, 200, 50, 80, false, true,
         1, datetime(2022-02-18 10:07:00 AM), 100, 200, 50, 80, true, true,
         2, datetime(2022-02-18 10:08:00 AM), 100, 200, 50, 80, false, true,
         2, datetime(2022-02-18 10:09:00 AM), 100, 200, 50, 80, true, true,
         2, datetime(2022-02-18 10:10:00 AM), 100, 200, 50, 80, false, true,
         2, datetime(2022-02-18 10:11:00 AM), 100, 200, 50, 80, true, true,
         2, datetime(2022-02-18 10:12:00 AM), 100, 200, 50, 80, false, true,
         2, datetime(2022-02-18 10:13:00 AM), 100, 200, 50, 80, true, true,
         2, datetime(2022-02-18 10:14:00 AM), 100, 200, 50, 80, false, true,
         2, datetime(2022-02-18 10:15:00 AM), 100, 200, 50, 80, true, true,
         2, datetime(2022-02-18 10:15:00 AM), 100, 200, 50, 80, false, true
        ];

What I am trying to achieve here is to check when is the batch finished. The logic to do that is to get the last value of a batch number and check the value after it. If the next value is different from the one before then the previous batch is finished. So in this case, batch number = 1 is finished but batch number 2 not yet. However, in this case I shouldn't be checking for a specific batch number because we are getting values in real time. The script should know by itself when is the batch finished probably by doing a scheduler which runs every 5 minutes to check if the batch is finished based on the logic I explained above (in bold), and when it knows that the batch is finished, it should project this batch number, Batch_Date (in this case for batch number = 1, the date is 2022-02-18 10:07:00 AM), and total power (which is based on some calculations only for this finished batch)

For example expected result for BatchNumber = 1:

enter image description here

In this case, once the script knows that batch number 2 is also finished a new record will be added with this batch number. Let us assume that one addition row was added to the dataset with batch number = 3, so the expected result would be:

enter image description here

So everytime the script knows that a batch is finished it should directly add it as a new record as shown in the screenshots, projecting the new batch, the date when it was finished and some calculations specifically for newly added batch.

I am a bit confused how can I do this in Kusto? If it is possible or not ? I don't know if a scheduler is needed in this case or if there is a better and efficient way to do it?

kusto-explorer kql azure-data-explorer
1 Answers

If the goal is reporting, we leverage materialized views

Demo

.create table data (BatchNumber: int,Timestamp:datetime, Power1:int, Power2: int, Speed1: int, Speed2: int, Enabled1: bool, Enabled2: bool)

.create-or-alter materialized-view data_mv on table data
{
    data
    | summarize BatchData = max(Timestamp), TotalPower = sum(coalesce(Power1,0) + coalesce(Power2,0)) by BatchNumber
}

.create-or-alter materialized-view data_max_BatchNumber_mv on table data
{
    data
    | summarize BatchNumber = max(BatchNumber) by dummy = 1
}

.create-or-alter function completed_batches_f ()
{
    let max_BatchNumber = toscalar(data_max_BatchNumber_mv | project BatchNumber);
    data_mv
    | where BatchNumber < max_BatchNumber
}

.ingest inline into table data <|
1, datetime(2022-02-18 10:00:00 AM), 100, 200, 50, 80, false, true
1, datetime(2022-02-18 10:01:00 AM), 100, 200, 50, 80, true, true
1, datetime(2022-02-18 10:02:00 AM), 100, 200, 50, 80, false, true
1, datetime(2022-02-18 10:03:00 AM), 100, 200, 50, 80, true, true
1, datetime(2022-02-18 10:04:00 AM), 100, 200, 50, 80, false, true

completed_batches_f
BatchNumber BatchData TotalPower 
.ingest inline into table data <|
1, datetime(2022-02-18 10:05:00 AM), 100, 200, 50, 80, true, true
1, datetime(2022-02-18 10:06:00 AM), 100, 200, 50, 80, false, true
1, datetime(2022-02-18 10:07:00 AM), 100, 200, 50, 80, true, true
2, datetime(2022-02-18 10:08:00 AM), 100, 200, 50, 80, false, true
2, datetime(2022-02-18 10:09:00 AM), 100, 200, 50, 80, true, true
2, datetime(2022-02-18 10:10:00 AM), 100, 200, 50, 80, false, true
2, datetime(2022-02-18 10:11:00 AM), 100, 200, 50, 80, true, true

completed_batches_f
BatchNumber BatchData TotalPower
1 2022-02-18T10:07:00Z 2400 
.ingest inline into table data <|
2, datetime(2022-02-18 10:12:00 AM), 100, 200, 50, 80, false, true
2, datetime(2022-02-18 10:13:00 AM), 100, 200, 50, 80, true, true
2, datetime(2022-02-18 10:14:00 AM), 100, 200, 50, 80, false, true
2, datetime(2022-02-18 10:15:00 AM), 100, 200, 50, 80, true, true
2, datetime(2022-02-18 10:15:00 AM), 100, 200, 50, 80, false, true

completed_batches_f
BatchNumber BatchData TotalPower
1 2022-02-18T10:07:00Z 2400 
.ingest inline into table data <|
3, datetime(2022-02-18 10:16:00 AM), 100, 200, 50, 80, false, true

completed_batches_f
BatchNumber BatchData TotalPower
1 2022-02-18T10:07:00Z 2400
2 2022-02-18T10:15:00Z 2700
user_63364790
Related