Both named windows and tables can have columns that hold events as column values, as further described in Section 6.12, “Events As Property”.

// Declare a table to hold a Count-min sketch approximate count per feed
create table AppoximateCountPerWord (feed string, approx countMinSketch())

// Declare a named window to hold 10 seconds of stock tick events
create window TenSecOfTicksWindow#time(10 sec) as StockTickEvent

select count(*) from TenSecOfTicksWindow

// does not continously output for table changes
select * from AppoximateCountPerWord

on event_type[(filter_criteria)] [as stream_name]
[insert into insert_into_def]
select select_list
from window_or_table_name [as stream_name]
[where criteria_expression]
[group by grouping_expression_list]
[having grouping_search_conditions]
[order by order_by_expression_list]

The event_type is the name of the type of events that trigger the query against the named window or table. It is optionally followed by filter_criteria which are filter expressions to apply to arriving events. The optional as keyword can be used to assign a stream name. Patterns or named windows can also be specified in the on clause, see the samples in Section 6.7.1, “Using Patterns in the On Delete Clause” (for a named window as a trigger only insert stream events trigger actions) (tables cannot be triggers).

The insert into clause works as described in Section 5.10, “Merging Streams and Continuous Insertion: the Insert Into Clause”. The select clause is described in Section 5.3, “Choosing Event Properties And Events: the Select Clause”. For all clauses the semantics are equivalent to a join operation: The properties of the triggering event or events are available in the select clause and all other clauses.

The window_or_table_name in the from clause is the name of the named window or table to select rows from. The as keyword is also available to assign a stream name to the table or named window. The as keyword is helpful in conjunction with wildcard in the select clause to select rows via the syntax select streamname.* .

The optional where clause contains a criteria_expression that correlates the arriving (triggering) event to the rows to be considered from the table or named window. The criteria_expression may also simply filter for rows to be considered by the query.

The group by clause, the having clause and the order by clause are all optional and work as described in earlier chapters.

Queries against tables and named windows work the same. The examples herein use the OrdersNamedWindow named window and the SecuritySummaryTable table to provide examples for each.

The sample statement below outputs, when a query event arrives, the count of all rows held by the SecuritySummaryTable table:

on QueryEvent select count(*) from SecuritySummaryTable

This sample query outputs the total volume per symbol ordered by symbol ascending and only non-zero volumes of all rows held by the OrdersNamedWindow named window:

on QueryEvent
select symbol, sum(volume) from OrdersNamedWindow
group by symbol having volume > 0 order by symbol

When using wildcard (*) to select from streams in an on-select clause, each stream, that is the triggering stream and the selected-upon table or named window, are selected, similar to a join. Therefore your wildcard select returns two columns: the triggering event and the selection result row, for each row.

on QueryEvent as queryEvent
select * from OrdersNamedWindow as win

The query above returns a queryEvent column and a win column for each event. If only a single stream's event is desired in the result, use select win.* instead.

Upon arrival of a QueryEvent event, this statement selects all rows in the OrdersNamedWindow named window:

on QueryEvent select win.* from OrdersNamedWindow as win

The engine executes the query on arrival of a triggering event, in this case a QueryEvent. It posts the query results to any listeners to the statement, in a single invocation, as the new data array.

The where clause filters and correlates rows in the table or named window with the triggering event, as shown next:

on QueryEvent(volume>0) as query
select query.symbol, query.volume, win.symbol  from OrdersNamedWindow as win
where win.symbol = query.symbol

Upon arrival of a QueryEvent, if that event has a value for the volume property that is greater than zero, the engine executes the query. The query considers all events currently held by the OrdersNamedWindow that match the symbol property value of the triggering QueryEvent event.

on OrdersNamedWindow as trig 
select onw.symbol, sum(onw.volume) 
from OrdersNamedWindow as onw 
where onw.symbol = trig.symbol

For updating a table or named window and for simultaneously checking if the updated row exists or for atomic update-insert operation on a named window or table, consider using on-merge as described in Section 6.8, “Triggered Upsert using the On-Merge Clause”. On-merge is similar to the SQL merge clause and provides what is known as an "Upsert" operation: Update existing events or if no existing event(s) are found then insert a new event, all in one atomic operation provided by a single EPL statement.

