Snapshot window (Azure Stream Analytics)

Article
08/02/2024

Snapshot windows groups events that have the same timestamp. Unlike other windowing types, which require a specific window function (such as SessionWindow(), you can apply a snapshot window by adding System.Timestamp() to the GROUP BY clause.

The following diagram illustrates a stream with a series of events and how they are mapped into snapshot windows.

Snapshot window diagram

System.Timestamp() can be considered in the GROUP BY clause as a key column or a snapshot window definition because it groups events into a window based on the equality of timestamps. When combined with another window function, System.Timestamp() is treated as a key and not as a window definition. System.Timestamp() does not produce an error when used with other window functions unlike when multiple window functions are used in the GROUP BY clause. Using both System.Timestamp() and a window function in the GROUP BY can be useful to batch the results.

Any expression that contains System.Timestamp() is not considered a window. For example, GROUP BY DATEPART(minute, System.Timestamp()) fails with the error "a window must be specified".

Syntax

System.Timestamp()

Examples

Basic example

The following example returns the count of tweets with the same topic type that occur at exactly the same time:

SELECT Topic, COUNT(*)
FROM TwitterStream TIMESTAMP BY CreatedAt
GROUP BY Topic, System.Timestamp()

Tumbling window snapshot example

A common usage for snapshot windows is to aggregate events after they have been grouped on a single timestamp via a preceding window function, like TumblingWindow below.

The following example returns the count of tweets with the same topic type by the same user within 30 minute intervals:

WITH PerInterval AS (
    SELECT Topic, User, COUNT(*)
    FROM TwitterStream TIMESTAMP BY CreatedAt
    GROUP BY Topic, User, TumblingWindow(minute, 30)
)
SELECT * INTO UserTopicsCount FROM PerInterval

To find the number of users and total tweets per topic within the same interval, you can use the result of the previous query. Because of the tumbling window results will all have timestamps aligned to the 30 minute boundary, you can use a snapshot window to return events at each boundary since they all have same timestamp value.

SELECT Topic, Users = COUNT(*), Total = SUM (count)
FROM PerInterval
GROUP BY Topic, System.Timestamp()

The previous query returned the number of users and total tweets per topic within the same 30 minute interval. To get same results once per 2 hours, add a tumbling window of 2 hours to the GROUP BY clause.

The following query returns results from all four 30 minute intervals at the end of each 2 hour window.

SELECT Topic, Users = COUNT(*), Total = SUM (count)
FROM PerInterval
GROUP BY Topic, System.Timestamp(), Tumbling (hour, 2)

Aggregate windows example

You can use System.Timestamp() as one of the windows in the aggregate Windows() construct.

SELECT 
    TollId, 
    COUNT(*) 
FROM Input TIMESTAMP BY EntryTime 
GROUP BY 
    TollId, 
    Windows(
        TumblingWindow(minute, 10),
        TumblingWindow(minute, 20),
        System.Timestamp())

Share via