Big Data Aggregation Strategy
We collect data in raw form and then display them in aggregations. That’s the most basic form of data analytics. Writing data aggregation queries should be a trivial task. The catch is when data got big, we can’t do real-time aggregations on raw data anymore. And the solution is to do pre-aggregation beforehand.
This post sketches out a simple plan that may suit many use cases that are in need of pre-aggregations.
1. Examination worker
This worker examines the raw data for objects that need to run the
aggregations on. This should be a cron job that triggers after a fixed
time range, based on your requirements for aggregation freshness. The
examination worker will create a
queued task in the task queue table
for the aggregation worker to work on.
A not-so-minor issue here is raw data may arrive after or before the examining time. So once in a while, we need to trigger a back-fill job that will do the data missing hunt.
2. Aggregation worker
This worker pulls the tasks from the task queue table, changes the
started, and then works on the real aggregation
job. After done with it, change the
completed. The status
change is a kind of optimistic lock mechanism that we will ensure by
setting the where condition when updating the value. Something like this:
UPDATE task_queue SET status = 'completed' WHERE status = 'started'
For aggregation data, new aggregated rows will replace the olds.
We handle the switch by using 2 extra columns:
The invalidation will look like this:
UPDATE agg_table SET valid = CASE WHEN aggregation_time < $1 THEN FALSE ELSE TRUE END
3. Clean-up worker
This worker will work like a vacuum, cleaning out completed tasks and invalidated aggregation rows.