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.
This chapter includes the following sections:
You can select from pseudocolumns, but you cannot modify their values. A pseudocolumn is also similar to a function without arguments (see Functions).
Oracle CQL supports the following pseudocolumns:
In CQL, every stream event is associated with a timestamp. The ELEMENT_TIME pseudo column returns the timestamp of the stream event. The datatype of ELEMENT_TIME pseudo column is Oracle CQL native bigint type. The unit of timestamp value returned by ELEMENT_TIME is in nanoseconds.
Note:
ELEMENT_TIME is not supported on members of an Oracle CQL relation. For more information, see Streams and Relations.
This section describes:
For more information, see:
The value of ELEMENT_TIME for each stream event is the timestamp of that event. The timestamp of stream event depends on the stream definition and source.
If source stream is a system timestamped stream, then the timestamp for a stream event is assigned by computing System.nanoTime(). For each event, ELEMENT_TIME pseudo column outputs the event's timestamp.
For example, consider a system timestamped stream defined as: tktest_S1(c1 integer).
select ELEMENT_TIME, to_timestamp(ELEMENT_TIME) from tktest_S1 Input (c1) Output (timestamp: element_time, to_timestamp(element_time)) 10 12619671878392750:+ 12619671878392750,05/26/1970 18:27:51 20 12619671889193750:+ 12619671889193750,05/26/1970 18:27:51 30 12619671890093750:+ 12619671890093750,05/26/1970 18:27:51 40 12619671891399750:+ 12619671891399750,05/26/1970 18:27:51 50 12619671896472750:+ 12619671896472750,05/26/1970 18:27:51
Note:
The output may vary for each execution and also depends on the machine as timestamp is computed by calculatingSystem.nanoTime().If source stream is an application timestamped stream, then timestamp for stream event is assigned by computing the application timestamp expression. The unit of computed timestamp value is always in nanoseconds. ELEMENT_TIME pseudo column outputs the event's timestamp.
For example, consider an application timestamped stream defined as tktest_S1(C1 integer, c2 bigint) and application timestamp expression as: c2*1000000000L.
select ELEMENT_TIME, to_timestamp(ELEMENT_TIME) from tktest_S1 Input(c1,c2) Output(timestamp: element_time, to_timestamp(element_time)) 10, 10 10000000000:+ 10000000000,12/31/1969 17:00:10 20, 20 20000000000:+ 20000000000,12/31/1969 17:00:20 30, 30 30000000000:+ 30000000000,12/31/1969 17:00:30 40, 40 40000000000:+ 40000000000,12/31/1969 17:00:40 50, 50 50000000000:+ 50000000000,12/31/1969 17:00:50
In the above query, the timestamp of each event is computed by computing c2*1000000000L for each event. You can see that ELEMENT_TIME is same as timestamp of the event.
If the derived 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.
If source stream is received from an inline CQL view, then the timestamp of stream event is computed by the view query. ELEMENT_TIME outputs the timestamp of event. The unit of ELEMENT_TIME is always in nanosecond and the datatype is a CQL native bigint type.
For example, consider an application timestamped stream defined as tktest_S1(C1 integer, c2 bigint) and application timestamp expression as: c2*1000000000L.
The view V is defined using the query ISTREAM(SELECT * FROM SYSTS_STREAM[RANGE 1 MINUTE SLIDE 15 SECONDS].
select ELEMENT_TIME, to_timestamp(ELEMENT_TIME) from V
Input(c1,c2) Output(timestamp: element_time, to_timestamp(element_time)) 10, 10 15000000000:+ 15000000000,12/31/1969 17:00:15 20, 20 30000000000:+ 30000000000,12/31/1969 17:00:30 30, 30 30000000000:+ 30000000000,12/31/1969 17:00:30 40, 40 45000000000:+ 45000000000,12/31/1969 17:00:45 50, 50 50000000000:+ 50000000000,12/31/1969 17:00:55
If source stream is received from a subquery, then CQL does not support ELEMENT_TIME on the subquery results.
The following example depicts the scenario which is not supported.
SELECT ELEMENT_TIME FROM ( ISTREAM(SELECT * FROM SYSTS_STREAM[RANGE 1 HOUR SLIDE 5 MINUTES])
This section describes how to use ELEMENT_TIME in various queries, including:
The following example shows how you can use the ELEMENT_TIME pseudocolumn in a select statement. Stream S1 has schema (c1 integer). Note that the function to_timestamp is used to convert the Long values to timestamp values.
<query id="q4"><![CDATA[
select
c1,
to_timestamp(element_time)
from
S1[range 10000000 nanoseconds slide 10000000 nanoseconds]
]]></query>
Timestamp Tuple 8000 80 9000 90 13000 130 15000 150 23000 230 25000 250
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 Using ELEMENT_TIME With GROUP BY describes.
You cannot use ELEMENT_TIME in the SELECT statement of the query because of the GROUP BY clause.
<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. The derived stream corresponding to V1 will contain a stream element each time (queryText, queryCount, maxTime) changes for a specific queryText group.
<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:
The element time associated with an output element of view V1 need not be the same as the value of the attributemaxTime 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 maxTimeattribute of the output event.
To select the ELEMENT_TIME of the output events of view V1, create a query.
<query id="Q1"><![CDATA[
SELECT
queryText,
queryCount,
ELEMENT_TIME as eventTime
FROM
V1
]]></query>
The following example 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).
...
PATTERN (F Nth+? L)
DEFINE
Nth AS
Nth.status >= F.status
AND
Nth.ELEMENT_TIME - F.ELEMENT_TIME < 10000000000L,
L AS
L.status >= F.status
AND
count(Nth.*) = 3
AND L.ELEMENT_TIME - F.ELEMENT_TIME < 10000000000L
...
To partition the incoming events, you need to have the information of the query name or identifier in the output. The query name or identifier should be part of tuple attribute and it should be accessed by calling TupleValue’s getter APIs.
For this purpose, Oracle CQL provides a new pseudo column ORA_QUERY_ID to access the query name in the output of a query.
You can get the query name in the output tuples by using the above pseudo column in the CQL query’s SELECT list as follows:
CREATE QUERY Q1 AS SELECT ORA_QUERY_ID from STREAM;
Each output event of the above query Q1 has only one attribute whose value is equal to query’s name or identifier. In the above query, for each incoming event to STREAM, the application sends an output tuple with one attribute that has the value Q1.
CREATE QUERY Q1 AS SELECT ORA_QUERY_ID, stock_quote, stock_price FROM StockStream;
The input and output values are listed below:
Input(stock_quote, stock_price) Output(ORA_QUERY_ID, stock_quote, stock_price) ORCL, 34 Q1, ORCL, 34 MSFT, 38 Q1, MSFT, 38 CSCO, 21 Q1, CSCO, 21 INTC, 24 Q1, INTC, 24 FB, 48 Q1, FB, 48