Create Clickstream-new table for latest values
The click-stream table should have at least the following columns:
- ID of a video (with the necessary identifiers that make the video completely identifiable)
- number of unique watchers
- number of completed
- (maybe in json format, or better: a list) the number of watchers per heartbeat (5 sec interval of the video)
Optimize materialized view
The materialized views calculate final values to show in the dashboard. This means that every time they update, they go through all the data and do the same calculations all the time, with the added operations that consider new data.
The idea is to change the way the update process works so that:
it fills the materialized view with non-final data it considers only the newest data
- doesn't use a materialized view anymore
- processes only the newest data from the click stream table
- The outcome is a table with as many rows as videos, with the final information of the video analytics in the columns
Also, to change the way materialized views are queried so that the new queries take into consideration that the data is partial and has to agglutinate the values in order to obtain final results.
The one that creates more conflict is the one of the click-stream events. Updating this view consumes a lot of space and time.
Considering that there will be more and more click-stream data, this is a risky one.