Chapter 6. EQL Reference

6.1. EQL Introduction

EQL statements are used to derive and aggragate information from one or more streams of events, and to join event streams. This section outlines EQL syntax. It also outlines the built-in views, which are the building blocks for deriving and aggregating information from event streams.

EQL is similar to SQL in it's use of the select clause and the where clause. Where EQL differs most from SQL is in the use of tables. EQL replaces tables with the concept of event streams.

EQL statements contain definitions of one or more views. Similar to tables in an SQL statement, views define the data available for querying and filtering. Some views represent windows over a stream of events. Other views derive statistics from event properties, group events or handle unique event property values. Views can be staggered onto each other to build a chain of views. The Esper engine makes sure that views are reused among EQL statements for efficiency.

The built-in set of views is:

  1. Views that represent moving event windows: win:lenght, win:time, win:time_batch, win:ext_time, ext:sort_window

  2. Views for aggregation: std:unique, std:group, std:last,

  3. Views that derive statistics: std:size, stat:uni, stat:linest, stat:correl, stat:weighted_avg, stat:multidim_stat

Esper can be extended by plugging-in custom developed views.

6.2. EQL Syntax

EQL queries are created and stored in the engine, and publish results as events are received by the engine or timer events occur that match the criteria specified in the query. Events can also be pulled from running EQL queries.

The select clause in an EQL query specifies the event properties or events to retrieve. The from clause in an EQL query specifies the event stream definitions and stream names to use. The where clause in an EQL query specifies search conditions that specify which event or event combination to search for. For example, the following statement returns the average price for IBM stock ticks in the last 30 seconds if the average hit 75 or more.

select average from StockTick(symbol='IBM').win:time(30).stat:uni('price') where average >= 75;

EQL queries follow the below syntax. EQL queries can be simple queries or more complex queries. A simple select contains only a select clause and a single stream definition. Complex EQL queries can be build that feature a more elaborate select list utilizing expressions, may join multiple streams or may contain a where clause that with search conditions.

select select_list
from stream_def [as name] [, stream_def [as name]] [,...]
[where search_conditions]

6.3. Choosing Event Properties And Events: the Select Clause

The select clause is required in all EQL statements. The select clause can be used to select all properties via the wildcard *, or to specify a list of event properties and expressions. The select clause defines the event type (event property names and types) of the resulting events published by the statement, or pulled from the statement.

6.3.1. Choosing all event properties: select *

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

select * from stream_def

The following statement selects all univariate statistics properties for the last 30 seconds of IBM stock ticks for price.

select * from StockTick(symbol='IBM').win:time(30).stat:uni('price')

In a join statement, using the select * syntax selects event properties that contain the events representing the joined streams themselves.

6.3.2. Choosing specific event properties

To chose the particular event properties to return:

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

The following statement selects the count and standard deviation properties for the last 100 events of IBM stock ticks for volume.

select count, stdev from StockTick(symbol='IBM').win:length(100).stat:uni('volume')

6.3.3. Expressions

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

6.3.4. Renaming event properties

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 event property.

select volume * price as volPrice from

6.4. Specifying Event Streams : the From Clause

The from clause is required in all EQL statements. It specifies one or more event streams. Each event stream can optionally be given a name by means of the as syntax.

from stream_def [as name] [, stream_def [as name]] [, ...]

The event stream definition stream_def as shown in the syntax above constists of an event type, an optional filter property list and an optional list of views that derive data from a stream must be supplied. The syntax for an event stream definition is as below:

event_type ( [filter_criteria] ) [.view_spec] [.view_spec] [...]

The following EQL statement 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 optional filter criteria consists of a filter for the event property symbol with the 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

Instead of the fully-qualified Java class name any other event name can be mapped via Configuration to a Java class, making the resulting statement more readable.

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

6.4.1. Specifying an event type

In the example above the event type was org.esper.example.StockTick. The event type is simply the fully qualified Java class name of the class of the event instances that are send into the runtime. Alternatively, via configuration any event name can be used instead of the fully qualified class name. The below example shows one way to obtain the fully qualified class name of a given Java class StockTick.

String eventName = StockTick.class.getName();
String stmt = "from " + eventName + ".win:length(100)"

6.4.2. Specifying event filter criteria

Filter criteria follow the same syntax as outlined in the event pattern section on filters, see Section 5.3, “Filter Expressions”. Filter criteria operators are: =, < , > , >=, <=. Ranges use the in keyword and round (...) or square brackets [].

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.

The below example is a filter criteria list that removes events based on category, price and grade.

from mypackage.RfidEvent(category="Perishable", price<1.00, grade in [1, 2])

6.4.3. Specifying views

Views are used to derive or aggregate data. Views can be staggered onto each other. The section below outlines the views available and plug-in of custom views.

Views can optionally take one or parameters. These parameters can constist of primitive constants such as String, boolean or numeric types. String arrays are also supported as a view parameter type.

Views can optionally take one or parameters. These parameters can constist of primitive constants such as String, boolean or numeric types. String arrays are also supported as a view parameter type.

The below example uses the car location event. It specifies an empty list of filter criteria by adding a empty round brackets () after the event type. The first view "std:group('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 length window for each car id. The next view "std:group({'expressway', 'direction', 'segment'})" groups each event by it's expressway, direction and segment property values. Again, the grouping is done for each car id considering the last 4 events only. The last view "std:size()" is used to report the number of events. Thus the below 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. The "as accSegment" syntax assigns the name accSegment to the resulting event stream.

String carLocEvent = CarLocEvent.class.getName();
String joinStatement = "select * from " + carLocEvent + 
            ".std:group('carId').win:length(4).std:group({'expressway', 'direction', 'segment'}).std:size() as accSegment" +

6.5. Specifying Search Conditions : the Where Clause

The where clause is optional in EQL statements. Via the where clause event streams can be joined and events can be filtered.

Comparison operators =, < , > , >=, <=, !=, <>, is null, is not null and logical combinations via and and or are supported in the where clause. The where keyword can also introduce join conditions as outlined in Section 6.7, “Joining Event Streams”. Where-clauses can also contain expressions. Some examples are listed below.

...where fraud.severity = 5 and amount > 500
...where (orderItem.orderId is null) or (orderItem.class != 10)		 
...where (orderItem.orderId = null) or (orderItem.class <> 10)		 
...where itemCount / packageCount > 10		 

6.6. Build-in views

This chapter outines the views that are built into Esper.

6.6.1. Window views Length window

Creates a moving window extending the specified number of elements into the past.

The below example calculates basic univariate statistics for the last 5 stock ticks for symbol IBM.

StockTickEvent(symbol='IBM').win:length(5).stat:uni('price') Time window

The time_window creates a moving time window extending from the specified time interval in seconds into the past based on the system time.

For the IBM stock tick events in the last 1000 milliseconds, calculate statistics on price.

StockTickEvent(symbol='IBM').win:time(1).stat:uni('price') Externally-timed window

Similar to the time window this view moving time window extending from the specified time interval in seconds into the past, but based on the millisecond time value supplied by an event property.

This view holds stock tick events of the last 10 seconds based on the timestamp property in StockTickEvent., 'timestamp') Time window buffer

This window view buffers events and releases them every specified time interval in one update.

Batch events into a 5 second window releasing new batches every 5 seconds. Listeners to updates posted by this view receive updated information only every 5 seconds.

6.6.2. Standard view set Unique

A view that includes only the most recent among events having the same value for the specified field.

The below example creates a view that retains only the last event per symbol.

StockTickEvent.std:unique('symbol') Group

This view groups events into sub-views by the value of the specified field.

This example calculates statistics on price separately for each symbol.

StockTickEvent.std:group('symbol').stat:uni('price') Size

This view returns the number of elements in view.

This example view reports the number of events within the last 1 minute. Last

This view exposes the last element of its parent view.

This example view contains the retains the statistics calculated on stock tick price for the symbol IBM.


6.6.3. Statistics views Univariate statistics

This view calculated basic univariate statistics on an event property.

Table 6.1. Univariate statistics derived properties

Property NameDescription
countNumber of values
sumSum of values
averageAverage of values
stdevSample standard deviation (square root of variance)
stdevpaPopulation standard deviation

The below example calculates price statistics on stock tick events for the last 10 events.'price') Regression

This view calculates regression on two event properties.

Table 6.2. Regression derived properties

Property NameDescription
yinterceptY Intercept

Calculate slope and y-intercept on price and offer for all events in the last 10 seconds.'price', 'offer') Correlation

Calculates the correlation on two event properties.

Table 6.3. Correlation derived properties

Property NameDescription
correlCorrelation between two event properties

Calculate correlation on price and offer over all stock tick events for IBM.

StockTickEvent(symbol='IBM').stat:correl('price', 'offer') Weighted average

Returns the weigthed average given a weight field and a field to compute the average for. Syntax: weighted_avg(field, weightField)

Table 6.4. Weighted average derived properties

Property NameDescription
averageWeighted average

Views that derive the volume-weighted average price for the last 3 seconds.

StockTickEvent(symbol='IBM').win:time(3000).stat:weighted_avg('price', 'volume') Multi-dimensional statistics

This view works similar to the std:group views in that it groups information by one or more event properties. The view accepts 3 or more parameters: The first parameter to the view defines the univariate statistics values to derive. The second parameter is the property name to derive data from. The remaining parameters supply the event property names to use to derive dimensions.

Table 6.5. Multi-dim derived properties

Property NameDescription
cubeThe cube following the interface

The example below derives the count, average and standard deviation latency of service measurement events per customer.

ServiceMeasurement.stat:multidim_stats({‘count’, ‘average’, ‘stdev’}, 
    'latency', 'customer')

This example derives the average latency of service measurement events per customer, service and error status for events in the last 30 seconds.{‘average’}, 
	'latency', 'customer', 'service', 'status')

6.6.4. Extension View Set Sorted Window View

This view sorts by values in the specified event property and keeps only the top elements up to the given size.

The syntax for this view is : sort(String propertyName, boolean isDescending, int size) .

These view can be used to sort on price descending keeping the lowest 10 prices and reporting statistics on price.

StockTickEvent.ext:sort('price', true, 10).stat:uni('price'))

6.7. Joining Event Streams

Two or more event streams can be part of the from clause and thus both streams determine the resulting events. The where-clause lists the join conditions that Esper uses to relate events in the two or more streams.

Each point in time that an event arrives to one of the event streams, the two event streams are joined and output rows are produced according to the where-clause.

This example joins 2 event streams. The first event stream consists of fraud warning events for which we keep the last 30 minutes (1800 seconds). 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 as fraud, as withdraw
 where fraud.accountNumber = withdraw.accountNumber

6.8. Outer Join

Esper supports left outer joins, right outer joins and full outer joins.

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 row in the event stream OrderList.

select * from as rfid
       left outer join as orderlist
     on rfid.itemId = orderList.itemId

Similarily, 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 row in the event stream RfidEvent.

select * from as rfid
       right outer join 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.

select * from as rfid
       full outer join as orderlist
     on rfid.itemId = orderList.itemId

The last 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 rows are produced. In the example below, when either an RfidEvent or an OrderList event arrive, one or more output event is produced.

6.9. View Plug-in

This is currently not supported (planned).