Remark (2017.11.30): CQL is no longer part of the default download. If you want to use cql, it must be installed. See How to install new features. A comfortable way is to use  "#REQUIRED  de.uniol.inf.is.odysseus.parser.cql2.feature.feature.group" (see Features and Updates)

This document describes the basic concepts of the Continuous Query Language (CQL) of Odysseus and shows how to use the language.

The Continuous Query Language (CQL) is a SQL based declarative query language. This document shows how to formulate queries with CQL.

Create Streams

The

This document describes the basic concepts of the Continuous Query Language (CQL) of Odysseus and shows how to use the language. The Continuous Query Language (CQL) is a SQL based declarative query language. This document shows how to formulate queries with CQL.

Create Streams

The create stream statement is used to tell Odysseus where the data comes from, this normally opens a connection to a source, e.g. a sensor or server.

The stream always consists of a name (here: "category") and a schema:

CREATE STREAM category (id INTEGER, name STRING, description STRING, parentid INTEGER) ....

...

Odysseus has a built-in byte-based format for transfering data. This is, for example, used by the nexmark example. This is called a "CHANNEL"-connection and looks like follows:

CREATE STREAM nexmark:person (timestamp STARTTIMESTAMP,id INTEGER,name STRING,email STRING,creditcard STRING,city STRING,state STRING) CHANNEL localhost : 65440

...

However, the recommended and new way is a generic access, which offers different protocols, wrappers etc. as described in The Odysseus Access Operator Frameworkframework. An example would be: 

CREATE STREAM nexmark:person (timestamp STARTTIMESTAMP, id INTEGER, name STRING, email STRING, creditcard STRING, city STRING, state STRING)
    WRAPPER 'GenericPush' 
    PROTOCOL 'SizeByteBuffer'
    TRANSPORT 'NonBlockingTcp'
    DATAHANDLER 'Tuple'
    OPTIONS ( 'port' '65440', 'host' 'odysseus.offis.uni-oldenburg.de', 'ByteOrder' 'Little_Endian')

As you may see, there is a direct mapping between the needed parameters. So you can use each Protocol Handler and Data Handlerhandler and Transport Handler in a CREATE STREAM statement. Thus, the wrapper must be also existing, which are e.g. GenericPush or GenericPull (see also The Odysseus Access Operator Frameworkframework). The Options-parameter is optional and is a comma separated list of key value pairs that are enclosed by quotation marks.

...

Similar to creating sources for incoming data by "create stream", you can also create sinks for outgoing data. The notation is very similar to "create stream". Since it is also based on the Access Framework, you can also need different Protocol Handler and Data Handlerhandler and Transport Handler. For example, the following creates a sink that writes a CSV file:

CREATE SINK writeout (timestamp STARTTIMESTAMP, auction INTEGER, bidder INTEGER, datetime LONG,    price DOUBLE)
    WRAPPER 'GenericPush'
    PROTOCOL 'CSV'
    TRANSPORT 'File'
    DATAHANDLER 'Tuple'
    OPTIONS ( 'filename' 'E:\test')

Drop Streams

You can drop a stream with:

DROP STREAM category

Since this statement would return an error if the stream "category" does not exist, you can add "IF EXISTS" to avoid this error (it checks, if the stream is existing before running the drop)

DROP STREAM category IF EXISTS

...

You can drop a sink with:

DROP SINK category

Since this statement would return an error if the stream "category" does not exist, you can add "IF EXISTS" to avoid this error (it checks, if the sink is existing before running the drop)

DROP SINK category IF EXISTS

...

We use the following example to explain basic details of CQL-Query.

ATTENTION: Currently, the * notation is not allowed for aggregation functions. So instead of count(*) use count(attribute). The parser error is not very helpful in this case: "Caused by: de.uniol.inf.is.odysseus.parser.cql.parser.ParseException: Encountered " "SELECT" "SELECT "" at line 1, column 1. Was expecting:   "REVOKE" ..."

SELECT auction, AVG(price)

SELECT auction, AVG(price) AS aprice 
FROM bid [SIZE 60 MINUTES ADVANCE 1 MINUTE TIME]
WHERE auction > 10 
GROUP BY auction 
HAVING aprice<100.0

Select

SELECT auction, AVG(price) AS aprice...

From

... FROM bid [SIZE 60 MINUTES ADVANCE 1 MINUTE TIME]... 

 aprice<100.0

Select

SELECT auction, AVG(price) AS aprice...

From

... FROM bid [SIZE 60 MINUTES ADVANCE 1 MINUTE TIME]... 

The most different parts between usual SQL and CQL is the FROM part, because you have the possibility to definie windows. CQL defines them by squared brackets.

The following parameters are available for time based windows (TIME):

• SIZE: Defines the size of the window, e.g. 60 MINUTES
• ADVANCE: After what time will the window move

The following parameters are available for element based windows (TUPLE)

• SIZE: Defines the size of the window in elements
• ADVANCE: After how many elements is the window moved

Futher information about windows can be found here.

More about the window syntax can be found at Select syntaxThe most different parts between usual SQL and CQL is the FROM part, because you have the possibility to definie windows. CQL defines them by squared brackets.

Where

... WHERE auction > 10 ...

...