CCL Examples

Continuous Computation Language

The Coral8 Engine is programmed via the Continuous Computation Language, CCL. CCL is based on SQL, but there are several important differences:

  • CCL queries are registered with the Server and execute continuously, while SQL queries execute only when submitted.
  • CCL contains many extensions useful for data stream processing, such as support for windows, control over when output is produced, etc.
  • CCL contains significant new features like Pattern Matching and Database Subqueries that make programming in CCL even simpler.

The best way to appreciate the power and ease of use of the CCL is to look at some examples. These examples of continuous queries are taken from real applications developed by Coral8's Beta customers. Note how similar to SQL they are.

Windows and Aggregation

This example from a financial service application computes the tick-by-tick average stock price over a sliding window of 5 minutes by symbol, for high volume trades: 

Insert Into
	StreamAvgPrices
Select
	Symbol, Avg(Price)
From 
	StreamTrades Keep 5 minutes 
Where 
	Volume >= 10000
Group By 
	Symbol

This query is computed continuously (or "tick-by-tick"): for each trade from StreamTrades with a Volume >= 10000, the average price is recomputed over the most recent five-minute period and is published to StreamAvgPrices.

Coral8 supports sliding and jumping windows which may be time-based or row-based, with the ability to retain, uniquely identify and remove rows based on one or more column values.

Correlation (Joins)

This example from a network security application produces an alert if an Intrusion Detection System (IDS) and a Virus Checker are seeing an attack from the same IP address within 10 minutes:

Insert Into 
	StreamAlertCommonIP
Select 
	StreamIDSAlerts.IP
From 
	StreamIDSAlerts Keep 10 minutes,
	StreamVirusCheckAlerts Keep 10 minutes,
Where 
	StreamIDSAlerts.IP= StreamVirusCheckAlerts.IP

Note how the join between two windows is similar to a join between two tables, except that it is executing continuously. The join condition can be arbitrarily complex. Inner joins as well as left, right, and full outer joins are all supported.

Pattern Matching

This example from an RFID monitoring application checks if a tag has been seen by readers A and B, then C, but not D, within a 10 second window.

Insert Into 
	StreamAlerts
Select 
	StreamA.id
From 
	StreamA a, StreamB b, StreamC c, StreamD d
Matching 
	[10 seconds: a && b, c, !d]
On
	a.id = b.id = c.id = d.id

The event pattern matching clause "Matching" uses operators "," (followed by), "&&" (and), "||" (or), and "!" (not, or the absence of an event) to express highly complex event patterns that would otherwise require multiple joins to detect.

Database Subqueries

CCL lets you embed SQL statements into CCL queries. SQL subqueries are executed "on demand" against the target database, and the results are cached in memory, if necessary. Here is a sample query from a trading application to illustrate the syntax for database subqueries:

Insert Into
	OutHistoricalComparison
Select
	InTrades.Symbol, InTrades.Price,
        DbResult.ClosingPrice
From InTrades,
    (Database "OracleDb"
     Schema "../schemas/closing-price.ccs"

     [[SELECT closing_price 
       FROM price_history ph 
       WHERE ph.symbol = InTrades.Symbol 
       AND ph.closing_date = current_date-1]]) as DbResult

This query joins the stream InTrades with an Oracle table that holds "price_history". A few points to note:

  • The Oracle SQL subquery is enclosed in doubled brackets to clearly separate it from the CCL query.
  • The subquery refers to InTrades.Symbol, a data value from the stream, to join the stream to the Oracle table.
  • The details of the Oracle database connection (OracleDb) are defined in a configuration file and may be reused by more than one subquery.
  • Data from the database will be cached, according to a cache policy specified in the configuration file.

CCL vs. Other Approaches

As one can see, CCL queries are very similar to SQL queries. This is one of the big advantages Coral8 has over other commercially available systems for solving continuous computation and complex event processing problems.

By comparison:

  • Some systems use languages which are quite cryptic and look nothing like SQL or any other familiar language.
  • Some systems simply use C or Java APIs.
  • Other systems don't even provide a language, but are UI-based.

Only the Coral8's CCL is a true SQL-like easy-to-use language for creating continuous computation and complex event processing applications.