There has been a significant interest in processing, within an RDBMS streams of changes to the data and reporting when the result of a query defined over the data changes. These queries are referred to as the continuous queries (CQ) since they continually produce results whenever new data arrives or existing data changes. The interest is driven by sensor and event data processing (RFID in particular), real-time reporting of Key Performance Indicators for Business Intelligence, and by security monitoring to discover sequence of prohibited or dangerous events. One primary reason for an RDBMS solution for stream computation is the declarative power of SQL that enables rapid and uniform (via a single language) application development and offers potential for optimizations of multiple stream queries using multi-query optimization techniques.

Stream processing has been researched extensively in the recent literature ([Sul96], [CC+02], [CC+03], [CD+00], [LPT99], [JMS95], [ABB+03], [SLR94], [CJSS03], [MSHR02]). It also has some prototype implementations ([CC+02], [CJSS03], [ABB+03], [CC+03]). In most of these works, streams are treated as append-only tables or transient queues and queries are expressed with SQL extensions using window constructs on the streams. Stream processing attracted previous interest in form of active databases [PD99] (driven by triggers and procedural programming) and in form of materialized views ([JMS95]).

In contrast to the literature where the sources of continuous queries are objects called streams, the sources for our CQ engine are transactions to the relational tables that can insert (like in streams), delete or modify data. Continuous query is defined as a relational set difference between the query result at time t1 and time t2 (t2 > t1) and is best explained using the analogy of materialized views (MVs). Given query Q and its materialized image MV1 at time t1, we accept committed DML changes to the Q’s tables, re-compute MV2 at time t2, and return query delta ΔQ = MV2 – MV1. The notion of query delta is very natural to monitoring applications that define data of interest using a SQL query (e.g., customers with balance less than 0) and need to monitor records entering the set (the insert delta), records leaving it (the delete delta), and records in the original set that change (the update delta). Frequency of re-computation is defined by the user and similarly to MVs, it can be on commit, on demand and periodic. Continuous query is a transactionally consistent query delta i.e., only committed changes are emitted by it. This allows us to build continuous queries that are restartable and consistent across system crashes.

The continuous results of CQ can be deposited either to a queue or appended to a table. Users are expected to asynchronously de-queue the results from the queue or retrieve it directly from the table. Management of the destination (queue or table) is left to the user since he/she may decide to preserve it for historical purposes. We also provide for procedural extensions for CQ. The process of emitting data from a CQ can invoke an RDBMS trigger or a user defined callback where further programmatic processing can be done.

Download