Using multiple, mixed length Windows in Stream analytics query results in high (100%) SU memory utlization
Tornikoski, Veli-Matti
0
Reputation points
I want to use Windows to collect both 3-hour and 1 day max, min, average and count of certain device messages arriving to iot hub. If I use both windows, as in code snippet, SU memory usage goes to 100%. If I use only one Window (does not matter which one), SU memory usage is about 20%.
Why is that, can't we use multiple Windows like that? For additional information, there are only about 10 messages hourly arriving at IoT Hub, so should not be heavy usage.
TumblingWindowResults AS (SELECT
System.Timestamp() as date,
packet.DevEUI as SensorID,
AVG(packet.value) AS temp_water,
MAX(packet.value) AS temp_water_max,
MIN(packet.value) AS temp_water_min,
COUNT(*) AS samples_count,
AVG(packet.LrrRSSI) as rssi,
System.Window().Id
FROM
Telemetry
WHERE
packet IS NOT NULL
AND packet.type LIKE 'digmat/sensor%'
AND packet.payload_hex IS NOT NULL
GROUP BY
packet.DevEUI,
Windows(
Window('1 day', TumblingWindow(day, 1)),
Window('3 hour', TumblingWindow(hour, 3)))
SELECT
DATEADD(DAY, -1,date) as date, SensorID, temp_water, temp_water_max, temp_water_min, samples_count, rssi
INTO
averagesday
FROM
TumblingWindowResults
WHERE
TumblingWindowResults.Id = '1 day'
SELECT
date, SensorID, temp_water, temp_water_max, temp_water_min, samples_count, rssi
INTO
averageshour
FROM
TumblingWindowResults
WHERE
TumblingWindowResults.Id = '3 h'