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.
Children Display |
---|
depth | 2 |
---|
excerpt | true |
---|
excerptType | simple |
---|
|
Create Streams
The create stream statement is
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:
Code Block |
---|
language | sql |
---|
theme | Eclipse | language | sql |
---|
linenumbers | true |
---|
|
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:
Code Block |
---|
language | sql |
---|
theme | Eclipse | language | sql |
---|
linenumbers | true |
---|
|
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:
Code Block |
---|
|
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:
Code Block |
---|
|
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:
Code Block |
---|
language | sql |
---|
theme | Eclipse | language | sql |
---|
linenumbers | true |
---|
|
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)
Code Block |
---|
language | sql |
---|
theme | Eclipse | language | sql |
---|
linenumbers | true |
---|
|
DROP STREAM category IF EXISTS |
...
You can drop a sink with:
Code Block |
---|
language | sql |
---|
theme | Eclipse | language | sql |
---|
linenumbers | true |
---|
|
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)
Code Block |
---|
language | sql |
---|
theme | Eclipse | language | sql |
---|
linenumbers | true |
---|
|
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" ..."
Code Block |
---|
|
SELECT auction, AVG(price) |
Code Block |
---|
|
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
Code Block |
---|
|
SELECT auction, AVG(price) AS aprice...
|
From
Code Block |
---|
|
... FROM bid [SIZE 60 MINUTES ADVANCE 1 MINUTE TIME]... |
Select
Code Block |
---|
|
SELECT auction, AVG(price) AS aprice...
|
From
Code Block |
---|
|
... 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
Code Block |
---|
|
... WHERE auction > 10 ...
|
...