CODEWITHSUNDEEP
azure-stream-analytics
khanikar
khanikar

Reputation: 71

Azure Stream Analytic SQL TumblingWindow expected Window is not returned

In Azure Stream Analytic (IoT Hub), no matter what time window I specify, the TumblingWindow function fails to compare time and won't return the window I need. I am trying to use the below SQL code block to return me a 2-second window, but the output includes all events. As there is no pivot function in Stream Analytic, I am using the method suggested by @Joe-Zhang

In this case, there is an IoT read event every 2 seconds, and I am expecting only one event to be returned -


with tempone as (
    
    select 
    cast(dataArr.ArrayValue.SourceTimestamp as datetime) as SourceTimestamp, 
    cast(valuesArr.ArrayValue.Address as bigint) as Address2, 
    max(cast(valuesArr.ArrayValue.Value as float)) as Value2

from iotinput i
cross apply GetArrayElements(i.Content) as contentArr
cross apply GetArrayElements(contentArr.ArrayValue.Data) as dataArr
cross apply GetArrayElements(dataArr.ArrayValue.[Values]) as valuesArr

WHERE cast(valuesArr.ArrayValue.Address as bigint) = 30002

GROUP BY cast(dataArr.ArrayValue.SourceTimestamp as datetime), 
    cast(valuesArr.ArrayValue.Address as bigint),
    TumblingWindow(second, 2)
),

temptwo AS ( 

    select 
    cast(dataArr.ArrayValue.SourceTimestamp as datetime) as SourceTimestamp, 
    cast(valuesArr.ArrayValue.Address as bigint) as Address3, 
    max(cast(valuesArr.ArrayValue.Value as float)) as Value3


from iotinput i
cross apply GetArrayElements(i.Content) as contentArr
cross apply GetArrayElements(contentArr.ArrayValue.Data) as dataArr
cross apply GetArrayElements(dataArr.ArrayValue.[Values]) as valuesArr

WHERE cast(valuesArr.ArrayValue.Address as bigint) = 30003

GROUP BY cast(dataArr.ArrayValue.SourceTimestamp as datetime), 
    cast(valuesArr.ArrayValue.Address as bigint),
    TumblingWindow(second, 2)
 )

select tempone.SourceTimestamp, tempone.Value2 as Temperature, temptwo.Value3 as Humidity from tempone
join temptwo on tempone.SourceTimestamp = temptwo.SourceTimestamp
and DATEDIFF(second,tempone, temptwo) BETWEEN 0 AND 2

Returned values -

enter image description here

Upvotes: 1

Views: 102

Answers (1)

Florian Eiden
Florian Eiden

Reputation: 842

If you don't use TIMESTAMP BY, the time logic will be based on the ingestion time.

Here it looks like you expect time processing to be done on SourceTimestamp, but you don't TIMESTAMP BY on it.

Upvotes: 1

Related Questions