Warning, this is going to be a dry post. Beer or water required before you continue reading.
For those that don’t already know Azure Stream Analytics is Microsoft’s cloud based service for the handling and manipulation of data feeds, predominantly in real-time. However the service has many applications with the ability to ingest a wide variety of data sources. One such use case for the analysis of data using the service comes when performing aggregations on the stream received by the job as an input. Like conventional querying the aggregation would be done against some grouping of the data set. Stream Analytics is no exception, however we need to understand that where we would previously alter our query to aggregate things by different groups of attributes (the query changes). We now fix our query when starting the job and stream data past it (the data changes). It is this shift in mind-set from a fixed dataset to a data stream that means we need to consider different grouping conditions for the attributes presented.
In Stream Analytics because, as the name suggests, data is streamed to/through the service time becomes the key for the grouping we impose for our aggregations. It is this time period for the grouping that is referred to as the window. To better understand what these windows look like lets compare some query syntax that we might use to produce a count on a dataset by day vs how we would aggregate this in a data stream by a window of day.
| Dataset | Data Stream |
 |
 |
If only everything could be explained by comparing a SQL like syntax for two different technologies 🙂
Assuming SQL is part of your brain parser we can predict from the above queries what results we are expecting to see. However for the data stream if nothing is received by our input within the defined window the count will be zero. I repeat the data changes, not the query as it’s streamed through the service. Thinking about the opposite of that for the above dataset query the results will only grow to include many day values as more data is inserted into the table. Where as the results in data stream will always return only one value as the data streams through the service and the window moves with the defined time period in the grouping.
Hopefully we now understand what a window is in our Stream Analytics job. Both in English and SQL!
If you still aren’t sure try this 1 liner from Microsoft’s MSDN page.
A window contains event data along a timeline and enables you to perform various operations against the events within that window.
Reference: https://msdn.microsoft.com/en-us/library/azure/dn835019.aspx
Next then, window types. This window of time for our stream of data can then take 3x different forms depending on what we need to report on; sliding, tumbling and hoping. Lets explore each in more detail with some potential real world examples to give there purpose some context. For the examples I’m going to use the health services as a basis for my scenarios.
Sliding
As a name suggests this first type of Stream Analytics windows slides with time. It has a defined size or duration and once set will move forwards aggregating any values in its scope.
Query example:
SELECT
DateAdd(hour,-1,System.TimeStamp) AS 'WinStartTime',
System.TimeStamp AS 'WinEndTime',
AVG([WaitTime]) AS 'SenorValue'
INTO
[TargetAlerts]
FROM
[SystemInput]
TIMESTAMP BY
[CreatedDateTime]
GROUP BY
SlidingWindow(Duration(hour, 1)) --window criteria
Scenario:
We are running an A&E department and we want to know the average wait time for all patients currently in the waiting room with a window duration of 1 hour. We may then want alerts if the current window value exceeds a set tolerance. What else happened in that window to clause that? Or we could just provide this information to waiting patients on a TV screen with a note that the sample is from a fixed size window. Call centres often provide there average wait time to answer the phone without any context of the window in which the aggregation was taken resulting in mislead expectations.
Tumbling
A tumbling window is a little harder to explain because how can time tumble. Well it doesn’t and nothing about tumbling down something should be implied. Instead think of the day as 24 separate windows all 1 hour in size or duration. Values stream into our job in the current 1 hour window. Then as we tick into the next hour the aggregate tumbles over and resets for the new window.
Query example:
SELECT
DateAdd(day,-1,System.TimeStamp) AS 'WinStartTime',
System.TimeStamp AS 'WinEndTime',
COUNT([ReferralId]) AS 'SenorValue'
INTO
[DashboardOutput]
FROM
[SystemInput]
TIMESTAMP BY
[CreatedDateTime]
GROUP BY
TumblingWindow(Duration(day, 1), Offset(millisecond, -1)) --window criteria
Scenario:
We are monitoring referrals being entered onto our hospitals computer system with a tumbling window of 1 day. Each day we count the number of referrals entered and display the information on a real-time dashboard. Triggers could then be put in place if the referrals entered fall below a given tolerance. Or if they exceed expected amounts because data isn’t being entered uniformly throughout the week.
Hoping
Hoping windows get a little easier to understand assuming we are comfortable with tumbling windows. The reason being is that a hop is the same as a tumble, but with an overlap in time. Sticking with our 24 hour day we could still have 24 hoping windows, but they now have a size or duration of 1.5hours meaning a 30 minute overlap.
Query example:
SELECT
DateAdd(hour,-1,System.TimeStamp) AS 'WinStartTime',
System.TimeStamp AS 'WinEndTime',
[WardName],
COUNT([PatientId]) AS 'SenorValue'
INTO
[DashboardOutput]
FROM
[SystemInput]
TIMESTAMP BY
[CreatedDateTime]
GROUP BY
[WardName],
HoppingWindow(Duration(hour, 1), Hop(minute, 30), Offset(millisecond, -1)) --window criteria
Scenario:
We are monitoring the number of patients on our wards with a simple count. A patient is then moved between wards but the move involves escorting the patient by wheel chair to the other side of the hospital. We want to allow enough time for the physical move to occur in our window aggregation so we allow a 30 minute overlap in the tumble. This means the patient may be double counted for a brief time. But that might be considered a better position than them not appearing on any ward. Eg. They virtual disappeared.
I hope you found this post useful. I did say it was going to be dry. There are a few pictures on the MSDN pages which might help with understanding but for me they were a little static and needed some animation or colour to be properly representative of each window type.
Many thanks for reading.
I have an Error with ” timestamp by ” while applying window tumbling as following:
I have a json file as below:
—————————-
{“imei”: {“imei”: “358174069248418F”, “imeiBinary”: “NYF0BpJIQY8=”,”imeiNotEncoded”: “358174069248418”,”valid”: 1},”dataPackets”: [[“msy.mxp.datapacket.AlarmNotification”,{“version”: 1, “id”: 21, “op”: 2,”sizeDynamic”: 0, “alarmStatus”: 4}],[“msy.mxp.datapacket.IOStatus”,{“version”: 1,”id”: 15, “op”: 2,”sizeDynamic”: 0,”ioStatus”: 135,”ioDirections”: 120}], [“msy.mxp.datapacket.LogicalStatus”,{“version”: 1,”id”: 16, “op”: 2,”sizeDynamic”: 0,”logicalStatus”: 5} ],[ “msy.mxp.datapacket.Position”, {“version”: 1,”id”: 19,”op”: 2,”latitude”: 40.835243,”longitude”: 14.246057,”altitude”: 40,”speed”: 0, “course”: 68, “gpsNumSatellite”: 5,”glonassNumSatellite”: 1,”fixValid”: 1,”timeValid”: 1,”wgs84degMinFormat”: 1, “glonass”: 1,”fixMode”: 3,”timestamp”: {“timeSecFrom1Gen2000″: 925560202,”time”: 1490648755000 }, “sizeDynamic”: 0} ] ]}
—————————–
and I am reading by the following query:
————————————-
WITH Datapackets AS
(
SELECT imei.imei as imei,
persistent as persistent,
[timestamp].[time] as input_time,
compressed as compressed,
GetArrayElement(dataPackets, 3) as position
FROM h24
), one as(
SELECT *,
GetRecordPropertyValue (GetArrayElement(position,1), ‘timestamp’) as position_timestamp –1st
from Datapackets
), two as (
select
imei,
GetRecordPropertyValue (GetArrayElement(position,1), ‘op’) as position_OP,
[position_timestamp].[time] as position_time,
dateadd(S, [position_timestamp].[timeSecFrom1Gen2000], ‘1970-01-01’) as timing,
GetRecordPropertyValue (GetArrayElement(position,1), ‘latitude’) as position_latitude,
GetRecordPropertyValue (GetArrayElement(position,1), ‘longitude’) as position_longitude,
GetRecordPropertyValue (GetArrayElement(position,1), ‘altitude’) as position_altitude,
GetRecordPropertyValue (GetArrayElement(position,1), ‘speed’) as position_speed
from one) SELECT * from two
———————————————-
Now I want to make window tumbling group by 30 seconds as following but I have a problem which tells me that timestamp property is not allowed for the input file”two” ,, here the query that I use
———————————————–
WITH Datapackets AS
(
SELECT imei.imei as imei,
persistent as persistent,
[timestamp].[time] as input_time,
compressed as compressed,
GetArrayElement(dataPackets, 3) as position
FROM h24
), one as(
SELECT *,
GetRecordPropertyValue (GetArrayElement(position,1), ‘timestamp’) as position_timestamp –1st
from Datapackets
), two as (
select
imei,
GetRecordPropertyValue (GetArrayElement(position,1), ‘op’) as position_OP,
[position_timestamp].[time] as position_time,
dateadd(S, [position_timestamp].[timeSecFrom1Gen2000], ‘1970-01-01’) as timing,
GetRecordPropertyValue (GetArrayElement(position,1), ‘latitude’) as position_latitude,
GetRecordPropertyValue (GetArrayElement(position,1), ‘longitude’) as position_longitude,
GetRecordPropertyValue (GetArrayElement(position,1), ‘altitude’) as position_altitude,
GetRecordPropertyValue (GetArrayElement(position,1), ‘speed’) as position_speed
from one) SELECT imei, System.TimeStamp AS ‘start’, Avg(position_speed), max(position_latitude)
FROM two TIMESTAMP BY TIMING GROUP BY imei, TIMING, TumblingWindow(duration(second, 30))
—————————————————–
The error appears in the last two lines (FROM two TIMESTAMP BY TIMING). Any suggestions about how I deal with this problem, Thanks
Hi Ahmed, thank you for your comment. Apologies, but this is a blog, not Stack Overflow. Can I recommend you post your issue else where, or we would be happy to support you with a days consultancy. Cheers Paul