Azure Streaming Analytics Lag() Function with example

The Lag() function in Azure Streaming Analytics is documented here.  I have been asked a few times now about Lag() and if it can refer to the projected values of another Lag() function in the previous event.  Let’s see.

 

The Event

My events are really simple and their schema is:

 

{
type: "string",
readingtaken: "datetime",
reading: 0
}

 

The Query

 

My query in ASA is

 

select
type,
readingtaken,
case
when reading IS NULL then LAG(reading,1,0) OVER (PARTITION BY type LIMIT DURATION(hh,24))
else reading
end as reading
into
dest
from
readings
where
type = 'temperature'

 

The Data Flow

 

Here are my events as they flow through the stream (these are the raw events and not what is projected rom the ASA query)

 

 

 

Here is what you as the consumer will see i.e. the projected events.

 

 

 

The Explanation

 

The event that is of interest is E3.

 

Here is an explanation of the projected events

 

E1 has the reading attribute and value as does E1(Projected)

E2 does not have the attribute and value but E2(Projected) uses the value from E1.

E3 again does not have the attribute and value.  E3 looks to E2 and not E2(Projected).  In E2 the value of reading is NULL and as per the documentation this is used.

 

 

Summary

Using Lag() you cannot or rather do not refer to the projected value of the previous event.  You refer to the actual event itself.  In the example above the reading attribute had an explicit value of NULL in E2 so that is what was used in E3

Working with Arrays in Azure Streaming Analytics

When working with Azure Streaming Analytics (ASA) it is extremely common to use JSON for your events.  JSON allows us to express data in a rich way with things like nested objects and arrays.  This though can cause some head scratching further along the chain when we need to work with that data,  In this post I want to look at working with arrays in ASA.

 

Here is my event data

As you can see I have an array of objects which describe samples.  In this format they are not much use to me.  What I need to do is Pivot this data as it comes through ASA ready to be put into my destination.

Here’s the query that does that

SELECT
source___timestamp,
source.time,
flattened.arrayindex,
flattened.arrayvalue.sample,
flattened.arrayvalue.x,
flattened.arrayvalue.y,
flattened.arrayvalue.z
INTO
output
FROM
source
CROSS APPLY GetElements(source.samples) AS flattened

And here is what it looks like on the other side

Perfect. It is now in a format I can use more easily.