CREATE WINDOW Statement

Syntax

CREATE INPUT WINDOW name schema_clause
primary_key_clause
[store_clause]
[keep_clause]
[autogenerate_clause];

CREATE [ LOCAL | OUTPUT ] WINDOW name schema_clause
{ PRIMARY KEY (column1, column2, ...) | PRIMARY KEY DEDUCED }
[store_clause]
[aging_clause]
[keep_clause]
[local-declare-block]
as_clause
;

Components

name	A name for the window being created.
schema_clause	Required for input windows, but optional for local and output windows. When the schema clause is not specified for local and output windows, it is automatically deduced by the compiler.
primary_key_clause	Set primary key.
store_clause	(Optional) Specifies the physical mechanism used to store the state of the records. If no clause is specified, project or module defaults apply.
autogenerate_clause	(Optional) Specify that the server will automatically generate values for one or more columns of datatype long. This can be used to generate a primary key for events that lack a natural key. Valid only for input windows. See AUTOGENERATE Clause for more information.
keep_clause	(Optional) Specifies the retention policy for the window. When not specified, the window uses the KEEP ALL retention policy as a default.
aging_clause	(Optional) Specifies the data aging policy. Used only with output or local windows.
local-declare-block	(Optional) Allows variable and function declarations that can be accessed in expressions in the query. You cannot define a local-declare-block on an input stream.
as_clause	Introduces a query to a statement.

Usage

The SCHEMA and PRIMARY KEY clauses are mandatory for an input window. The SCHEMA clause is optional for derived windows. If a SCHEMA is not defined the compiler implicitly determines it based on the projection list. For derived windows, the primary key may be either deduced or explicitly specified. There are a few exceptions to these rules, which is noted in the appropriate context.

The CREATE WINDOW statement can also includes a STORE clause to determine how records are stored, and a KEEP clause to determine how many records are stored and for how long. The window can be of type input, output, or local. Local and output windows can include an AGING clause that specifies the data aging policy.

Example

This example creates a local window containing only position records received in the last ten minutes. It also uses the AGES clause to flag records that have not updated in the last 5 seconds by setting the value in the AgeColumn.

CREATE WINDOW TradesAge
PRIMARY KEY DEDUCED
KEEP 10 MINUTES
AGES EVERY 5 SECONDS SET AgeColumn 5 TIMES
AS
SELECT Trades.*, 0 AgeColumn FROM Trades;

This example creates a local window containing only position records recieved in the last ten minutes. Inclusion of the local declare block reports how many records have been processed (including updates and deletes).

CREATE WINDOW TradesAge
PRIMARY KEY DEDUCED
KEEP 10 MINUTES
AGES EVERY 5 SECONDS SET AgeColumn 5 TIMES
DECLARE
    long counter := 0;

    long getRecordCount() {
        return ++counter;
    }
END
AS
SELECT Trades.*, getRecordCount() RecordCount, 0 AgeColumn FROM Trades;

The following statement creates a window that maintains only the last 1000 rows while also getting updates on the age of the rows. The TradeId value is automatically generated beginning at 0.

CREATE INPUT WINDOW FreshTrades
SCHEMA (TradeId long, Symbol string, Shares integer, Price money(4), Age integer)
PRIMARY KEY (TradeId)
KEEP 1000 ROWS
AGES EVERY 5 MINUTES SET Age 100 TIMES
AUTOGENERATE (TradeId);