Damir Dobric Posts » Time projections in Azure Stream Analytic

Time projections in Azure Stream Analytic

Damir Dobric Posts

Tags

View more

Next talks:

 

    

Follow me on Twitter: #ddobric



 

 

Archives

Syndication

 

Before I demonstrate some examples, please take a look on the data stream which my Raspberry Pi is sending to EventHub. The stream shown below contains two seconds of temperature measurements of two sensors “UWP” (running on Pi2) and WIN32 (running as console application).

image

Following code snippet shows the class which describes the data:

public class SensorData

{

        public int temp{ get; set; }

        public string outtime{ get; set; }

        public string deviceid{ get; set; }

}

And following code-snippet shows how the data is serialized before sending it to IoTHub.

using (MemoryStream ms = new MemoryStream())
{

     DataContractJsonSerializer ser = new 
     DataContractJsonSerializer(typeof(SensorData));

     ser.WriteObject(ms, new SensorData()
     {

          deviceid= "UWP",

          temp = DateTime.Now.Second,

          outtime =
          DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")

                       
      });

      
       ms.Position = 0;
       msg.BodyStream = ms;

 

       await serviceClient.Send(msg);

 }

As every event, EventData (message in hub) contains timestamp values. One of times is the time when the event was recorded on device

How to get all data?

Following query simply retrieves all data.

image

Here is the result.

image

What is SystemTime?

System time is the current time in the moment of processing of the query. It is NOT the time when the event has entered the EventHub. It is some kind time when the event has entered the Analytics Source, which is almost equal to processing time of the analytic job. If you execute following query

image

you will get this result:

image

I executed this query at 15:45 at June 3th and events are generated at June 2nd. If you execute any Windowing query operation you results will not be correct.

Time Projection

To fix the issue from following example, you should inject your own time directly on sensor. I did that with following line of code:

          Time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")

You are probably wandering why I’m using string instead of time. I tis my paranoia. We have so many time notation on the planet. Because of this time conversions are mostly very painful. Fortunately ASA (Azure Stream Analytics) can understand this format. If you just leave it as DatTime, good bless you.
If you specify some value in Time-property, which is not passable, you will get a default time (see later).

But, I have to be somehow able to notify my query about my time property. Folloing query shows how to replace TIMESTAMP with the value from event property. I used outtime property.

image

This is the result of the query. As you see TIMESTAMP equals outtime.

image

Because we now have the correct timestamp value, we can use it in “windowing” operations. I will grab out number of events which every of two sensors produces in one minute:

Stream Analytics tumbling window 5 mins

This is my query:

image

and this is my result:

image

Compare this result and you will see that values in results match to the data source.

How to get Alarm?

Following query shows how many events reached the level of 59 degree, which is some hypothetic threshold  value.

image

If you take a look in sample data, you will see that sensor WIN32 generated 3 values of 59 degree in the last minute.

image

Timestamp is not System.Timestamp

Note that TimeStamp is not equal System.Timestamp.

image

image

 

Parsing issues when projecting time

What will happen if we project the system time to some column which does not contain time?
In that case

image

In that case we will get following result:

image

As you see by projecting the column to time stamp the column itself is also reflected in projected timestamp value. In other words if time stamp is slightly changing time value by parsing, the resulting value will also be
reflected in the original column value.

All queries which I have used:

SELECT
  SYSTEM.TIMESTAMP as ts, deviceid, COUNT(*)
INTO
    [outputjob2]
FROM
    [factorybhstream]
   
     TIMESTAMP BY outtime
     
GROUP BY deviceid, TUMBLINGWINDOW(mi, 1)

SELECT
  SYSTEM.TIMESTAMP as ts, outtime, Temp
INTO
    [outputjob2]
FROM
    [factorybhstream]
   
  TIMESTAMP BY outtime

SELECT
  SYSTEM.TIMESTAMP as systime, outtime as eventtime, Temp, COUNT(*)
INTO
    [outputjob2]
FROM
    [factorybhstream]  

SELECT
  SYSTEM.TIMESTAMP as ts, deviceid, COUNT(*)
INTO
    [outputjob2]
FROM
    [factorybhstream]
   
     TIMESTAMP BY outtime
    
     WHERE temp >= 59
     
GROUP BY deviceid, TUMBLINGWINDOW(mi, 1)

Testing Datasource which I have used:

