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.

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

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:

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.

[Add keywords if applicable. Keywords are used for Flare SEO and will not be displayed in the output. Separate keywords with commas. Example: campaigns, campaign setup, setting up campaigns]