Azure stream anlytics test query output is different from Sql database logged output.

Umair Akhtar 41 Reputation points
2022-11-14T12:19:24.69+00:00

So i have this query in which Azure stream analytics test query output is different from Sql database logged output.

SELECT
DeviceId,
System.Timestamp() AS Windowtime,
AVG(temperature) as Temperature,
AVG(humidity) as Humidity
INTO databaseoutput
FROM iothubinput
WHERE DeviceId = 'Raspberry Pi Web Client'
GROUP BY DeviceId, TUMBLINGWINDOW(SECOND,10)

This query returns this timestamp (Windowtime) "2022-11-14T12:00:50.0000000Z"'. This timestamp shows seconds also since I am using tumbling window. But in sql database it is only logging the data per minute such as "2022-11-14 11:17:00.000", "2022-11-14 11:15:00.000". I am using sql database for line charts in powerapp where I need every timestamp for every 10 seconds which does not seem to be the case since data is every 1 minute in database. It seems even though I set TUMBLINGWINDOW(SECOND,10). Also, I am using Rasberry Pi simulator, so it is sending data every 2 seconds.

How can I fix this query so that sql database gets timestamp every 10 seconds such as 2022-11-14 11:17:00.000", "2022-11-14 11:17:10.000", "2022-11-14 11:17:20.000" etc?

Also current time is in UTC. I would want to convert it to local time i.e. Easter European time zone. Any help would be appreciated.

Thanks

Azure SQL Database
Azure Stream Analytics
Azure Stream Analytics
An Azure real-time analytics service designed for mission-critical workloads.
342 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Umair Akhtar 41 Reputation points
    2022-11-16T13:58:42.013+00:00

    I had some other column in database as well which I deleted before. Would this be have any impact? When I created UTC Time converter function using UDF it asked to change datetime properties of nvarchar(max) so i changed properties in sql db column to match with stream as well. UTC time to Eastern European time zone UDF function is below:

    function main(utcDate) {
    return (new Date(utcDate)).toLocaleString({ timeZone: 'EET' });
    }

    But It still did not convert the time when used in stream query so I ended up using below query:

    SELECT
    DeviceId,
    DATEADD(hour, 2, EventProcessedUtcTime) AS Windowtime,
    temperature as Temperature,
    humidity as Humidity
    INTO databaseoutput
    FROM Input
    WHERE DeviceId = 'Raspberry Pi Web Client'

    Above query did show the converted Easter European time zone (EET) in SQL DB. However, In database It shows 2022-11-16 15:17:19.807, 2022-11-16 15:16:50.600 while device sensor sending data almost every 2 seconds. So i need to fix this so SQL Database takes values same interval as sensor i.e. 2 seconds or close.

    Also, I have to take average of 10 seconds using TUMBLINGWINDOWN apparently. But How do I use it with above query. Or is there a better way to do so? because with UDF convert function, timestamp did not convert to local time zone i.e. EET in sql db nor in stream test output.

    Kindly advise

    Thank you

    0 comments No comments

  2. BhargavaGunnam-MSFT 28,931 Reputation points Microsoft Employee
    2022-11-17T00:31:47.113+00:00

    Hello @Umair Akhtar ,

    As per your first query, you are using system.timestamp() as windowstime- this is the time when the event was generated.

    and you are inserting this time stamp to SQLdatabase alias databaseoutput

    and I see you set the TUMBLINGWINDOW(SECOND,10). As per the issue, you are not able to see the output SQL time for 10 seconds.

    I believe the issue is with your simulator. (not with SQL database output).

    To test this, you can remove the System.Timestamp() AS Windowtime and run the query.

    SELECT
    DeviceId,
    AVG(temperature) as Temperature,
    AVG(humidity) as Humidity
    INTO databaseoutput
    FROM iothubinput
    WHERE DeviceId = 'Raspberry Pi Web Client'
    GROUP BY DeviceId, TUMBLINGWINDOW(SECOND,10)

    once the job starts, you can query the SQL table and see.

    for your next questions, your query is correct

    select getdate(), -- consider getdate() is the UTC
    DATEADD(hour, 2, getdate()) AS EasterEuropeantime


  3. Umair Akhtar 41 Reputation points
    2022-11-17T11:45:44.58+00:00

    So I ran this query

    SELECT
    DeviceId,
    DATEADD(hour, 2, EventProcessedUtcTime) AS Windowtime,
    AVG(temperature) as Temperature,
    AVG(humidity) as Humidity
    INTO databaseoutput
    FROM iothubinput
    WHERE DeviceId = 'Raspberry Pi Web Client'
    GROUP BY DeviceId, EventProcessedUtcTime, TUMBLINGWINDOW(SECOND,30)

    Windowtime Temperature
    2022-11-17 13:35:50.950 22,2065550214542
    2022-11-17 13:35:28.793 27,2587782688978
    2022-11-17 13:35:03.047 21,6540319607921
    2022-11-17 13:34:32.200 21,0907923111868
    2022-11-17 13:34:05.343 26,6955568574692
    2022-11-17 13:33:32.437 23,8203880068635

    2022-11-17 13:31:39.050 26,9005199840313
    2022-11-17 13:31:39.050 24,1342440424321

    So if you see the database output in above table. Table is not receiving average values under 30 seconds. Instead, It is logging the value as they are coming from simulator. In Simulator, setInterval(sendMessage, 2000) so it is about about every 2 seconds. But SQL DB is showing the completely different timestamp. So the question is, how do i get average values based on tumbling window? Plus, how to avoid duplicate events because some of the timestamp if you see has same timestamp such as 2022-11-17 13:31:39.050 but different temperature value.

    Or would this solve with real sensor?

    Thank you