Azure Stream Analytics - JSON Array, windowing and previous value

Question

I receive containers of senor data as an input for ASA. Containers look like this.

{
"data": [
  {
    "sensor_id": 55,
    "timestamp": 1663075725000,
    "value" : 32.12
  },
  {
    "sensor_id": 55,
    "timestamp": 1663075725025,
    "value" : 33.23
  },
  {
    "sensor_id": 12,
    "timestamp": 16630757255543,
    "value" : "on"
  },
  {
    "sensor_id": 458,
    "timestamp": 1663075725993,
    "value" : 102
  },...
}]

The data is send from different vehicles. The vehicle identifier is sent as a custom property.

My goal is, to detect things like "over-speeding" (speed > x km/h for at least 5 seconds) or "handbrake while driving" (speed > x km/h and handbrake = "on" for at least 2 seconds)

I have no clue how to achieve this.

My first idea was, to split the container into single "rows" and add vehicle identifier. I achieved this with following query:

WITH Signals AS
(
   SELECT   
   arrayElement.ArrayValue as sig,
   GetMetadataPropertyValue(msg, '[User].[Vehicle]') as vehicle,
   FROM input as msg  
   CROSS APPLY GetArrayElements(msg.data) AS arrayElement
)
SELECT
    sig.sensor_id, sig."timestamp", sig.value, vehicle
INTO
    output
FROM
    Signals

That works, but unfortunately I don't see the vehicle value in the preview window in Azure portal (but that's mentioned in the docs)

But now I don't know how to go further...

Should I send the output to a new event hub and create a separate ASA job and do the other calculations or could I do this in this query? How could a query look like for getting things like "over-speeding" (sensor x of one vehicle > x for y seconds) or "handbrake driving" (sensor y = "on" and sensor x of one vehicle > x for y seconds)?

Any help appreciated!

Update As mentioned here (In Azure Stream Analytics Query I am getting an error when using Timestamp by) I think, I will need to split my work in multiple ASA jobs. First one doing the CROSS APPLY and pushing to a separate event hub, second job could than do the calculation.
So, please help me with a query for "handbrake driving" (sensor_id 12 = "on" and sensor 55 > 10 for 3 seconds).
Many thanks!

Vignesh Chandramohan · Accepted Answer · 2023-01-29 22:05:44Z

0

You can do this in a single stream analytics job. Steps would be

Expand the array using cross apply, project vehicle id as well from input property. You query covered this. Do this is a with step
Over-speeding: speed > x km/h for at least 5 seconds. Group by vehicle, do a hopping window of 1 second with a window size of 5 seconds, and filter rows where min(speed) > x km/h.
Hand brake while driving: sensor y = "on" and sensor x of one vehicle > x for y seconds . Similar to 2, compute two aggregates. One for min speed, and another for number of distinct hand brake sensor values using count(distinct) when (sensor name = y).
Use this query pattern for sending these each output to different destination.
If you would like to combine to output for a single vehicle into a single object, you could also use union + group by + collect.

answered Jan 29, 2023 at 22:05

Vignesh Chandramohan

1,30610 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Azure Stream Analytics - JSON Array, windowing and previous value

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related