Calcite doesn’t currently have pattern-matching of events. I believe that Esper’s MATCH_RECOGNIZE is based on Oracle’s MATCH_RECOGNIZE [1]. And Teradata has something similar.
It would be a good addition to Calcite’s SQL. It would be useful for both streaming data (as in Esper) and historical tabular data (as in Oracle). I have logged https://issues.apache.org/jira/browse/CALCITE-1570 <https://issues.apache.org/jira/browse/CALCITE-1570> to track. I know that Flink have added both streaming SQL (based on Calcite SQL) and also CEP support (using an API). If this feature existed in Calcite SQL I’m pretty sure they’d want to use it. Maybe they’ll help develop it. How might this feature be implemented? At first sight these queries require expensive self-joins but I believe that you can implement many cases (if not all) using windowed aggregation. A regular expression can be implemented as a DFA [1] so we could build a window function that takes the state as of the previous row, the values of the triggering expressions, and returns the state as of the current row. Julian [1] https://oracle-base.com/articles/12c/pattern-matching-in-oracle-database-12cr1 <https://oracle-base.com/articles/12c/pattern-matching-in-oracle-database-12cr1> > On Jan 9, 2017, at 9:49 AM, Peter Michalák (PGR) > <[email protected]> wrote: > > Hello All, > > I’m still only getting into Calcite, but I couldn’t find anything regarding > pattern matching for streaming events (similar to what Esper offers). Could > you please help me understand how I could rewrite following query so Calcite > is able to parse and validate it: > > SqlNode parse = planner.parse(“SELECT r1('ts') as ts FROM SqrtEdEvents > MATCH_RECOGNIZE (MEASURES A AS r1, B AS r2 PATTERN (A B) DEFINE A AS > (A.sqrted > THR), B AS (B.sqrted <= THR))”) > > The EPL query compares ‘sqrted’ values from each two latest events and fires > immediately (and only once) when readings drop below ‘THR’ threshold. > > Is there a plan within your community to enhance Calcite expressiveness in > this way or you don’t see a need for this? I would be happy to help. > > > Best Regards, > -Peter > > > -- > Peter Michalák > CDT Cloud Computing for Big Data > Digital Institute, Newcastle University > www.bigdata-cdt.ac.uk<http://www.bigdata-cdt.ac.uk/> > linkedin.com/in/petermichalak<http://linkedin.com/in/petermichalak> > @petomichalak > > >
