The CQL Continuous Query Language

STREAM Online Help > Running the System > CQL Continuous Query Language

This section gives a quick introduction to the CQL declarative continuous query language for streams and relations supported by the STREAM data stream management system. We also point to literature where more information on CQL can be obtained. We recommend that users of the demo read this page before attempting to run queries on STREAM.

Introduction

CQL, which stands for Continuous Query Language, is based on modifications to SQL to support streams plus relations and continuous result semantics. CQL retains SQL's familiar Select-From-Where syntax. Some new reserved words have been added to support sliding windows, sampling, etc. We will introduce the syntax and semantics of CQL through a few examples.

Example CQL Queries

Here are a few example queries. These example queries are provided in the Scripts menu of the stream client so that they can be registered by simply running the corresponding script. These queries are over streams R and S both with a two-attribute schema consisting of a 20-byte character attribute name and an integer attribute value. These two streams have also been preregistered with the system for the convenience of the user. The name of each query corresponds to the name of the script for registering the query in the Scripts menu of the stream client.

One Stream Query
This query is probably the simplest query that one can think of over stream R.
SELECT *
FROM R

Simple Aggregation Query
This query is a simple windowed aggregation over stream S, the window consisting of the tuples in the last 15 seconds in S.
SELECT name, AVG(value)
FROM S RANGE 15 SECONDS
GROUP BY name

Simple Join Query
This query is a simple windowed equijoin query over streams R and S on attribute name, the windows each consisting of the last 10 tuples in the respective streams.
SELECT *
FROM R ROWS 10, S ROWS 10
WHERE R.name = S.name

These example queries show how CQL retains the look and feel of SQL by retaining SQL's Select-From-Where-GroupBy syntax. CQL supports streams in addition to relations. A stream in CQL is a possibly infinite multiset of timestamped elements. Streams can appear only in the From clause of a CQL query and they must be accompanied by a sliding window specification, with the default window consisting of all tuples that have arrived in the stream so far. (Since the example One Stream Query does not specify a window over R, the default window will be used.) Apart from the input streams that arrive at the system, streams can also be derived from other streams and relations using CQL (sub)queries.

A relation in CQL is like a materialized view in a traditional DBMS: A relation is a time-varying multiset of tuples. That is, a relation contains a (possibly empty) multiset of tuples at any point in time and this multiset changes over time though insertions and deletions of tuples.

A sliding window is applied to a stream and it converts the stream into a relation. The three common types of sliding windows used in CQL include:
  1. Tuple-based windows: A tuple-based window is specified in the form ROWS N, where N is a positive integer, and consists of the last N tuples in the stream at any point of time. The example Simple Join Query specifies tuple-based windows of size 10 over streams R and S.
  2. Time-based windows: A time-based window is specified in the form RANGE N SECONDS, where N is a positive integer, and consists of tuples in the stream that have timestamps that are within the last N seconds. The example Simple Aggregation Query specifies a time-based sliding window of 15 seconds on stream S. Other specifications of time-based windows include: RANGE N MINUTES, RANGE N HOURS, and RANGE N DAYS.
  3. Now windows: Now windows are a special case of time-based windows consisting of tuples in the current instant of time. Since the lowest granularity of time supported by STREAM is 1 microsecond, the current instant means the current microsecond in STREAM. Now windows are commonly used in stream applications and are specified using the NOW keyword. Examples of Now windows are given in the Running the Benchmark section of this help.

CQL Semantics

We now explain the semantics of a CQL query. The FROM clause of a CQL query specifies a relation or the join of two or more relations. Recall that if a stream appears in the FROM clause of a CQL query, then the stream must be converted into a relation by applying a sliding window, the default window consisting of all tuples in the stream so far. The rest of the query, namely the SELECT, WHERE, and GROUP BY clauses apply over the relational output of the FROM clause, just like in a SQL query. The final output of a SELECT-FROM-WHERE-GROUPBY CQL query is a relation. Recall that a relation is just like a materialized view in a DBMS. All three example queries above produce relational results. Thus, if you run these queries in the system by streaming R and S, you can observe that the continuous result produced by the stream client contains the insertions and deletions to the output relation, indicated by "+" and "-" respectively. So far we saw how CQL queries convert streams to relations and operate on relations. CQL also supports the conversion of relations to streams. The complete picture is shown below:

ISTREAM, DSTREAM, and RSTREAM are the three operators supported by CQL to convert relations to streams in queries. The semantics of these conversions are defined below:

Intuitively, applying ISTREAM over a relation Rel (in CQL, ISTREAM (SELECT * FROM Rel)) outputs a stream consisting of the insertions to Rel. (Recall again that a relation in CQL is just like a materialized view in a DBMS.) Similarly, applying DSTREAM over a relation Rel (in CQL, DSTREAM (SELECT * FROM Rel)) outputs a stream consisting of the deletions from Rel. Applying RSTREAM over a relation Rel (in CQL, RSTREAM (SELECT * FROM Rel)) will output a stream consisting of the contents of Rel at each instant of time. We give one example query here to illustrate these operators. More examples follow in the Running the Benchmark section of this help.

Assume we have an relation CurPrice(stock integer, price float), where price represents the current price of the stock stock. We are interested in the average price of each stock over the last day. This query can be expressed in STREAM in the following manner:
REGISTER STREAM PriceStream (stock integer, price float);
ISTREAM (SELECT * FROM CurPrice) INTO PriceStream;
SELECT stock, AVG(price)
FROM PriceStream RANGE 1 DAY
GROUP BY stock
The above specification creates a derived stream called PriceStream and specifies that this stream contains the insertions to the CurPrice relation. The CQL query then applies a 1-day window over PriceStream and does the required aggregation.

More example CQL queries are given in the Running the Benchmark section of the help. Before we give the citations to literature where more information on the syntax, semantics, and execution of CQL queries can be obtained, here is something that must be borne in mind:

WARNING: The CQL syntax supported currently by STREAM differs from the example CQL queries that have appeared in previous papers and talks on CQL and also from the queries in the Stream Query Repository. The authoritative source on CQL query syntax supported currently by STREAM is available here.

Noteworthy differences in CQL syntax between the demo and earlier papers, talks, and the stream query repository include:
  1. Window specification.
  2. CQL in STREAM currently does not support the sampling clause or the window slide parameter. These will be added shortly.
  3. CQL subqueries are not supported by STREAM. CQL queries which need subqueries can be written using CQL Views supported by STREAM. Please refer to examples of CQL Views given in the Running the Benchmark section of the help.

More Information on CQL

A complete treatment of CQL is beyond the scope of this document. For more information, please refer to our paper on CQL, Jennifer Widom's presentation on CQL available here, and the Stream Query Repository. Please bear in mind the above warning on the differences in CQL syntax between the demo and these papers, talks, and the stream query repository.

Last modified: Mon Jan 8 00:34:05 PST 2003