define table <table-id> (<attribute-name> <type> {, <attribute-name> <type>}*)
{ from ('parameterName'='value')+ }?
Event tables allow users to store, retrieve and process events in a database table-like structure. These are designed for use cases where events need to be extracted from the stream and accumulated over a long period for real-time or later batch processing, such as performing comparisons with the incoming event stream or feeding them to BAM.
Unlike in windows which are predefined, event tables can have more sophisticated storage and retrieval criteria and a single event table can be used in multiple SiddhiQL expressions. Depending on the requirements, an event table can be defined either in-memory or in a relational database. CEP supports event tables for widely used databases such as MySQL.
In Memory Database Event Tables
These are created in memory which will be fast and easier to define. Following example shows a definition for an in memory event table.
define table cseEventTable (symbol string, price int, volume float);
Relational Database Event Tables
Two types of relational databases are currently supported by Siddhi.
MySQL
H2
The following example shows defining a table with table id 'cseEventTable' which actually stores events in a relational database table named 'cepEventTable'.
define table cseEventTable (symbol string, price int, volume float) from ('datasource.name'='cepDataSource', 'table.name'='cepEventTable')
CEP supports caching for relational database tables with a few different algorithms. To change the algorithm, the users can use the optional 'caching.algorithm' parameter when defining the table as follows:
define table cseEventTable (symbol string, price int, volume float) from ('datasource.name'='cepDataSource', 'table.name'='cepEventTable', 'caching.algorithm'='LRU')
Note
Currently there are three cache management algorithms supported by CEP. Those are:
- Basic: a size-based algorithm, events are cached on a FIFO manner. The oldest event will be dropped when the cache is full.
- LRU (Least Recently Used): least recently used event will be dropped when the cache is full.
- LFU (Least Frequently Used): least frequently used event will be dropped when the cache is full.
Note that specifying an algorithm is optional and when the user doesn't specify an algorithm, caching gets disabled.
Optionally you can define a cache size when defining the table as follows using 'cache.size' parameter:
define table cseEventTable (symbol string, price int, volume float) from ('datasource.name'='cepDataSource', 'table.name'='cepEventTable', 'caching.algorithm'='LRU', 'cache.size'='10000')
In addition, it is also possible to define a relational database event table with a user-provided SQL query as follows. This can be used when the created table needs to be customized, such as having a primary key etc.
define table cseEventTable (symbol string, price int, volume float) from ('datasource.name'='cepDataSource', 'database.name'='cepdb', 'table.name'='cepEventTable' 'create.query'='CREATE TABLE cepEventTable (symbol VARCHAR(40), price DECIMAL, volume BIGINT )')
Note
The 'datasource.name' given here is injected to the Siddhi engine by the WSO2 CEP server. To configure data sources in WSO2 CEP, please refer to the data sources configuration documentation in the Admin Guide.
Note
Note that from CEP 3.1.0 onwards, the parameter 'database.name' is not supported. Instead, users are required to create the data source pointing to a specific database which will be used to create the tables.
Note
The table id can be different from the table name, and Siddhi will always refer to the table id defined here. However, the table id cannot be same as an already existing stream id, since syntactically both are considered in the same manner in the query language.