CREATE MATERIALIZED WINDOW Statement

Syntax 

CREATE [MEMORY|DISK] MATERIALIZED WINDOW window_identifier AS
  stream_identifier '['window_specification']'
;

Substitutable Fields

window_identifier

A unique name for the materialized window declaration.

stream_identifier

The name of the stream used to populate the materialized window with tuples.

window_specification

A description of the window in the format:

  SIZE size 
  {TIME | TUPLES | ON field_identifier_w}
  [PARTITION BY field_identifier_p[,...]]
size

The size of the window, expressed as either the number of tuples, an interval of time, or a range of values within a tuple field.

field_identifier_w

The tuple field used to set window size.

field_identifier_p

The tuple field to use to create windows for separate groupings of tuples. The PARTITION BY clause can only be used with tuple-based materialized windows.

Discussion

Important

The Materialized Window data construct remains available in StreamBase, but its use is discouraged. Materialized Windows may be superseded in a future release by a new feature with similar capabilities.

Since a stream is a potentially unlimited source of input data, some mechanism must exist to subdivide a stream into analyzable segments. A materialized window specification describes a managed view of tuples passing on a stream. The view can be based on a fixed number of tuples, a time interval, or a field value. The materialized window can optionally be partitioned into multiple windows based on a tuple field value.

A materialized window is much like a table. However, StreamBase transparently creates a primary key for the window. If desired, the CREATE INDEX statement can be used to define one, or more, secondary keys.

In a StreamSQL application, the tuples stored within the materialized window can be accessed through a SELECT statement in which the FROM clause references both an input stream and the materialized window. To insure that the result set returned by the SELECT statement is not empty, the FROM clause must use an OUTER JOIN to join the stream and materialized window. If defined, secondary key fields are used within the WHERE clause to limit the number of tuples returned from the materialized view.

Note that the square braces are a required part of the syntax (indicated by the single quotation marks). A materialized window specification immediately follows the identifier for the stream on which it is applied. The FROM clause of the SELECT statement then uses the window_identifier to reference the windowed stream. For example: 

CREATE MEMORY MATERIALIZED WINDOW window_identifier AS stream_identifier_1[SIZE 3 TUPLES];
CREATE INDEX index_identifier ON window_identifier [USING {HASH|BTREE}] (field_identifier[,...]);
SELECT ... FROM stream_identifier_2 OUTER JOIN window_identifier WHERE index_identifier...;

The target list of the SELECT clause can include fields from the tuple on stream_identifier_2 or from the tuple retrieved from the materialized window.

The SIZE and one of the choices TIME | TUPLES | ON field_identifier are required entries.

SIZE sets the size of the window to either a fixed interval of time, a fixed number of tuples, or a specified range of values within one of the input tuple's fields.

TIME | TUPLES | ON field_identifier determines whether the window is based on time, a fixed number of tuples, or a field value.

Using the TIME specification means that window size will be based on the system time on the computer running the StreamBase application. The value following SIZE is interpreted as seconds.

Using the TUPLE specification means that window size will be based on the number of tuples. The value following SIZE is interpreted as number of tuples.

Using the ON field_identifier specification means that window size will be based on the value of a field in the input tuple. The value following SIZE is interpreted as field values.

PARTITION BY identifies a field within the input tuple that will be used to group the incoming tuples. This clause can only be used with tuple-based materialized windows.

Related Topics

Back to Top ^