This topic describes how to use the session window function in Realtime Compute.

Description

A session window groups elements by session activity. Unlike tumbling and sliding windows, session windows do not overlap and are not fixed in size. If a session window does not receive elements within a certain period of time, the session is disconnected and the window is closed.

A session window is configured by using a gap, which defines the length of an inactive period. For example, a data stream that represents mouse click activities may include highly clustered mouse click events, separated by inactive periods. The data that arrives after a specified gap is assigned to a new window.

The following figure shows a session window. Different keys form different windows due to data distribution differences.

Syntax

The SESSION function is used to define a session window in a GROUP BY clause. 
SESSION(<time-attr>, <gap-interval>)
<gap-interval>: INTERVAL 'string' timeUnit
Note The <time-attr> parameter must be a valid time attribute in a data stream to specify whether it is the processing time or event time. For more information about how to define Time attributes and Watermark, see Window function overview.

Window identifier functions

A window identifier function specifies the start or end time of a window, or the window time attribute for aggregation of lower-level windows.

Window identifier function Return type Description
SESSION_START (<time-attr>, <gap-interval>) TIMESTAMP Returns the start time (including the boundary value) of the window. For example, if the window is [00:10, 00:15), 00:10 is returned. This is the first time recorded in the session window.
SESSION_END (<time-attr>, <gap-interval>) TIMESTAMP Returns the end time (including the boundary value) of the window. For example, if the window is [00:00, 00:15), 00:15 is returned. This is the last time recorded in the session window plus <gap-interval.
SESSION_ROWTIME (<time-attr>, <gap-interval>) TIMESTAMP (rowtime-attr) Returns the end time (excluding the boundary value) of the window. For example, if the window is [00:00, 00:15), 00:14:59.999 is returned. The return value is an event time attribute, based on which time type operations such as window cascading can be performed. The function applies only to windows based on the event time.
SESSION_PROCTIME (<time-attr>, <gap-interval>) TIMESTAMP (rowtime-attr) Returns the end time (excluding the boundary value) of the window. For example, if the window is [00:00, 00:15), 00:14:59.999 is returned. The return value is a processing time attribute, based on which time type operations such as window cascading can be performed. The function applies only to windows based on the processing time.

Example

The following example describes how to compute the number of clicks per user during each active session. The session timeout interval is 30 seconds.
  • Test data
    username (VARCHAR) click_url (VARCHAR) ts (TIMESTAMP)
    Jark http://taobao.com/xxx 2017-10-10 10:00:00.0
    Jark http://taobao.com/xxx 2017-10-10 10:00:10.0
    Jark http://taobao.com/xxx 2017-10-10 10:00:49.0
    Jark http://taobao.com/xxx 2017-10-10 10:01:05.0
    Jark http://taobao.com/xxx 2017-10-10 10:01:58.0
    Timo http://taobao.com/xxx 2017-10-10 10:02:10.0
  • Test statements 
    CREATE TABLE user_clicks(
username varchar,
click_url varchar,
ts timeStamp,
WATERMARK wk FOR ts as withOffset(ts, 2000) -- Define a watermark for rowtime.
) with (
type='datahub',
...
);

CREATE TABLE session_output(
window_start TIMESTAMP,
window_end TIMESTAMP,
username VARCHAR,
clicks BIGINT
) with (
type='rds',
...
);

INSERT INTO session_output
SELECT
SESSION_START(ts, INTERVAL '30' SECOND),
SESSION_END(ts, INTERVAL '30' SECOND),
username,
COUNT(click_url)
FROM user_clicks
GROUP BY SESSION(ts, INTERVAL '30' SECOND), username
  • Test results
    window_start (TIMESTAMP) window_end (TIMESTAMP) username (VARCHAR) clicks (BIGINT)
    2017-10-10 10:00:00.0 2017-10-10 10:00:40.0 Jark 2
    2017-10-10 10:00:49.0 2017-10-10 10:01:35.0 Jark 2
    2017-10-10 10:01:58.0 2017-10-10 10:02:28.0 Jark 1
    2017-10-10 10:02:10.0 2017-10-10 10:02:40.0 Timo 1