Recognizing patterns in a sequence of rows has been a capability that was widely desired, but not really possible with SQL until now. There were many workarounds, but these were difficult to write, hard to understand, and inefficient to execute.
With Oracle Database 12c you can use the MATCH_RECOGNIZE clause to perform pattern matching in SQL to do the following:
This tutorial is designed to help show you how you can run sessionization analysis on application logs, web logs, etc. that are in JSON format.
For this tutorial we are going to define a session as a sequence of one or more events where the inter-timestamp gap is less than 10 minutes between events.
Using our simplified JSON table of click data, which will create in the next step, we will create a sessionization data set which tracks each session, the duration of the session and the number of clicks/events using the new 12c MATCH_RECOGNIZE feature.
First step is to create the JSON table that will hold our data from our session log file. The log will provide the details of the time and account/user id.
Oracle Database 12c supports storing JSON documents inside the database. Use following code to create a table to store the transaction log which is in JSON format
CREATE TABLE json_sessionization
(session_doc CLOB,
CONSTRAINT "VALID_JSON" CHECK (session_doc IS JSON) ENABLE
);Next step is to add some data to our JSON table using the normal JSON notation of key-value pairs.
INSERT INTO json_sessionization VALUES ('{"time_id":"1","user_id":"Mary"}');
INSERT INTO json_sessionization VALUES ('{"time_id":"2","user_id":"Sam"}');
INSERT INTO json_sessionization VALUES ('{"time_id":"11","user_id":"Mary"}');
INSERT INTO json_sessionization VALUES ('{"time_id":"12","user_id":"Sam"}');
INSERT INTO json_sessionization VALUES ('{"time_id":"22","user_id":"Sam"}');
INSERT INTO json_sessionization VALUES ('{"time_id":"23","user_id":"Mary"}');
INSERT INTO json_sessionization VALUES ('{"time_id":"32","user_id":"Sam"}');
INSERT INTO json_sessionization VALUES ('{"time_id":"34","user_id":"Mary"}');
INSERT INTO json_sessionization VALUES ('{"time_id":"43","user_id":"Sam"}');
INSERT INTO json_sessionization VALUES ('{"time_id":"44","user_id":"Mary"}');
INSERT INTO json_sessionization VALUES ('{"time_id":"47","user_id":"Sam"}');
INSERT INTO json_sessionization VALUES ('{"time_id":"48","user_id":"Sam"}');
INSERT INTO json_sessionization VALUES ('{"time_id":"53","user_id":"Mary"}');
INSERT INTO json_sessionization VALUES ('{"time_id":"59","user_id":"Sam"}');
INSERT INTO json_sessionization VALUES ('{"time_id":"60","user_id":"Sam"}');
INSERT INTO json_sessionization VALUES ('{"time_id":"63","user_id":"Mary"}');
INSERT INTO json_sessionization VALUES ('{"time_id":"68","user_id":"Sam"}');
COMMIT;Running our first sessionization report using only the main keywords within MATCH_RECOGNIZE clause
The keywords that we are going to use are:
ALL ROWS PER MATCH
You will sometimes want summary data about the matches and other times need details. For this report we are going to return a detailed report.PATTERN
The PATTERN clause lets you define which pattern variables must be matched, the sequence in which they must be matched, and the quantity of rows which must be matched. The PATTERN clause specifies a regular expression for the match search.DEFINE
The PATTERN clause depends on pattern variables, therefore, you must have a clause to define these variables. They are specified in the DEFINE clause. DEFINE is a required clause, used to specify the conditions that a row must meet to be mapped to a specific pattern variable.
So let's write our first MATCH_RECOGNIZE statement based on our business requirements:
SELECT
time_id,
userid
FROM (SELECT
TO_NUMBER(j.session_doc.time_id) as time_id,
j.session_doc.user_id as userid
FROM json_sessionization j)
MATCH_RECOGNIZE(
ALL ROWS PER MATCH
PATTERN (b s+)
DEFINE
s as (time_id - PREV(time_id) <= 10));
As we mentioned earlier, the PATTERN defines a regular expression, which is a highly expressive way to search for patterns. PATTERN (b s+) says that the pattern we are searching for has two pattern variables: b and s. The star sign (*) after s means that zero or more rows must be mapped to confirm a match.
The plus sign (+) is part of a library of quantifiers that can be used to describe your pattern. We will explore these in a separate tutorial
DEFINE gives us the conditions that must be met for a row to map to your row pattern variables b and s. Because there is no condition for b, any row can be mapped to b. Why have a pattern variable with no condition? You use it as a starting point for testing for matches. S takes advantage of the PREV() function, which lets them compare the timestamp in the current row to the timestamp in the prior row. S is matched when a row has a timestamp than is within 10 minutes of the row that preceded it.
Congratulations your report runs! We have a list of events where each event is within 10 minutes of the previous event
But we don't actually know how many sessions each user logged, so how can we find out this information?
We can use the internal MATCH_NUMBER() function to return the session id for each user - assuming that a session contains clicks that are within a 10 minute interval of previous click
What does MATCH_NUMBER() actually do?
You might have a large number of matches for your pattern inside a given row partition. How do you tell these matches apart? This is done with the MATCH_NUMBER() function. Matches within a row pattern partition are numbered sequentially starting with 1 in the order they are found. This numbering starts at 1 within each row pattern partition, because there is no linked ordering across row pattern partitions.
SELECT
time_id,
userid,
session_id
FROM (SELECT
TO_NUMBER(j.session_doc.time_id) as time_id,
j.session_doc.user_id as userid
FROM json_sessionization j)
MATCH_RECOGNIZE(
MEASURES match_number() as session_id
ALL ROWS PER MATCH
PATTERN (b s+)
DEFINE
s as (time_id - PREV(time_id) <= 10)
);
What you will notice is that each row has a session id of 1 which means that we are only finding one instance of our pattern within our data set.
Obviously this is not correct because just looking at the raw data we can see that Mary has logged more than one session
SELECT
TO_NUMBER(j.session_doc.time_id) as time_id,
j.session_doc.user_id as user_id
FROM json_sessionization j
WHERE j.session_doc.user_id = 'Mary'
What we need to do is use the PARTITION BY and ORDER BY key phrases to correctly divide up our data set by each user and then within each user make sure the data is ordered by our timestamp so we can determine if the clicks recorded in our log by each user are within 10 minutes of their previous click.
We need to expand our MATCH_RECOGNIZE() clause as shown here:
SELECT
time_id,
userid,
session_id
FROM (SELECT
TO_NUMBER(j.session_doc.time_id) as time_id,
j.session_doc.user_id as userid
FROM json_sessionization j)
MATCH_RECOGNIZE(
PARTITION BY userid ORDER BY time_id
MEASURES match_number() as session_id
ALL ROWS PER MATCH
PATTERN (b s+)
DEFINE
s as (time_id - PREV(time_id) <=10)
);
Now you should see that Mary has logged 3 sessions and Sam has logged three sessions. Which means that have now correctly specified our MATCH_RECOGNIZE clause to create our sessionization report.
After we have found a match (and this actually includes empty matches as well!) we need to decide where to start searching for the next match. This is controlled by the AFTER MATCH SKIP clause. There are five possible options for controlling the start point and the default for the clause is AFTER MATCH SKIP PAST LAST ROW.
In this case we are going to use the default.
SELECT
time_id,
userid,
session_id
FROM (SELECT
TO_NUMBER(j.session_doc.time_id) as time_id,
j.session_doc.user_id as userid
FROM json_sessionization j)
MATCH_RECOGNIZE(
PARTITION BY userid ORDER BY time_id
MEASURES match_number() as session_id
ALL ROWS PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (b s+)
DEFINE
s as (time_id - PREV(time_id) <=10)
);
Note the position of the SKIP TO syntax within the MATCH_RECOGNIZE code - it comes just before the PATTERN clause
The results from the above code are exactly the same as the results from the previous example which indicates that although the previous example did not include a SKIP TO clause the default processing was implemented.
The next stage is to count the number of events within each session and calculate the duration of each session
How do we do that? We can use some of the other built-in measures such as FIRST() and LAST() to extract values from our resultset and we can calculate new values such as the duration of a session.
In our code we will add some new measures
SELECT
time_id,
userid,
session_id,
no_of_events,
start_time,
end_time,
session_duration
FROM (SELECT
TO_NUMBER(j.session_doc.time_id) as time_id,
j.session_doc.user_id as userid
FROM json_sessionization j)
MATCH_RECOGNIZE(
PARTITION BY userid ORDER BY time_id
MEASURES match_number() as session_id,
COUNT(*) as no_of_events,
FIRST(b.time_id) start_time,
LAST(s.time_id) end_time,
LAST(s.time_id) - FIRST(b.time_id) session_duration
ALL ROWS PER MATCH
PATTERN (b s+)
DEFINE
s as (time_id - PREV(time_id) <= 10)
);
This is looking good but it would it would be better if we could create a summary report
What we really want is a report that shows one line for each session, shows the number of events, the start time and the session duration.
To return a summary report we only need to change one line of code!
We just need to change the output type from ALL ROWS PER MATCH to ONE ROW PER MATCH and remove the reference to tstamp within the SELECT clause.
SELECT
userid,
session_id,
no_of_events,
start_time,
end_time,
session_duration
FROM (SELECT
TO_NUMBER(j.session_doc.time_id) as time_id,
j.session_doc.user_id as userid
FROM json_sessionization j)
MATCH_RECOGNIZE(
PARTITION BY userid ORDER BY time_id
MEASURES match_number() as session_id,
COUNT(*) as no_of_events,
FIRST(b.time_id) start_time,
LAST(s.time_id) end_time,
LAST(s.time_id) - FIRST(b.time_id) session_duration
ONE ROW PER MATCH
PATTERN (b s+)
DEFINE
s as (time_id - PREV(time_id) <= 10)
);
...and there is our finished sessionization report.