A retention policy specifies the maximum number of rows or the maximum period of time that data are retained in a window.
In CCL, you can specify a retention policy when defining a Window. You can also create an Unnamed Window by specifying a retention policy on a Window or Delta Stream when it is used as a source to another element.
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 with no modifier produces a Sliding Window, which deletes individual rows once a maximum age is reached or the maximum number of rows are retained.
In a count-based policy, a constant integer specifies the maximum number of rows retained in the window. You can use parameters in the count expression.
The default value for SLACK is 1, which means that after the window reaches the maximum number of records, every new record inserted deletes the oldest record. This causes a significant impact on performance. Larger slack value s improve performance by reducing the need to constantly delete rows.
Count-based retention policies can also support retention based on content/column values using the PER sub-clause. A PER sub-clause can contain an individual column or a comma-delimited list of columns. A column can only be used once in a PER sub-clause. Specifying the primary key or autogenerate columns as a column in the PER sub-clause will result in a compiler warning. This is because these are unique entities for which multiple values cannot be retained.
The following example creates a Sliding Window that retains the most recent 100 records that match the filter condition. Once there are 100 records in the window, the arrival of a new record causes the deletion of the oldest record in the window.
CREATE WINDOW Last100Trades PRIMARY KEY DEDUCED KEEP 100 ROWS AS SELECT * FROM Trades WHERE Trades.Volume > 1000;
Adding the SLACK value of 10 means the window may contain as many as 110 records before any records are deleted.
CREATE WINDOW Last100Trades PRIMARY KEY DEDUCED KEEP 100 ROWS SLACK 10 AS SELECT * FROM Trades WHERE Trades.Volume > 1000;
This example creates a Jumping Window named TotalCost from the source stream Trades. This window will retain a maximum of ten rows, and delete all ten retained rows on the arrival of a new row.
CREATE WINDOW TotalCost PRIMARY KEY DEDUCTED AS SELECT trd.*, trd.Price * trd.Size TotalCst FROM Trades trd KEEP EVERY 10 ROWS;
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) ;
In a Sliding Windows time-based policy, a constant interval expression specifies the maximum age of the rows retained in the window. In a Jumping Window time-based retention policy, all the rows produced in the specified time interval are deleted after the interval has expired.
The following example creates a Sliding Window that retains each record received for ten minutes. As each individual row exceeds the ten minute retention time limit, it is deleted.
CREATE WINDOW RecentPositions PRIMARY KEY DEDUCED KEEP 10 MINS AS SELECT * FROM Positions;
This example creates a Jumping Window named Win1 that keeps every row that arrives within the 100 second interval. When the time interval expires, all of the rows retained are deleted.
CREATE WINDOW Win1 PRIMARY KEY DEDUCED AS SELECT * FROM Source1 KEEP EVERY 100 SECONDS;
The PER sub-clause supports content-based data retention, wherein data is retained for a specific time period (specified by an interval) for each unique column value/combination. A PER sub-clause can contain a single column or a comma-delimited list of columns, but you can use each column only once in the same PER clause.
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) ;
When the insertion of one or more new rows into a window triggers deletion of preexisting rows (due to retention), the window propagates the inserted and deleted rows downstream to relevant streams and subscribers. However, the inserted rows are placed before the deleted rows, since the inserts trigger the deletes.
An aging policy can be set to flag records that have not been updated within a defined interval. This is useful for detecting records that may be stale. Aging policies are an advanced, optional feature for a window or other stateful element.