CCL

Jumping Windows

Using the KEEP clause in CCL, you can define 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). In version 5.0, retention policies always created sliding windows. In a sliding window, individual rows are deleted once a maximum age is reached or the maximum number of rows are retained (depending on the policy type).

Version 5.1 introduces the EVERY modifier to the KEEP clause. When you use the EVERY modifier in your KEEP clause, you create a retention policy that uses a jumping window. In a jumping window, all of the retained rows are deleted when the time interval expires or a row arrives that would exceed the maximum number of rows.

For example, if you create a retention policy to keep five rows of data in a sliding window, the window holds the first five rows as they feed into the stream. When the sixth row enters the stream, the first row slides out of the window, and the sixth row slides in. If you define a retention policy to keep five rows of data in a jumping window, the window holds the first five rows as they feed into the stream. When the sixth row enters the stream, all five retained rows jump out of the window, and the sixth row jumps in.

The following sample code, provided only for illustrative purposes, creates an unnamed jumping window on the source Source1 that will retain every row until the end of a 100 second interval.

CREATE WINDOW Win1
PRIMARY KEY DEDUCED
AS SELECT * FROM Source1 
KEEP EVERY 100 SECONDS;

For detailed information on syntax and usage, see the Programmers Reference.

Content-based Retention

When working with the KEEP clause in version 5.1, you can now use the PER sub-clause to retain data based on column content. In a time-based policy, data is retained for a specific time period (specified by interval) for each unique column/value combination. In a count-based policy, the specified number of rows (specified by count) are retained for each unique column/value combination. In either case, you can specify one or several columns, but you cannot use the same column more than once in the same PER sub-clause.

In the following sample, provided only for illustrative purposes, Win1 is a derived window that retains data for each unique value combination of Col1 and Col2. The window is a Jumping window that retains 5 rows. Upon arrival of the sixth unique value combination of Col1 and Col2, the first 5 rows are deleted and the sixth row is stored.

 CREATE WINDOW Win1
PRIMARY KEY DEDUCED
AS
SELECT * FROM InputWin
KEEP EVERY 5 ROWS PER (Col1, Col2);

For detailed information on syntax and usage, see the Programmers Reference.

Splitting Input into Multiple Outputs

The CREATE SPLITTER statement lets you evaluate an input stream for particular conditions. As the rows in the stream are evaluated, the splitter directs them to different target streams based on which conditions are met.

CREATE SPLITTER Splitter1 AS
WHEN Trades.Symbol IN ('IBM', 'ORCL' ) THEN ProcessHardWareStock, ProcessSoftwareStock
WHEN Trades.Symbol IN ('SAP', 'MSFT') THEN ProcessSoftwareStock
ELSE ProcessOtherStock
SELECT * FROM Trades;

When a stream is filtered into more than two target streams, a splitter is typically more efficient, both in terms of CPU utilization and throughput, than an equivalent construct composed of two or more streams that does a filter. Unlike other streams in Event Stream Processor, a splitter and all its target streams run in a single thread.

For detailed information on syntax and usage, see the Programmers Reference.

Automatic Key Generation

When you are working with input data that does not have a natural primary key, use the AUTOGENERATE clause to automatically generate values that can be used, for example, as a primary key for the data. Specify a column that uses the long data type. At runtime, values for that column are generated automatically for each insert in the stream. By default, values start at zero, but you can override this using the FROM clause and specifying a start value with either a parameter or an explicit long number.

The automatically-generated column is incremented only on an insert, and any preexisting value in an automatically-generated column is, on insert, ignored. On an update, delete, or upsert, a preexisting value in the automatically-generated column is used as-provided in the input row. This rule has the potential to produce duplicate rows in a window, so do not use the AUTOGENERATE clause with upserts, especially when the automatically generated column is a primary key.

CREATE INPUT STREAM Trades
SCHEMA (TradeId long, Symbol string, Shares integer, Price money(4))
AUTOGENERATE (TradeId);

For detailed information on syntax and usage, see the Programmers Reference.