Sample Code and Applications
About the ClickStream Analysis Demo
The ClickStream Analysis Demo illustrates a solution to a webapp scenario: the ability to determine in near real time when a user's session has ended, within a configurable threshold, based on their click activity.
The demo consists of one incoming stream, where each tuple represents a user's click even in the webapp. There is a timeout threshold, in which user click activity is checked. The start and end of a user's session is detected; additionally, a returning user's session is detected. There are two output streams: one stream has user session begin events and the other stream has the session end events. The output client displays each user's session's beginning and ending time.
The StreamBase operators are laid out as shown below.
+ Click image to enlarge.
Stream SQL
StreamBase Studio can generate StreamSQL code for your application as described in the StreamSQL Reference Guide. With the ClickStream Analysis demo, we provide a more optimized version of the StreamSQL code, as shown below.
-- Input stream that application receives updates on.
CREATE INPUT STREAM ClickStream ( userID string(16),timestamp timestamp );
-- Table to maintain user state.
CREATE TABLE UserActivity (
userID string(16) PRIMARY KEY,
lastActivity timestamp );
-- Create timeout threshold by adding it the ClickStream.
CREATE STREAM ThresholdStream AS
SELECT userID,seconds(15) AS timeoutThreshold, now() as timestamp
FROM ClickStream;
-- Read the user's last activity.
CREATE STREAM ReadUserActivity AS
SELECT ThresholdStream.userID AS userID,
ThresholdStream.timeoutThreshold AS timeoutThreshold,
ThresholdStream.timestamp AS timestamp,
UserActivity.lastActivity AS lastActivity
FROM ThresholdStream OUTER JOIN UserActivity
WHERE ThresholdStream.userID == UserActivity.userID;
-- Delete all activity less than a threshold from the current timestamp.
CREATE STREAM UserSessionEnding AS
DELETE FROM UserActivity USING
ThresholdStream
WHERE UserActivity.lastActivity <= timestamp-timeoutThreshold
RETURNING timestamp AS timeEventTriggered,
UserActivity.userID AS userID,
UserActivity.lastActivity AS lastActivity;
-- Output Stream that only includes non null activity and userIDs.
CREATE OUTPUT STREAM UserSessionEnded AS
SELECT * FROM UserSessionEnding
WHERE notnull(UserSessionEnding.userID)
&& notnull(UserSessionEnding.lastActivity);
--Update the specific userID's last activity with a new timestamp.
INSERT INTO UserActivity (userID, lastActivity)
SELECT userID,timestamp
FROM ThresholdStream
ON DUPLICATE KEY
UPDATE lastActivity = timestamp;
-- Create rules for detecting a new session (implemented as a filter query).
CREATE STREAM DetectNewSession AS
SELECT * FROM ReadUserActivity
WHERE isnull(lastActivity) ||
(timestamp-lastActivity) > timeoutThreshold;
-- Cleanup output schema by explicitly outputing three fields.
CREATE OUTPUT STREAM UserSessionBegins AS
SELECT userID,timestamp,timeoutThreshold FROM DetectNewSession;
Downloading, Installing, and Running the ClickStream Analysis
The ClickStream Analysis Demo is included in both the Developer and Enterprise Editions of StreamBase Studio. Download and Install StreamBase Developer Edition.
After installing StreamBase Studio, run the demo with these steps:
- Open the Demo Perspective in one of these ways:
- On the Welcome Page, click Studio Plus Demos
- From any other Perspective, choose Window > Open Perspective > Demo
- In the Demo Chooser, choose eBusiness - ClickStream Analysis and click the Load Demo button.
« More Sample Code and Applications.