KEEP Clause

Syntax

KEEP {{[EVERY] count ROW[S][SLACK slackcount][PER(col1[,…])]} | ALL [ROW[S]]}

KEEP [EVERY] interval [PER (col1[,…])]

KEEP [EVERY] { count_policy | time_policy } | ALL;

Components

`count_policy`	Specify the maximum number of records that will be retained in the window as either a simple maximum `nn ROWS` or a maximum with some slack `nn ROWS SLACK mm`. A larger slack value improves performance by reducing the need to delete one row every time a row is inserted. The number of rows, nn, and the slack value, mm, can be either an integer value or an expression. Note: The `SLACK` component cannot be used with the `EVERY` modifier.
`time_policy`	Specify the length of time that records will be retained in the window as described in the Intervals topic.
`ALL`	Specifies that all of the rows received will be retained.
`EVERY`	Specifies that when the maximum number of records is exceeded or the time interval expires, every retained record is deleted. When this modifier is used, the resulting window is a Jumping Window. Otherwise, the resulting window is a Sliding Window
`PER`	Specifies that the retention policy will be applied to groups of rows rather than at the window level.

Usage

The KEEP clause defines a retention policy for a Named or Unnamed Window. Window retention policies include time-based policies (the time duration for which a window retains rows) and count-based policies (the maximum number of rows that the window can retain). If you omit the KEEP clause from a window definition, the default policy is KEEP ALL.

Including the EVERY modifier in the KEEP clause produces a Jumping Window, which deletes all of the retained rows when the time interval expires or a row arrives that would exceed the maximum number of rows.

Specifying the KEEP clause without the EVERY modifier produces a Sliding Window, which deletes individual rows once a maximum age is reached or the maximum number of rows are retained. Specifying a SLACK value causes the retention mechanism to get triggered when the number of stored rows equals (count+slackcount) as opposed to count. When specifying a Sliding Window with a count-based retention policy, you can specify a SLACK value to enhance performance by requiring less frequent cleaning of memory stores. Slack cannot be specified for windows using time-based retention policies.

The location of the KEEP clause in the CREATE WINDOW statement determines whether a named or an unnamed window is created. When the KEEP clause is specified for the window being created, a Named Window is created. If there is a KEEP clause in the query portion of the statement, however, an Unnamed Window is implicitly created. This is the case where there is a KEEP clause attached to the FROM clause of the query.

Note: The SLACK value cannot be used with the EVERY modifier, and thus cannot be used in a Jumping Windows retention policy.

Use the PER sub-clause within the KEEP clause syntax to retain data based on content for both named and unnamed windows. The feature supports both row-based and time-based retention. Rather than applying the retention policy at the window level, it will be applied to individual groups of rows based on the PER expression.

Note that unnamed windows can be created on delta streams or windows, but they cannot be created on streams. Windows on streams must be created explicitly using a CREATE WINDOW statement.

The following example creates a sliding window that retains 2 rows for each unique value of Symbol. Once 2 records have been stored for any unique Symbol value, arrival of a third record (with the same Symbol value) will result in deletion of the oldest stored record with the same Symbol value.

CREATE SCHEMA TradesSchema (
        Id integer,
        TradeTime date,
        Venue string,
        Symbol string,
        Price float,
        Shares integer )
;

CREATE INPUT WINDOW TradesWin1
    SCHEMA TradesSchema
    PRIMARY KEY(Id)
    KEEP 2 ROWS PER(Symbol)
;

The following example creates a jumping window that retains 5 seconds worth of data for each unique value of Symbol.

CREATE SCHEMA TradesSchema (
        Id integer,
        TradeTime date,
        Venue string,
        Symbol string,
        Price float,
        Shares integer )
;

CREATE INPUT WINDOW TradesWin2
    SCHEMA TradesSchema
    PRIMARY KEY(Id)
    KEEP EVERY 5 SECONDS PER(Symbol)
;