Time-based windows as well as pattern observers and guards take a time period as a parameter. Time periods follow the syntax below.

time-period : [year-part] [month-part] [week-part] [day-part] [hour-part] 
      [minute-part] [seconds-part] [milliseconds-part]

year-part : (number|variable_name) ("years" | "year")
month-part : (number|variable_name) ("months" | "month")
week-part : (number|variable_name) ("weeks" | "week")
day-part : (number|variable_name) ("days" | "day")
hour-part : (number|variable_name) ("hours" | "hour")
minute-part : (number|variable_name) ("minutes" | "minute" | "min")
seconds-part : (number|variable_name) ("seconds" | "second" | "sec")
milliseconds-part : (number|variable_name) ("milliseconds" | "millisecond" | "msec")

Some examples of time periods are:

10 seconds
10 minutes 30 seconds
20 sec 100 msec
1 day 2 hours 20 minutes 15 seconds 110 milliseconds
0.5 minutes
1 year
1 year 1 month

Variable names and substitution parameters '?' for prepared statements are also allowed as part of a time period expression.

A unit in the month part is equivalent to 30 days.

Comments can appear anywhere in the EPL or pattern statement text where whitespace is allowed. Comments can be written in two ways: slash-slash (// ...) comments and slash-star (/* ... */) comments.

Slash-slash comments extend to the end of the line:

// This comment extends to the end of the line.
// Two forward slashes with no whitespace between them begin such comments.

select * from MyEvent  // this is a slash-slash comment

// All of this text together is a valid statement.

Slash-star comments can span multiple lines:

/* This comment is a "slash-star" comment that spans multiple lines.
 * It begins with the slash-star sequence with no space between the '/' and '*' characters.
 * By convention, subsequent lines can begin with a star and are aligned, but this is 
 * not required.
 */		
select * from MyEvent  /* this also works */

Comments styles can also be mixed:

select field1, // first comment
  /* second comment*/  field2
  from MyEvent

Certain words such as select, delete or set are reserved and may not be used as identifiers. Please consult Appendix B, Reserved Keywords for the list of reserved keywords and permitted keywords.

Names of built-in functions and certain auxiliary keywords are permitted as event property names and in the rename syntax of the select clause. For example, count is acceptable.

Consider the example below, which assumes that 'last' is an event property of MyEvent:

// valid
select last, count(*) as count from MyEvent

This example shows an incorrect use of a reserved keyword:

// invalid
select insert from MyEvent

EPL offers an escape syntax for reserved keywords: Event properties as well as event or stream names may be escaped via the backwards apostrophe ` (ASCII 96) character.

The next example queries an event type by name Order (a reserved keyword) that provides a property by name insert (a reserved keyword):

// valid
select `insert` from `Order`

Escaping event property names is described in Section 2.2.1, “Escape Characters”.

The sample EPL below escapes the single quote in the string constant John's, and filters out order events where the name value matches:

select * from OrderEvent(name='John\'s')
// ...equivalent to...
select * from OrderEvent(name='John\u0027s')

The next EPL escapes the string constant Quote "Hello":

select * from OrderEvent(description like "Quote \"Hello\"")
// is equivalent to
select * from OrderEvent(description like "Quote \u0022Hello\u0022")

When building an escape string via the API, escape the backslash, as shown in below code snippet:

epService.getEPAdministrator().createEPL("select * from OrderEvent(name='John\\'s')");
// ... and for double quotes...
epService.getEPAdministrator().createEPL("select * from OrderEvent(
  description like \"Quote \\\"Hello\\\"\")");

EPL returns double-type values for division regardless of operand type. EPL can also be configured to follow Java rules for integer arithmetic instead as described in Section 15.4.22, “Engine Settings related to Expression Evaluation”.

Division by zero returns positive or negative infinity. Division by zero can be configured to return null instead.

select 'volume' as field1,
   "sleep" as field2, 
  "\u0041" as unicodeA

select true as field1, 
  false as field2

select 1 as field1, 
  -1 as field2, 
  1e2 as field3

select 1L as field1, 
  1l as field2

select 1.67 as field1, 
  167e-2 as field2, 
  1.67d as field3

select 1.2f as field1, 
  1.2F as field2

select 0x10 as field1

For declaring expressions that are visible across multiple EPL statements i.e. globally visible expressions please consult Section 5.19.1, “Declaring a Global Expression” that explains the create expression clause.

An expression declaration follows the lambda-style expression syntax. This syntax was chosen as it typically allows for a shorter and more concise expression body that can be easier to read then most procedural code.

The syntax for an expression declaration is:

expression expression_name { expression_body }

An expression declaration consists of the expression name and an expression body. The expression_name is any identifier. The expression_body contains optional parameters and the expression. The parameter types and the return type of the expression is determined by the engine and do not need to be specified.

Parameters to a declared expression can be a stream name, pattern tag name or wildcard (*). Use wildcard to pass the event itself to the expression. In a join or subquery, or more generally in an expression where multiple streams or pattern tags are available, the EPL must specify the stream name or pattern tag name and cannot use wildcard.

In the expression body the => lambda operator reads as "goes to". The left side of the lambda operator specifies the input parameters (if any) and the right side holds the expression. The lambda expression x => x * x is read "x goes to x times x".

In the expression body, if your expression takes no parameters, you may simply specify the expression and do not need the => lambda operator.

If your expression takes one parameters, specify the input parameter name followed by the => lambda operator and followed by the expression. The synopsis for use with a single input parameter is:

expression_body:   input_param_name => expression 

If your expression takes two or more parameters, specify the input parameter names in parenthesis followed by the => lambda operator followed by the expression. The synopsis for use with a multiple input parameter is:

expression_body:   (input_param [,input_param [,...]]) => expression 

The following example declares an expression that returns two times PI (ratio of the circumference of a circle to its diameter) and demonstrates its use in a select-clause:

expression twoPI { Math.PI * 2} select twoPI() from SampleEvent

The next example declares an expression that accepts one parameter: a MarketData event. The expression computes a new "mid" price based on the buy and sell price:

expression midPrice { x => (x.buy + x.sell) / 2 } 
select midPrice(md) from MarketDataEvent as md

The variable name can be left off if event property names resolve without ambiguity.

This example EPL removes the variable name x:

expression midPrice { x => (buy + sell) / 2 } 
select midPrice(md) from MarketDataEvent as md

The next example EPL specifies wildcard instead:

expression midPrice { x => (buy + sell) / 2 } 
select midPrice(*) from MarketDataEvent

A further example that demonstrates two parameters is listed next. The example joins two streams and uses the price value from MarketDataEvent and the sentiment value of NewsEvent to compute a weighted sentiment:

expression weightedSentiment { (x, y) => x.price * y.sentiment } 
select weightedSentiment(md, news) 
from MarketDataEvent.std:lastevent() as md, NewsEvent.std:lastevent() news

Any expression can be used in the expression body including aggregations, variables, subqueries or further declared expressions. Sub-queries, when used without in or exists, must be placed within parenthesis.

An example subquery within a declared expression is shown next:

expression newsSubq(md) { 
    (select sentiment from NewsEvent.std:unique(symbol) where symbol = md.symbol) 
} 
select newsSubq(mdstream)
from MarketDataEvent mdstream

When using declared expressions please note these limitations:

The following scope rules apply for declared expressions:

Please see Chapter 18, Script Support for more information.

You may refer to a context in the EPL text by specifying the context keyword followed by a context name. Context are described in more detail at Chapter 4, Context and Context Partitions

The effect of referring to a context is that your statement operates according to the context dimensional information as declared for the context.

The synopsis is:

... context context_name ...

You may refer to a context in all statements except for the following types of statements:

The syntax for selecting all event properties in a stream is:

select * from stream_def

The following statement selects StockTick events for the last 30 seconds of IBM stock ticks.

select * from StockTick(symbol='IBM').win:time(30 sec)

You may well be asking: Why does the statement specify a time window here? First, the statement is meant to demonstrate the use of * wildcard. When the engine pushes statement results to your listener and as the statement does not select remove stream events via rstream keyword, the listener receives only new events and the time window could be left off. By adding the time window the pull API (iterator API or JDBC driver) returns the last 30 seconds of events.

The * wildcard and expressions can also be combined in a select clause. The combination selects all event properties and in addition the computed values as specified by any additional expressions that are part of the select clause. Here is an example that selects all properties of stock tick events plus a computed product of price and volume that the statement names 'pricevolume':

select *, price * volume as pricevolume from StockTick

When using wildcard (*), Esper does not actually read or copy your event properties out of your event or events, neither does it copy the event object. It simply wraps your native type in an EventBean interface. Your application has access to the underlying event object through the getUnderlying method and has access to the property values through the get method.

In a join statement, using the select * syntax selects one event property per stream to hold the event for that stream. The property name is the stream name in the from clause.

To choose the particular event properties to return:

select event_property [, event_property] [, ...] from stream_def

The following statement simply selects the symbol and price properties of stock ticks, and the total volume for stock tick events in a 60-second time window.

select symbol, price, sum(volume) from StockTick(symbol='IBM').win:time(60 sec)

The following statement declares a further view onto the event stream of stock ticks: the univariate statistics view (stat:uni). The statement selects the properties that this view derives from the stream, for the last 100 events of IBM stock ticks in the length window.

select datapoints, total, average, variance, stddev, stddevpa
from StockTick(symbol='IBM').win:length(100).stat:uni(volume)

The select clause can contain one or more expressions.

select expression [, expression] [, ...] from stream_def

The following statement selects the volume multiplied by price for a time batch of the last 30 seconds of stock tick events.

select volume * price from StockTick.win:time_batch(30 sec)

Event properties and expressions can be renamed using below syntax.

select [event_property | expression] as identifier [, ...]

The following statement selects volume multiplied by price and specifies the name volPrice for the resulting column.

select volume * price as volPrice from StockTick

Identifiers cannot contain the "." (dot) character, i.e. "vol.price" is not a valid identifier for the rename syntax.

If your statement is joining multiple streams, your may specify property names that are unique among the joined streams, or use wildcard (*) as explained earlier.

In case the property name in your select or other clauses is not unique considering all joined streams, you will need to use the name of the stream as a prefix to the property.

This example is a join between the two streams StockTick and News, respectively named as 'tick' and 'news'. The example selects from the StockTick event the symbol value using the 'tick' stream name as a prefix:

select tick.symbol from StockTick.win:time(10) as tick, News.win:time(10) as news
where news.symbol = tick.symbol

Use the wildcard (*) selector in a join to generate a property for each stream, with the property value being the event itself. The output events of the statement below have two properties: the 'tick' property holds the StockTick event and the 'news' property holds the News event:

select * from StockTick.win:time(10) as tick, News.win:time(10) as news

The following syntax can also be used to specify what stream's properties to select:

select stream_name.* [as name] from ...

The selection of tick.* selects the StockTick stream events only:

select tick.* from StockTick.win:time(10) as tick, News.win:time(10) as news
where tick.symbol = news.symbol

The next example uses the as keyword to name each stream's joined events. This instructs the engine to create a property for each named event:

select tick.* as stocktick, news.* as news 
from StockTick.win:time(10) as tick, News.win:time(10) as news
where stock.symbol = news.symbol

The output events of the above example have two properties 'stocktick' and 'news' that are the StockTick and News events.

The stream name itself, as further described in Section 5.4.5, “Using the Stream Name”, may be used within expressions or alone.

This example passes events to a user-defined function named compute and also shows insert-into to populate an event stream of combined events:

insert into TickNewStream select tick, news, MyLib.compute(news, tick) as result
from StockTick.win:time(10) as tick, News.win:time(10) as news
where tick.symbol = news.symbol

// second statement that uses the TickNewStream stream
select tick.price, news.text, result from TickNewStream

In summary, the stream_name.* streamname wildcard syntax can be used to select a stream as the underlying event or as a property, but cannot appear within an expression. While the stream_name syntax (without wildcard) always selects a property (and not as an underlying event), and can occur anywhere within an expression.

If your statement employs pattern expressions, then your pattern expression tags events with a tag name. Each tag name becomes available for use as a property in the select clause and all other clauses.

For example, here is a very simple pattern that matches on every StockTick event received within 30 seconds after start of the statement. The sample selects the symbol and price properties of the matching events:

select tick.symbol as symbol, tick.price as price
from pattern[every tick=StockTick where timer:within(10 sec)]

The use of the wildcard selector, as shown in the next statement, creates a property for each tagged event in the output. The next statement outputs events that hold a single 'tick' property whose value is the event itself:

select * from pattern[every tick=StockTick where timer:within(10 sec)]

You may also select the matching event itself using the tick.* syntax. The engine outputs the StockTick event itself to listeners:

select tick.* from pattern[every tick=StockTick where timer:within(10 sec)]

A tag name as specified in a pattern is a valid expression itself. This example uses the insert into clause to make available the events matched by a pattern to further statements:

// make a new stream of ticks and news available
insert into StockTickAndNews 
select tick, news from pattern [every tick=StockTick -> news=News(symbol=tick.symbol)]

// second statement to select from the stream of ticks and news
select tick.symbol, tick.price, news.text from StockTickAndNews

The optional istream, irstream and rstream keywords in the select clause control the event streams posted to listeners and observers to a statement.

If neither keyword is specified, and in the default engine configuration, the engine posts only insert stream events via the newEvents parameter to the update method of UpdateListener instances listening to the statement. The engine does not post remove stream events, by default.

The insert stream consists of the events entering the respective window(s) or stream(s) or aggregations, while the remove stream consists of the events leaving the respective window(s) or the changed aggregation result. See Chapter 3, Processing Model for more information on insert and remove streams.

The engine posts remove stream events to the oldEvents parameter of the update method only if the irstream keyword occurs in the select clause. This behavior can be changed via engine-wide configuration as described in Section 15.4.17, “Engine Settings related to Stream Selection”.

By specifying the istream keyword you can instruct the engine to only post insert stream events via the newEvents parameter to the update method on listeners. The engine will then not post any remove stream events, and the oldEvents parameter is always a null value.

By specifying the irstream keyword you can instruct the engine to post both insert stream and remove stream events.

By specifying the rstream keyword you can instruct the engine to only post remove stream events via the newEvents parameter to the update method on listeners. The engine will then not post any insert stream events, and the oldEvents parameter is also always a null value.

The following statement selects only the events that are leaving the 30 second time window.

select rstream * from StockTick.win:time(30 sec)

The istream and rstream keywords in the select clause are matched by same-name keywords available in the insert into clause. While the keywords in the select clause control the event stream posted to listeners to the statement, the same keywords in the insert into clause specify the event stream that the engine makes available to other statements.

Property or column names can optionally be qualified by a stream name and the provider URI. The syntax is:

[[provider_URI.]stream_name.]property_name

The provider_URI is the URI supplied to the EPServiceProviderManager class, or the string default for the default provider.

This example assumes the provider is the default provider:

select MyEvent.myProperty from MyEvent
// ... equivalent to ...
select default.MyEvent.myProperty from MyEvent

Stream names can also be qualified by the provider URI. The syntax is:

[provider_URI.]stream_name

The next example assumes a provider URI by name of Processor:

select Processor.MyEvent.myProperty from Processor.MyEvent

The optional distinct keyword removes duplicate output events from output. The keyword must occur after the select keyword and after the optional irstream keyword.

The distinct keyword in your select instructs the engine to consolidate, at time of output, the output event(s) and remove output events with identical property values. Duplicate removal only takes place when two or more events are output together at any one time, therefore distinct is typically used with a batch data window, output rate limiting, on-demand queries, on-select or iterator pull API.

If two or more output event objects have same property values for all properties of the event, the distinct removes all but one duplicated event before outputting events to listeners. Indexed, nested and mapped properties are considered in the comparison, if present in the output event.

The next example outputs sensor ids of temperature sensor events, but only every 10 seconds and only unique sensor id values during the 10 seconds:

select distinct sensorId from TemperatureSensorEvent output every 10 seconds

Use distinct with wildcard (*) to remove duplicate output events considering all properties of an event.

This example statement outputs all distinct events either when 100 events arrive or when 10 seconds passed, whichever occurs first:

select distinct * from TemperatureSensorEvent.win:time_length_batch(10, 100)

When selecting nested, indexed, mapped or dynamic properties in a select clause with distinct, it is relevant to know that the comparison uses hash code and the Java equals semantics.

For transposing an instance of a Java object returned by an expression to a stream use the transpose function as described in Section 9.4, “Select-Clause transpose Function”.

By default, for certain select-clause expressions that output events or a collection of events, the engine outputs the underlying event objects. With outputs we refer to the data passed to listeners, subscribers and inserted-into into another stream via insert-into.

The select-clause expressions for which underlying event objects are output by default are:

To have the engine output EventBean instance(s) instead, add @eventbean to the relevant expressions of the select-clause.

The sample EPL shown below outputs current data window contents as EventBean instances into the stream OutStream, thereby statements consuming the stream may operate on such instances:

insert into OutStream 
select prevwindow(s0) @eventbean as win 
from MyEvent.win:length(2) as s0

The next EPL consumes the stream and selects the last event:

select win.lastOf() from OutStream

It is not necessary to use @eventbean if an event type by the same name (OutStream in the example) is already declared and a property exist on the type by the same name (win in this example) and the type of the property is the event type (MyEvent in the example) returned by the expression. This is further described in Section 5.10.8, “Select-Clause Expression And Inserted-Into Column Event Type”.

The stream_def syntax for a filter-based event stream is as below:

event_stream_name [(filter_criteria)] [contained_selection] [.view_spec] [.view_spec] [...]

The event_stream_name is either the name of an event type or name of an event stream populated by an insert into statement or the name of a named window.

The filter_criteria is optional and consists of a list of expressions filtering the events of the event stream, within parenthesis after the event stream name.

The contained_selection is optional and is for use with coarse-grained events that have properties that are themselves one or more events, see Section 5.20, “Contained-Event Selection” for the synopsis and examples.

The view_spec are optional view specifications, which are combinable definitions for retaining events and for deriving information from events.

The following EPL statement shows event type, filter criteria and views combined in one statement. It selects all event properties for the last 100 events of IBM stock ticks for volume. In the example, the event type is the fully qualified Java class name org.esper.example.StockTick. The expression filters for events where the property symbol has a value of "IBM". The optional view specifications for deriving data from the StockTick events are a length window and a view for computing statistics on volume. The name for the event stream is "volumeStats".

select * from 
  org.esper.example.StockTick(symbol='IBM').win:length(100).stat:uni(volume) as volumeStats

Esper filters out events in an event stream as defined by filter criteria before it sends events to subsequent views. Thus, compared to search conditions in a where clause, filter criteria remove unneeded events early. In the above example, events with a symbol other then IBM do not enter the time window.

select * from com.mypackage.myevents.RfidEvent

select * from RfidEvent

select * from org.myorg.rfid.IRfidReadable

select * from RfidEvent(category="Perishable")

select * from com.mycompany.RfidEvent(MyRFIDLib.isInRange(x, y) or (x < 0 and y < 0))

select * from RfidEvent(zone=1, category=10)
...is equivalent to...
select * from RfidEvent(zone=1 and category=10)

mypackage.RfidEvent(x in [100:200], y in [0:100])

mypackage.RfidEvent(x between 100 and 200, y between 0 and 50)

mypackage.RfidEvent(x not in [0:100])

mypackage.RfidEvent(x not between 0 and 100)

mypackage.RfidEvent(category in ('Perishable', 'Container'))

mypackage.RfidEvent(category not in ('Household', 'Electrical'))

Event pattern expressions can also be used to specify one or more event streams in an EPL statement. For pattern-based event streams, the event stream definition stream_def consists of the keyword pattern and a pattern expression in brackets []. The syntax for an event stream definition using a pattern expression is below. As in filter-based event streams, an optional list of views that derive data from the stream can be supplied.

pattern [pattern_expression] [.view_spec] [.view_spec] [...]

The next statement specifies an event stream that consists of both stock tick events and trade events. The example tags stock tick events with the name "tick" and trade events with the name "trade".

select * from pattern [every tick=StockTickEvent or every trade=TradeEvent]

This statement generates an event every time the engine receives either one of the event types. The generated events resemble a map with "tick" and "trade" keys. For stock tick events, the "tick" key value is the underlying stock tick event, and the "trade" key value is a null value. For trade events, the "trade" key value is the underlying trade event, and the "tick" key value is a null value.

Lets further refine this statement adding a view the gives us the last 30 seconds of either stock tick or trade events. Lets also select prices and a price total.

select tick.price as tickPrice, trade.price as tradePrice, 
       sum(tick.price) + sum(trade.price) as total
  from pattern [every tick=StockTickEvent or every trade=TradeEvent].win:time(30 sec)

Note that in the statement above tickPrice and tradePrice can each be null values depending on the event processed. Therefore, an aggregation function such as sum(tick.price + trade.price)) would always return null values as either of the two price properties are always a null value for any event matching the pattern. Use the coalesce function to handle null values, for example: sum(coalesce(tick.price, 0) + coalesce(trade.price, 0)).

Views are used to specify an expiry policy for events (data window views) and also to derive data. Views can be staggered onto each other. See the section Chapter 12, EPL Reference: Views on the views available that also outlines the different types of views: Data Window views and Derived-Value views.

Views can optionally take one or more parameters. These parameters are expressions themselves that may consist of any combination of variables, arithmetic, user-defined function or substitution parameters for prepared statements, for example.

The example statement below outputs a count per expressway for car location events (contains information about the location of a car on a highway) of the last 60 seconds:

select expressway, count(*) from CarLocEvent.win:time(60) 
group by expressway

The next example serves to show staggering of views. It uses the std:groupwin view to create a separate length window per car id:

select cardId, expressway, direction, segment, count(*) 
from CarLocEvent.std:groupwin(carId).win:length(4) 
group by carId, expressway, direction, segment

The first view std:groupwin(carId) groups car location events by car id. The second view win:length(4) keeps a length window of the 4 last events, with one separate length window for each car id. The example reports the number of events per car id and per expressway, direction and segment considering the last 4 events for each car id only.

Note that the group by syntax is generally preferable over std:groupwin for grouping information as it is SQL-compliant, easier to read and does not create a separate data window per group. The std:groupwin in above example creates a separate data window (length window in the example) per group, demonstrating staggering views.

When views are staggered onto each other as a chain of views, then the insert and remove stream received by each view is the insert and remove stream made available by the view (or stream) earlier in the chain.

The special keep-all view keeps all events: It does not provide a remove stream, i.e. events are not removed from the keep-all view unless by means of the on-delete syntax or by revision events.

Data window views provide an expiry policy that indicates when to remove events from the data window, with the exception of the keep-all data window which has no expiry policy and the std:groupwin grouped-window view for allocating a new data window per group.

EPL allows the freedom to use multiple data window views onto a stream and thus combine expiry policies. Combining data windows into an intersection (the default) or a union can achieve a useful strategy for retaining events and expiring events that are no longer of interest. Named windows and the on-delete syntax provide an additional degree of freedom.

In order to combine two or more data window views there is no keyword required. The retain-intersection keyword is the default and the retain-union keyword may instead be provided for a stream.

The concept of union and intersection come from Set mathematics. In the language of Set mathematics, two sets A and B can be "added" together: The intersection of A and B is the set of all things which are members of both A and B, i.e. the members two sets have "in common". The union of A and B is the set of all things which are members of either A or B.

Use the retain-intersection (the default) keyword to retain an intersection of all events as defined by two or more data windows. All events removed from any of the intersected data windows are entered into the remove stream. This is the default behavior if neither retain keyword is specified.

Use the retain-union keyword to retain a union of all events as defined by two or more data windows. Only events removed from all data windows are entered into the remove stream.

The next example statement totals the price of OrderEvent events in a union of the last 30 seconds and unique by product name:

select sum(price) from OrderEvent.win:time(30 sec).std:unique(productName) retain-union

In the above statement, all OrderEvent events that are either less then 30 seconds old or that are the last event for the product name are considered.

Here is an example statement totals the price of OrderEvent events in an intersection of the last 30 seconds and unique by product name:

select sum(price) from OrderEvent.win:time(30 sec).std:unique(productName) retain-intersection

In the above statement, only those OrderEvent events that are both less then 30 seconds old and are the last event for the product name are considered. The number of events that the engine retains is the number of unique events per product name in the last 30 seconds (and not the number of events in the last 30 seconds).

For an intersection the engine retains the minimal number of events representing that intersection. Thus when combining a time window of 30 seconds and a last-event window, for example, the number of events retained at any time is zero or one event (and not 30 seconds of events).

When combining a batch window into an intersection with another data window the combined data window gains batching semantics: Only when the batch criteria is fulfilled does the engine provide the batch of intersecting insert stream events. Multiple batch data windows may not be combined into an intersection.

In below table we provide additional examples for data window intersections:

For advanced users and for backward compatibility, it is possible to configure Esper to allow multiple data window views without either of the retain keywords, as described in Section 15.4.12.2, “Configuring Multi-Expiry Policy Defaults”.

Your from clause may assign a name to each stream. This assigned stream name can serve any of the following purposes.

First, the stream name can be used to disambiguate property names. The stream_name.property_name syntax uniquely identifies which property to select if property names overlap between streams. Here is an example:

select prod.productId, ord.productId from ProductEvent as prod, OrderEvent as ord

Second, the stream name can be used with a wildcard (*) character to select events in a join, or assign new names to the streams in a join:

// Select ProductEvent only
select prod.* from ProductEvent as prod, OrderEvent

// Assign column names 'product' and 'order' to each event
select prod.* as product, ord.* as order from ProductEvent as prod, OrderEvent as ord

Further, the stream name by itself can occur in any expression: The engine passes the event itself to that expression. For example, the engine passes the ProductEvent and the OrderEvent to the user-defined function 'checkOrder':

select prod.productId, MyFunc.checkOrder(prod, ord) 
from ProductEvent as prod, OrderEvent as ord

Last, you may invoke an instance method on each event of a stream, and pass parameters to the instance method as well. Instance method calls are allowed anywhere in an expression.

The next statement demonstrates this capability by invoking a method 'computeTotal' on OrderEvent events and a method 'getMultiplier' on ProductEvent events:

select ord.computeTotal(prod.getMultiplier()) from ProductEvent as prod, OrderEvent as ord

Instance methods may also be chained: Your EPL may invoke a method on the result returned by a method invocation.

Assume that your product event exposes a method getZone which returns a zone object. Assume that the Zone class declares a method checkZone. This example statement invokes a method chain:

select prod.getZone().checkZone("zone 1") from ProductEvent as prod

The aggregate functions are further documented in Section 9.2, “Aggregation Functions”. You can use aggregate functions to calculate and summarize data from event properties. For example, to find out the total price for all stock tick events in the last 30 seconds, type:

select sum(price) from StockTickEvent.win:time(30 sec)

Here is the syntax for aggregate functions:

aggregate_function( [all | distinct] expression)

You can apply aggregate functions to all events in an event stream window or other view, or to one or more groups of events. From each set of events to which an aggregate function is applied, Esper generates a single value.

Expression is usually an event property name. However it can also be a constant, function, or any combination of event property names, constants, and functions connected by arithmetic operators.

For example, to find out the average price for all stock tick events in the last 30 seconds if the price was doubled:

select avg(price * 2) from StockTickEvent.win:time(30 seconds)

You can use the optional keyword distinct with all aggregate functions to eliminate duplicate values before the aggregate function is applied. The optional keyword all which performs the operation on all events is the default.

You can use aggregation functions in a select clause and in a having clause. You cannot use aggregate functions in a where clause, but you can use the where clause to restrict the events to which the aggregate is applied. The next query computes the average and sum of the price of stock tick events for the symbol IBM only, for the last 10 stock tick events regardless of their symbol.

select 'IBM stats' as title, avg(price) as avgPrice, sum(price) as sumPrice
from StockTickEvent.win:length(10)
where symbol='IBM'

In the above example the length window of 10 elements is not affected by the where clause, i.e. all events enter and leave the length window regardless of their symbol. If we only care about the last 10 IBM events, we need to add filter criteria as below.

select 'IBM stats' as title, avg(price) as avgPrice, sum(price) as sumPrice
from StockTickEvent(symbol='IBM').win:length(10)
where symbol='IBM'

You can use aggregate functions with any type of event property or expression, with the following exceptions:

Esper ignores any null values returned by the event property or expression on which the aggregate function is operating, except for the count(*) function, which counts null values as well. All aggregate functions return null if the data set contains no events, or if all events in the data set contain only null values for the aggregated expression.

When you include filters, the where condition, the group by clause and the having condition in an EPL statement the sequence in which each clause affects events determines the final result:

The following query illustrates the use of filter, where, group by and having clauses in one statement with a select clause containing an aggregate function.

select tickDataFeed, stddev(price)
from StockTickEvent(symbol='IBM').win:length(10) 
where volume > 1000
group by tickDataFeed 
having stddev(price) > 0.8

Esper filters events using the filter criteria for the event stream StockTickEvent. In the example above only events with symbol IBM enter the length window over the last 10 events, all other events are simply discarded. The where clause removes any events posted by the length window (events entering the window and event leaving the window) that do not match the condition of volume greater then 1000. Remaining events are applied to the stddev standard deviation aggregate function for each tick data feed as specified in the group by clause. Each tickDataFeed value generates one event. Esper applies the having clause and only lets events pass for tickDataFeed groups with a standard deviation of price greater then 0.8.

The keyed segmented context create context ... partition by and the group by clause as well as the built-in std:groupwin view are similar in their ability to group events but very different in their semantics. This section explains the key differences in their behavior and use.

The keyed segmented context as declared with create context ... partition by and context .... select ... creates a new context partition per key value(s). The engine maintains separate data window views as well as separate aggregations per context partition; thereby the keyed segmented context applies to both. See Section 4.2.2, “Keyed Segmented Context” for additional examples.

The group by clause works together with aggregation functions in your statement to produce an aggregation result per group. In greater detail, this means that when a new event arrives, the engine applies the expressions in the group by clause to determine a grouping key. If the engine has not encountered that grouping key before (a new group), the engine creates a set of new aggregation results for that grouping key and performs the aggregation changing that new set of aggregation results. If the grouping key points to an existing set of prior aggregation results (an existing group), the engine performs the aggregation changing the prior set of aggregation results for that group.

The std:groupwin view is a built-in view that groups events into data windows. The view is described in greater detail in Section 12.3.2, “Grouped Data Window (std:groupwin)”. Its primary use is to create a separate data window per group, or more generally to create separate instances of all its sub-views for each grouping key encountered.

The table below summarizes the point:

select symbol, avg(price) from StockTickEvent group by symbol
// ... is equivalent to ...
select symbol, average from StockTickEvent.std:groupwin(symbol).stat:uni(price)

select symbol, sum(price) from StockTickEvent.win:length(10) group by symbol
// ... NOT equivalent to ...
select symbol, sum(price) from StockTickEvent.std:groupwin(symbol).win:length(10)

The output clause is optional in Esper and is used to control or stabilize the rate at which events are output and to suppress output events. The EPL language provides for several different ways to control output rate.

Here is the syntax for the output clause that specifies a rate in time interval or number of events:

output [after suppression_def] 
  [[all | first | last | snapshot] every output_rate [seconds | events]]
[and when terminated]

An alternate syntax specifies the time period between output as outlined in Section 5.2.1, “Specifying Time Periods” :

output [after suppression_def] 
  [[all | first | last | snapshot] every time_period]
[and when terminated]

A crontab-like schedule can also be specified. The schedule parameters follow the pattern observer parameters and are further described in Section 6.6.2.2, “timer:at” :

output [after suppression_def] 
  [[all | first | last | snapshot] at 
   (minutes, hours, days of month, months, days of week [, seconds])]
[and when terminated]

For use with contexts, in order to trigger output only when a context partition terminates, specify when terminated as further described in Section 4.5, “Output When Context Partition Ends”:

output [after suppression_def] 
  [[all | first | last | snapshot] when terminated 
  [and termination_expression]
  [then set variable_name = assign_expression [, variable_name = assign_expression [,...]]]
  ]

Last, output can be controlled by an expression that may contain variables, user-defined functions and information about the number of collected events. Output that is controlled by an expression is discussed in detail below.

The after keyword and suppression_def can appear alone or together with further output conditions and suppresses output events.

For example, the following statement outputs, every 60 seconds, the total price for all orders in the 30-minute time window:

select sum(price) from OrderEvent.win:time(30 min) output snapshot every 60 seconds

The all keyword is the default and specifies that all events in a batch should be output, each incoming row in the batch producing an output row. Note that for statements that group via the group by clause, the all keyword provides special behavior as below.

The first keyword specifies that only the first event in an output batch is to be output. Using the first keyword instructs the engine to output the first matching event as soon as it arrives, and then ignores matching events for the time interval or number of events specified. After the time interval elapsed, or the number of matching events has been reached, the next first matching event is output again and the following interval the engine again ignores matching events. For statements that group via the group by clause, the first keywords provides special behavior as below.

The last keyword specifies to only output the last event at the end of the given time interval or after the given number of matching events have been accumulated. Again, for statements that group via the group by clause the last keyword provides special behavior as below.

The snapshot keyword indicates that the engine output current computation results considering all events as per views specified and/or current aggregation results. While the other keywords control how a batch of events between output intervals is being considered, the snapshot keyword outputs all current state of a statement independent of the last batch. Its output is equivalent to the iterator method provided by a statement. The snapshot keyword requires a data window declaration if not used within a join and outputs only the last event if used without a data window.

The output_rate is the frequency at which the engine outputs events. It can be specified in terms of time or number of events. The value can be a number to denote a fixed output rate, or the name of a variable whose value is the output rate. By means of a variable the output rate can be controlled externally and changed dynamically at runtime.

Please consult the Appendix A, Output Reference and Samples for detailed information on insert and remove stream output for the various output clause keywords.

For use with contexts you may append the keywords and when terminated to trigger output at the rate defined and in addition trigger output when the context partition terminates. Please see Section 4.5, “Output When Context Partition Ends” for details.

The time interval can also be specified in terms of minutes; the following statement is identical to the first one.

select * from StockTickEvent output every 1.5 minutes

A second way that output can be stabilized is by batching events until a certain number of events have been collected:

select * from StockTickEvent output every 5 events

Additionally, event output can be further modified by the optional last keyword, which causes output of only the last event to arrive into an output batch.

select * from StockTickEvent output last every 5 events

Using the first keyword you can be notified at the start of the interval. The allows to watch for situations such as a rate falling below a threshold and only be informed every now and again after the specified output interval, but be informed the moment it first happens.

select * from TickRate where rate<100 output first every 60 seconds

A sample statement using the Unix "crontab"-command schedule is shown next. See Section 6.6.2.2, “timer:at” for details on schedule syntax. Here, output occurs every 15 minutes from 8am to 5:45pm (hours 8 to 17 at 0, 15, 30 and 45 minutes past the hour):

select symbol, sum(price) from StockTickEvent group by symbol output at (*/15, 8:17, *, *, *)

output [after suppression_def] 
  [[all | first | last | snapshot] when trigger_expression 
    [then set variable_name = assign_expression [, variable_name = assign_expression [,...]]]
  [and when terminated 
    [and termination_expression]
    [then set variable_name = assign_expression [, variable_name = assign_expression [,...]]]
  ]

select sum(price) from StockTickEvent output when OutputTriggerVar = true

select sum(price) from StockTickEvent 
  output when OutputTriggerVar = true  
  then set OutputTriggerVar = false

output after time_period | number events [...]

select sum(price) from OrderEvent.win:time(30 min) output after 30 min snapshot every 1 min

select sum(price) from OrderEvent.win:time(1 min) output after 1 min

select * from pattern[every o=OrderEvent->p=OrderEvent(id=o.id)] output after 5 events

Remove stream events can also be useful in conjunction with aggregation and the output clause: When the engine posts remove stream events for fully-aggregated queries, it presents the aggregation state before the expiring event leaves the data window. Your application can thus easily obtain a delta between the new aggregation value and the prior aggregation value.

The engine evaluates the having-clause at the granularity of the data posted by views. That is, if you utilize a time window and output every 10 events, the having clause applies to each individual event or events entering and leaving the time window (and not once per batch of 10 events).

The output clause interacts in two ways with the group by and having clauses. First, in the output every n events case, the number n refers to the number of events arriving into the group by clause. That is, if the group by clause outputs only 1 event per group, or if the arriving events don't satisfy the having clause, then the actual number of events output by the statement could be fewer than n.

Second, the last, all and first keywords have special meanings when used in a statement with aggregate functions and the group by clause:

Please consult the Appendix A, Output Reference and Samples for detailed information on insert and remove stream output for aggregation and group-by.

By adding an output rate limiting clause to a statement that contains a group by clause we can control output of groups to obtain one row for each group, generating an event per group at the given output frequency.

The next statement outputs total price per symbol cumulatively (no data window was used here). As it specifies the all keyword, the statement outputs the current value for all groups seen so far, regardless of whether the group was updated in the last interval. Output occurs after an interval of 5 seconds passed and at the end of each subsequent interval:

select symbol, sum(price) from StockTickEvent group by symbol output all every 5 seconds

The below statement outputs total price per symbol considering events in the last 3 minutes. When events leave the 3-minute data window output also occurs as new aggregation values are computed. The last keyword instructs the engine to output only those groups that had changes. Output occurs after an interval of 10 seconds passed and at the end of each subsequent interval:

select symbol, sum(price) from StockTickEvent.win:time(3 min)
group by symbol output last every 10 seconds

This statement also outputs total price per symbol considering events in the last 3 minutes. The first keyword instructs the engine to output as soon as there is a new value for a group. After output for a given group the engine suppresses output for the same group for 10 seconds and does not suppress output for other groups. Output occurs again for that group after the interval when the group has new value(s):

select symbol, sum(price) from StockTickEvent.win:time(3 min)
group by symbol output first every 10 seconds

Output rate limiting provides output events to your application in regular intervals. Between intervals, the engine uses a buffer to hold events until the output condition is reached. If your application has high-volume streams, you may need to be mindful of the memory needs for output rates.

The output clause with the snapshot keyword does not require a buffer, all other output keywords do consume memory until the output condition is reached.

Sometimes your events may carry properties that are themselves event objects. Therefore EPL offers a special syntax to insert the value of a property itself as an event into a stream:

insert into stream_name select property_name.* from ...

This feature is only supported for JavaBean events and for Map and Object-array (Object[]) event types that associate an event type name with the property type. It is not supported for XML events. Nested property names are also not supported.

In this example, the class Summary with properties bid and ask that are of type Quote is:

public class Summary {
  private Quote bid;
  private Quote ask;
  ...

The statement to populate a stream of Quote events is thus:

insert into MyBidStream select bid.* from Summary

The insert into clause allows to merge multiple event streams into a event single stream. The clause names an event stream to insert into by specifing an event_stream_name. The first statement that inserts into the named stream defines the stream's event types. Further statements that insert into the same event stream must match the type of events inserted into the stream as declared by the first statement.

One approach to merging event streams specifies individual colum names either in the select clause or in the insert into clause of the statement. This approach has been shown in earlier examples.

Another approach to merging event streams specifies the wildcard (*) in the select clause (or the stream wildcard) to select the underlying event. The events in the event stream must then have the same event type as generated by the from clause.

Assume a statement creates an event stream named MergedStream by selecting OrderEvent events:

insert into MergedStream select * from OrderEvent

A statement can use the stream wildcard selector to select only OrderEvent events in a join:

insert into MergedStream select ord.* from ItemScanEvent, OrderEvent as ord

And a statement may also use an application-supplied user-defined function to convert events to OrderEvent instances:

insert into MergedStream select MyLib.convert(item) from ItemScanEvent as item

Esper specifically recognizes a conversion function as follows: A conversion function must be the only selected column, and it must return either a Java object or java.util.Map or Object[] (object array). Your EPL should not use the as keyword to assign a column name.

Variant streams can be declared by means of create variant schema or can be predefined via runtime or initialization-time configuration as described in Section 15.4.27, “Variant Stream”. Your application may declare or predefine variant streams to carry events of a limited set of event types, or you may choose the variant stream to carry any and all types of events. This choice affects what event properties are available for consuming statements or patterns of the variant stream.

Assume that an application predefined a variant stream named OrderStream to carry only ServiceOrder and ProductOrder events. An insert into clause inserts events into the variant stream:

insert into OrderStream select * from ServiceOrder

insert into OrderStream select * from ProductOrder

Here is a sample statement that consumes the variant stream and outputs a total price per customer id for the last 30 seconds of ServiceOrder and ProductOrder events:

select customerId, sum(price) from OrderStream.win:time(30 sec) group by customerId

If your application predefines the variant stream to hold specific type of events, as the sample above did, then all event properties that are common to all specified types are visible on the variant stream, including nested, indexed and mapped properties. For access to properties that are only available on one of the types, the dynamic property syntax must be used. In the example above, the customerId and price were properties common to both ServiceOrder and ProductOrder events.

For example, here is a consuming statement that selects a service duraction property that only ServiceOrder events have, and that must therefore be casted to double and null values removed in order to aggregate:

select customerId, sum(coalesce(cast(serviceDuraction?, double), 0)) 
from OrderStream.win:time(30 sec) group by customerId

If your application predefines a variant stream to hold any type of events (the any type variance), then all event properties of the variant stream are effectively dynamic properties.

For example, an application may define an OutgoingEvents variant stream to hold any type of event. The next statement is a sample consumer of the OutgoingEvents variant stream that looks for the destination property and fires for each event in which the property exists with a value of 'email':

select * from OutgoingEvents(destination = 'email')

Your insert into clause may also directly instantiate and populate application underlying event objects or Map or Object[] event objects. This is described in greater detail in Section 2.12, “Event Objects Instantiated and Populated by Insert Into”.

If instead you have an expression that returns an event object, please read on to the next section.

You can transpose an object returned as an expression result into a stream using the transpose function as described further in Section 9.4, “Select-Clause transpose Function”.

When you declare the inserted-into event type in advance to the statement that inserts, the engine compares the inserted-into event type information to the return type of expressions in the select-clause. The comparison uses the column alias assigned to each select-clause expression using the as keyword.

When the inserted-into column type is an event type and when using a subquery or the new operator, the engine compares column names assigned to subquery columns or new operator columns.

For example, assume a PurchaseOrder event type that has a property called items that consists of Item rows:

create schema Item(name string, price double)

create schema PurchaseOrder(orderId string, items Item[])

Declare a statement that inserts into the PurchaseOrder stream:

insert into PurchaseOrder 
select '001' as orderId, new {name='i1', price=10} as items
from TriggerEvent

The alias assigned to the first and second expression in the select-clause, namely orderId and items, both match the event property names of the Purchase Order event type. The column names provided to the new operator also both match the event property names of the Item event type.

When the event type declares the column as a single value (and not an array) and when the select-clause expression produces a multiple rows, the engine only populate the first row.

Consider a PurchaseOrder event type that has a property called item that consists of a single Item event:

create schema PurchaseOrder(orderId string, items Item)

The sample subquery below populates only the very first event, discarding remaining subquery result events, since the items property above is declared as holding a single Item-typed event only (versus Item[] to hold multiple Item-typed events).

insert into PurchaseOrder select 
(select 'i1' as name, 10 as price from HistoryEvent.win:length(2)) as items 
from TriggerEvent

Consider using a subquery with filter, or one of the enumeration methods to select a specific subquery result row.

The exists condition is considered "to be met" if the subquery returns at least one row. The not exists condition is considered true if the subquery returns no rows.

The synopsis for the exists keyword is as follows:

exists (subquery)

Let's take a look at a simple example. The following is an EPL statement that uses the exists condition:

select assetId from RFIDEvent as RFID 
  where exists (select * from Asset.std:unique(assetId) where assetId = RFID.assetId)

This select statement will return all RFID events where there is at least one event in Assets unique by asset id with the same asset id.

The in subquery condition is true if the value of an expression matches one or more of the values returned by the subquery. Consequently, the not in condition is true if the value of an expression matches none of the values returned by the subquery.

The synopsis for the in keyword is as follows:

expression in (subquery)

The right-hand side subquery must return exactly one column.

The next statement demonstrates the use of the in subquery condition:

select assetId from RFIDEvent
  where zone in (select zone from ZoneUpdate(status = 'closed').win:time(10 min))

The above statement demonstrated the in subquery to select RFID events for which the zone status is in a closed state.

Note that if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of the in construct will be null, not false (or true for not-in). This is in accordance with SQL's normal rules for Boolean combinations of null values.

The any subquery condition is true if the expression returns true for one or more of the values returned by the subquery.

The synopsis for the any keyword is as follows:

expression operator any (subquery)
expression operator some (subquery)

The left-hand expression is evaluated and compared to each row of the subquery result using the given operator, which must yield a Boolean result. The result of any is "true" if any true result is obtained. The result is "false" if no true result is found (including the special case where the subquery returns no rows).

The operator can be any of the following values: =, !=, <>, <, <=, >, >=.

The some keyword is a synonym for any. The in construct is equivalent to = any.

The right-hand side subquery must return exactly one column.

The next statement demonstrates the use of the any subquery condition:

select * from ProductOrder as ord
  where quantity < any
    (select minimumQuantity from MinimumQuantity.win:keepall())

The above query compares ProductOrder event's quantity value with all rows from the MinimumQuantity stream of events and returns only those ProductOrder events that have a quantity that is less then any of the minimum quantity values of the MinimumQuantity events.

Note that if there are no successes and at least one right-hand row yields null for the operator's result, the result of the any construct will be null, not false. This is in accordance with SQL's normal rules for Boolean combinations of null values.

The all subquery condition is true if the expression returns true for all of the values returned by the subquery.

The synopsis for the all keyword is as follows:

expression operator all (subquery)

The left-hand expression is evaluated and compared to each row of the subquery result using the given operator, which must yield a Boolean result. The result of all is "true" if all rows yield true (including the special case where the subquery returns no rows). The result is "false" if any false result is found. The result is null if the comparison does not return false for any row, and it returns null for at least one row.

The operator can be any of the following values: =, !=, <>, <, <=, >, >=.

The not in construct is equivalent to != all.

The right-hand side subquery must return exactly one column.

The next statement demonstrates the use of the all subquery condition:

select * from ProductOrder as ord
  where quantity < all
    (select minimumQuantity from MinimumQuantity.win:keepall())

The above query compares ProductOrder event's quantity value with all rows from the MinimumQuantity stream of events and returns only those ProductOrder events that have a quantity that is less then all of the minimum quantity values of the MinimumQuantity events.

The optional group by clause in subqueries works the same way as the group-by clause outside of subqueries, except that it impacts only those aggregations within the subquery.

The following restrictions apply:

Your subquery may select multiple columns in the select clause including multiple aggregated values from a data window or named window.

The following example is a correlated subquery that selects wildcard and in addition selects the bid and offer properties of the last MarketData event for the same symbol as the arriving OrderEvent:

select *,
  (select bid, offer from MarketData.std:unique(symbol) as md 
   where md.symbol = oe.symbol) as bidoffer
from OrderEvent oe

Output events for the above query contain all properties of the original OrderEvent event. In addition each output event contains a bidoffer nested property that itself contains the bid and offer properties. You may retrieve the bid and offer from output events directly via the bidoffer.bid property name syntax for nested properties.

The next example is similar to the above query but instead selects aggregations and selects from a named window by name OrderNamedWindow (creation not shown here). For each arriving OrderEvent it selects the total quantity and count of all order events for the same client, as currently held by the named window:

select *,
  (select sum(qty) as sumPrice, count(*) as countRows 
   from OrderNamedWindow as onw
   where onw.client = oe.client) as pastOrderTotals
from OrderEvent as oe

The next EPL statement computes a prorated quantity considering the maximum and minimum quantity for the last 1 minute of order events:

expression subq {
  (select max(quantity) as maxq, min(quantity) as minq from OrderEvent.win:time(1 min))
}
select (quantity - minq) / (subq().maxq  - subq().minq) as prorated
from OrderEvent

Output events for the above query contain all properties of the original OrderEvent event. In addition each output event contains a pastOrderTotals nested property that itself contains the sumPrice and countRows properties.

While a subquery cannot change the cardinality of the selected stream, a subquery can return multiple values from the selected data window or named window. This section shows examples of the window aggregation function as well as the use of enumeration methods with subselects.

Consider using an inner join, outer join or unidirectional join instead to achieve a 1-to-many cardinality in the number of output events.

The next example is an uncorrelated subquery that selects all current ZoneEvent events considering the last ZoneEvent per zone for each arriving RFIDEvent.

select assetId,
 (select window(z.*) as winzones from ZoneEvent.std:unique(zone) as z) as zones
 from RFIDEvent

Output events for the above query contain two properties: the assetId property and the zones property. The latter property is a nested property that contains the winzones property. You may retrieve the zones from output events directly via the zones.winzones property name syntax for nested properties.

In this example for a correlated subquery against a named window we assume that the OrderNamedWindow has been created and contains order events. The query returns for each MarketData event the list of order ids for orders with the same symbol:

select price,
 (select window(orderId) as winorders 
  from OrderNamedWindow onw 
  where onw.symbol = md.symbol) as orderIds
 from MarketData md

Output events for the above query contain two properties: the price property and the orderIds property. The latter property is a nested property that contains the winorders property of type array.

Another option to reduce selected rows to a single value is through the use of enumeration methods.

select price,
 (select *  from OrderNamedWindow onw
  where onw.symbol = md.symbol).selectFrom(v => v) as ordersSymbol
 from MarketData md

Output events for the above query also contain a Collection of underlying events in the ordersSymbol property.

The following hints are available to tune performance and memory use of subqueries.

Use the @Hint('set_noindex') hint for a statement that utilizes one or more subqueries. It instructs the engine to always perform a full table scan. The engine does not build an implicit index or use an explicitly-created index when this hint is provided. Use of the hint may result in reduced memory use but poor statement performance.

The following hints are available to tune performance and memory use of subqueries that select from named windows.

Named windows are globally-visible data windows. As such an application may create explicit indexes as discussed in Section 5.15.13, “Explicitly Indexing Named Windows”. The engine may also elect to create implicit indexes (no create-index EPL required) for index-based lookup of rows when executing on-select, on-merge, on-update and on-delete statements and for statements that subquery a named window.

By default and without specifying a hint, each statement that subqueries a named window also maintains its own index for looking up events held by the named window. The engine maintains the index by consuming the named window insert and remove stream. When the statement is destroyed it releases that index.

Specify the @Hint('enable_window_subquery_indexshare') hint to enable subquery index sharing for named windows. When using this hint, indexes for subqueries are maintained by the named window itself (and not each statement context partition), are shared between one or more statements and may also utilize explicit indexes. Specify the hint once as part of the create window statement.

This sample EPL statement creates a named window with subquery index sharing enabled:

@Hint('enable_window_subquery_indexshare')
create window AllOrdersNamedWindow.win:keepall() as OrderMapEventType

When subquery index sharing is enabled, performance may increase as named window stream consumption is no longer needed for correlated subqueries. You may also expect reduced memory use especially if a large number of EPL statements perform similar subqueries against a named window. Subquery index sharing may require additional short-lived object creation and may slightly increase lock held time for named windows.

The following statement performs a correlated subquery against the named window above. When a settlement event arrives it select the order detail for the same order id as provided by the settlement event:

select 
  (select * from AllOrdersNamedWindow as onw 
    where onw.orderId = se.orderId) as orderDetail
  from SettlementEvent as se

With subquery index sharing enabled the engine maintains an index of order events by order id for the named window, and shares that index between additional statements until the time all utilizing statements are destroyed.

You may disable subquery index sharing for a specific statement by specifying the @Hint('disable_window_subquery_indexshare') hint, as this example shows, causing the statement to maintain its own index:

@Hint('disable_window_subquery_indexshare')
select 
  (select * from AllOrdersNamedWindow as onw 
    where onw.orderId = se.orderId) as orderDetail
  from SettlementEvent as se

Two or more event streams can be part of the from-clause and thus both (all) streams determine the resulting events. This section summarizes the important concepts. The sections that follow present more detail on each topic.

The default join is an inner join which produces output events only when there is at least one match in all streams.

Consider the sample statement shown next:

select * from TickEvent.std:lastevent(), NewsEvent.std:lastevent()

The above statement outputs the last TickEvent and the last NewsEvent in one output event when either a TickEvent or a NewsEvent arrives. If no TickEvent was received before a NewsEvent arrives, no output occurs. Similarly when no NewsEvent was received before a TickEvent arrives, no output occurs.

The where-clause lists the join conditions that Esper uses to relate events in the two or more streams.

The next example statement retains the last TickEvent and last NewsEvent per symbol, and joins the two streams based on their symbol value:

select * from TickEvent.std:unique(symbol) as t, NewsEvent.std:unique(symbol) as n
where t.symbol = n.symbol

As before, when a TickEvent arrives for a symbol that has no matching NewsEvent then there is no output event.

An outer join does not require each event in either stream to have a matching event. The full outer join is useful when output is desired when no match is found. The different outer join types (full, left, right) are explained in more detail below.

This example statement is an outer-join and also returns the last TickEvent and last NewsEvent per symbol:

select * from TickEvent.std:unique(symbol) as t
full outer join NewsEvent.std:unique(symbol) as n on t.symbol = n.symbol

In the sample statement above, when a TickEvent arrives for a symbol that has no matching NewsEvent, or when a NewsEvent arrives for a symbol that has no matching TickEvent, the statement still produces an output event with a null column value for the missing event.

Note that each of the sample queries above defines a data window. The sample queries above use the last-event data window (std:lastevent) or the unique data window (std:unique). A data window serves to indicate the subset of events to join from each stream and may be required depending on the join.

In above queries, when either a TickEvent arrives or when a NewsEvent arrives then the query evaluates and there is output. The same holds true if additional streams are added to the from-clause: Each of the streams in the from-clause trigger the join to evaluate.

The unidirectional keyword instructs the engine to evaluate the join only when an event arrives from the single stream that was marked with the unidirectional keyword. In this case no data window should be specified for the stream marked as unidirectional since the keyword implies that the current event of that stream triggers the join.

Here is the sample statement above with unidirectional keyword, so that output occurs only when a TickEvent arrives and not when a NewsEvent arrives:

select * from TickEvent as t unidirectional, NewsEvent.std:unique(symbol) as n 
where t.symbol = n.symbol

It is oftentimes the case that an aggregation (count, sum, average) only needs to be calculated in the context of an arriving event or timer. Consider using the unidirectional keyword when aggregating over joined streams.

An EPL pattern is a normal citizen also providing a stream of data consisting of pattern matches. A time pattern, for example, can be useful to evaluate a join and produce output upon each interval.

This sample statement includes a pattern that fires every 5 seconds and thus triggers the join to evaluate and produce output, computing an aggregated total quantity per symbol every 5 seconds:

select symbol, sum(qty) from pattern[every timer:interval(5 sec)] unidirectional, 
  TickEvent.std:unique(symbol) t, NewsEvent.std:unique(symbol) as n 
where t.symbol = n.symbol group by symbol

Named windows as well as reference and historical data such as stored in your relational database, and data returned by a method invocation, can also be included in joins as discussed in Section 5.13, “Accessing Relational Data via SQL” and Section 5.14, “Accessing Non-Relational Data via Method Invocation”.

Related to joins are subqueries: A subquery is a select within another statement, see Section 5.11, “Subqueries”

The engine performs extensive query analysis and planning, building internal indexes and strategies as required to allow fast evaluation of many types of queries.

Each point in time that an event arrives to one of the event streams, the two event streams are joined and output events are produced according to the where clause when matching events are found for all joined streams.

This example joins 2 event streams. The first event stream consists of fraud warning events for which we keep the last 30 minutes. The second stream is withdrawal events for which we consider the last 30 seconds. The streams are joined on account number.

select fraud.accountNumber as accntNum, fraud.warning as warn, withdraw.amount as amount,
       max(fraud.timestamp, withdraw.timestamp) as timestamp, 'withdrawlFraud' as desc
  from com.espertech.esper.example.atm.FraudWarningEvent.win:time(30 min) as fraud,
       com.espertech.esper.example.atm.WithdrawalEvent.win:time(30 sec) as withdraw
 where fraud.accountNumber = withdraw.accountNumber

Joins can also include one or more pattern statements as the next example shows:

select * from FraudWarningEvent.win:time(30 min) as fraud,
    pattern [every w=WithdrawalEvent -> PINChangeEvent(acct=w.acct)].std:lastevent() as withdraw
 where fraud.accountNumber = withdraw.w.accountNumber

The statement above joins the last 30 minutes of fraud warnings with a pattern. The pattern consists of every withdrawal event that is followed by a PIN change event for the same account number. It joins the two event streams on account number. The last-event view instucts the join to only consider the last pattern match.

In a join and outer join, your statement must declare a data window view or other view onto each stream. Streams that are marked as unidirectional and named windows as well as database or methods in a join are an exception and do not require a view to be specified. If you are joining an event to itself via contained-event selection, views also do not need to be specified. The reason that a data window must be declared is that a data window specifies which events are considered for the join (i.e. last event, last 10 events, all events, last 1 second of events etc.).

The next example joins all FraudWarningEvent events that arrived since the statement was started, with the last 20 seconds of PINChangeEvent events:

select * from FraudWarningEvent.win:keepall() as fraud, PINChangeEvent.win:time(20 sec) as pin
 where fraud.accountNumber = pin.accountNumber

The above example employed the special keep-all view that retains all events.

Esper supports left outer joins, right outer joins, full outer joins and inner joins in any combination between an unlimited number of event streams. Outer and inner joins can also join reference and historical data as explained in Section 5.13, “Accessing Relational Data via SQL”, as well as join data returned by a method invocation as outlined in Section 5.14, “Accessing Non-Relational Data via Method Invocation”.

The keywords left, right, full and inner control the type of the join between two streams. The optional on clause specifies one or more properties that join each stream. The synopsis is as follows:

...from stream_def [as name] 
  ((left|right|full outer) | inner) join stream_def 
  [on property = property [and property = property ...] ]
  [ ((left|right|full outer) | inner) join stream_def [on ...]]...

If the outer join is a left outer join, there will be an output event for each event of the stream on the left-hand side of the clause. For example, in the left outer join shown below we will get output for each event in the stream RfidEvent, even if the event does not match any event in the event stream OrderList.

select * from RfidEvent.win:time(30 sec) as rfid
       left outer join
       OrderList.win:length(10000) as orderlist
     on rfid.itemId = orderList.itemId

Similarly, if the join is a Right Outer Join, then there will be an output event for each event of the stream on the right-hand side of the clause. For example, in the right outer join shown below we will get output for each event in the stream OrderList, even if the event does not match any event in the event stream RfidEvent.

select * from RfidEvent.win:time(30 sec) as rfid
       right outer join
       OrderList.win:length(10000) as orderlist
       on rfid.itemId = orderList.itemId

For all types of outer joins, if the join condition is not met, the select list is computed with the event properties of the arrived event while all other event properties are considered to be null.

The next type of outer join is a full outer join. In a full outer join, each point in time that an event arrives to one of the event streams, one or more output events are produced. In the example below, when either an RfidEvent or an OrderList event arrive, one or more output event is produced. The next example shows a full outer join that joins on multiple properties:

select * from RfidEvent.win:time(30 sec) as rfid
       full outer join
       OrderList.win:length(10000) as orderlist
       on rfid.itemId = orderList.itemId and rfid.assetId = orderList.assetId

The last type of join is an inner join. In an inner join, the engine produces an output event for each event of the stream on the left-hand side that matches at least one event on the right hand side considering the join properties. For example, in the inner join shown below we will get output for each event in the RfidEvent stream that matches one or more events in the OrderList data window:

select * from RfidEvent.win:time(30 sec) as rfid
       inner join
       OrderList.win:length(10000) as orderlist
       on rfid.itemId = orderList.itemId and rfid.assetId = orderList.assetId

Patterns as streams in a join follow this rule: If no data window view is declared for the pattern then the pattern stream retains the last match. Thus a pattern must have matched at least once for the last row to become available in a join. Multiple rows from a pattern stream may be retained by declaring a data window view onto a pattern using the pattern [...].view_specification syntax.

This example outer joins multiple streams. Here the RfidEvent stream is outer joined to both ProductName and LocationDescription via left outer join:

select * from RfidEvent.win:time(30 sec) as rfid
      left outer join ProductName.win:keepall() as refprod
        on rfid.productId = refprod.prodId
      left outer join LocationDescription.win:keepall() as refdesc
        on rfid.location = refdesc.locId

If the optional on clause is specified, it may only employ the = equals operator and property names. Any other operators must be placed in the where-clause. The stream names that appear in the on clause may refer to any stream in the from-clause.

Your EPL may also provide no on clause. This is useful when the streams that are joined do not provide any properties to join on, for example when joining with a time-based pattern.

The next example employs a unidirectional left outer join such that the engine, every 10 seconds, outputs a count of the number of RfidEvent events in the 60-second time window.

select count(*) from
  pattern[every timer:interval(1)] unidirectional 
  left outer join
  RfidEvent.win:time(60 sec)

When joining 3 or more streams (including any relational or non-relational sources as below) it can sometimes help to provide the query planner instructions how to best execute the join. The engine compiles a query plan for the EPL statement at statement creation time. You can output the query plan to logging (see configuration).

An outer join that specifies only inner keywords for all streams is equivalent to an default (inner) join. The following two statements are equivalent:

select * from TickEvent.std:lastevent(), 
    NewsEvent.std:lastevent() where tick.symbol = news.symbol

Equivalent to:

select * from TickEvent.std:lastevent() 
	inner join NewsEvent.std:lastevent() on tick.symbol = news.symbol

For all types of joins, the query planner determines a query graph: The term is used here for all the information regarding what properties or expressions are used to join the streams. The query graph thus includes the where-clause expressions as well as outer-join on-clauses if this statement is an outer join. The query planner also computes a dependency graph which includes information about all historical data streams (relational and non-relational as below) and their input needs.

For default (inner) joins the query planner first attempts to find a path of execution as a nested iteration. For each stream the query planner selects the best order of streams available for the nested iteration considering the query graph and dependency graph. If the full depth of the join is achievable via nested iteration for all streams without full table scan then the query planner uses that nested iteration plan. If not, then the query planner re-plans considering a merge join (Cartesian) approach instead.

Specify the @Hint('PREFER_MERGE_JOIN') to instruct the query planner to prefer a merge join plan instead of a nested iteration plan. Specify the @Hint('FORCE_NESTED_ITER') to instruct the query planner to always use a nested iteration plan.

For example, consider the below statement. Depending on the number of matching rows in OrderBookOne and OrderBookTwo (named windows in this example, and assumed to be defined elsewhere) the performance of the join may be better using the merge join plan.

@Hint('PREFER_MERGE_JOIN') 
select * from TickEvent.std:lastevent() t, 
	OrderBookOne ob1, OrderBookOne ob2
where ob1.symbol = t.symbol and ob2.symbol = t.symbol 
and ob1.price between t.buy and t.sell and ob2.price between t.buy and t.sell

For outer joins the query planner considers nested iteration and merge join (Cartesian) equally and above hints don't apply.

To join an event stream against stored data, specify the sql keyword followed by the name of the database and a parameterized SQL query. The syntax to use in the from clause of an EPL statement is:

sql:database_name [" parameterized_sql_query "]

The engine uses the database_name identifier to obtain configuration information in order to establish a database connection, as well as settings that control connection creation and removal. Please see Section 15.4.9, “Relational Database Access” to configure an engine for database access.

Following the database name is the SQL query to execute. The SQL query can contain one or more substitution parameters. The SQL query string is placed in single brackets [ and ]. The SQL query can be placed in either single quotes (') or double quotes ("). The SQL query grammer is passed to your database software unchanged, allowing you to write any SQL query syntax that your database understands, including stored procedure calls.

Substitution parameters in the SQL query string take the form ${expression}. The engine resolves expression at statement execution time to the actual expression result by evaluating the events in the joined event stream or current variable values, if any event property references or variables occur in the expression. An expression may not contain EPL substitution parameters.

The engine determines the type of the SQL query output columns by means of the result set metadata that your database software returns for the statement. The actual query results are obtained via the getObject on java.sql.ResultSet.

The sample EPL statement below joins an event stream consisting of CustomerCallEvent events with the results of an SQL query against the database named MyCustomerDB and table Customer:

select custId, cust_name from CustomerCallEvent,
  sql:MyCustomerDB [' select cust_name from Customer where cust_id = ${custId} ']

The example above assumes that CustomerCallEvent supplies an event property named custId. The SQL query selects the customer name from the Customer table. The where clause in the SQL matches the Customer table column cust_id with the value of custId in each CustomerCallEvent event. The engine executes the SQL query for each new CustomerCallEvent encountered.

If the SQL query returns no rows for a given customer id, the engine generates no output event. Else the engine generates one output event for each row returned by the SQL query. An outer join as described in the next section can be used to control whether the engine should generate output events even when the SQL query returns no rows.

The next example adds a time window of 30 seconds to the event stream CustomerCallEvent. It also renames the selected properties to customerName and customerId to demonstrate how the naming of columns in an SQL query can be used in the select clause in the EPL query. And the example uses explicit stream names via the as keyword.

select customerId, customerName from
  CustomerCallEvent.win:time(30 sec) as cce,
  sql:MyCustomerDB ["select cust_id as customerId, cust_name as customerName from Customer 
                  where cust_id = ${cce.custId}"] as cq

Any window, such as the time window, generates insert stream (istream) events as events enter the window, and remove stream (rstream) events as events leave the window. The engine executes the given SQL query for each CustomerCallEvent in both the insert stream and the remove stream. As a performance optimization, the istream or rstream keywords in the select clause can be used to instruct the engine to only join insert stream or remove stream events, reducing the number of SQL query executions.

Since any expression may be placed within the ${...} syntax, you may use variables or user-defined functions as well.

The next example assumes that a variable by name varLowerLimit is defined and that a user-defined function getLimit exists on the MyLib imported class that takes a LimitEvent as a parameter:

select * from LimitEvent le, 
  sql:MyCustomerDB [' select cust_name from Customer where 
      amount > ${max(varLowerLimit, MyLib.getLimit(le))} ']

The example above takes the higher of the current variable value or the value returned by the user-defined function to return only those customer names where the amount exceeds the computed limit.

Consider using the EPL where clause to join the SQL query result to your event stream. Similar to EPL joins and outer-joins that join event streams or patterns, the EPL where clause provides join criteria between the SQL query results and the event stream (as a side note, an SQL where clause is a filter of rows executed by your database on your database server before returning SQL query results).

Esper analyzes the expression in the EPL where clause and outer-join on clause, if present, and builds the appropriate indexes from that information at runtime, to ensure fast matching of event stream events to SQL query results, even if your SQL query returns a large number of rows. Your applications must ensure to configure a cache for your database using Esper configuration, as such indexes are held with regular data in a cache. If you application does not enable caching of SQL query results, the engine does not build indexes on cached data.

The sample EPL statement below joins an event stream consisting of OrderEvent events with the results of an SQL query against the database named MyRefDB and table SymbolReference:

select symbol, symbolDesc from OrderEvent as orders,
  sql:MyRefDB ['select symbolDesc from SymbolReference'] as reference
  where reference.symbol = orders.symbol

Notice how the EPL where clause joins the OrderEvent stream to the SymbolReference table. In this example, the SQL query itself does not have a SQL where clause and therefore returns all rows from table SymbolReference.

If your application enables caching, the SQL query fires only at the arrival of the first OrderEvent event. When the second OrderEvent arrives, the join execution uses the cached query result. If the caching policy that you specified in the Esper database configuration evicts the SQL query result from cache, then the engine fires the SQL query again to obtain a new result and places the result in cache.

If SQL result caching is enabled and your EPL where clause, as show in the above example, provides the properties to join, then the engine indexes the SQL query results in cache and retains the index together with the query result in cache. Thus your application can benefit from high performance index-based lookups as long as the SQL query results are found in cache.

The SQL result caches operate on the level of all result rows for a given parameter set. For example, if your query returns 10 rows for a certain set of parameter values then the cache treats all 10 rows as a single entry keyed by the parameter values, and the expiry policy applies to all 10 rows and not to each individual row.

It is also possible to join multiple autonomous database systems in a single query, for example:

select symbol, symbolDesc from OrderEvent as orders,
  sql:My_Oracle_DB ['select symbolDesc from SymbolReference'] as reference,
  sql:My_MySQL_DB ['select orderList from orderHistory'] as history
  where reference.symbol = orders.symbol
  and history.symbol = orders.symbol 

You can use outer joins to join data obtained from an SQL query and control when an event is produced. Use a left outer join, such as in the next statement, if you need an output event for each event regardless of whether or not the SQL query returns rows. If the SQL query returns no rows, the join result populates null values into the selected properties.

select custId, custName from
  CustomerCallEvent as cce
  left outer join 
  sql:MyCustomerDB ["select cust_id, cust_name as custName 
                     from Customer where cust_id = ${cce.custId}"] as cq
  on cce.custId = cq.cust_id

The statement above always generates at least one output event for each CustomerCallEvent, containing all columns selected by the SQL query, even if the SQL query does not return any rows. Note the on expression that is required for outer joins. The on acts as an additional filter to rows returned by the SQL query.

Pattern statements and SQL queries can also be applied together in useful ways. One such use is to poll or request data from a database at regular intervals or following the schedule of the crontab-like timer:at.

The next statement is an example that shows a pattern that fires every 5 seconds to query the NewOrder table for new orders:

insert into NewOrders
select orderId, orderAmount from
  pattern [every timer:interval(5 sec)],
  sql:MyCustomerDB ['select orderId, orderAmount from NewOrders']

Usually your SQL query will take part in a join and thus be triggered by an event or pattern occurrence. Instead, your application may need to poll a SQL query and thus use Esper query execution and caching facilities and obtain event data and metadata.

Your EPL statement can specify an SQL statement without a join. Such a stand-alone SQL statement does not post new events, and may only be queried via the iterator poll API. Your EPL and SQL statement may still use variables.

The next statement assumes that a price_var variable has been declared. It selects from the relational database table named NewOrder all rows in which the price column is greater then the current value of the price_var EPL variable:

select * from sql:MyCustomerDB ['select * from NewOrder where ${price_var} > price']

Use the iterator and safeIterator methods on EPStatement to obtain results. The statement does not post events to listeners, it is strictly passive in that sense.

The engine translates SQL queries into JDBC java.sql.PreparedStatement statements by replacing ${name} parameters with '?' placeholders. It obtains name and type of result columns from the compiled PreparedStatement meta data when the EPL statement is created.

The engine supplies parameters to the compiled statement via the setObject method on PreparedStatement. The engine uses the getObject method on the compiled statement PreparedStatement to obtain column values.

Certain JDBC database drivers are known to not return metadata for precompiled prepared SQL statements. This can be a problem as metadata is required by Esper. Esper obtains SQL result set metadata to validate an EPL statement and to provide column types for output events. JDBC drivers that do not provide metadata for precompiled SQL statements require a workaround. Such drivers do generally provide metadata for executed SQL statements, however do not provide the metadata for precompiled SQL statements.

Please consult the Chapter 15, Configuration for the configuration options available in relation to metadata retrieval.

To obtain metadata for an SQL statement, Esper can alternatively fire a SQL statement which returns the same column names and types as the actual SQL statement but without returning any rows. This kind of SQL statement is referred to as a sample statement in below workaround description. The engine can then use the sample SQL statement to retrieve metadata for the column names and types returned by the actual SQL statement.

Applications can provide a sample SQL statement to retrieve metadata via the metadatasql keyword:

sql:database_name ["parameterized_sql_query" metadatasql "sql_meta_query"] 

The sql_meta_query must be an SQL statement that returns the same number of columns, the same type of columns and the same column names as the parameterized_sql_query, and does not return any rows.

Alternatively, applications can choose not to provide an explicit sample SQL statement. If the EPL statement does not use the metadatasql syntax, the engine applies lexical analysis to the SQL statement. From the lexical analysis Esper generates a sample SQL statement adding a restrictive clause "where 1=0" to the SQL statement.

Alternatively, you can add the following tag to the SQL statement: ${$ESPER-SAMPLE-WHERE}. If the tag exists in the SQL statement, the engine does not perform lexical analysis and simply replaces the tag with the SQL where clause "where 1=0". Therefore this workaround is applicable to SQL statements that cannot be correctly lexically analyzed. The SQL text after the placeholder is not part of the sample query. For example:

select mycol from sql:myDB [
  'select mycol from mytesttable ${$ESPER-SAMPLE-WHERE} where ....'], ...

If your parameterized_sql_query SQL query contains vendor-specific SQL syntax, generation of the metadata query may fail to produce a valid SQL statement. If you experience an SQL error while fetching metadata, use any of the above workarounds with the Oracle JDBC driver.

As part of database access configuration you may optionally specify SQL type mappings. These mappings apply to all queries against the same database identified by name.

If your application must perform SQL-query-specific or EPL-statement-specific mapping or conversion between types, the facility to register a conversion callback exists as follows.

Use the @Hook instruction and HookType.SQLCOL as part of your EPL statement text to register a statement SQL parameter or column conversion hook. Implement the interface com.espertech.esper.client.hook.SQLColumnTypeConversion to perform the input parameter or column value conversion.

A sample statement with annotation is shown:

@Hook(type=HookType.SQLCOL, hook='MyDBTypeConvertor')
select * from sql:MyDB ['select * from MyEventTable]

The engine expects MyDBTypeConvertor to resolve to a class (considering engine imports) and instantiates one instance of MyDBTypeConvertor for each statement.

Your application may also directly convert a SQL result row into a Java class which is an opportunity for your application to interrogate and transform the SQL row result data freely before packing the data into a Java class. Your application can additionally indicate to skip SQL result rows.

Use the @Hook instruction and HookType.SQLROW as part of your EPL statement text to register a statement SQL output row conversion hook. Implement the interface com.espertech.esper.client.hook.SQLOutputRowConversion to perform the output row conversion.

A sample statement with annotation is shown:

@Hook(type=HookType.SQLROW, hook='MyDBRowConvertor')
select * from sql:MyDB ['select * from MyEventTable]

The engine expects MyDBRowConvertor to resolve to a class (considering engine imports) and instantiates one MyDBRowConvertor instance for each statement.

The syntax for a method invocation in the from clause of an EPL statement is:

method:class_name.method_name[(parameter_expressions)]

The method keyword denotes a method invocation. It is followed by a class name and a method name separated by a dot (.) character. If you have parameters to your method invocation, these are placed in round brackets after the method name. Any expression is allowed as a parameter, and individual parameter expressions are separated by a comma. Expressions may also use event properties of the joined stream.

In the sample join statement shown next, the method 'lookupAsset' provided by class 'MyLookupLib' returns one or more rows based on the asset id (a property of the AssetMoveEvent) that is passed to the method:

select * from AssetMoveEvent, method:MyLookupLib.lookupAsset(assetId)

The following statement demonstrates the use of the where clause to join events to the rows returned by a method invocation, which in this example does not take parameters:

select assetId, assetDesc from AssetMoveEvent as asset, 
       method:MyLookupLib.getAssetDescriptions() as desc 
where asset.assetid = desc.assetid

Your method invocation may return zero, one or many rows for each method invocation. If you have caching enabled through configuration, then Esper can avoid the method invocation and instead use cached results. Similar to SQL joins, Esper also indexes cached result rows such that join operations based on the where clause or outer-join on clause can be very efficient, especially if your method invocation returns a large number of rows.

If the time taken by method invocations is critical to your application, you may configure local caches as Section 15.4.7, “Cache Settings for From-Clause Method Invocations” describes.

Esper analyzes the expression in the EPL where clause and outer-join on clause, if present, and builds the appropriate indexes from that information at runtime, to ensure fast matching of event stream events to method invocation results, even if your method invocation returns a large number of rows. Your applications must ensure to configure a cache for your method invocation using Esper configuration, as such indexes are held with regular data in a cache. If you application does not enable caching of method invocation results, the engine does not build indexes on cached data.

Usually your method invocation will take part in a join and thus be triggered by an event or pattern occurrence. Instead, your application may need to poll a method invocation and thus use Esper query execution and caching facilities and obtain event data and metadata.

Your EPL statement can specify a method invocation in the from clause without a join. Such a stand-alone method invocation does not post new events, and may only be queried via the iterator poll API. Your EPL statement may still use variables.

The next statement assumes that a category_var variable has been declared. It polls the getAssetDescriptions method passing the current value of the category_var EPL variable:

select * from method:MyLookupLib.getAssetDescriptions(category_var)]

Use the iterator and safeIterator methods on EPStatement to obtain results. The statement does not post events to listeners, it is strictly passive in that sense.

Your application must provide a Java class that exposes a public static method. The method must accept the same number and type of parameters as listed in the parameter expression list.

If your method invocation returns either no row or only one row, then the return type of the method can be a Java class or a java.util.Map. If your method invocation can return more then one row, then the return type of the method must be an array of Java class or an array of Map.

If you are using a Java class or an array of Java class as the return type, then the class must adhere to JavaBean conventions: it must expose properties through getter methods.

If you are using java.util.Map as the return type or an array of Map, then the map should have String-type keys and object values (Map<String, Object>). When using Map as the return type, your application must provide a second method that returns property metadata, as the next section outlines.

Your application method must return either of the following:

As an example, consider the method 'getAssetDescriptions' provided by class 'MyLookupLib' as discussed earlier:

select assetId, assetDesc from AssetMoveEvent as asset,
       method:com.mypackage.MyLookupLib.getAssetDescriptions() as desc 
  where asset.assetid = desc.assetid

The 'getAssetDescriptions' method may return multiple rows and is therefore declared to return an array of the class 'AssetDesc'. The class AssetDesc is a POJO class (not shown here):

public class MyLookupLib {
  ...
  public static AssetDesc[] getAssetDescriptions() {
    ...
    return new AssetDesc[] {...};
  }

The example above specifies the full Java class name of the class 'MyLookupLib' class in the EPL statement. The package name does not need to be part of the EPL if your application imports the package using the auto-import configuration through the API or XML, as outlined in Section 15.4.6, “Class and package imports”.

Your application may return java.util.Map or an array of Map from method invocations. If doing so, your application must provide metadata about each row: it must declare the property name and property type of each Map entry of a row. This information allows the engine to perform type checking of expressions used within the statement.

You declare the property names and types of each row by providing a method that returns property metadata. The metadata method must follow these conventions:

In the following example, a class 'MyLookupLib' provides a method to return historical data based on asset id and asset code:

select assetId, location, x_coord, y_coord from AssetMoveEvent as asset,
       method:com.mypackage.MyLookupLib.getAssetHistory(assetId, assetCode) as history

A sample implementation of the class 'MyLookupLib' is shown below.

public class MyLookupLib {
  ...
  // For each column in a row, provide the property name and type
  //
  public static Map<String, Class> getAssetHistoryMetadata() {
    Map<String, Class> propertyNames = new HashMap<String, Class>();
    propertyNames.put("location", String.class);
    propertyNames.put("x_coord", Integer.class);
    propertyNames.put("y_coord", Integer.class);
    return propertyNames;
  }
... 
  // Lookup rows based on assetId and assetCode
  // 
  public static Map<String, Object>[] getAssetHistory(String assetId, String assetCode) {
    Map rows = new Map[2];	// this sample returns 2 rows
    for (int i = 0; i < 2; i++) {
      rows[i] = new HashMap();
      rows[i].put("location", "somevalue");
      rows[i].put("x_coord", 100);
      // ... set more values for each row
    }
    return rows;
  }

In the example above, the 'getAssetHistoryMetadata' method provides the property metadata: the names and types of properties in each row. The engine calls this method once per statement to determine event typing information.

The 'getAssetHistory' method returns an array of Map objects that are two rows. The implementation shown above is a simple example. The parameters to the method are the assetId and assetCode properties of the AssetMoveEvent joined to the method. The engine calls this method for each insert and remove stream event in AssetMoveEvent.

To indicate that no rows are found in a join, your application method may return either a null value or an array of size zero.

The create window statement creates a named window by specifying a window name and one or more data window views, as well as the type of event to hold in the named window.

There are two syntaxes for creating a named window: The first syntax allows to model a named window after an existing event type or an existing named window. The second syntax is similar to the SQL create-table syntax and provides a list of column names and column types.

A new named window starts up empty. It must be explicitly inserted into by one or more statements, as discussed below. A named window can also be populated at time of creation from an existing named window.

If your application stops or destroys the statement that creates the named window, any consuming statements no longer receive insert or remove stream events. The named window can also not be deleted from after it was stopped or destroyed.

The create window statement posts to listeners any events that are inserted into the named window as new data. The statement posts all deleted events or events that expire out of the data window to listeners as the remove stream (old data). The named window contents can also be iterated on via the pull API to obtain the current contents of a named window.

[context context_name] 
create window window_name.view_specifications 
    [as] [select list_of_properties from] event_type_or_windowname
    [insert [where filter_expression]]

create window AllOrdersNamedWindow.win:keepall() as OrderMapEventType

create window OrdersTimeWindow.win:time(30 sec) as 
  select symbol, volume, price from OrderEvent

create window OrdersTimeWindow.win:time(30 sec) as 
  select symbol as sym, volume as vol, price, 1 as alertId from OrderEvent

[context context_name] 
create window window_name.view_specifications [as] (column_name column_type 
  [,column_name column_type [,...])

create window SecurityEvent.win:time(30 sec) 
    (ipAddress string, userId String, numAttempts int, properties String[])

create schema SecurityData (name String, roles String[])

create window SecurityEvent.win:time(30 sec) 
    (ipAddress string, userId String, secData SecurityData, historySecData SecurityData[])

@EventRepresentation(array=true) create window FooWindow.win:time(5 sec) as (string prop1)

For inserting into a named window and for simulateously checking if the inserted row already exists in the named window or for atomic update-insert operation on a named window, consider using on-merge as described in Section 5.15.12, “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.

In this example we first create a named window using some of the columns of an OrderEvent event type:

create window OrdersWindow.win:keepall() as select symbol, volume, price from OrderEvent

The insert into the named window selects individual columns to be inserted:

insert into OrdersWindow(symbol, volume, price) select name, count, price from FXOrderEvent

An alternative form is shown next:

insert into OrdersWindow select name as symbol, vol as volume, price from FXOrderEvent

Following above statement, the engine enters every FXOrderEvent arriving into the engine into the named window 'OrdersWindow'.

The following EPL statements create a named window for an event type backed by a Java class and insert into the window any 'OrderEvent' where the symbol value is IBM:

create window OrdersWindow.win:time(30) as com.mycompany.OrderEvent

insert into OrdersWindow select * from com.mycompany.OrderEvent(symbol='IBM')

The last example adds one column named 'derivedPrice' to the 'OrderEvent' type by specifying a wildcard, and uses a user-defined function to populate the column:

create window OrdersWindow.win:time(30) as select *, price as derivedPrice from OrderEvent

insert into OrdersWindow select *, MyFunc.func(price, percent) as derivedPrice from OrderEvent

Event representations based on Java base classes or interfaces, and subclasses or implementing classes, are compatible as these statements show:

// create a named window for the base class
create window OrdersWindow.std:unique(name) as select * from ProductBaseEvent

// The ServiceProductEvent class subclasses the ProductBaseEvent class
insert into OrdersWindow select * from ServiceProductEvent

// The MerchandiseProductEvent class subclasses the ProductBaseEvent class
insert into OrdersWindow select * from MerchandiseProductEvent

To avoid duplicate events inserted in a named window and atomically check if a row already exists, use on-merge as outlined in Section 5.15.12, “Triggered Upsert using the On-Merge Clause”. An example:

on ServiceProductEvent as spe merge OrdersWindow as win
where win.id = spe.id when not matched then insert select *

create window OrdersWindow.win:time(30) as select *, price as priceTotal from OrderEvent

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

create window OrdersWindow.win:time(30) as select this, price as priceTotal from OrderEvent

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

Your application can insert rows into a named window using on-demand (fire-and-forget, non-continuous) queries as described in Section 14.5, “On-Demand Fire-And-Forget Query Execution”.

The syntax for the insert into clause is as follows:

insert into window_name [(property_names)]
select value_expressions

The window_name is the name of the named window to insert events into.

After the named window name you can optionally provide a comma-separated list of event property names. When providing property names, the order of expressions in the select-clause must match the order of property names specified (select-clause property names are ignored). When not providing property names, the select-clause expressions must name the event properties to be inserted into by assigning a property name.

It follows the select keyword and a list of value expressions that provide values for the event properties of the event to be inserted into the named window.

The example code snippet inserts a new order event into the OrdersWindow named window:

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

If you do not specify event property names, the select-clause expressions must name the event property names.

The following EPL inserts the same values as above but specifies property names as part of the select-clause expressions:

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

on event_type[(filter_criteria)] [as stream_name]
[insert into insert_into_def]
select select_list
from window_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. 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 an stream name. Patterns or named windows can also be specified in the on clause, see the samples in Section 5.15.10.1, “Using Patterns in the On Delete Clause” (for named windows only insert stream events trigger actions).

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_name in the from clause is the name of the named window to select events from. The as keyword is also available to assign a stream name to the named window. The as keyword is helpful in conjunction with wildcard in the select clause to select named window events via the syntax select streamname.* .

The optional where clause contains a criteria_expression that correlates the arriving (triggering) event to the events to be considered from the named window. The criteria_expression may also simply filter for events in the named window 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.

The similarities and differences between an on select clause and a regular or outer join are as follows:

The iterator of the EPStatement object representing the on select clause returns the last batch of selected events in response to the last triggering event, or null if the last triggering event did not select any rows.

For correlated queries that correlate triggering events with events held by a named window, Esper internally creates efficient indexes to enable high performance querying of events. It analyzes the where clause to build one or more indexes for fast lookup in the named window based on the properties of the triggering event.

The next statement demonstrates the concept. Upon arrival of a QueryEvent event the statement selects all events 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. By prefixing the wildcard (*) selector with the stream name, the select clause returns only events of the named window and does not also return triggering events.

The where clause filters and correlates events in the 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 then 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. The engine then posts query results to the statement's listeners.

Aggregation, grouping and ordering of results are possible as this example shows:

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

The above statement outputs the total volume per symbol for those groups where the sum of the volume is greater then zero, ordered by symbol ascending. The engine computes and posts the output based on the current contents of the 'OrdersNamedWindow' named window considering all events in the named window, since the query does not have a where clause.

When using wildcard (*) to select from streams in an on-select clause, each stream, that is the the triggering stream and the selected-upon named window, are selected, similar to a join. Therefore your wildcard select returns two columns: the triggering event and the selection result event, 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.

To trigger an on-select when an update to the selected named window occurs or when the triggering event is the same event that is being inserted into the named window, specify the named window name as the event type.

The next query fires the select for every change to the named window OrdersNamedWindow:

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

For updating a named window and for simulateously checking if the updated row exists in the named window or for atomic update-insert operation on a named window, consider using on-merge as described in Section 5.15.12, “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_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_name is the name of the named window to update events. The as keyword is also available to assign a name to the named window.

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()

functionname(...)

clearQuantities(orderRow)

on UpdateOrderEvent update AllOrdersNamedWindow set price = 0

on ZeroVolumeEvent update AllOrdersNamedWindow set price = 0 where volume <= 0

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

on pattern [every ord=OrderUpdateEvent(volume>0) or every flush=FlushOrderEvent] 
update AllOrdersNamedWindow 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 as upd update MyWindow as win set win.populateFrom(upd)

Your application can update named window rows using on-demand (fire-and-forget, non-continuous) queries as described in Section 14.5, “On-Demand Fire-And-Forget Query Execution”.

The syntax for the update clause is as follows:

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

The window_name is the name of the named window to delete events from. The as keyword is also available to assign a name to the named window.

After the set keyword follows a comma-separated list of mutation expressions. For fire-and-forget queries the following restriction applies: Subqueries, aggregation functions and the prev or prior function may not be used in expressions. Mutation expressions are detailed in Section 5.15.8, “Updating Named Windows: the On Update clause”.

The optional where clause contains a criteria_expression that identifies events to be updated.

The example code snippet updates those rows of the named window that have a negative value for volume:

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

To instruct the engine to use the initial property value before update, prefix the event property name with the literal initial.

Your application can delete rows from a named window using on-demand (fire-and-forget, non-continuous) queries as described in Section 14.5, “On-Demand Fire-And-Forget Query Execution”.

The syntax for the delete clause is as follows:

delete from window_name [as stream_name]
[where criteria_expression]

The window_name is the name of the named window to delete events from. The as keyword is also available to assign a name to the named window.

The optional where clause contains a criteria_expression that identifies events to be removed from the named window.

The example code snippet deletes from a named window all rows that have a negative value for volume:

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

on event_type[(filter_criteria)] [as stream_name]
merge [into] window_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 5.15.8, “Updating Named Windows: 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 events in the named window 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 events in the named window 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 event, if any. After completing all matching events the engine continues to the next triggering event if any.

In the first example we declare 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.std: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.win: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.

For correlated queries (as above) that correlate triggering events with events held by a named window, Esper internally creates efficient indexes to enable high performance update and removal of events especially from named windows that hold large numbers of events.

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. Upon iteration, the statement provides the last inserted events, if any.

The following limitations apply to on-merge statements:

Please use the following syntax to create an explicit index on a named window:

create [unique] index index_name on named_window_name (property [hash|btree] 
    [, property] [hash|btree] [,...] )

The optional unique keyboard indicates that the property or properties 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 named_window_name is the name of an existing named window. If the named window has data already, the engine builds an index for the data in the named window.

The list of property names are the properties of events within the named window to include in the index. Following each property name you may specify the optional hash or btree keyword.

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 =.

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.win: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. 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.win: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

As outlined in Section 2.10, “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. Lets 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 15.4.26, “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.win: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:

The synopsis of the create schema syntax providing property names and types is:

create [map | objectarray] schema schema_name [as] 
    (property_name property_type [,property_name property_type [,...])
  [inherits inherited_event_type[, inherited_event_type] [,...]]
  [starttimestamp timestamp_property_name]
  [endtimestamp timestamp_property_name]
  [copyfrom copy_type_name [, copy_type_name] [,...]]

The create keyword can be followed by map to instruct the engine to represent events of that type by the Map event representation, or objectarray to denote an Object-array event type. If neither the map or objectarray keywords are provided, the engine-wide default event representation applies.

After create schema follows a schema_name. The schema name is the event type name.

The property_name is an identifier providing the event property name. The property_type is also required for each property. Valid property types are listed in Section 5.18.1, “Creating Variables: the Create Variable clause” and in addition include:

The optional inherits keywords is followed by a comma-separated list of event type names that are the supertypes to the declared type.

The optional starttimestamp keyword is followed by a property name. Use this to tell the engine that your event has a timestamp. The engine checks that the property name exists on the declared type and returns a date-time value. Declare a timestamp property if you want your events to implicitly carry a timestamp value for convenient use with interval algebra methods as a start timestamp.

The optional endtimestamp keyword is followed by a property name. Use this together with starttimestamp to tell the engine that your event has a duration. The engine checks that the property name exists on the declared type and returns a date-time value. Declare an endtimestamp property if you want your events to implicitly carry a duration value for convenient use with interval algebra methods.

The optional copyfrom keyword is followed by a comma-separate list of event type names. For each event type listed, the engine looks up that type and adds all event property definitions to the newly-defined type, in addition to those listed explicitly (if any).

A few example event type declarations follow:

// Declare type SecurityEvent
create schema SecurityEvent as (ipAddress string, userId String, numAttempts int)
			
// Declare type AuthorizationEvent with the roles property being an array of String 
// and the hostinfo property being a POJO object
create schema AuthorizationEvent(group String, roles String[], hostinfo com.mycompany.HostNameInfo)

// Declare type CompositeEvent in which the innerEvents property is an array of SecurityEvent
create schema CompositeEvent(group String, innerEvents SecurityEvent[])

// Declare type WebPageVisitEvent that inherits all properties from PageHitEvent
create schema WebPageVisitEvent(userId String) inherits PageHitEvent

// Declare a type with start and end timestamp (i.e. event with duration).
create schema RoboticArmMovement (robotId string, startts long, endts long) 
  starttimestamp startts endtimestamp endts
  
// Create a type that has all properties of SecurityEvent plus a userName property
create schema ExtendedSecurityEvent (userName string) copyfrom SecurityEvent

// Create a type that has all properties of SecurityEvent 
create schema SimilarSecurityEvent () copyfrom SecurityEvent

// Create a type that has all properties of SecurityEvent and WebPageVisitEvent plus a userName property
create schema WebSecurityEvent (userName string) copyfrom SecurityEvent, WebPageVisitEvent

To elaborate on the inherits keyword, consider the following two schema definitions:

create schema Foo as (string prop1)

create schema Bar() inherits Foo

Following above schema, Foo is a supertype or Bar and therefore any Bar event also fulfills Foo and matches where Foo matches. An EPL statement such as select * from Foo returns any Foo event as well as any event that is a subtype of Foo such as all Bar events. When your EPL queries don't use any Foo events there is no cost, thus inherits is generally an effective way to share properties between types. The start and end timestamp are also inherited from any supertype that has the timestamp property names defined.

The optional copyfrom keyword is for defining a schema based on another schema. This keyword causes the engine to copy property definitions: There is no inherits, extends, supertype or subtype relationship between the types listed.

To define an event type Bar that has the same properties as Foo:

create schema Foo as (string prop1)

create schema Bar() copyfrom Foo

To define an event type Bar that has the same properties as Foo and that adds its own property prop2:

create schema Foo as (string prop1)

create schema Bar(string prop2) copyfrom Foo

If neither the map or objectarray keywords are provided, and if the create-schema statement provides the @EventRepresentation(array=true) annotation the engine expects object array events. If the statement provides the @EventRepresentation(array=false) annotation the engine expects Map objects as events. If neither annotation is provided, the engine uses the configured default event representation as discussed in Section 15.4.11.1, “Default Event Representation”.

The following two EPL statements both instructs the engine to represent Foo events as object arrays. When sending Foo events into the engine use the sendEvent(Object[] data, String typeName) footprint.

create objectarray schema Foo as (string prop1)

@EventRepresentation(array=true) create schema Foo as (string prop1)

The next two EPL statements both instructs the engine to represent Foo events as Maps. When sending Foo events into the engine use the sendEvent(Map data, String typeName) footprint.

create map schema Foo as (string prop1)

@EventRepresentation(array=false) create schema Foo as (string prop1)

When using Java classes as the underlying event representation your application may simply provide the class name:

create schema schema_name [as] class_name
  [starttimestamp timestamp_property_name]
  [endtimestamp timestamp_property_name]

The class_name must be a fully-qualified class name (including the package name) if imports are not configured. If you application configures imports then the simple class name suffices without package name.

The optional starttimestamp and endtimestamp keywords have a meaning as defined earlier.

The next example statements declare an event type based on a class:

// Shows the use of a fully-qualified class name to declare the LoginEvent event type
create schema LoginEvent as com.mycompany.LoginValue

// When the configuration includes imports, the declaration does not need a package name
create schema LogoutEvent as SignoffValue

A variant stream is a predefined stream into which events of multiple disparate event types can be inserted. Please see Section 5.10.3, “Merging Disparate Types of Events: Variant Streams” for rules regarding property visibility and additional information.

The synopsis is:

create variant schema schema_name [as] eventtype_name|* [, eventtype_name|*] [,...]

Provide the variant keyword to declare a variant stream.

The '*' wildcard character declares a variant stream that accepts any type of event inserted into the variant stream.

Provide eventtype_name if the variant stream should hold events of the given type only. When using insert into to insert into the variant stream the engine checks to ensure the inserted event type or its supertypes match the required event type.

A few examples are shown below:

// Create a variant stream that accepts only LoginEvent and LogoutEvent event types
create variant schema SecurityVariant as LoginEvent, LogoutEvent

// Create a variant stream that accepts any event type
create variant schema AnyEvent as *

The create variable syntax creates a new variable by defining the variable type and name. In alternative to the syntax, variables can also be declared in the runtime and engine configuration options.

The synopsis for creating a variable is as follows:

create [constant] variable variable_type [[]] variable_name [ = assignment_expression ]

Specify the optional constant keyword when the variable is a constant whose associated value cannot be altered. Your EPL design should prefer constant variables over non-constant variables.

The variable_type can be any of the following:

variable_type
	:  string
	|  char 
	|  character
	|  bool 
	|  boolean
	|  byte
	|  short 
	|  int 
	|  integer 
	|  long 
	|  double
	|  float
	|  object
	|  enum_class
	|  class_name
	|  event_type_name

All variable types accept null values. The object type is for an untyped variable that can be assigned any value. You can provide a class name (use imports) or a fully-qualified class name to declare a variable of that Java class type including an enumeration class. You can also supply the name of an event type to declare a variable that holds an event of that type.

Append [] to the variable type to declare an array variable. A limitation is that if your variable type is an event type then array is not allowed.

The variable_name is an identifier that names the variable. The variable name should not already be in use by another variable.

The assignment_expression is optional. Without an assignment expression the initial value for the variable is null. If present, it supplies the initial value for the variable.

The EPStatement object of the create variable statement provides access to variable values. The pull API methods iterator and safeIterator return the current variable value. Listeners to the create variable statement subscribe to changes in variable value: the engine posts new and old value of the variable to all listeners when the variable value is updated by an on set statement.

The example below creates a variable that provides a threshold value. The name of the variable is var_threshold and its type is long. The variable's initial value is null as no other value has been assigned:

create variable long var_threshold

This statement creates an integer-type variable named var_output_rate and initializes it to the value ten (10):

create variable integer var_output_rate = 10

The next statement declares a constant string-type variable:

create constant variable string const_filter_symbol = 'GE'

In addition to creating a variable via the create variable syntax, the runtime and engine configuration API also allows adding variables. The next code snippet illustrates the use of the runtime configuration API to create a string-typed variable:

epService.getEPAdministrator().getConfiguration()
  .addVariable("myVar", String.class, "init value");

The following example declares a constant that is an array of string:

create constant variable string[] const_filters = {'GE', 'MSFT'}

The next example declares a constant that is an array of enumeration values. It assumes the Color enumeration class was imported:

create constant variable Color[] const_colors = {Color.RED, Color.BLUE}

The engine removes the variable if the statement that created the variable is destroyed and all statements that reference the variable are also destroyed. The getVariableNameUsedBy and the removeVariable methods, both part of the runtime ConfigurationOperations API, provide use information and can remove a variable. If the variable was added via configuration, it can only be removed via the configuration API.

The on set statement assigns a new value to one or more variables when a triggering event arrives or a triggering pattern occurs. Use the setVariableValue methods on EPRuntime to assign variable values programmatically.

The synopsis for setting variable values is:

on event_type[(filter_criteria)] [as stream_name]
  set variable_name = expression [, variable_name = expression [,...]]

The event_type is the name of the type of events that trigger the variable assignments. 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 an stream name. Patterns and named windows can also be specified in the on clause.

The comma-separated list of variable names and expressions set the value of one or more variables. Subqueries may by part of expressions however aggregation functions and the prev or prior function may not be used in expressions.

All new variable values are applied atomically: the changes to variable values by the on set statement become visible to other statements all at the same time. No changes are visible to other processing threads until the on set statement completed processing, and at that time all changes become visible at once.

The EPStatement object provides access to variable values. The pull API methods iterator and safeIterator return the current variable values for each of the variables set by the statement. Listeners to the statement subscribe to changes in variable values: the engine posts new variable values of all variables to any listeners.

In the following example, a variable by name var_output_rate has been declared previously. When a NewOutputRateEvent event arrives, the variable is updated to a new value supplied by the event property 'rate':

on NewOutputRateEvent set var_output_rate = rate

The next example shows two variables that are updated when a ThresholdUpdateEvent arrives:

on ThresholdUpdateEvent as t 
  set var_threshold_lower = t.lower,
      var_threshold_higher = t.higher

The sample statement shown next counts the number of pattern matches using a variable. The pattern looks for OrderEvent events that are followed by CancelEvent events for the same order id within 10 seconds of the OrderEvent:

on pattern[every a=OrderEvent -> (CancelEvent(orderId=a.orderId) where timer:within(10 sec))]
  set var_counter = var_counter + 1

A variable name can be used in any expression and can also occur in an output rate limiting clause. This section presents examples and discusses performance, consistency and atomicity attributes of variables.

The next statement assumes that a variable named 'var_threshold' was created to hold a total price threshold value. The statement outputs an event when the total price for a symbol is greater then the current threshold value:

select symbol, sum(price) from TickEvent 
group by symbol 
having sum(price) > var_threshold

In this example we use a variable to dynamically change the output rate on-the-fly. The variable 'var_output_rate' holds the current rate at which the statement posts a current count to listeners:

select count(*) from TickEvent output every var_output_rate seconds

Variables are optimized towards high read frequency and lower write frequency. Variable reads do not incur locking overhead (99% of the time) while variable writes do incur locking overhead.

The engine softly guarantees consistency and atomicity of variables when your statement executes in response to an event or timer invocation. Variables acquire a stable value (implemented by versioning) when your statement starts executing in response to an event or timer invocation, and variables do not change value during execution. When one or more variable values are updated via on set statements, the changes to all updated variables become visible to statements as one unit and only when the on set statement completes successfully.

The atomicity and consistency guarantee is a soft guarantee. If any of your application statements, in response to an event or timer invocation, execute for a time interval longer then 15 seconds (default interval length), then the engine may use current variable values after 15 seconds passed, rather then then-current variable values at the time the statement started executing in response to an event or timer invocation.

The length of the time interval that variable values are held stable for the duration of execution of a given statement is by default 15 seconds, but can be configured via engine default settings.

A variable of type object (or java.lang.Object via the API) can be assigned any value including null. When using an object-type variable in an expression, your statement may need to cast the value to the desired type.

The following sample EPL creates a variable by name varobj of type object:

create variable object varobj

The create variable syntax and the API accept a fully-qualified class name or alternatively the name of an event type. This is useful when you want a single variable to have multiple property values to read or set.

The next statement assumes that the event type PageHitEvent is declared:

create variable PageHitEvent varPageHitZero

These example statements show two ways of assigning to the variable:

// You may assign the complete event
on PageHitEvent(ip='0.0.0.0') pagehit set varPageHitZero = pagehit

// Or assign individual properties of the event
on PageHitEvent(ip='0.0.0.0') pagehit set varPageHitZero.userId = pagehit.userId

When using class or event-type variables, in order for the engine to assign property values, the underlying event type must allow writing property values. If using JavaBean event classes the class must have setter methods and a default constructor. The underlying event type must also be copy-able i.e. implement Serializable or configure a copy method.

Similarly statements may use properties of class or event-type variables as this example shows:

select * from FirewallEvent(userId=varPageHitZero.userId)

Instances method can also be invoked:

create variable com.example.StateChecker stateChecker

select * from TestEvent as e where stateChecker.checkState(e)

The syntax and additional examples for declaring an expression is outlined in Section 5.2.8, “Expression Declaration”, which discusses declaring expressions that are visible within the same EPL statement i.e. visible locally only.

When using the create expression syntax to declare an expression the engine remembers the expression and allows the expression to be referenced in all other EPL statements.

The below EPL declares a globally visible expression that computes a mid-price:

create expression midPrice { in => (buy + sell) / 2 }

The next EPL returns mid-price for each event:

select midPrice(md) from MarketDataEvent as md

The expression name must be unique for global expressions. It is not possible to declare the same global expression twice with the same name.

Your application can declare an expression of the same name local to a given EPL statement as well as globally using create expression. The locally-declared expression overrides the globally declared expression.

The engine validates globally declared expressions at the time your application creates a statement that references the global expression. When a statement references a global expression, the engine uses that statement's type information to validate the global expressions. Global expressions can therefore be dynamically typed and type information does not need to be the same for all statements that reference the global expression.

This example shows a sequence of EPL, that can be created in the order shown, and that demonstrates expression validation at time of referral:

create expression minPrice {(select min(price) from OrderWindow)}

create window OrderWindow.win:time(30) as OrderEvent

insert into OrderWindow select * from OrderEvent

// Validates and incorporates the declared global expression
select minPrice() as minprice from MarketData

The syntax and additional examples for declaring scripts is outlined in Chapter 18, Script Support, which discusses declaring scripts that are visible within the same EPL statement i.e. visible locally only.

When using the create expression syntax to declare a script the engine remembers the script and allows the script to be referenced in all other EPL statements.

The below EPL declares a globally visible script in the JavaScript dialect that computes a mid-price:

create expression midPrice(buy, sell) [ (buy + sell) / 2 ]

The next EPL returns mid-price for each event:

select midPrice(buy, sell) from MarketDataEvent

The engine validates globally declared scripts at the time your application creates a statement that references the global script. When a statement references a global script, the engine uses that statement's type information to determine parameter types. Global scripts can therefore be dynamically typed and type information does not need to be the same for all statements that reference the global script.

The script name in combination with the number of parameters must be unique for global scripts. It is not possible to declare the same global script twice with the same name and number of parameters.

Your application can declare a script of the same name and number of parameters that is local to a given EPL statement as well as globally using create expression. The locally-declared script overrides the globally declared script.

The optional select clause provides control over which fields are available in output events. The expressions in the select-clause apply only to the properties available underneath the property in the from clause, and the properties of the enclosing event.

When no select is specified, only the properties underneath the selected property are available in output events.

In summary, the select clause may contain:

The next query's select clause selects each review for each book, and the order id as well as the book id of each book:

select * from MediaOrder[select orderId, bookId from books.book][select * from review]
// ... equivalent to ...
select * from MediaOrder[select orderId, bookId from books.book][review]]

Listeners to the statement above receive an event for each review of each book. Each output event has all properties of the review row, and in addition the bookId of each book and the orderId of the order. Thus bookId and orderId are found in each result event, duplicated when there are multiple reviews per book and order.

The above query uses wildcard (*) to select all properties from reviews. As has been discussed as part of the select clause, the wildcard (*) and property_alias.* do not copy properties for performance reasons. The wildcard syntax instead specifies the underlying type, and additional properties are added onto that underlying type if required. Only one wildcard (*) and property_alias.* (unless used with a column rename) may therefore occur in the select clause list of expressions.

All the following queries produce an output event for each review of each book. The next sample queries illustrate the options available to control the fields of output events.

The output events produced by the next query have all properties of each review and no other properties available:

select * from MediaOrder[books.book][review]

The following query is not a valid query, since the order id and book id are not part of the contained-event selection:

// Invalid select-clause: orderId and bookId not produced.
select orderId, bookId from MediaOrder[books.book][review]

This query is valid. Note that output events carry only the orderId and bookId properties and no other data:

select orderId, bookId from MediaOrder[books.book][select orderId, bookId from review]
//... equivalent to ...
select * from MediaOrder[select orderId, bookId from books.book][review]

This variation produces output events that have all properties of each book and only reviewId and comment for each review:

select * from MediaOrder[select * from books.book][select reviewId, comment from review]
// ... equivalent to ...
select * from MediaOrder[books.book as book][select book.*, reviewId, comment from review]

The output events of the next EPL have all properties of the order and only bookId and reviewId for each review:

select * from MediaOrder[books.book as book]
    [select mediaOrder.*, bookId, reviewId from review] as mediaOrder

This EPL produces output events with 3 columns: a column named mediaOrder that is the order itself, a column named book for each book and a column named review that holds each review:

insert into ReviewStream select * from MediaOrder[books.book as book]
  [select mo.* as mediaOrder, book.* as book, review.* as review from review as review] as mo

// .. and a sample consumer of ReviewStream...
select mediaOrder.orderId, book.bookId, review.reviewId from ReviewStream

Please note these limitations:

The optional where clause may be used to filter out properties at the same level that the where-clause occurs.

The properties in the filter expression must be relative to the property in the from clause or the enclosing event.

This query outputs all books with a given author:

select * from MediaOrder[books.book where author = 'Heinlein']

This query outputs each review of each book where a review comment contains the word 'good':

select * from MediaOrder[books.book][review where comment like 'good']

This section discusses contained-event selection in joins.

When joining within the same event it is not required that views are specified. Recall, in a join or outer join there must be views specified that hold the data to be joined. For self-joins, no views are required and the join executes against the data returned by the same event.

This query inner-joins items to books where book id matches the product id:

select book.bookId, item.itemId 
from MediaOrder[books.book] as book, 
      MediaOrder[items.item] as item 
where productId = bookId

Query results for the above query when sending the media order event as shown earlier are:

The next example query is a left outer join. It returns all books and their items, and for books without item it returns the book and a null value:

select book.bookId, item.itemId 
from MediaOrder[books.book] as book 
  left outer join 
    MediaOrder[items.item] as item 
  on productId = bookId

Query results for the above query when sending the media order event as shown earlier are:

A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in null values for missing matches on either side.

This example query is a full outer join, returning all books as well as all items, and filling in null values for book id or item id if no match is found:

select orderId, book.bookId,item.itemId 
from MediaOrder[books.book] as book 
  full outer join 
     MediaOrder[select orderId, * from items.item] as item 
  on productId = bookId 
order by bookId, item.itemId asc

As in all other continuous queries, aggregation results are cumulative from the time the statement was created.

The following query counts the cumulative number of items in which the product id matches a book id:

select count(*) 
from MediaOrder[books.book] as book, 
      MediaOrder[items.item] as item 
where productId = bookId

The unidirectional keyword in a join indicates to the query engine that aggregation state is not cumulative. The next query counts the number of items in which the product id matches a book id for each event:

select count(*) 
from MediaOrder[books.book] as book unidirectional, 
      MediaOrder[items.item] as item 
where productId = bookId

The next example splits an event representing a sentence into multiple events in which each event represents a word. It represents all events and the logic to split events into contained events as Java code. The next chapter has additional examples that use Map-type events and put contained-event logic into a separate expression or script.

The sentence event in this example is represented by a class declared as follows:

public class SentenceEvent {
  private final String sentence;

  public SentenceEvent(String sentence) {
    this.sentence = sentence;
  }

  public WordEvent[] getWords() {
    String[] split = sentence.split(" ");
    WordEvent[] words = new WordEvent[split.length];
    for (int i = 0; i < split.length; i++) {
      words[i] = new WordEvent(split[i]);
    }
    return words;
  }
}

The sentence event as above provides an event property words that returns each word event.

The declaration of word event is also a class:

public class WordEvent {
  private final String word;

  public WordEvent(String word) {
    this.word = word;
  }

  public String getWord() {
    return word;
  }
}

The EPL statement to populate a stream of words from a sentence event is:

insert into WordStream select * from SentenceEvent[words]

Finally, the API call to send a sentence event to the engine is shown here:

epService.getEPRuntime().sendEvent(new SentenceEvent("Hello Word Contained Events"));

The examples herein are not based on the POJO events of the prior example. They are meant to demonstrate different types of contained-event expressions and the use of @type(type_name) to identify the event type of the return values of the contained-event expression.

The example first defines a few sample event types:

create schema SentenceEvent(sentence String)

create schema WordEvent(word String)

create schema CharacterEvent(char String)

The following EPL assumes that your application defined a plug-in single-row function by name splitSentence that returns an array of Map, producting output events that are WordEvent events:

insert into WordStream select * from SentenceEvent[splitSentence(sentence)@type(WordEvent)]

The example EPL shown next invokes a JavaScript function which returns some events of type WordEvent:

expression Collection js:splitSentenceJS(sentence) [
importPackage(java.util);
var words = new ArrayList();
words.add(Collections.singletonMap('word', 'wordOne'));
words.add(Collections.singletonMap('word', 'wordTwo'));
words;
]
select * from SentenceEvent[splitSentenceJS(sentence)@type(WordEvent)]

In the next example the sentence event first gets split into words and then each word event gets split into character events via an additional splitWord single-row function, producing events of type CharacterEvent:

select * from SentenceEvent
  [splitSentence(sentence)@type(WordEvent)]
  [splitWord(word)@type(CharacterEvent)]

The following restrictions apply to contained-event selection:

When updating event objects the engine maintains consistency across statements. The engine ensures that an update to an event does not impact the results of statements that look for or retain the original un-updated event. As a result the engine may need to copy an event object to maintain consistency.

In the case your application utilizes Java objects as the underlying event representation and an update statement updates properties on an object, then in order to maintain consistency across statements it is necessary for the engine to copy the object before changing properties (and thus not change the original object).

For Java application objects, the copy operation is implemented by serialization. Your event object must therefore implement the java.io.Serializable interface to become eligible for update. As an alternative to serialization, you may instead configure a copy method as part of the event type configuration via ConfigurationEventTypeLegacy.