Aggregate within 7 days

ggonong 11 Reputation points
2023-09-26T19:48:56.24+00:00

Hi All,

I have a stream analytics query that aggregates data within 7 days, but seems it is not getting data through in the output (cosmos db).

Here is my sample query:

SELECT a.Name, b.TotalAmt

FROM table1 AS a

LEFT OUTER JOIN (

SELECT Id, SUM(Amount) AS TotalAmt

FROM table2

GROUP BY TumblingWindow( dd, 7 ), Id

) AS b ON a.Id = b.Id AND DATEDIFF(dd, a, b) BETWEEN 0 AND 6

This query executes and giving me a sample output. But when I start the job, the data is not getting through to the output.

Is it maybe because the data is not worth 7 days?

Cheers,

Azure Stream Analytics
Azure Stream Analytics
An Azure real-time analytics service designed for mission-critical workloads.
342 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ShaikMaheer-MSFT 38,321 Reputation points Microsoft Employee
    2023-09-27T06:26:16.32+00:00

    Hi ggonong,

    Thank you for posting query in Microsoft Q&A Platform.

    It is possible that the delay in output is due to the large time window of 7 days. When a Stream Analytics job starts, the input events are read. But, there can be a delay in the output, in certain circumstances. Large time values in temporal query elements can contribute to the output delay. To produce the correct output over large time windows, the streaming job reads data from the latest time possible to fill the time window. The data can be up to seven days past. No output produces until the outstanding input events are read. This problem can surface when the system upgrades the streaming jobs. When an upgrade takes place, the job restarts. Such upgrades generally occur once every couple of months.

    One mitigation for this kind of first output delay is to use query parallelization techniques, such as partitioning the data. Or, you can add more Streaming Units to improve the throughput until the job catches up.

    You can also check the following:

    • Check if the input data is being received by the Stream Analytics job. You can do this by checking the input metrics in the Monitoring section of the Stream Analytics job.
    • Check if the output data is being written to the Cosmos DB. You can do this by checking the output metrics in the Monitoring section of the Stream Analytics job.
    • Check if there are any errors in the job. You can do this by checking the job logs in the Monitoring section of the Stream Analytics job.

    If you are still unable to resolve the issue, please provide more information about the job and the input data so that I can assist you better.