couchdb-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Cory Zue <>
Subject Re: How best to workaround time filtering + aggregation
Date Thu, 16 Jun 2011 22:13:51 GMT
Hi Aroj,

Welcome to my least favorite thing in couch!

If you anticipate high volumes of data I'd rule out (1). Likewise list
functions (2), I don't think will help you unless I misunderstand how
they work. You're still going to have to load all that data into the
list function which I doubt saves you much overhead as an alternative
to (1).

3, 4, and 5 are all solid options. The right answer for you will
likely be a tradeoff between how much you can tailor your use case to
fit in a relatively simple view (5), versus the desire to do more
complex and freeform querying in the future (3, 4). As for the choice
between SQL and Lucene, it just depends how comfortable you are with
the two technologies. Lucene will likely be less work to setup. SQL
will likely be easier to query.

[plug] If you end up going with option 3 you may find the blog post I
just wrote about almost this exact use case useful:


Cory L. Zue
Dimagi, Inc

On Thu, Jun 16, 2011 at 4:38 PM, Aroj George <> wrote:
> Hi All,
> We have this requirement where we want to do aggregations over a set of time
> series data after we have filtered it by time, in response to the time range
> values input by a user.
> Size: 10 million documents.
> Aggregations to be applied: Sum, Count, Average, Max, Min, Latest etc..
> Grouped By: Clinic Id,Field Name
> *
> Doc Structure:*
> { clinic id: 10, data :  { beds : 10, meds: 20, doctors : 30 }, timestamp :
> T1 }
> *View Structure:*
> key: timestamp, clinic id, field name ( T1, 10, beds )
> value: field value (10)
> The above view will allow us to filter by a time range, but not group by
> clinic id + field name, because of the timestamp being the first part of the
> key.
> So the only option it seems is to do the time filtering in Couch and do the
> aggregations on the filtered set outside of Couch.
> *Options:*
> 1.  *Python*
> Load the filtered set in the client, (python) and use pythons's built in
> reduce functions like sum, count etc.
> Concerns: Loading a huge set of data into the python client may not be
> efficient.
> 2. *List functions*
> Use the Couch view to do time filter + do the aggregation in a list
> function.
> The key advantage here could be that everything is being done in Couch. This
> not what a list function is meant for,
> but given that aggregation via map/reduce is not possible in this case,
> could this be a reasonable solution?
> Concerns:
> Will the javascript list function be slower than even doing the aggregation
> in python?
> Can I switch to an erlang list function to speed things up?
> 3. *SQL*
> Load the data into a SQL table at regular intervals or using the _changes
> view, with the below columns,
> and use SQL's group by and sum,count functions to return the query results.
> Entity ID, Field Name, Field Value, Timestamp
> Select sum(field value) group by entity id, field name where timestamp > T1
> and timestamp < T2
> 4. *Lucene*
> I am not sure, but can something like Elastic Search (
> or Solr help here?
> 5. *Multiple Queries*
> Another option I could think of is, to change the view key to
> *[clinic id, field name, timestamp]* ie add the timestamp at the end.
> Now do a query for each clinic and field, like start_key = [clinic id, field
> name, T1] and end_key = [clinic id, field name, T2] and group = 2.
> This will give me the result, but I will have to do a query for (number of
> clinics * number of fields) times.
> Which again will be too inefficient because of that many HTTP calls. Unless
> I can make all multiple such queries in a single HTTP GET?
> Can I optimise this somehow?
> What do you guys think, which of the above is the best option? How best
> would you solve this use case?
> The question essentially being, what's the most efficient  and scalable way
> to perform real time aggregations on a set of data with time as filter
> criteria?
> The primary impact of the time filter being that we can't really do the
> aggregations using the map reduce views.
> Rgds,
> Aroj

View raw message