[
{"outtime":"2015-06-02T21:38:00.3770000Z","deviceid":"UWP","temp":6},
{"outtime":"2015-06-02T21:38:10.5020000Z","deviceid":"WIN32","temp":55},
{"outtime":"2015-06-02T21:38:20.5700000Z","deviceid":"UWP","temp":6},
{"outtime":"2015-06-02T21:38:30.5990000Z","deviceid":"WIN32","temp":56},
{"outtime":"2015-06-02T21:38:40.6620000Z","deviceid":"UWP","temp":6},
{"outtime":"2015-06-02T21:38:50.6790000Z","deviceid":"WIN32","temp":56},
{"outtime":"2015-06-02T21:38:59.7990000Z","deviceid":"UWP","temp":6},

{"outtime":"2015-06-02T21:39:11.8110000Z","deviceid":"UWP","temp":6},
{"outtime":"2015-06-02T21:39:20.9900000Z","deviceid":"UWP","temp":6},
{"outtime":"2015-06-02T21:39:25.0710000Z","deviceid":"WIN32","temp":56},
{"outtime":"2015-06-02T21:39:30.1370000Z","deviceid":"UWP","temp":6},
{"outtime":"2015-06-02T21:39:35.1980000Z","deviceid":"UWP","temp":7},
{"outtime":"2015-06-02T21:39:40.2380000Z","deviceid":"UWP","temp":7},
{"outtime":"2015-06-02T21:39:45.4610000Z","deviceid":"WIN32","temp":57},
{"outtime":"2015-06-02T21:39:50.5450000Z","deviceid":"WIN32","temp":57},
{"outtime":"2015-06-02T21:39:12.5560000Z","deviceid":"UWP","temp":7},
{"outtime":"2015-06-02T21:39:12.5840000Z","deviceid":"UWP","temp":7},
{"outtime":"2015-06-02T21:39:12.8070000Z","deviceid":"UWP","temp":7},


{"outtime":"2015-06-02T21:40:12.8320000Z","deviceid":"UWP","temp":7},
{"outtime":"2015-06-02T21:40:12.8530000Z","deviceid":"WIN32","temp":57},
{"outtime":"2015-06-02T21:40:12.9130000Z","deviceid":"WIN32","temp":57},
{"outtime":"2015-06-02T21:40:12.9780000Z","deviceid":"UWP","temp":7},
{"outtime":"2015-06-02T21:40:13.1100000Z","deviceid":"UWP","temp":7},
{"outtime":"2015-06-02T21:40:13.2950000Z","deviceid":"UWP","temp":8},
{"outtime":"2015-06-02T21:40:13.3210000Z","deviceid":"UWP","temp":8},
{"outtime":"2015-06-02T21:40:13.3420000Z","deviceid":"WIN32","temp":57},
{"outtime":"2015-06-02T21:40:13.4640000Z","deviceid":"WIN32","temp":58},
{"outtime":"2015-06-02T21:40:13.5570000Z","deviceid":"UWP","temp":8},
{"outtime":"2015-06-02T21:40:13.6780000Z","deviceid":"UWP","temp":8},
{"outtime":"2015-06-02T21:40:13.8140000Z","deviceid":"WIN32","temp":58},

{"outtime":"2015-06-02T21:50:13.8690000Z","deviceid":"UWP","temp":8},
{"outtime":"2015-06-02T21:50:13.8920000Z","deviceid":"UWP","temp":8},
{"outtime":"2015-06-02T21:50:13.9400000Z","deviceid":"WIN32","temp":58},
{"outtime":"2015-06-02T21:50:14.0170000Z","deviceid":"UWP","temp":8},
{"outtime":"2015-06-02T21:50:14.2610000Z","deviceid":"UWP","temp":9},
{"outtime":"2015-06-02T21:50:14.2670000Z","deviceid":"WIN32","temp":58},
{"outtime":"2015-06-02T21:50:14.2870000Z","deviceid":"UWP","temp":9},
{"outtime":"2015-06-02T21:50:14.3520000Z","deviceid":"UWP","temp":9},
{"outtime":"2015-06-02T21:50:14.3600000Z","deviceid":"WIN32","temp":59},
{"outtime":"2015-06-02T21:50:14.5270000Z","deviceid":"WIN32","temp":59},
{"outtime":"2015-06-02T21:50:14.5820000Z","deviceid":"UWP","temp":9},
{"outtime":"2015-06-02T21:50:14.5990000Z","deviceid":"WIN32","temp":59},
{"outtime":"2015-06-02T21:50:14.6780000Z","deviceid":"UWP","temp":9},
Posted Jun 04 2015, 09:34 AM by Damir Dobric
Filed under: , , , , ,
developers.de is a .Net Community Blog powered by daenet GmbH.