The syntax for the on update clause is as follows:

on event_type[(filter_criteria)] [as stream_name]
update window_or_table_name [as stream_name]
set mutation_expression [, mutation_expression [,...]]
[where criteria_expression]

The event_type is the name of the type of events that trigger an update of rows in a named window. It is optionally followed by filter_criteria which are filter expressions to apply to arriving events. The optional as keyword can be used to assign a name for use in expressions and the where clause. Patterns and named windows can also be specified in the on clause.

The window_or_table_name is the name of the table or named window to update rows. The as keyword is also available to assign a name to the named window or table.

After the set keyword follows a list of comma-separated mutation_expression expressions. A mutation expression is any valid EPL expression. Subqueries may by part of expressions however aggregation functions and the prev or prior function may not be used in expressions.

The below table shows some typical mutation expessions:

property_name = value_expression

price = 10, side = 'BUY'

alias_or_windowname.methodname(...)

orderWindow.clear()

property_name.methodname(...)

accountMap.clear()

functionname(...)

clearQuantities(orderRow)

on UpdateOrderEvent update OrdersNamedWindow set price = 0

on ResetEvent update SecuritySummaryTable set active = false where active = true

on NewOrderEvent(volume>0) as myNewOrders
update OrdersNamedWindow as myNamedWindow 
set price = myNewOrders.price
where myNamedWindow.symbol = myNewOrders.symbol

on pattern [every ord=OrderUpdateEvent(volume>0) or every flush=FlushOrderEvent] 
update OrdersNamedWindow as win
set price = case when ord.price is null then flush.price else ord.price end
where ord.id = win.id or flush.id = win.id

on UpdateEvent as upd
update MyWindow as win
set field_a = 1, 
  field_b = win.field_a, // assigns the value 1 
  field_c = initial.field_a // assigns the field_a original value before update

on UpdateEvent as upd update MyWindow as win set copyFields(win, upd, initial)

on UpdateEvent update MyWindow as win set someproperty.clear()

on UpdateEvent as upd update MyWindow as win set win.populateFrom(upd)

on event_type[(filter_criteria)] [as stream_name]
merge [into] window_or_table_name [as stream_name]
[where criteria_expression]
  when [not] matched [and search_condition]
    then [
      insert [into streamname]
          [ (property_name [, property_name] [,...]) ] 
          select select_expression [, select_expression[,...]]
          [where filter_expression]
      |
      update set mutation_expression [, mutation_expression [,...]]
          [where filter_expression]
      |
      delete
          [where filter_expression]
    ]
    [then [insert|update|delete]] [,then ...]
  [when ...  then ... [...]] 

After update follows the set keyword and one or more mutation expressions. For mutation expressions please see Section 6.6, “Updating Data: the On Update clause”. It follows an optional where-clause that may return Boolean false to indicate that the action should not be applied.

After delete follows an optional where-clause that may return Boolean false to indicate that the action should not be applied.

When according to the where-clause criteria_expression the engine finds no rows in the named window or table that match the condition, the engine evaluates each when not matched clause. If the optional search condition returns true or no search condition was provided then the engine performs all of the actions listed after each then.

When according to the where-clause criteria_expression the engine finds one or more rows in the named window or table that match the condition, the engine evaluates each when matched clause. If the optional search condition returns true or no search condition was provided the engine performs all of the actions listed after each then.

The engine executes when matched and when not matched in the order specified. If the optional search condition returns true or no search condition was specified then the engine takes the associated action (or multiple actions for multiple then keywords). When the block of actions completed the engine proceeds to the next matching row, if any. After completing all matching rows the engine continues to the next triggering event if any.

On-merge can be used with tables and named windows. The examples herein declare a ProductWindow named window and also use the SecuritySummaryTable table to provide examples for each.

This example statement updates the SecuritySummaryTable table when a ResetEvent arrives setting the active column's value to false:

on ResetEvent merge SecuritySummaryTable
when matched and active = true then update set active = false

A longer example utilizing a named window follows. We start by declaring a schema that provides a product id and that holds a total price:

create schema ProductTotalRec as (productId string, totalPrice double)

