We are pleased to share several updates to the Azure Stream Analytics query language included in today’s Azure update.
We have added the new analytics function LAST, which enables you to retrieve the most recent event in a data stream in a given timeframe. This function was requested by customers to enable scenarios like performing “last known good” value lookups.
For example, this query will return the last not-null sensor reading for every event.
SELECT sensorId, LAST(reading) OVER (PARTITION BY sensorId LIMIT DURATION(hour, 1) WHEN reading IS NOT NULL) FROM input
Note that the WHEN clause is used inside the OVER construct to express the condition for the events to be considered in the function. Similarly, the WHEN clause can now be used across all Analytic Functions.
New Array functions
Previously, Azure Stream analytics enabled accessing Array data via the CROSS APPLY operator coupled with the GetElements function.
Now it is easier to access individual array elements with new functions:
GetArrayElement – Get individual element from the array field
GetArrayElements – Get all array values and indexes
GetArrayLength – Get the length of array
New Record functions
Previously Stream Analytics allowed accessing nested field using dot notation.
In this release we added new functions to help in cases where field names are dynamic and are not known at the time of query authoring.
GetRecordPropertyValue – Returns the property value given the path
GetRecordProperties– Get all record properties to be used with CROSS APPLY operator