finding last 5 minutes page views

1
votes

I have a kafka topic by the name of page_views and stream by the name of pageviews. Now I want to calculate last 5 minutes page viewed.I am using ksql. Tried with 

SELECT after->pageview_id FROM pageviews WHERE after->pageview_id >= NOW() - INTERVAL 10 MINUTE;

and 

SELECT AFTER ->pageview_id FROM pageviews WHERE after->pageview_id >= sysdate - 5/(24*60);

but not working. This is nested avro schema.

1
apache-kafkaksqldb

1 Answers

3
votes

You can use HOPPING window to emulate sliding window in KSQL. For a hopping window you should specify the window size which in this case is 5 minutes and an advance value which indicate how the window moves, i.e. slides (for example, every 1 second). So you can write a query like this:

CREATE STREAM foo AS SELECT after->pageview_id AS pv_id FROM pageviews;

CREATE TABLE bar AS SELECT pv_id, COUNT(pv_id) FROM foo WINDOW HOPPING (SIZE 5 MINUTES, ADVANCE BY 1 SECOND) GROUP BY pv_id;

For more information on HOPPING WINDOW refer to the following pages: