www.espertech.comDocumentation

Appendix A. Output Reference and Samples

This section specifies the output of a subset of EPL continuous queries, for two purposes: First, to help application developers understand streaming engine output in response to incoming events and in response to time passing. Second, to document and standardize output for EPL queries in a testable and trackable fashion.

The section focuses on a subset of features, namely the time window, aggregation, grouping, and output rate limiting. The section does not currently provide examples for many of the other language features, thus there is no example for other data windows (the time window is used here), joins, sub-selects or named windows etc.

Rather then just describe syntax and output, this section provides detailed examples for each of the types of queries presented. The input for each type of query is always the same set of events, and the same timing. Each event has three properties: symbol, volume and price. The property types are string, long and double, respectively.

The chapters are organized by the type of query: The presence or absence of aggregation functions, as well as the presence or absence of a group by clause change statement output as described in ???.

You will notice that some queries utilize the order by clause for sorting output. The reason is that when multiple output rows are produced at once, the output can be easier to read if it is sorted.

With output rate limiting, the engine invokes your listener even if there are no results to indicate when the output condition has been reached. Such is indicated as (empty result) in the output result columns.

The output columns show both insert and remove stream events. Insert stream events are delivered as an array of EventBean instances to listeners in the newData parameter, while remove stream events are delivered to the oldData parameter of listeners. Delivery to observers follows similar rules.

For the purpose of illustration and documentation, the example data set demonstrates input and remove streams based on a time window of a 5.5 second interval. The statement utilizing the time window could look as follows:

select symbol, volume, price from MarketData#time(5.5 sec)

We have picked a time window to demonstrate the output for events entering and leaving a data window with an expiration policy. The time window provides a simple expiration policy based on time: if an event resides in the time window more then 5.5 seconds, the engine expires the event from the time window.

The input events and their timing are below. The table should be read, starting from top, as "The time starts at 0.2 seconds. Event E1 arrives at 0.2 seconds with properties [S1, 100, 25]. At 0.8 second event E2 arrives with properties [S2, 5000, 9.0]" and so on.

                       Input                                 
-----------------------------------------------  
 Time Symbol  Volume   Price
  0.2                          
          S1     100    25.0   Event E1 arrives
  0.8                          
          S2    5000     9.0   Event E2 arrives
  1.0                          
  1.2                          
  1.5                          
          S1     150    24.0   Event E3 arrives
          S3   10000     1.0   Event E4 arrives
  2.0                          
  2.1                          
          S1     155    26.0   Event E5 arrives
  2.2                          
  2.5                          
  3.0                          
  3.2                          
  3.5                          
          S3   11000     2.0   Event E6 arrives
  4.0                          
  4.2                          
  4.3                          
          S1     150    22.0   Event E7 arrives
  4.9                          
          S3   11500     3.0   Event E8 arrives
  5.0                          
  5.2                          
  5.7                          Event E1 leaves the time window
  5.9                          
          S3   10500     1.0   Event E9 arrives
  6.0                          
  6.2                          
  6.3                          Event E2 leaves the time window
  7.0                          Event E3 and E4 leave the time window
  7.2                          

The event data set assumes a time window of 5.5 seconds. Thus at time 5.7 seconds the first arriving event (E1) leaves the time window.

The data set as above shows times between 0.2 seconds and 7.2 seconds. Only a couple of time points have been picked for the table to keep the set of time points constant between statements, and thus make the test data and output easier to understand.

This chapter provides sample output for queries that do not have aggregation functions and do not have a group by clause.

Using the SNAPSHOT keyword in the output clause, the engine posts data window contents when the output condition is reached.

The statement for this sample reads:

select irstream symbol, volume, price from MarketData#time(5.5 sec)
output snapshot every 1 seconds

The output is as follows:

                       Input                                  Output
                                                 Insert Stream    Remove Stream
-----------------------------------------------  ----------------------------------
 Time Symbol  Volume   Price
  0.2                          
         IBM     100    25.0   Event E1 arrives
  0.8                          
        MSFT    5000     9.0   Event E2 arrives
  1.0                          
  1.2                          
                                                 [IBM, 100, 25.0]                       
                                                 [MSFT, 5000, 9.0]                      
  1.5                          
         IBM     150    24.0   Event E3 arrives
         YAH   10000     1.0   Event E4 arrives
  2.0                          
  2.1                          
         IBM     155    26.0   Event E5 arrives
  2.2                          
                                                 [IBM, 100, 25.0]                       
                                                 [MSFT, 5000, 9.0]                      
                                                 [IBM, 150, 24.0]                       
                                                 [YAH, 10000, 1.0]                      
                                                 [IBM, 155, 26.0]                       
  2.5                          
  3.0                          
  3.2                          
                                                 [IBM, 100, 25.0]                       
                                                 [MSFT, 5000, 9.0]                      
                                                 [IBM, 150, 24.0]                       
                                                 [YAH, 10000, 1.0]                      
                                                 [IBM, 155, 26.0]                       
  3.5                          
         YAH   11000     2.0   Event E6 arrives
  4.0                          
  4.2                          
                                                 [IBM, 100, 25.0]                       
                                                 [MSFT, 5000, 9.0]                      
                                                 [IBM, 150, 24.0]                       
                                                 [YAH, 10000, 1.0]                      
                                                 [IBM, 155, 26.0]                       
                                                 [YAH, 11000, 2.0]                      
  4.3                          
         IBM     150    22.0   Event E7 arrives
  4.9                          
         YAH   11500     3.0   Event E8 arrives
  5.0                          
  5.2                          
                                                 [IBM, 100, 25.0]                       
                                                 [MSFT, 5000, 9.0]                      
                                                 [IBM, 150, 24.0]                       
                                                 [YAH, 10000, 1.0]                      
                                                 [IBM, 155, 26.0]                       
                                                 [YAH, 11000, 2.0]                      
                                                 [IBM, 150, 22.0]                       
                                                 [YAH, 11500, 3.0]                      
  5.7                          Event E1 leaves the time window
  5.9                          
         YAH   10500     1.0   Event E9 arrives
  6.0                          
  6.2                          
                                                 [MSFT, 5000, 9.0]                      
                                                 [IBM, 150, 24.0]                       
                                                 [YAH, 10000, 1.0]                      
                                                 [IBM, 155, 26.0]                       
                                                 [YAH, 11000, 2.0]                      
                                                 [IBM, 150, 22.0]                       
                                                 [YAH, 11500, 3.0]                      
                                                 [YAH, 10500, 1.0]                      
  6.3                          Event E2 leaves the time window
  7.0                          Event E3 and E4 leave the time window
  7.2                          
                                                 [IBM, 155, 26.0]                       
                                                 [YAH, 11000, 2.0]                      
                                                 [IBM, 150, 22.0]                       
                                                 [YAH, 11500, 3.0]                      
                                                 [YAH, 10500, 1.0] 			

This chapter provides sample output for queries that have aggregation functions, and that do not have a group by clause, and in which all event properties are under aggregation.

This chapter provides sample output for queries that have aggregation functions, and that do not have a group by clause, and in which there are event properties that are not under aggregation.

This chapter provides sample output for queries that have aggregation functions, and that have a group by clause, and in which all event properties are under aggregation or appear in the group by clause.

This chapter provides sample output for queries that have aggregation functions, and that have a group by clause, and in which some event properties are not under aggregation.

The statement for this sample reads:

select irstream symbol, volume, sum(price) from MarketData#time(5.5 sec) 
group by symbol 
output snapshot every 1 seconds

The output is as follows:

                       Input                                  Output
                                                 Insert Stream    Remove Stream
-----------------------------------------------  ----------------------------------
 Time Symbol  Volume   Price
  0.2                          
         IBM     100    25.0   Event E1 arrives
  0.8                          
        MSFT    5000     9.0   Event E2 arrives
  1.0                          
  1.2                          
                                                 [IBM, 100, 25.0]                       
                                                 [MSFT, 5000, 9.0]                      
  1.5                          
         IBM     150    24.0   Event E3 arrives
         YAH   10000     1.0   Event E4 arrives
  2.0                          
  2.1                          
         IBM     155    26.0   Event E5 arrives
  2.2                          
                                                 [IBM, 100, 75.0]                       
                                                 [MSFT, 5000, 9.0]                      
                                                 [IBM, 150, 75.0]                       
                                                 [YAH, 10000, 1.0]                      
                                                 [IBM, 155, 75.0]                       
  2.5                          
  3.0                          
  3.2                          
                                                 [IBM, 100, 75.0]                       
                                                 [MSFT, 5000, 9.0]                      
                                                 [IBM, 150, 75.0]                       
                                                 [YAH, 10000, 1.0]                      
                                                 [IBM, 155, 75.0]                       
  3.5                          
         YAH   11000     2.0   Event E6 arrives
  4.0                          
  4.2                          
                                                 [IBM, 100, 75.0]                       
                                                 [MSFT, 5000, 9.0]                      
                                                 [IBM, 150, 75.0]                       
                                                 [YAH, 10000, 3.0]                      
                                                 [IBM, 155, 75.0]                       
                                                 [YAH, 11000, 3.0]                      
  4.3                          
         IBM     150    22.0   Event E7 arrives
  4.9                          
         YAH   11500     3.0   Event E8 arrives
  5.0                          
  5.2                          
                                                 [IBM, 100, 97.0]                       
                                                 [MSFT, 5000, 9.0]                      
                                                 [IBM, 150, 97.0]                       
                                                 [YAH, 10000, 6.0]                      
                                                 [IBM, 155, 97.0]                       
                                                 [YAH, 11000, 6.0]                      
                                                 [IBM, 150, 97.0]                       
                                                 [YAH, 11500, 6.0]                      
  5.7                          Event E1 leaves the time window
  5.9                          
         YAH   10500     1.0   Event E9 arrives
  6.0                          
  6.2                          
                                                 [MSFT, 5000, 9.0]                      
                                                 [IBM, 150, 72.0]                       
                                                 [YAH, 10000, 7.0]                      
                                                 [IBM, 155, 72.0]                       
                                                 [YAH, 11000, 7.0]                      
                                                 [IBM, 150, 72.0]                       
                                                 [YAH, 11500, 7.0]                      
                                                 [YAH, 10500, 7.0]                      
  6.3                          Event E2 leaves the time window
  7.0                          Event E3 and E4 leave the time window
  7.2                          
                                                 [IBM, 155, 48.0]                       
                                                 [YAH, 11000, 6.0]                      
                                                 [IBM, 150, 48.0]                       
                                                 [YAH, 11500, 6.0]                      
                                                 [YAH, 10500, 6.0]  

This chapter provides sample output for queries that have aggregation functions, and that have a group by clause, and in which all event properties are under aggregation or appear in the group by clause, and the group by clause has a rollup, cube or grouping sets keyword(s) instructing the engine to perform multi-level aggregation.

The statement for this sample reads:

select irstream symbol, volume, sum(price)
from MarketData#time(5.5 sec) 
group by rollup(symbol)
output all every 1 seconds

The output is as follows:

                       Input                                  Output
                                                 Insert Stream    Remove Stream
-----------------------------------------------  ----------------------------------
 Time Symbol  Volume   Price
  0.2                          
         IBM     100    25.0   Event E1 arrives
  0.8                          
        MSFT    5000     9.0   Event E2 arrives
  1.0                          
  1.2                          
                                                 [IBM, 25.0]        [IBM, null]         
                                                 [MSFT, 9.0]        [MSFT, null]        
                                                 [null, 34.0]       [null, null]        
  1.5                          
         IBM     150    24.0   Event E3 arrives
         YAH   10000     1.0   Event E4 arrives
  2.0                          
  2.1                          
         IBM     155    26.0   Event E5 arrives
  2.2                          
                                                 [IBM, 75.0]        [IBM, 25.0]         
                                                 [MSFT, 9.0]        [MSFT, 9.0]         
                                                 [YAH, 1.0]         [YAH, null]         
                                                 [null, 85.0]       [null, 34.0]        
  2.5                          
  3.0                          
  3.2                          
                                                 [IBM, 75.0]        [IBM, 75.0]         
                                                 [MSFT, 9.0]        [MSFT, 9.0]         
                                                 [YAH, 1.0]         [YAH, 1.0]          
                                                 [null, 85.0]       [null, 85.0]        
  3.5                          
         YAH   11000     2.0   Event E6 arrives
  4.0                          
  4.2                          
                                                 [IBM, 75.0]        [IBM, 75.0]         
                                                 [MSFT, 9.0]        [MSFT, 9.0]         
                                                 [YAH, 3.0]         [YAH, 1.0]          
                                                 [null, 87.0]       [null, 85.0]        
  4.3                          
         IBM     150    22.0   Event E7 arrives
  4.9                          
         YAH   11500     3.0   Event E8 arrives
  5.0                          
  5.2                          
                                                 [IBM, 97.0]        [IBM, 75.0]         
                                                 [MSFT, 9.0]        [MSFT, 9.0]         
                                                 [YAH, 6.0]         [YAH, 3.0]          
                                                 [null, 112.0]      [null, 87.0]        
  5.7                          Event E1 leaves the time window
  5.9                          
         YAH   10500     1.0   Event E9 arrives
  6.0                          
  6.2                          
                                                 [IBM, 72.0]        [IBM, 97.0]         
                                                 [MSFT, 9.0]        [MSFT, 9.0]         
                                                 [YAH, 7.0]         [YAH, 6.0]          
                                                 [null, 88.0]       [null, 112.0]       
  6.3                          Event E2 leaves the time window
  7.0                          Event E3 and E4 leave the time window
  7.2                          
                                                 [IBM, 48.0]        [IBM, 72.0]         
                                                 [MSFT, null]       [MSFT, 9.0]         
                                                 [YAH, 6.0]         [YAH, 7.0]          
                                                 [null, 54.0]       [null, 88.0]