locked
SA Query MINIMUM Function Stuck At Zero RRS feed

  • Question

  • Hello,

    I am experiencing a problem where my streaming analytics query MINIMUM is stuck at 0. The time windows is SLIDINGWINDOW(second, 15). I also have a MAXIMUM in the query and it seems to work (except occassionally it also gets stuck at some non zero large value but this is far less frequent). It seems to occur when one of our devices goes offline and then returns. We thought it might be a timestamp late/out of order issue but so far cannot force the issue to happen.

    The FastMovement data (although not viewable) stays above zero for well over 15 seconds. If I stop the query and restart it, it resumes normal operation. If I capture data using the portal then use it to test the query, the output of MINIMUM is computed correctly.

    Thank You for any help,

    Dan

    Here is the query. MovementDemo1 is a PowerBI dataset output. Data is measured 1 per second and sent to the IOT Hub every 15 seconds. Events Arrive Late = 30 min and Out of Order is 20 seconds with Action set to DROP.

    WITH SleepWindowStats AS
    (
    SELECT
    DeviceId,
    System.Timestamp AS Timestamp,
    MIN(FastMovement) AS FastMovementMinimum,
    MAX(FastMovement) AS FastMovementMaximum,
    AVG(RespirationRate) AS RespirationRateAverage,
    TopOne() OVER (ORDER BY DateTimeOffset DESC) as TopEvent
    FROM
    DeviceMessages
    TIMESTAMP BY
    DateTimeOffset OVER DeviceId
    WHERE
    MessageTypeId = 0
    GROUP BY
    SLIDINGWINDOW(second, 15),
    DeviceId

    ),

    SELECT
    DeviceId,
    Timestamp,
    FastMovementMinimum,
    FastMovementMaximum,
    RespirationRateAverage AS RespirationRateMaximum,
    TopEvent.RespirationRate AS RespirationRate,
    TopEvent.FastMovement AS FastMovement,
    TopEvent.Distance as Distance
    INTO
    MovementDemo1
    FROM
    SleepWindowStats
    WHERE
    DeviceId = 'demo1'

    Here is a plot of the data:



    • Edited by shredfoot Friday, February 1, 2019 5:13 PM
    Friday, February 1, 2019 4:58 PM

All replies

  • Hi shredfoot,

    To understand the issue better, I would recommend you to use the following troubleshooting guides :

    https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-troubleshoot-query

    https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-job-diagnostic-logs

    Once we have narrowed down to the problem, we can work on solving it.


    MSDN

    Wednesday, February 6, 2019 11:26 AM
  • Thank you for the links. I went through those before posting the question in the forum and went back through them upon your request.

    I believe that the problem is known. When my device (raspberry pi) restarts the time stamp is incorrect until NTP manages to correct it. These incorrect time (possibly in the past, possibly in the future, possibly changing once NTP hones in) are causing the SA Query to error both unexpectedly and silently. 

    As you can see in the plot in the original post, the MAXIMUM function continues to output correctly but the MINIMUM does not. Sometimes it is the other way around. Sometimes it is both. I sampled the data from the timespan in the plot and uploaded the sample data to SA and used the TEST function in the portal and the result were CORRECT...the MINIMUM WAS NOT ZERO. As stated in the documentation the TEST functionality is not perfect because of the temporal nature of the query is not exactly recreated in that scenario. 

    TLDR; I believe that there is possibly a bug in the temporal aggregate functions that I using when the input application timestamps behave oddly (ie they stop for a period of time and then resume with a value that is "far" in the past or future).

    Is this scenario (device disappears and reappears with incorrect time) not supported?

    Thank You

    Dan

    Wednesday, February 6, 2019 6:58 PM