CREATE WINDOW

To aggregate, join, or perform calculations on the data, you must create a bounded data set. The usual way to do this is with a window, which bounds the stream by a specified number of events, a period of time, or both. As discussed in the Concepts Guide (see Window), this may be a sliding window, which always contains the most recent set of events a jumping window, which breaks the stream up into successive chunks, or a session window, which breaks the stream up into chunks when there are gaps in the flow of events, that is, when no new event has been received for a specified period of time (the idle timeout).

The syntax for sliding and jumping windows is:

CREATE [ JUMPING ] WINDOW <name> 
OVER <stream name> 
KEEP {
 <int> ROWS |
 WITHIN <int> { SECOND | MINUTE | HOUR | DAY } |
 <int> ROWS WITHIN <int> { SECOND | MINUTE | HOUR | DAY } }
[ PARTITION BY <field name> ];

The syntax for session windows is:

CREATE SESSION WINDOW <name>
OVER <stream name>
IDLE TIMEOUT <int> { SECOND | MINUTE | HOUR | DAY }
[ PARTITION BY <field name> ];

If JUMPING or SESSION is not specified, the window uses the sliding mode (see the Concepts Guide for an explanation of the difference).

If PARTITION BY is specified, the criteria will be applied separately for each value of the specified field name.

This example from MultiLogApp creates a one-hour jumping window for each company's API usage stream:

CREATE JUMPING WINDOW CompanyWindow 
OVER CompanyApiUsageStream 
KEEP WITHIN 1 HOUR ON logTime 
PARTITION BY company;

The hour will be timed separately for each company, starting when the first event for that company is received.

The following is a detailed guide to the syntax for various types of windows. For more detailed discussion of windows in the context of applications, see Bounding data with windows.

Bounding data in batches (jumping) by system time

CREATE JUMPING WINDOW <name> OVER <stream name>
KEEP WITHIN <int> { SECOND | MINUTE | HOUR | DAY }
[ PARTITION BY <field name> ];

With this syntax, the window will output a batch of data each time the specified time interval has elapsed. For example, this window will emit a set of data every five minutes:

CREATE JUMPING WINDOW P2J_ST
OVER PosSource_TransformedStream
KEEP WITHIN 5 MINUTE;

Bounding data in batches (jumping) by event time

CREATE JUMPING WINDOW <name> OVER <stream name>
KEEP WITHIN <int> { SECOND | MINUTE | HOUR | DAY } ON <timestamp field name>
[ PARTITION BY <field name> ];

With this syntax, the window will output a batch of data each time it receives an event in which the specified timestamp field's value exceeds oldest event in the window by the specified amount of time. For example, assuming data is received continuously, this window will emit a set of data every five minutes:

CREATE JUMPING WINDOW P5J_ET
OVER PosSource_TransformedStream
KEEP WITHIN 5 MINUTE 
ON dateTime;

Bounding data in batches (jumping) by event count

CREATE JUMPING WINDOW <name>
OVER <stream name>
KEEP <int> ROWS
WITHIN <int>
[ PARTITION BY <field name> ];

With this syntax, the window will output a batch of data each time it contains the specified number of events. For example, the following will break the stream up into batches of 100 events:

CREATE JUMPING WINDOW P11J_ROWS
OVER PosSource_TransformedStream
KEEP 100 ROWS;

Bounding data continuously (sliding) by time

CREATE WINDOW <name> OVER <stream name>
KEEP WITHIN <int> { SECOND | MINUTE | HOUR | DAY }
[ ON <timestamp field name> ] ]
[ SLIDE <int> { SECOND | MINUTE | HOUR | DAY } ]
[ PARTITION BY <field name> ];

With this syntax, the window emits data every time an event is added to the window, first removing any events that exceed the specified time interval from the window. For example, the following will emit the events received in the past five minutes each time it receives a new event:

CREATE WINDOW P1S_ST
OVER PosSource_TransformedStream
KEEP WITHIN 5 MINUTE;

The following is similar but uses event time:

CREATE WINDOW P4S_ET 
OVER PosSource_TransformedStream 
KEEP WITHIN 5 MINUTE 
ON dateTime;

If you want to get events less often, use the Output interval (UI) / SLIDE (TQL) option. For example, the following will emit the past five minutes of events once a minute:

CREATE WINDOW P3S_ST_OI
OVER PosSource_TransformedStream
KEEP WITHIN 5 MINUTE
SLIDE 1 MINUTE;

CREATE WINDOW P6S_ET_OI
OVER PosSource_TransformedStream
KEEP WITHIN 5 MINUTE
ON dateTime
SLIDE 1 MINUTE;

Bounding data continuously (sliding) by event count

CREATE WINDOW <name> OVER <stream name>
KEEP <int> ROWS 
[ SLIDE <int> ]
[ PARTITION BY <field name> ];

With this syntax, when the window has received the specified number of events, it will emit its contents. From then on, every time it receives a new event, it will remove the event that has been in the window the longest, then emit the remaining events contents. For example, the following will send the first 100 events it receives, and from then on send the most recent 100 events every time it receives a new one:

CREATE WINDOW P10S_ROWS
OVER PosSource_TransformedStream
KEEP 100 ROWS;

If you want to get events less often, use the Output interval (UI) / SLIDE (TQL) option. For example, the following will emit the most recent 100 events every tenth event:

CREATE WINDOW P12S_ROWS_OI 
OVER PosSource_TransformedStream 
KEEP 100 ROWS 
SLIDE 10; 

Advanced window settings (RANGE / Timeout)

When a window's size is based on events (KEEP ROWS) or event time (KEEP WITHIN <int> <time unit> ON <timestamp field name>), the window may not jump for far longer than is desired. Use the RANGE or Timeout property to force the window to jump within a set period. For example:

CREATE JUMPING WINDOW MyWindow OVER MyStream KEEP WITHIN 5 MINUTE ON DateTime

If there is an hour gap between events, the window could be open for over an hour without sending any data. To prevent that, use the Timeout (UI) or RANGE (TQL) option. Use the following when the window size is based on event time:

CREATE [ JUMPING ] WINDOW <name> OVER <stream name> KEEP RANGE <int> { SECOND | MINUTE | HOUR | DAY } ON <timestamp field name> WITHIN <int> { SECOND | MINUTE | HOUR | DAY } [ PARTITION BY <field name> ];

Note

Note that Timeout / RANGE is always based on system time.

Use the following when the window size is based on event count:

CREATE [ JUMPING ] WINDOW <name> OVER <stream name> KEEP <int> ROWS WITHIN <int> { SECOND | MINUTE | HOUR | DAY } [ PARTITION BY <field name> ];

Note

Note that when Timeout is used with used with Time it maps to RANGE, but when used with Events it maps to WITHIN.

Jumping by event time

With the following settings, when events enter the window steadily, the window will jump every five minutes, but if there is a gap in events, the window will always jump within six minutes.

TQL: CREATE JUMPING WINDOW P8J_ET_TO OVER PosSource_TransformedStream KEEP RANGE 6 MINUTE ON dateTime WITHIN 5 MINUTE;

UI: Mode: Jumping Size of Window: Advanced Time: 5 minute Timeout: 6 minute On: logTime

Jumping by event count

The following will emit a batch of events every time the count reaches 100 or ten seconds has elapsed from the last time it emitted data:

TQL: CREATE JUMPING WINDOW P14J_ROWS_TO OVER PosSource_TransformedStream KEEP 100 ROWS WITHIN 10 SECOND;

UI: Mode: Jumping Size of Window: Advanced Events: 100 Timeout: 10 second

Sliding by event time

With the following settings, when there is a gap in events, the window will always emit its contents within six minutes.

TQL: CREATE WINDOW P7S_ET_TO OVER PosSource_TransformedStream KEEP RANGE 6 MINUTE ON dateTime WITHIN 5 MINUTE;

UI: Mode: Sliding Size of Window: Advanced Time: 5 minute Timeout: 6 minute On: dateTime

Sliding by event count

With these settings, when there is a gap in events, the window will always emit its contents within six minutes.

TQL: CREATE WINDOW P13S_ROWS_TO OVER PosSource_TransformedStream KEEP 100 ROWS WITHIN 10 SECOND;

UI: Mode: Sliding Size of Window: Advanced Events: 100 Timeout: 10

CREATE SESSION WINDOW MySessionWindow 
OVER MyStream
IDLE TIMEOUT 1 MINUTE;