We create a named window that holds a row for each unique product:

create window ProductWindow#unique(productId) as ProductTotalRec

The events for this example are order events that hold an order id, product id, price, quantity and deleted-flag declared by the next schema:

create schema OrderEvent as (orderId string, productId string, price double, 
    quantity int, deletedFlag boolean)

The following EPL statement utilizes on-merge to total up the price for each product based on arriving order events:

on OrderEvent oe
  merge ProductWindow pw
  where pw.productId = oe.productId
  when matched
    then update set totalPrice = totalPrice + oe.price
  when not matched 
    then insert select productId, price as totalPrice

In the above example, when an order event arrives, the engine looks up in the product named window the matching row or rows for the same product id as the arriving event. In this example the engine always finds no row or one row as the product named window is declared with a unique data window based on product id. If the engine finds a row in the named window, it performs the update action adding up the price as defined under when matched. If the engine does not find a row in the named window it performs the insert action as defined under when not matched, inserting a new row.

The insert keyword may be followed by a list of columns as shown in this EPL snippet:

// equivalent to the insert shown in the last 2 lines in above EPL
...when not matched 
    then insert(productId, totalPrice) select productId, price

The second example demonstrates the use of a select-clause with wildcard, a search condition and the delete keyword. It creates a named window that holds order events and employs on-merge to insert order events for which no corresponding order id was found, update quantity to the quantity provided by the last arriving event and delete order events that are marked as deleted:

create window OrderWindow#keepall as OrderEvent

on OrderEvent oe
  merge OrderWindow pw
  where pw.orderId = oe.orderId
  when not matched 
    then insert select *
  when matched and oe.deletedFlag=true
    then delete
  when matched
    then update set pw.quantity = oe.quantity, pw.price = oe.price

In the above example the oe.deletedFlag=true search condition instructs the engine to take the delete action only if the deleted-flag is set.

You may specify multiple actions by providing multiple then keywords each followed by an action. Each of the insert, update and delete actions can itself have a where-clause as well. If a where-clause exists for an action, the engine evaluates the where-clause and applies the action only if the where-clause returns Boolean true.

This example specifies two update actions and uses the where-clause to trigger different update behavior depending on whether the order event price is less than zero. This example assumes that the host application defined a clearorder user-defined function, to demonstrate calling a user-defined function as part of the update mutation expressions:

on OrderEvent oe
  merge OrderWindow pw
  where pw.orderId = oe.orderId
  when matched
    then update set clearorder(pw) where oe.price < 0
    then update set pw.quantity = oe.quantity, pw.price = oe.price where oe.price >= 0

To insert events into another stream and not the named window, use insert into streamname.

In the next example each matched-clause contains two actions, one action to insert a log event and a second action to insert, delete or update:

on OrderEvent oe
  merge OrderWindow pw
  where pw.orderId = oe.orderId
  when not matched 
    then insert into LogEvent select 'this is an insert' as name
    then insert select *
  when matched and oe.deletedFlag=true
    then insert into LogEvent select 'this is a delete' as name
    then delete
  when matched
    then insert into LogEvent select 'this is a update' as name
    then update set pw.quantity = oe.quantity, pw.price = oe.price

While the engine evaluates and executes all actions listed under the same matched-clause in order, you may not rely on updated field values of an earlier action to trigger the where-clause of a later action. Similarly you should avoid simultaneous update and delete actions for the same match: the engine does not guarantee whether the update or the delete take final affect.

Your application can subscribe a listener to on merge statements to determine inserted, updated and removed events. Statements post any events that are inserted to, updated or deleted from a named window to all listeners attached to the statement as new data and removed data.

The following limitations apply to on-merge statements:

The syntax to create an explicit index on a named window or table is:

create [unique] index index_name on window_or_table_name (
  column_expression [hash|btree|index_type_expression] 
  [, column_expression] [hash|btree|index_type_expression] 
  [,...]
)

The optional unique keyboard indicates that the column expressions uniquely identify rows. If unique is not specified the index allows duplicate rows.

The index_name is the name assigned to the index. The name uniquely identifies the index and is used in engine query plan logging.

The window_or_table_name is the name of an existing table or named window. If the named window or table has rows already, the engine builds an index for the rows.

