PHP, MongoDB and power monitoring with MapReduce or Aggregation Framework

Question

I'm currently logging power measurements (watts) at varying intervals (between 1 and 5 seconds) to my MongoDB 2.2 (db -> monitoring -> kWh). The data within my collection is packaged as below.

{
   "_id":ObjectId("5060c134f05e888e03000001"),
   "reading":"power",
   "watts":"549.",
   "datetime":1348518196
}

I need to aggregate the information to an hourly basis, therefore sum all the watts from the start of an hour to the end and divide by the number of readings during the hour. I need to be able to push this result to a new collection within MongoDB by means of PHP. This could of course be run as a cron job, but is there is a mechanism to perform this as part of an insert?

The datetime field is a Unix timestamp.

score 1 · Accepted Answer · edited May 23 '17 at 10:24

You could do this easily with MR with an emit function like:

function(){
    emit(hour, {count: this.watts});
}

The hour variable there would be a normalised hour (as shown in my PHP snippet below) of the time that the rows were processed, using a method like that shown here: convert date to timestamp in javascript? or you can just pass in an external variable from the PHP cronjob of the mktime().

Doing an extremely simple reduce to just sum them up and doing a out of merge to the main hourly aggregation collection running from a PHP cronjob calling the MR.

However this seems kinda overkill for this sort of thing and personally I would do this straight in PHP like:

$cursor = $db->collection->find(array('datetime' => array('$gte' => time()-3600)));
$sumWatts = 0;
foreach($cursor as $_id => $row){
   $sumWatts += $row['watts'];
}
$db->otherCollection->insert(array('sum' => $sumWatts, 'hour' => mktime(date('H'), 0, 0))));

That will normalise the hour of all rows to the full hour of when it was processed.

Though you could also use an the aggregation framework using the $sum operator to complete this, reading it into PHP and then writing it out.

However I think for this particular type of aggregation straight PHP is probably simpler and easier and possibly even faster for this single field over this time span.

If you were aggregating huge amounts and many fields then I would say do this in a MR which can run overtime etc.

PHP, MongoDB and power monitoring with MapReduce or Aggregation Framework

1 Answers1