Oracle® Fusion Middleware CQL Language Reference for Oracle Complex Event Processing 11g Release 1 (11.1.1.6.0) Part Number E12048-06 |
|
|
PDF · Mobi · ePub |
This chapter provides a reference for Oracle Continuous Query Language (Oracle CQL) pseudocolumns, which you can query for but which are not part of the data from which an event was created.
You can select from pseudocolumns, but you cannot modify their values. A pseudocolumn is also similar to a function without arguments (see Section 1.1.11, "Functions").
Oracle CQL supports the following pseudocolumns:
Every stream element of a base stream or derived stream (a view that evaluates to a stream) has an associated element time.The ELEMENT_TIME
pseudo column returns this time as an Oracle CQL native type bigint
.
Note:
ELEMENT_TIME
is not supported on members of an Oracle CQL relation. For more information, see Section 1.1.1, "Streams and Relations".This section describes:
Section 3.2.1, "Understanding the Value of the ELEMENT_TIME Pseudocolumn"
Section 3.2.2, "Using the ELEMENT_TIME Pseudocolumn in Oracle CQL Queries"
For more information, see:
The value of the ELEMENT_TIME
pseudocolumn depends on whether or not you configure the stream element's channel as system- or application-timestamped.
In this case, the element time for a stream element is assigned by the Oracle CEP system in such a way that subtracting two values of system-assigned time will give a duration that roughly matches the elapsed wall clock time.
For more information, see "System-Timestamped Channels" in the Oracle Fusion Middleware Developer's Guide for Oracle Complex Event Processing for Eclipse.
In this case, the associated element time is assigned by the application using the application assembly file wlevs:expression
element to specify a derived timestamp expression.
Oracle CEP processes the result of this expression as follows:
Section 3.2.1.2.1, "Dervied Timestamp Expression Evalutes to int or bigint"
Section 3.2.1.2.2, "Dervied Timestamp Expression Evalutes to timestamp"
For more information, see "Application-Timestamped Channels" in the Oracle Fusion Middleware Developer's Guide for Oracle Complex Event Processing for Eclipse.
If the dervied timestamp expression evaluates to an Oracle CQL native type of int
, then it is cast to and returned as a corresponding bigint
value. If the expression evaluates to an Oracle CQL native type of bigint
, that value is returned as is.
If the derived timestamp expression evaluates to an Oracle CQL native type of timestamp
, it is converted to a long
value by expressing this time value as the number of milliseconds since the standard base time known as "the epoch", namely January 1, 1970, 00:00:00 GMT.
This section describes how to use ELEMENT_TIME
in various queries, including:
Example 3-1 shows how you can use the ELEMENT_TIME
pseudocolumn in a select statement. Stream S1
has schema (c1 integer)
. Given the input stream that Example 3-2 shows, this query returns the results that Example 3-3 shows. Note that the function to_timestamp
is used to convert the Long
values to timestamp values.
Example 3-1 ELEMENT_TIME Pseudocolumn in a Select Statement
<query id="q4"><![CDATA[ select c1, to_timestamp(element_time) from S1[range 10000000 nanoseconds slide 10000000 nanoseconds] ]]></query>
Example 3-3 Output Relation
Timestamp Tuple Kind Tuple 8000 + 80,12/31/1969 17:00:08 8010 - 80,12/31/1969 17:00:08 9000 + 90,12/31/1969 17:00:09 9010 - 90,12/31/1969 17:00:09 13000 + 130,12/31/1969 17:00:13 13010 - 130,12/31/1969 17:00:13 15000 + 150,12/31/1969 17:00:15 15010 - 150,12/31/1969 17:00:15 23000 + 230,12/31/1969 17:00:23 23010 - 230,12/31/1969 17:00:23 25000 + 250,12/31/1969 17:00:25 25010 - 250,12/31/1969 17:00:25
If your query includes a GROUP BY
clause, you cannot use the ELEMENT_TIME
pseudocolumn in the SELECT
statement directly. Instead, use a view as Section 3.2.2.2, "Using ELEMENT_TIME With GROUP BY" describes.
Consider query Q1
that Example 3-4 shows. You cannot use ELEMENT_TIME
in the SELECT
statement of the query because of the GROUP BY
clause.
Example 3-4 Query With GROUP BY
<query id="Q1"><![CDATA[ SELECT R.queryText AS queryText, COUNT(*) AS queryCount FROM queryEventChannel [range 30 seconds] AS R GROUP BY queryText ]]></query>
Instead, create a view as Example 3-5 shows. The derived stream corresponding to V1
will contain a stream element each time (queryText
, queryCount
, maxTime
) changes for a specific queryText
group.
Example 3-5 View
<view id="V1"><![CDATA[ ISTREAM ( SELECT R.queryText AS queryText, COUNT(*) AS queryCount, MAX(R.ELEMENT_TIME) as maxTime FROM queryEventChannel [range 30 seconds] AS R GROUP BY queryText ) ]]></view>
Note that the element time associated with an output element of view V1 need not be the same as the value of the attribute maxTime for that output event. For example, as the window slides and an element from the queryEventChannel input stream expires from the window, the queryCount for that queryText group would change resulting in an output. However, since there was no new event from the input stream queryEventChannel entering the window, the maxTime among all events in the window has not changed, and the value of the maxTime attribute for this output event would be the same as the value of this attribute in the previous output event.
However, the ELEMENT_TIME of the output event corresponds to the instant where the event has expired from the window, which is different than the latest event from the input stream, making this is an example where ELEMENT_TIME of the output event is different from value of "maxTime" attribute of the output event.
To select the ELEMENT_TIME of the output events of view V1, create a query as Example 3-6 shows.
Example 3-7 shows how the ELEMENT_TIME
pseudocolumn can be used in a pattern query. Here a tuple or event matches correlation variable Nth
if the value of Nth.status
is >= F.status
and if the difference between the Nth.ELEMENT_TIME
value of that tuple and the tuple that last matched F
is less than the given interval as a java.lang.Math.Bigint(Long)
.