After the table name or named window name follows a list of pairs of column_expression column expression and index type.

A column expression is the expression that is subject to index building. Typically a column expression is an event property or column name. For special application-provided or spatial indexes other column expressions are allowed and such indexes may allow multiple columns to be combined.

Following each column expression you may specify the index type by providing the optional hash or btree keywords or an index_type_expression. For special application-provided or spatial indexes please use the index_type_expression.

If you specify no keyword or the hash keyword for a property, the index will be a hash-based (unsorted) index in respect to that property. If you specify the btree keyword, the index will be a binary-tree-based sorted index in respect to that property. You may combine hash and btree properties for the same index. Specify btree for a property if you expect to perform numerical or string comparison using relational operators (<, >, >=, <=), the between or the in keyword for ranges and inverted ranges. Use hash (the default) instead of btree if you expect to perform exact comparison using =.

For hash and btree index types the column expression must be an event property or column name. Expressions such as col+1 are not currently supported for hash and btree index types but are supported for other index types.

The create table syntax is the same for tables and named windows. The examples herein create a new UserProfileWindow named window and also use the SecuritySummaryTable table.

This sample EPL creates an non-unique index on the active column of table SecuritySummaryTable:

create index MyIndex on SecuritySummaryTable(active)

We list a few example EPL statements next that create a named window and create a single index:

// create a named window
create window UserProfileWindow#time(1 hour) select * from UserProfile

// create a non-unique index (duplicates allowed) for the user id property only
create index UserProfileIndex on UserProfileWindow(userId)

Next, execute an on-demand fire-and-forget query as shown below, herein we use the prepared version to demonstrate:

String query = "select * from UserProfileWindow where userId='Joe'";
EPOnDemandPreparedQuery prepared = epRuntime.prepareQuery(query);
// query performance excellent in the face of large number of rows
EPOnDemandQueryResult result = prepared.execute();
// ...later ...
prepared.execute();	// execute a second time

A unique index is generally preferable over non-unique indexes. For named windows, if your data window declares a unique data window (std:unique, std:firstunique, including intersections and grouped unique data windows) it is not necessary to create a unique index unless index sharing is enabled, since the engine considers the unique data window declaration in query planning.

The engine enforces uniqueness (e.g. unique constraint) for unique indexes. If your application inserts a duplicate row the engine raises a runtime exception when processing the statement and discards the row. The default error handler logs such an exception and continues.

For example, if the user id together with the profile id uniquely identifies an entry into the named window, your application can create a unique index as shown below:

// create a unique index on user id and profile id
create unique index UserProfileIndex on UserProfileWindow(userId, profileId)

By default, the engine builds a hash code -based index useful for direct comparison via equals (=). Filter expressions that look for ranges or use in, between do not benefit from the hash-based index and should use the btree keyword. For direct comparison via equals (=) then engine does not use btree indexes.

The next example creates a composite index over two fields symbol and buyPrice:

// create a named window
create window TickEventWindow#time(1 hour) as (symbol string, buyPrice double)

// create a non-unique index 
create index idx1 on TickEventWindow(symbol hash, buyPrice btree)

A sample fire-and-forget query is shown below (this time the API calls are not shown):

// query performance excellent in the face of large number of rows
select * from TickEventWindow where symbol='GE' and buyPrice between 10 and 20

Fire-and-Forget queries can be run against both tables and named windows. We use the term property and column interchangeably.

For selecting from named windows and tables, please see the examples in Section 16.5, “On-Demand Fire-And-Forget Query Execution”.

For data manipulation (insert, update, delete) queries, the on-demand query API returns the inserted, updated or deleted rows when the query executes against a named window.

insert into window_or_table_name [(property_names)]
values (value_expressions)

insert into window_or_table_name [(property_names)]
select value_expressions

String query = 
  "insert into OrdersWindow(orderId, symbol, price) values ('001', 'GE', 100)";
epService.getEPRuntime().executeQuery(query);

String query = 
  "insert into OrdersWindow(orderId, symbol, price) select '001', 'GE', 100";
epService.getEPRuntime().executeQuery(query);

insert into OrdersWindow
select '001' as orderId, 'GE' as symbol, 100 as price

