Table of Contents
Oracle CEP CQL Language Reference
PrefaceIntroduction to Oracle CQLBasic Elements of Oracle CQLPseudocolumnsOperatorsFunctions: Single-RowFunctions: AggregateFunctions: Colt Single-RowFunctions: Colt AggregateFunctions: java.lang.MathFunctions: User-DefinedExpressionsConditionsCommon Oracle CQL DDL ClausesOracle CQL Queries, Views, and JoinsPattern Recognition With MATCH_RECOGNIZEOracle CQL Statements
Thank you for your feedback!You select, process, and filter element data from streams and relations using Oracle CQL queries and views.
A top-level SELECT statement that you create using the QUERY statement is called a query.
A top-level VIEW statement that you create using the VIEW statement is called a view (the Oracle CQL equivalent of a subquery).
A join is a query that combines rows from two or more streams, views, or relations.
For more information, see:
An Oracle CQL query is an operation that you express in Oracle CQL syntax and execute on an Oracle CEP CQL Processor to process data from one or more streams or views. For more information, see Section 14.2, "Queries".
An Oracle CQL view represents an alternative selection on a stream or relation. In Oracle CQL, you use a view instead of a subquery. For more information, see Section 14.3, "Views".
Oracle CEP performs a join whenever multiple streams appear in the FROM clause of the query. For more information, see Section 14.4, "Joins".
Example 14-1 shows typical Oracle CQL queries defined in an Oracle CQL processor component configuration file for the processor named proc.
Example 14-1 Typical Oracle CQL Query
Sign In<?xml version="1.0" encoding="UTF-8"?>
<n1:config
xsi:schemaLocation="http://www.bea.com/ns/wlevs/config/application wlevs_application_config.xsd"
xmlns:n1="http://www.bea.com/ns/wlevs/config/application"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<processor>
<name>proc</name>
<rules>
<view id="lastEvents" schema="cusip mbid srcId bidQty ask askQty seq"><![CDATA[
select cusip, mod(bid) as mbid, srcId, bidQty, ask, askQty, seq
from filteredStream[partition by srcId, cusip rows 1]
]]></view>
<query id="q1"><![CDATA[
SELECT *
FROM lastEvents [Now]
WHERE price > 10000
]]></query>
</rules>
</processor>
</n1:config>
As Example 14-1 shows, the rules element contains each Oracle CQL statement in a rule, view, or query child element:
view: contains Oracle CQL view statements (the Oracle CQL equivalent of subqueries). The view element id attribute defines the name of the view.
In Example 14-1, the view element specifies an Oracle CQL view statement (the Oracle CQL equivalent of a subquery).
query: contains Oracle CQL select statements. The query element id attribute defines the name of the query.
In Example 14-1, the query element specifies an Oracle CQL query statement. The query statement selects from the view. By default, the results of a query are output to a down-stream channel. You can control this behavior in the channel configuration using a selector element.
For more information, see "How to Configure a Channel in the Default Component Configuration File Using Oracle CEP IDE for Eclipse" in the Oracle CEP IDE Developer's Guide for Eclipse.
Each Oracle CQL statement is contained in a <![CDATA[ ... ]]> tag and does not end in a semicolon (;).
For more information, see:
To create an Oracle CQL query:
Using Oracle CEP IDE for Eclipse, create an Oracle CEP application and Event Processing Network (EPN).
For more information, see Oracle CEP IDE Developer's Guide for Eclipse.
In the EPN Editor, right-click an Oracle CQL processor and select Go to Configuration Source as Figure 14-1 shows.
Figure 14-1 Navigating to the Configuration Source of a Processor from the EPN Editor
The EPN Editor opens the corresponding component configuration file for this processor and positions the cursor in the appropriate processor element as Figure 14-2 shows.
Figure 14-2 Editing the Configuration Source for a Processor
Create queries and views and register user-defined functions and windows.
For examples, see
Using Oracle CEP IDE for Eclipse, package your Oracle CEP application and deploy to the Oracle CEP server.
Queries are the principle means of extracting information from data streams and views.
The query clause itself is made up of one of the following parts:
sfw_block: use this select-from-where clause to express a CQL query.
For more information, see Section 14.2.1.1, "Select, From, Where Block".
binary: use this clause to perform set operations on the tuples that two queries or views return.
For more information, see Section 14.2.1.8, "Binary Clause"
xstream_clause: use this clause to specify a relation-to-stream operator that applies to the query.
For more information, see Section 14.2.1.9, "Xstream Clause"
The following sections discuss the basic query types that you can create:
This section summarizes the basic building blocks that you use to construct an Oracle CQL query, including:
Use the sfw_block to specify the select, from, and optional where clauses of your Oracle CQL query.
The sfw_block is made up of the following parts:
Use this clause to specify the stream elements you want in the query's result set. The select_clause may specify all stream elements using the * operator or a list of one or more stream elements.
The list of expressions that appears after the SELECT keyword and before the from_clause is called the select list. Within the select list, you specify one or more stream elements in the set of elements you want Oracle CEP to return from one or more streams or views. The number of stream elements, and their datatype and length, are determined by the elements of the select list.
Optionally, specify distinct if you want Oracle CEP to return only one copy of each set of duplicate tuples selected. Duplicate tuples are those with matching values for each expression in the select list.
For more information, see select_clause::=
Use this clause to specify the streams and views that provide the stream elements you specify in the select_clause (see Section 14.2.1.2, "Select Clause").
The from_clause may specify one or more comma-delimited relation_variable clauses.
For more information, see from_clause::=
You can select from any of the data sources that your relation_variable clause specifies.
You can use the relation_variable clause AS operator to define an alias to label the immediately preceding expression in the select list so that you can reference the result by that (see Section 2.8.1, "Aliases in the relation_variable Clause").
If you create a join (see Section 14.4, "Joins") between two or more streams, view, or relations that have some stream element names in common, then you must qualify stream element names with the name of their stream, view, or relation. Example 14-2 shows how to use stream names to distinguish between the customerID stream element in the OrderStream and the customerID stream element in the CustomerStream.
Example 14-2 Fully Qualified Stream Element Names
<query id="q0"><![CDATA[
select * from OrderStream, CustomerStream
where
OrderStream.customerID = CustomerStream.customerID
]]></query>
Otherwise, fully qualified stream element names are optional. However, Oracle recommends that you always qualify stream element references explicitly. Oracle CEP often does less work with fully qualified stream element names.
Use this optional clause to specify conditions that determine when the select_clause returns results (see Section 14.2.1.2, "Select Clause").
For more information, see opt_where_clause::=.
Use this optional clause to group (partition) results. This clause does not guarantee the order of the result set. To order the groupings, use the order by clause.
For more information, see:
Use this optional clause to order all results or the top-n results.
For more information, see:
Use this optional clause to restrict the groups of returned stream elements to those groups for which the specified condition is TRUE. If you omit this clause, then Oracle CEP returns summary results for all groups.
For more information, see opt_having_clause::=.
Use the binary clause to perform set operations on the tuples that two queries or views return, including:
EXCEPT
MINUS
INTERSECT
UNION and UNION ALL
IN and NOT IN
For more information, see binary::=.
Use this clause to take either a select-from-where clause or binary clause and return its results as one of IStream, DStream, or Rstream relation-to-stream operators.
For more information, see:
Example 14-3 shows a simple query that selects all stream elements from a single stream.
<query id="q0"><![CDATA[
select * from OrderStream where orderAmount > 10000.0
]]></query>
For more information, see "Query".
Example 14-4 shows a query that selects all stream elements from stream S2, with schema (c1 integer, c2 float), using a built-in tuple-based stream-to-relation window operator.
Example 14-4 Built-In Window Query
<query id="BBAQuery"><![CDATA[
create query q209 as select * from S2 [range 5 minutes] where S2.c1 > 10
]]></query>
For more information, see:
Example 14-5 shows a query that uses the MATCH_RECOGNIZE clause to express complex relationships among the stream elements of ItemTempStream.
Example 14-5 MATCH_RECOGNIZE Query
<query id="detectPerish"><![CDATA[
select its.itemId
from tkrfid_ItemTempStream MATCH_RECOGNIZE (
PARTITION BY itemId
MEASURES A.itemId as itemId
PATTERN (A B* C)
DEFINE
A AS (A.temp >= 25),
B AS ((B.temp >= 25) and (to_timestamp(B.element_time) - to_timestamp(A.element_time) < INTERVAL "0 00:00:05.00" DAY TO SECOND)),
C AS (to_timestamp(C.element_time) - to_timestamp(A.element_time) >= INTERVAL "0 00:00:05.00" DAY TO SECOND)
) as its
]]></query>
For more information, see:
Example 14-6 shows a view v1 and a query q1 on that view. The view selects from a stream S1 of xmltype stream elements. The view v1 uses the XMLTABLE clause to parse data from the xmltype stream elements using XPath expressions. Note that the data types in the view's schema match the datatypes of the parsed data in the COLUMNS clause. The query q1 selects from this view as it would from any other data source. The XMLTABLE clause also supports XML namespaces.
<view id="v1" schema="orderId LastShares LastPrice"><![CDATA[
select
X.OrderId,
X.LastShares,
X.LastPrice
from
S1
XMLTABLE (
"//FILL"
PASSING BY VALUE
S1.c1 as "."
COLUMNS
OrderId char(16) PATH "fn:data(../@ID)",
LastShares integer PATH "fn:data(@LastShares)",
LastPrice float PATH "fn:data(@LastPx)"
) as X
]]></view>
<query id="q1"><![CDATA[
IStream(
select
orderId,
sum(LastShares * LastPrice),
sum(LastShares * LastPrice) / sum(LastShares)
from
v1[now]
group by orderId
)
]]></query>
For more information, see:
Use the ORDER BY clause to order the rows selected by a query.
Sorting by position is useful in the following cases:
To order by a lengthy select list expression, you can specify its position in the ORDER BY clause rather than duplicate the entire expression.
For compound queries containing set operators UNION, INTERSECT, MINUS, or UNION ALL, the ORDER BY clause must specify positions or aliases rather than explicit expressions. Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query.
The mechanism by which Oracle CEP sorts values for the ORDER BY clause is specified by your Java locale.
Queries are the principle means of extracting information from data streams and relations. A view represents an alternative selection on a stream or relation that you can use to create subqueries.
A view is only accessible by the queries that reside in the same processor and cannot be exposed beyond that boundary.
In Example 14-7, query BBAQuery selects from view MAXBIDMINASK which in turn selects from other views such as BIDMAX which in turn selects from other views. Finally, views such as lastEvents select from an actual event source: filteredStream. Each such view represents a separate derived stream drawn from one or more base streams.
Example 14-7 Using Views Instead of Subqueries
<view id="lastEvents" schema="cusip bid srcId bidQty ask askQty seq"><![CDATA[
select cusip, bid, srcId, bidQty, ask, askQty, seq
from filteredStream[partition by srcId, cusip rows 1]
]]></view>
<view id="bidask" schema="cusip bid ask"><![CDATA[
select cusip, max(bid), min(ask)
from lastEvents
group by cusip
]]></view>
<view id="bid" schema="cusip bid seq"><![CDATA[
select ba.cusip as cusip, ba.bid as bid, e.seq
from bidask as ba, lastEvents as e
WHERE e.cusip = ba.cusip AND e.bid = ba.bid
]]></view>
<view id="bid1" schema="cusip maxseq"><![CDATA[
select b.cusip, max(seq) as maxseq
from bid as b
group by b.cusip
]]></view>
<view id="BIDMAX" schema="cusip seq srcId bid bidQty"><![CDATA[
select e.cusip, e.seq, e.srcId, e.bid, e.bidQty
from bid1 as b, lastEvents as e
where (e.seq = b.maxseq)
]]></view>
<view id="ask" schema="cusip ask seq"><![CDATA[
select ba.cusip as cusip, ba.ask as ask, e.seq
from bidask as ba, lastEvents as e
WHERE e.cusip = ba.cusip AND e.ask = ba.ask
]]></view>
<view id="ask1" schema="cusip maxseq"><![CDATA[
select a.cusip, max(seq) as maxseq
from ask as a
group by a.cusip
]]></view>
<view id="ASKMIN" schema="cusip seq srcId ask askQty"><![CDATA[
select e.cusip, e.seq, e.srcId, e.ask, e.askQty
from ask1 as a, lastEvents as e
where (e.seq = a.maxseq)
]]></view>
<view id="MAXBIDMINASK" schema="cusip bidseq bidSrcId bid askseq askSrcId ask bidQty askQty"><![CDATA[
select bid.cusip, bid.seq, bid.srcId as bidSrcId, bid.bid, ask.seq, ask.srcId as askSrcId, ask.ask, bid.bidQty, ask.askQty
from BIDMAX as bid, ASKMIN as ask
where bid.cusip = ask.cusip
]]></view>
<query id="BBAQuery"><![CDATA[
ISTREAM(select bba.cusip, bba.bidseq, bba.bidSrcId, bba.bid, bba.askseq, bba.askSrcId, bba.ask, bba.bidQty, bba.askQty, "BBAStrategy" as intermediateStrategy, p.seq as correlationId, 1 as priority
from MAXBIDMINASK as bba, filteredStream[rows 1] as p where bba.cusip = p.cusip)
]]></query>
Using this technique, you can achieve the same results as in the subquery case. However, using views you can better control the complexity of queries and reuse views by name in other queries.
If you create a join (see Section 14.4, "Joins") between two or more views that have some stream element names in common, then you must qualify stream element names with names of streams. Example 14-8 shows how to use view names to distinguish between the seq stream element in the BIDMAX view and the seq stream element in the ASKMIN view.
Example 14-8 Using View Names to Distinguish Between Stream Elements of the Same Name
<view id="MAXBIDMINASK" schema="cusip bidseq bidSrcId bid askseq askSrcId ask bidQty askQty"><![CDATA[
select bid.cusip, bid.seq, bid.srcId as bidSrcId, bid.bid, ask.seq, ask.srcId as askSrcId, ask.ask, bid.bidQty, ask.askQty
from BIDMAX as bid, ASKMIN as ask
where bid.cusip = ask.cusip
]]></view>
Otherwise, fully qualified stream element names are optional. However, it is always a good idea to qualify stream element references explicitly. Oracle CEP often does less work with fully qualified stream element names.
You can specify any query type in the definition of your view. For more information, see Section 14.2, "Queries".
For complete details on the view statement, see "View".
A join is a query that combines rows from two or more streams, views, or relations. Oracle CEP performs a join whenever multiple streams appear in the FROM clause of the query. The select list of the query can select any stream elements from any of these streams. If any two of these streams have a stream element name in common, then you must qualify all references to these stream elements throughout the query with stream names to avoid ambiguity.
If you create a join between two or more streams, view, or relations that have some stream element names in common, then you must qualify stream element names with the name of their stream, view, or relation. Example 14-9 shows how to use stream names to distinguish between the customerID stream element in the OrderStream stream and the customerID stream element in the CustomerStream stream.
Example 14-9 Fully Qualified Stream Element Names
<query id="q0"><![CDATA[
select * from OrderStream[range 5] as orders, CustomerStream[range 3] as customers where
orders.customerID = customers.customerID
]]></query>
Otherwise, fully qualified stream element names are optional. However, Oracle recommends that you always qualify stream element references explicitly. Oracle CEP often does less work with fully qualified stream element names.
Oracle CEP supports the following types of joins:
By default, Oracle CEP performs an inner join (sometimes called a simple join): a join of two or more streams that returns only those stream elements that satisfy the join condition.
Example 14-10 shows how to create a query q4 that uses an inner join between streams S0, with schema (c1 integer, c2 float), and S1, with schema (c1 integer, c2 float).
An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
You specify an outer join in the FROM clause of a query using LEFT or RIGHT OUTER JOIN ... ON syntax.
(non_mt_relation_list::=, relation_variable::=, non_mt_cond_list::=)
Example 14-11 shows how to create a query q5 that uses a left outer join between streams S0, with schema (c1 integer, c2 float), and S1, with schema (c1 integer, c2 float).
<query id="q5"><![CDATA[
SELECT a.c1+b.c1
FROM S0[range 5] AS a LEFT OUTER JOIN S1[range 3] AS b ON b.c2 = a.c2
WHERE b.c2 > 3
]]></query>
Use the ON clause to specify a join condition. Doing so lets you specify join conditions separate from any search or filter conditions in the WHERE clause.
You can perform the following types of outer join:
To write a query that performs an outer join of streams A and B and returns all stream elements from A (a left outer join), use the LEFT OUTER JOIN syntax in the FROM clause as Example 14-12 shows. For all stream elements in A that have no matching stream elements in B, Oracle CEP returns null for any select list expressions containing stream elements of B.
To write a query that performs an outer join of streams A and B and returns all stream elements from B (a right outer join), use the RIGHT OUTER JOIN syntax in the FROM clause as Example 14-13 shows. For all stream elements in B that have no matching stream elements in A, Oracle CEP returns null for any select list expressions containing stream elements of A.
You can create an outer join that refers or looks-back to a previous outer join as Example 14-14 shows.
You can access an Oracle CEP cache from an Oracle CQL statement or user-defined function.
For more information, see:
"Configuring Oracle CEP Caching" in the Oracle CEP IDE Developer's Guide for Eclipse
"Accessing a Cache From an Oracle CQL Statement" in the Oracle CEP IDE Developer's Guide for Eclipse
"Accessing a Cache From an Oracle CQL User-Defined Function" in the Oracle CEP IDE Developer's Guide for Eclipse