Oracle® CEP CQL Language Reference 11g Release 1 (11.1.1) Part Number E12048-02 |
|
|
View PDF |
This chapter describes the various Oracle CQL data definition language (DDL) and data modification language (DML) statements that Oracle CEP supports.
Oracle CQL supports the following DDL statements:
Note:
In stream input examples, lines beginning withh
(such as h 3800
) are heartbeat input tuples. These inform Oracle CEP that no further input will have a timestamp lesser than the heartbeat value.For more information, see:
Purpose
Use the query statement to define a Oracle CQL query that you reference by identifier
in subsequent Oracle CQL statements.
Prerequisites
If your query references a stream or view, then the stream or view must already exist.
If the query already exists, Oracle CEP server throws an exception.
For more information, see:
Syntax
You express a query in a <query></query>
element as Example 16-1 shows. Specify the identifier
as the query
element id
attribute.
Example 16-1 Query in a <query></query> Element
<query id="q0"><![CDATA[ select * from OrderStream where orderAmount > 10000.0 ]]></query>
query::=
(sfw_block::=, xstream_clause::=, binary::=)
sfw_block::=
(select_clause::=, from_clause::=, opt_where_clause::=, opt_group_by_clause::=, order_by_clause::=, order_by_top_clause::=, opt_having_clause::=)
projterm::=
from_clause::=
(non_mt_relation_list::=, relation_variable::=, non_mt_cond_list::=)
(identifier::=, window_type::=, pattern_recognition_clause::=, xmltable_clause::=)
window_type::=
(identifier::=, non_mt_attr_list::=, time_spec::=)
(pattern_partition_clause::=, order_by_list::=)
orderterm::=
(order_expr::=, null_spec::=, asc_desc::=)
null_spec::=
asc_desc::=
binary::=
(xmlnamespace_clause::=, const_string::=, xqryargs_list::=, xtbl_cols_list::=)
Semantics
named_query
Specify the Oracle CQL query statement itself (see "query").
If you plan to configure the query with USE UPDATE SEMANTICS
, you must declare one or more stream elements as a primary key (out_of_line_constraint::=).
For syntax, see "Query".
query
You can create an Oracle CQL query from any of the following clauses:
sfw_block
: a select, from, and other optional clauses (see "sfw_block").
binary
: an optional set operation clause (see "binary").
xstream_clause
: apply an optional relation-to-stream operator to your sfw_block
or binary
clause to control how the query returns its results (see "xstream_clause").
For syntax, see query::=.
sfw_block
Specify the select, from, and other optional clauses of the Oracle CQL query. You can specify any of the following clauses:
select_clause
: the stream elements to select from the stream or view you specify (see "select_clause").
from_clause
: the stream or view from which your query selects (see "from_clause").
opt_where_clause
: optional conditions your query applies to its selection (see "opt_where_clause")
opt_group_by_clause
: optional grouping conditions your query applies to its results (see "opt_group_by_clause")
order_by_clause
: optional ordering conditions your query applies to its results (see "order_by_clause")
order_by_top_clause
: optional ordering conditions your query applies to the top-n
elements in its results (see "order_by_top_clause")
opt_having_clause
: optional clause your query uses to restrict the groups of returned stream elements to those groups for which the specified condition
is TRUE
(see "opt_having_clause")
For syntax, see sfw_block::= (parent: query::=).
select_clause
Specify the select clause of the Oracle CQL query statement.
If you specify the asterisk (*
), then this clause returns all tuples, including duplicates and nulls.
Otherwise, specify the individual stream elements you want (see "non_mt_projterm_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 an example, see "Select and Distinct Examples".
For syntax, see select_clause::= (parent: sfw_block::=).
non_mt_projterm_list
Specify the projection term ("projterm") or comma separated list of projection terms in the select clause of the Oracle CQL query statement.
For syntax, see non_mt_projterm_list::= (parent: select_clause::=).
projterm
Specify a projection term in the select clause of the Oracle CQL query statement. You can select any element from any of stream or view in the from_clause
(see "from_clause") using the identifier
of the element.
Optionally, you can specify an arithmetic expression on the projection term.
Optionally, use the AS
keyword to specify an alias for the projection term instead of using the stream element name as is.
For syntax, see projterm::= (parent: non_mt_projterm_list::=).
from_clause
Specify the from clause of the Oracle CQL query statement by specifying the individual streams or views from which your query selects (see "non_mt_relation_list").
To perform an outer join, use the LEFT
or RIGHT OUTER JOIN ... ON
syntax. To perform an inner join, use the WHERE
clause.
For more information, see:
For syntax, see from_clause::= (parent: sfw_block::=).
non_mt_relation_list
Specify the stream or view ("relation_variable") or comma separated list of streams or views in the from clause of the Oracle CQL query statement.
For syntax, see non_mt_relation_list::= (parent: from_clause::=).
relation_variable
Use the relation_variable
statement to specify a stream or view from which the Oracle CQL query statement selects.
You can specify a previously registered or created stream or view directly by its identifier
you used when you registered or created the stream or view. Optionally, use the AS
keyword to specify an alias for the stream or view instead of using its name as is.
To specify a built-in stream-to-relation operator, use a window_type
clause (see "window_type"). Optionally, use the AS
keyword to specify an alias for the stream or view instead of using its name as is.
To apply advanced comparisons optimized for data streams to the stream or view, use a pattern_recognition_clause
(see "pattern_recognition_clause"). Optionally, use the AS
keyword to specify an alias for the stream or view instead of using its name as is.
To process xmltype
stream elements using XPath and XQuery, use an xmltable_clause
(see "xmltable_clause"). Optionally, use the AS
keyword to specify an alias for the stream or view instead of using its name as is.
To perform an outer join, use the LEFT
or RIGHT OUTER JOIN ... ON
syntax. To perform an inner join, use the WHERE
clause.
For more information, see:
For syntax, see relation_variable::= (parent: non_mt_relation_list::=).
window_type
Specify a built-in stream-to-relation operator.
For more information, see Section 1.1.3, "Stream-to-Relation Operators (Windows)".
For syntax, see window_type::= (parent: relation_variable::=).
time_spec
Specify the time over which a range or partitioned range sliding window should slide.
Default: if units are not specified, Oracle CEP assumes [second|seconds]
.
For more information, see "Range-Based Stream-to-Relation Window Operators" and "Partitioned Stream-to-Relation Window Operators".
For syntax, see time_spec::= (parent: window_type::=).
opt_where_clause
Specify the (optional) where clause of the Oracle CQL query statement.
For syntax, see opt_where_clause::= (parent: sfw_block::=).
opt_group_by_clause
Specify the (optional) GROUP BY
clause of the Oracle CQL query statement. Use the GROUP
BY
clause if you want Oracle CEP to group the selected stream elements based on the value of expr
(s) and return a single (aggregate) summary result for each group.
Expressions in the GROUP
BY
clause can contain any stream elements or views in the FROM
clause, regardless of whether the stream elements appear in the select list.
The GROUP
BY
clause groups stream elements but does not guarantee the order of the result set. To order the groupings, use the ORDER
BY
clause.
For syntax, see opt_group_by_clause::= (parent: sfw_block::=).
order_by_clause
Specify the ORDER BY
clause of the Oracle CQL query statement as a comma-delimited list ("order_by_list") of one or more order terms (see "orderterm"). Use the ORDER
BY
clause to specify the order in which stream elements on the left-hand side of the rule are to be evaluated. The expr
must resolve to a dimension or measure column.
For more information, see Section 14.2.6, "Sorting Query Results".
For syntax, see order_by_clause::= (parent: sfw_block::=).
order_by_top_clause
Specify the ORDER BY
clause of the Oracle CQL query statement as a comma-delimited list ("order_by_list") of one or more order terms (see "orderterm") followed by a ROWS
keyword and integer number (n
) of elements. Use this form of the ORDER BY
clause to select the top-n
elements over a stream or relation. This clause always returns a relation.
Consider the following example queries:
At any point of time, the output of the following example query will be a relation having top 10 stock symbols throughout the stream.
select stock_symbols from StockQuotes order by stock_price rows 10
At any point of time, the output of the following example query will be a relation having top 10 stock symbols from last 1 hour of data.
select stock_symbols from StockQuotes[range 1 hour] order by stock_price rows 10
For more information, see
For syntax, see order_by_top_clause::= (parent: sfw_block::=).
order_by_list
Specify a comma-delimited list of one ore more order terms (see "orderterm") in an (optional) ORDER BY
clause.
For syntax, see order_by_list::= (parent: order_by_clause::=).
orderterm
A stream element (attr::=) or positional index (constant int) to a stream element. Optionally, you can configure whether or not nulls are ordered first or last using the NULLS
keyword (see "null_spec").
order_expr (order_expr::=) can be an attr
or constant_int
. The attr
(attr::=) can be any stream element or pseudo column.
For syntax, see orderterm::= (parent: order_by_list::=).
null_spec
Specify whether or not nulls are ordered first (NULLS FIRST
) or last (NULLS LAST
) for a given order term (see "orderterm").
For syntax, see null_spec::= (parent: orderterm::=).
asc_desc
Specify whether an order term is ordered in ascending (ASC
) or descending (DESC
) order.
For syntax, see asc_desc::= (parent: orderterm::=).
opt_having_clause
Use the HAVING
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.
Specify GROUP
BY
and HAVING
after the opt_where_clause
. If you specify both GROUP
BY
and HAVING
, then they can appear in either order.
For an example, see "HAVING Example".
For syntax, see opt_having_clause::= (parent: sfw_block::=).
binary
Use the binary
clause to perform set operations on the tuples that two streams or views return.
For examples, see:
For syntax, see binary::= (parent: query::=).
xstream_clause
Use an xstream_clause
to specify a relation-to-stream operator that applies to the query.
For more information, see Section 1.1.4, "Relation-to-Stream Operators".
For syntax, see xstream_clause::= (parent: query::=).
xmltable_clause
Use an xmltable_clause
to process xmltype
stream elements using XPath and XQuery. You can specify a comma separated list (see xtbl_cols_list::=) of one or more XML table columns (see xtbl_col::=), with or without an XML namespace.
For examples, see:
For syntax, see xmltable_clause::= (parent: relation_variable::=).
pattern_recognition_clause
Use a pattern_recognition_clause
to perform advanced comparisons optimized for data streams.
For more information and examples, see Chapter 15, "Pattern Recognition With MATCH_RECOGNIZE".
For syntax, see pattern_recognition_clause::= (parent: relation_variable::=).
Examples
The following examples illustrate the various semantics that this statement supports:
For more examples, see Chapter 14, "Oracle CQL Queries, Views, and Joins".
Simple Query Example
Example 16-2 shows how to register a simple query q0
that selects all (*
) tuples from stream OrderStream
where stream element orderAmount
is greater than 10000.
Example 16-2 REGISTER QUERY
<query id="q0"><![CDATA[ select * from OrderStream where orderAmount > 10000.0 ]]></query>
HAVING Example
Consider the query q4
in Example 16-3 and the data stream S2
in Example 16-4. Stream S2
has schema (c1 integer, c2 integer)
. The query returns the relation in Example 16-5.
Example 16-3 HAVING Query
<query id="q4"><![CDATA[ select c1, sum(c1) from S2[range 10] group by c1 having c1 > 0 and sum(c1) > 1 ]]></query>
Example 16-4 HAVING Stream Input
Timestamp Tuple 1000 ,2 2000 ,4 3000 1,4 5000 1, 6000 1,6 7000 ,9 8000 ,
BINARY Example: UNION and UNION ALL
Given the relations R1
and R2
in Example 16-7 and Example 16-8, respectively, the UNION
query q1
in Example 16-6 returns the relation in Example 16-9 and the UNION ALL
query q2
in Example 16-6 returns the relation in Example 16-10.
Example 16-6 Set Operators: UNION Query
<query id="q1"><![CDATA[ R1 UNION R2 ]]></query> <query id="q2"><![CDATA[ R1 UNION ALL R2 ]]></query>
Example 16-7 Set Operators: UNION Relation Input R1
Timestamp Tuple Kind Tuple 200000: + 20,0.2 201000: - 20,0.2 400000: + 30,0.3 401000: - 30,0.3 100000000: + 40,4.04 100001000: - 40,4.04
Example 16-8 Set Operators: UNION Relation Input R2
Timestamp Tuple Kind Tuple 1002: + 15,0.14 2002: - 15,0.14 200000: + 20,0.2 201000: - 20,0.2 400000: + 30,0.3 401000: - 30,0.3 100000000: + 40,4.04 100001000: - 40,4.04
Example 16-9 Set Operators: UNION Relation Output
Timestamp Tuple Kind Tuple 1002: + 15,0.14 2002: - 15,0.14 200000: + 20,0.2 201000: - 20,0.2 400000: + 30,0.3 401000: - 30,0.3 100000000: + 40,4.04 100001000: - 40,4.04
Example 16-10 Set Operators: UNION ALL Relation Output
Timestamp Tuple Kind Tuple 1002: + 15,0.14 2002: - 15,0.14 200000: + 20,0.2 200000: + 20,0.2 20100: - 20,0.2 201000: - 20,0.2 400000: + 30,0.3 400000: + 30,0.3 401000: - 30,0.3 401000: - 30,0.3 100000000: + 40,4.04 100000000: + 40,4.04 10001000: - 40,4.04 100001000: - 40,4.04
BINARY Example: INTERSECT
Given the relations R1
and R2
in Example 16-12 and Example 16-13, respectively, the INTERSECT
query q1
in Example 16-11 returns the relation in Example 16-14.
Example 16-12 Set Operators: INTERSECT Relation Input R1
Timestamp Tuple Kind Tuple 1000: + 10,30 1000: + 10,40 2000: + 11,20 3000: - 10,30 3000: - 10,40
Example 16-13 Set Operators: INTERSECT Relation Input R2
Timestamp Tuple Kind Tuple 1000: + 10,40 2000: + 10,30 2000: - 10,40 3000: - 10,30
Example 16-14 Set Operators: INTERSECT Relation Output
Timestamp Tuple Kind Tuple 1000: + 10,30 1000: + 10,40 1000: - 10,30 1000: - 10,40 1000: + 10,40 2000: + 11,20 2000: - 11,20 2000: + 10,30 2000: - 10,40 3000: - 10,30
BINARY Example: MINUS
Given the relations R1
and R2
in Example 16-16 and Example 16-17, respectively, the MINUS
query q1
in Example 16-15 returns the relation in Example 16-18.
Example 16-16 Set Operators: MINUS Relation Input R1
Timestamp Tuple Kind Tuple 1500: + 10,40 2000: + 10,30 2000: - 10,40 3000: - 10,30
Example 16-17 Set Operators: MINUS Relation Input R2
Timestamp Tuple Kind Tuple 1000: + 11,20 2000: + 10,40 3000: - 10,30
Example 16-18 Set Operators: MINUS Relation Output
Timestamp Tuple Kind Tuple 1000: + 10,40.0 2000: - 10,40.0
Select and Distinct Examples
Consider the query q1
in Example 16-19. Given the data stream S
in Example 16-20, the query returns the relation in Example 16-21.
Example 16-19 Select DISTINCT Query
<query id="q1"><![CDATA[ SELECT DISTINCT FROM S WHERE c1 > 10 ]]></query>
Example 16-20 Select DISTINCT Stream Input
Timestamp Tuple 1000 23 2000 14 3000 13 5000 22 6000 11 7000 10 8000 9 10000 8 11000 7 12000 13 13000 14
XMLTABLE Query Example
Consider the query q1
in Example 16-22 and the data stream S
in Example 16-23. Stream S
has schema (c1 xmltype)
. The query returns the relation in Example 16-24. For more information, see Section 14.2.5, "XMLTable Query".
Example 16-22 XMLTABLE Query
<query id="q1"><![CDATA[ SELECT X.Name, X.Quantity from S1 XMLTable ( "//item" PASSING BY VALUE S1.c2 as "." COLUMNS Name CHAR(16) PATH "/item/productName", Quantity INTEGER PATH "/item/quantity" ) AS X ]]></query>
Example 16-23 XMLTABLE Stream Input
Timestamp Tuple 3000 "<purchaseOrder><shipTo><name>Alice Smith</name><street>123 Maple Street</street><city>Mill Valley</city><state>CA</state><zip>90952</zip> </shipTo><billTo><name>Robert Smith</name><street>8 Oak Avenue</street><city>Old Town</city><state>PA</state> <zip>95819</zip> </billTo><comment>Hurry, my lawn is going wild!</comment><items> <item><productName>Lawnmower </productName><quantity>1</quantity><USPrice>148.95</USPrice><comment>Confirm this is electric</comment></item><item> <productName>Baby Monitor</productName><quantity>1</quantity> <USPrice>39.98</USPrice> <shipDate>1999-05-21</shipDate></item></items> </purchaseOrder>" 4000 "<a>hello</a>"
Example 16-24 XMLTABLE Relation Output
Timestamp Tuple Kind Tuple 3000: + <productName>Lawnmower</productName>,<quantity>1</quantity> 3000: + <productName>Baby Monitor</productName>,<quantity>1</quantity>
XMLTABLE With XML Namespaces Query Example
Consider the query q1
in Example 16-25 and the data stream S1
in Example 16-26. Stream S1
has schema (c1 xmltype)
. The query returns the relation in Example 16-27. For more information, see Section 14.2.5, "XMLTable Query".
Example 16-25 XMLTABLE With XML Namespaces Query
<query id="q1"><![CDATA[ SELECT * from S1 XMLTable ( XMLNAMESPACES('http://example.com' as 'e'), 'for $i in //e:emps return $i/e:emp' PASSING BY VALUE S1.c1 as "." COLUMNS empName char(16) PATH 'fn:data(@ename)', empId integer PATH 'fn:data(@empno)' ) AS X ]]></query>
Example 16-26 XMLTABLE With XML Namespaces Stream Input
Timestamp Tuple 3000 "<emps xmlns=\"http://example.com\"><emp empno=\"1\" deptno=\"10\" ename=\"John\" salary=\"21000\"/><emp empno=\"2\" deptno=\"10\" ename=\"Jack\" salary=\"310000\"/><emp empno=\"3\" deptno=\"20\" ename=\"Jill\" salary=\"100001\"/></emps>" h 4000
Example 16-27 XMLTABLE With XML Namespaces Relation Output
Timestamp Tuple Kind Tuple 3000: + John,1 3000: + Jack,2 3000: + Jill,3
ORDER BY ROWS Query Example
Consider the query q1
in Example 16-28. Given the data stream S0
in Example 16-29, the query returns the relation in Example 16-30.
Example 16-28 ORDER BY ROWS Query
<query id="q1"><![CDATA[ create query q1 as select c1 ,c2 from S0 order by c1,c2 rows 5 ]]></query>
Purpose
Use view statement to create a view over a base stream or relation that you reference by identifier
in subsequent Oracle CQL statements.
Prerequisites
For more information, see:
Syntax
You express the a view in a <view></view>
element as Example 16-31 shows. Specify the identifier
as the view
element id
attribute. Optionally, specify the schema as the view
element schema
attribute.
Example 16-31 View in a <view></view> Element
<view id="v2" schema="cusip bid ask"><![CDATA[ IStream(select * from S1[range 10 slide 10]) ]]></view>
The body of the view has the same syntax as a query. For more information, see "Query".
Examples
The following examples illustrate the various semantics that this statement supports. For more examples, see Chapter 14, "Oracle CQL Queries, Views, and Joins".
Registering a View Example
Example 16-32 shows how to register view v2
.