Get first record in Tumbling Window using Azure Stream Analytics

Question

Some part of my project using Esper in Java for complex Event processing. I'm planning to replace Esper with Azure Stream Analytics.

Use Case: FTOD (First Ticket of the Day) & FTOP (First Ticket of Project)

I'm continuously getting ticket data from Eventhub and want to generate 2 types of alerts (FTOD & FTOP). I think thumblingWindow is the best fit for this scenario.

But I'm not able to pick first record in window. Any suggestion how to pick first record in 24 hours window?

Below is Esper query for FTOD

     String statementQuery = "context context_" + plantIdStr
          + " select distinct * from TicketInfoComplete as ticket where plantId = '"
          + entry.getKey() + "' and ruleType='FTOD' output first every 24 hours";

Below is my incoming message data

[{"DeviceSerialNumber":"190203XXX001TEST","MessageTimestamp":"2019-02-11T13:46:08.0000000Z","PlantId":"141","ProjectId":"Mobitest","ProjectName":"Mobitest","TicketNumber":"84855","TicketDateTimeinUTC":"2019-02-11T13:46:08.0000000Z","AdditionalInfo":{"value123":"value2"},"Timeout":60000,"Traffic":1,"Make":"Z99","TruckMake":"Z99","PlantName":"RMZ","Status":"Valid","PlantMakeSerialNumber":"Z99|190203XXX001TEST","ErrorMessageJsonString":"[]","Timezone":"India Standard Time"}]

Answer 1

Based on your description, I think you could know about LAST operator with the GROUP BY condition. LAST allows one to look up the most recent event in an event stream within defined constraints.

In Stream Analytics, the scope of LAST (that is, how far back in history from the current event it needs to look) is always limited to a finite time interval, using the LIMIT DURATION clause. LAST can optionally be limited to only consider events that match the current event on a certain property or condition using the PARTITION BY and WHEN clauses. LAST is not affected by predicates in WHERE clause, join conditions in JOIN clause, or grouping expressions in GROUP BY clause of the current query.

Please see the example in above document:

SELECT    
       LAST(TicketNumber) OVER (LIMIT DURATION(hour, 24))  
FROM input 

---

Just for summarized, the isFirst method need to be considered when you want to get the first item.

Answer 2

Exact Query What I have used after using IsFirst Method for FTOD & FTOP alert.

SELECT 
DeviceSerialNumber,MessageTimestamp,PlantId,TruckId,ProjectId,ProjectName,
CustomerId,CustomerName,TicketNumber,TicketDateTimeinUTC,TruckSerialNumber,
TruckMake,PlantName,PlantMakeSerialNumber,Timezone,'FTOD' as alertType
INTO
[alertOutput]
FROM
[ticketInput]
where ISFIRST(mi, 2)=1

SELECT 
DeviceSerialNumber,MessageTimestamp,PlantId,TruckId,ProjectId,ProjectName,
CustomerId,CustomerName,TicketNumber,TicketDateTimeinUTC,TruckSerialNumber,
TruckMake,PlantName,PlantMakeSerialNumber,Timezone,'FTOP' as alertType
INTO
[ftopOutput]
FROM
[ticketInput]
where ISFIRST(mi, 2) OVER (PARTITION BY PlantId) = 1