insert into OrdersWindow values ('001', 'GE', 100)

update window_or_table_name [as stream_name]
set mutation_expression [, mutation_expression [,...]]
[where criteria_expression]

String query = "update OrdersNamedWindow set volume = 0 where volumne = 0";
epService.getEPRuntime().executeQuery(query);

delete from window_or_table_name [as stream_name]
	[where criteria_expression]

String query = "delete from OrdersNamedWindow where volume <= 0";
epService.getEPRuntime().executeQuery(query);

As outlined in Section 2.8, “Updating, Merging and Versioning Events”, revision event types process updates or new versions of events held by a named window.

A revision event type is simply one or more existing pre-configured event types whose events are related, as configured by static configuration, by event properties that provide same key values. The purpose of key values is to indicate that arriving events are related: An event amends, updates or adds properties to an earlier event that shares the same key values. No additional EPL is needed when using revision event types for merging event data.

Revision event types can be useful in these situations:

To better illustrate, consider a revision event type that represents events for creation and updates to user profiles. Let's assume the user profile creation events carry the user id and a full profile. The profile update events indicate only the user id and the individual properties that actually changed. The user id property shall serve as a key value relating profile creation events and update events.

A revision event type must be configured to instruct the engine which event types participate and what their key properties are. Configuration is described in Section 17.4.29, “Revision Event Type” and is not shown here.

Assume that an event type UserProfileRevisions has been configured to hold profile events, i.e. creation and update events related by user id. This statement creates a named window to hold the last 1 hour of current profiles per user id:

create window UserProfileWindow#time(1 hour) select * from UserProfileRevisions

insert into UserProfileWindow select * from UserProfileCreation

insert into UserProfileWindow select * from UserProfileUpdate

In revision event types, the term base event is used to describe events that are subject to update. Events that update, amend or add additional properties to base events are termed delta events. In the example, base events are profile creation events and delta events are profile update events.

Base events are expected to arrive before delta events. In the case where a delta event arrives and is not related by key value to a base event or a revision of the base event currently held by the named window the engine ignores the delta event. Thus, considering the example, profile update events for a user id that does not have an existing profile in the named window are not applied.

When a base or delta event arrives, the insert and remove stream output by the named window are the current and the prior version of the event. Let's come back to the example. As creation events arrive that are followed by update events or more creation events for the same user id, the engine posts the current version of the profile as insert stream (new data) and the prior version of the profile as remove stream (old data).

Base events are also implicitly delta events. That is, if multiple base events of the same key property values arrive, then each base event provides a new version. In the example, if multiple profile creation events arrive for the same user id then new versions of the current profile for that user id are output by the engine for each base event, as it does for delta events.

The expiry policy as specified by view definitions applies to each distinct key value, or multiple distinct key values for composite keys. An expiry policy re-evaluates when new versions arrive. In the example, user profile events expire from the time window when no creation or update event for a given user id has been received for 1 hour.

Several strategies are available for merging or overlaying events as the configuration chapter describes in greater detail.

Any of the Map, XML and JavaBean event representations as well as plug-in event representations may participate in a revision event type. For example, profile creation events could be JavaBean events, while profile update events could be java.util.Map events.

Delta events may also add properties to the revision event type. For example, one could add a new event type with security information to the revision event type and such security-related properties become available on the resulting revision event type.

The following restrictions apply to revision event types:

Columns in a named window and table may also hold an event or multiple events. More information on the insert into clause providing event columns is in Section 5.10.5, “Event as a Property”.

A sample declaration for a named window and a table is:

create schema InnerData (value string)

create table ContainerTable (innerdata InnerData)

create window ContainerWindow#time(30) as (innerdataArray InnerData[]) // array of events

The second sample creates a named window that specifies two columns: A column that holds an OrderEvent, and a column by name priceTotal. A matching insert into statement is also part of the sample:

create window OrdersWindow#time(30) as select this, price as priceTotal from OrderEvent

insert into OrdersWindow select order, price * unit as priceTotal  
from ServiceOrderEvent as order

Note that the this proprerty must exist on the event and must return the event class itself (JavaBean events only). The property type of the additional priceTotal column is the property type of the existing price property.