Streams Query Syntax
Overview
The Oracle Infinity Streams Lab supports tailoring a stream to your specific needs through Streams Queries. Streams Queries are similar to SQL in that you can specify which data you’d like to include in your stream, any conditions that the data has to match, etc. Below is a reference that dives into the details of Streams Queries, and how you can use them to enable powerful analytics and data pipeline use cases.
Query Format:
Streams Queries are composed in the following format:
SELECT <select criteria> WHERE <where criteria> HAVING <having criteria>
Select Criteria - Required
The select criteria is a comma separated list of values to be included in the message.
Type of Select criteria | Example | Behavior | Selection applied to | Notes |
Basic segment | data.wt.ti
|
The parameter is included in the outgoing message if present in the current event | Current event if matching the WHERE criteria | |
---|---|---|---|---|
Basic prefix wildcard | data.*
|
All parameters in the current event that match the filter are included in the outgoing message
|
Current event if matching the WHERE criteria | |
All wild card | *
|
All parameters in the current event are included in the outgoing message | Current event if matching the WHERE criteria | |
Any selector | ANY(data.wt.ti)
|
Include a single event value from the session. Which value specifically is returned is irrelevant | Current event, or any other event in the session with the parameter, if matching the WHERE criteria. | Wild cards are not supported |
Earliest selector | EARLIEST(data.wt.ti)
|
Include the value from the earliest event in the session that has the parameter | All events matching the HAVING criteria | Wild cards are not supported |
Earliest with null | EARLIEST_INCLUDE_NULL(data.wt.ti)
|
Include the value from the first event in the session evaluated if present | All events matching the HAVING criteria | Wild cards are not supported |
Count Selector | COUNT(data.wt.ti)
|
Include the number of events in the session that have a value for the specified key | All events matching the HAVING criteria | Wild cards are not supported |
Count Distinct | COUNT_DISTINCT(data.wt.ti)
|
Include the number of unique values for the specified key | All events matching the HAVING criteria |
Wild cards are not supported Case insensitive matching |
Count all | EVENT_COUNT()
|
Include the number of events evaluated | All events matching the HAVING criteria | Wild cards are not supported |
Latest selector | LATEST(data.wt.ti)
|
Include the last value found | All events matching the HAVING criteria | Wild cards are not supported |
Latest with null | LATEST_INCLUDE_NULL(data.wt.ti)
|
Include the value from the last event evaluated if present | All events matching the HAVING criteria | Wild cards are not supported |
Concat | CONCAT(data.wt.ti, ';')
|
Include all values for the specified key concatenated into a single string with the provided separator | All events matching the HAVING criteria |
Wild cards are not supportedSeparators can be strings of multiple characters Order and case are maintained |
Concat distinct | CONCAT_DISTINCT(data.wt.ti, ',')
|
Include all distinct values for the specified key concatenated into a single string with the provided separator | All events matching the HAVING criteria |
Wild cards are not supportedSeparators can be strings of multiple charactersOrder and case are maintainedCase is not used when determining uniqueness. If the value for one event is 'AaA' and the value of the next event is 'aAa', we will only include 'AaA' in the distinct list of values |
Latest exclude like |
|
Include the last value found that doesn't match the LIKE expression | All events matching the HAVING criteria |
Only a single matching expression is supported. All syntax supported by the LIKE Operator is allowed |
Latest include like | LATEST_INCLUDE_LIKE(data.wt.ti, '*a*')
|
Include the last value found that matches the LIKE expression | All events matching the HAVING criteria |
Only a single matching expression is supported. All syntax supported by the LIKE Operator is allowed |
Earliest exclude like | EARLIEST_EXCLUDE_LIKE(data.wt.ti, '')
|
Include the first value found that doesn't match the LIKE expression | All events matching the HAVING criteria |
Only a single matching expression is supported. All syntax supported by the LIKE Operator is allowed |
Earliest include like | EARLIEST_INCLUDE_LIKE(data.wt.ti, 'abc')
|
Include the last value found that matches the LIKE expression | All events matching the HAVING criteria |
Only a single matching expression is supported. All syntax supported by the LIKE Operator is allowed |
Aliasing - Optional
Select criteria can take an optional alias with the format: <selection>
AS '<alias>
'.
<selection>
can be an from the table above, without using wildcards, and <alias>
can be any string
For example:
SELECT session.visitor_id AS 'visitor.id', session.session_id as 'visitor.session', EARLIEST(data.wt.cg_n) AS 'content.group.first', LATEST(data.wt.cg_n) AS 'content.group.last'
yields this message:
{
"meta": {
"schema_version": "3.0",
"api_version": "3.0",
"message_type": "session_update",
"stream_type": "session_all"
},
"content": {
"group": {
"first": [
"Shop Maxymised"
],
"last": [
"Cart"
]
}
},
"visitor": {
"id": "1348447491483863571",
"session": "1532364415416010076"
}
}
Where Criteria - Optional
The WHERE
criteria defines if/when a streams message is emitted. When an event is added to a session, it is compared against this criteria and if it evaluates to true, an update message is emitted.
If no WHERE
clause is specified, every event that comes in to the session will result in an update message being emitted, as well as a closed message when the session is closed.
The ALL
and the ANY
operators are used if you want to evaluate all the events in the session, as opposed to just the current event being processed.
Note: Aliases and Select Critera are only used when sending messages, and cannot be used within the query in a WHERE
(or HAVING
) clause.
For example, this syntax is NOT supported:
- Select data.wt.ti as 'A' where 'A" = 'B'
- Select * where earliest(data.wt.ti) = 'B'
- Select count(data.wt.ti) where count(data.wt.ti) >= 1
- Select event_count() where event_count() >= 1
Type of Where criteria | Example | Behavior | Scope | Notes |
Equals | data.wt.ti='title'
|
A message will be sent if the parameter exists, with the exact value specified. | n/a |
Wild cards are not supported Case insensitive matching |
---|---|---|---|---|
Exists | Exists(data.wt.ti)
|
A message will be sent if the parameter exists, regardless of value | Single Event Only |
Wild cards are not supported Case insensitive matching |
Not Exists | NotExists(data.wt.ti)
Not_Exists(data.wt.ti) |
A message will be sent if the parameter does not exist | Single Event Only |
Wild cards are not supported Case insensitive matching |
Not Equal | data.wt.ti!='title'
|
A message will be sent if the parameter doesn't exist, or doesn't have the exact value specified. | n/a |
Wild cards are not supported Case insensitive matching |
Like | data.wt.ti LIKE 't*'
|
A message will be sent if the parameter exists, and matches the value specified. | n/a | Case insensitive matching |
Not Like |
|
A message will be sent if the parameter doesn't exist, or it does not match the value specified. | n/a |
Case insensitive matching Acceptable: "NOT_LIKE" or "NOTLIKE" |
All | ALL(data.wt.cg_n
= 'cg1')
|
A message will be sent if the parameter exists with the specified value on every event in the session | All events in session |
Case insensitive matching All events in session |
Any | ANY(data.wt.cg_n
= 'cg1')
|
A message will be sent if the parameter exists with the specified value on at least one event in the session | All events in session |
Case insensitive matching All events in session |
Less Than | data.wt.tx_u < 3
|
A message will be sent if the parameter exists, and matches the value specified. | n/a | Numeric values only |
Less Than or Equal To | data.wt.tx_u <= 3
|
A message will be sent if the parameter exists, and matches the value specified. | n/a | Numeric values only |
Greater Than | data.wt.tx_u > 3
|
A message will be sent if the parameter exists, and matches the value specified. | n/a | Numeric values only |
Greater Than or Equal To | data.wt.tx_u >= 3
|
A message will be sent if the parameter exists, and matches the value specified. | n/a | Numeric values only |
OR | data.wt.tx_u < 3
OR
|
A message will be sent if the parameter exists, and matches the value specified for either clause | n/a | |
AND |
AND
|
A message will be sent if the parameter exists, and matches the value specified for all clauses | n/a |
Having Criteria - Optional
The HAVING
clause defines which events in the session will be included, when querying session level data.
The HAVING
clause is different from the WHERE
clause, in that the HAVING
clauses determines WHICH events should be included in the message, while the WHERE
clause determines IF a message should be sent.
It is important to note that the HAVING
clause is only applied to selections using the following list of session level Selectors:
Having Clause Selectors |
---|
CONCAT |
CONCAT_DISTINCT |
COUNT |
COUNT_DISTINCT |
EARLIEST |
EARLIEST_INCLUDE_NULL |
EVENT_COUNT() |
LATEST |
LATEST_INCLUDE_NULL |
For example, given this select statement:
SELECT data.wt.ti AS 'current_page_title', CONCAT_DISTINCT(data.wt.ti, ',') AS 'all_phone_page_titles' HAVING data.wt.ti like '*phones*'
Since there is no WHERE
clause, we will send out a message with every event.
The value of data.wt.ti
will be sent as 'current_page_title' on every event, if present, regardless of whether or not it matches the HAVING clause. This is because a session-level selector is not being used here.
A session-level selector of CONCAT_DISTINCT
is being used for 'all_phone_page_titles', so all distinct values from every event that match the HAVING clause will also be included on every message.
For example:
{
"meta": {
"schema_version": "3.0",
"api_version": "3.0",
"message_type": "session_update",
"stream_type": "session_all"
},
"all_phone_page_titles": "Google Phones,Samsung Phones",
"current_page_title": "SLR Cameras"
}
If no HAVING
clause is specified, every event in the session will be included when selecting session-level data.
The operators for HAVING
are basically the same as WHERE
, however you cannot use ALL or ANY in the HAVING
clause. Also, just like the WHERE
clause, Aliases and Select Criteria are not supported.
Type of Having criteria | Example | Behavior | Scope | Notes |
Equals | data.wt.ti='title'
|
An event will be included in the selected data if the parameter exists with the exact value specified. | All events in the session will be scanned to find a match |
Wild cards are not supported Case insensitive matching |
---|---|---|---|---|
Exists | Exists(data.wt.ti)
|
An event will be included in the selected data if the parameter exists, regardless of value | All events in the session will be scanned to find a match |
Wild cards are not supported Case insensitive matching |
Not Exists |
|
An event will be included in the selected data if the parameter does not exist | All events in the session will be scanned to find a match |
Wild cards are not supported Case insensitive matching |
Not Equal | data.wt.ti!='title'
|
An event will be included in the selected data if the parameter doesn't exist, or doesn't have the exact value specified. |
All events in the session will be scanned to find a match |
Wild cards are not supported Case insensitive matching |
Like | data.wt.ti LIKE 't*'
|
An event will be included in the selected data if the parameter exists, and matches the value specified. | All events in the session will be scanned to find a match | Case insensitive matching |
Not Like |
|
An event will be included in the selected data if the parameter doesn't exist, or it does not match the value specified. | All events in the session will be scanned to find a match | Case insensitive matching |
Less Than | data.wt.tx_u < 3
|
An event will be included in the selected data if the parameter exists, and matches the value specified. | All events in the session will be scanned to find a match | Numeric values only |
Less Than or Equal To | data.wt.tx_u <= 3
|
An event will be included in the selected data if the parameter exists, and matches the value specified. | All events in the session will be scanned to find a match | Numeric values only |
Greater Than | data.wt.tx_u > 3
|
An event will be included in the selected data if the parameter exists, and matches the value specified. | All events in the session will be scanned to find a match | Numeric values only |
Greater Than or Equal To | data.wt.tx_u >= 3
|
An event will be included in the selected data if the parameter exists, and matches the value specified. | All events in the session will be scanned to find a match | Numeric values only |
OR |
|
An event will be included in the selected data if the parameter exists, and matches the value specified for either clause | All events in the session will be scanned to find a match | |
AND |
|
An event will be included in the selected data if the parameter exists, and matches the value specified for all clauses | All events in the session will be scanned to find a match |