Reporting against a CouchDB database

[Matthew Buckett <ma...@it.ox.ac.uk>]

We're using CouchDB as a store for a mobile app that is collecting
some user entered data. Each user has their own database in CouchDB
and we're wanting todo some general reporting on the entered data. Our
current plan is:

- replicate all the users data into a central database, each users DB
has a replication function to add it to the one central DB.
- create some views on the central DB which enable the reporting.
- Create some HTML/JS reporting pages which access the central DB.

- Has anyone else done some reporting against CouchDB?
- Did you use continuous replication or just batch replication?
- Should we just export all the data and import it into a general
reporting tool?

Thanks.

-- 
  Matthew Buckett, VLE Developer, IT Services, University of Oxford

Re: Reporting against a CouchDB database

[Mike <mi...@wolman.co.uk>]

Hi

We have a number of client couchdbs which we query as one.

I initially wrote: https://github.com/sysadminmike/couch-to-postgres

To help get ad hoc reports from our couch data using postgres.

The above utility will stream changes for each couchdb into its own 
postgres table which can then be queried and collated together using 
standard sql UNION.

Mike.


On 04/03/2016 11:54, Matthew Buckett wrote:
> We're using CouchDB as a store for a mobile app that is collecting
> some user entered data. Each user has their own database in CouchDB
> and we're wanting todo some general reporting on the entered data. Our
> current plan is:
>
> - replicate all the users data into a central database, each users DB
> has a replication function to add it to the one central DB.
> - create some views on the central DB which enable the reporting.
> - Create some HTML/JS reporting pages which access the central DB.
>
> - Has anyone else done some reporting against CouchDB?
> - Did you use continuous replication or just batch replication?
> - Should we just export all the data and import it into a general
> reporting tool?
>
> Thanks.
>

Re: Reporting against a CouchDB database

[Mike <mi...@wolman.co.uk>]

Hi,

It is not possible to query with couch in the same way you can with 
postgres.  For example you can query the documents with LIKE 
'%sometext%' which is very hard to do / takes a long time with temp 
views for one off queries.

Also with postgres it is then possible to JOIN documents together - very 
simple example:

WITH companies AS (
  SELECT id,
         doc ->> 'name' AS company_name,
    FROM couchdata WHERE doc @> '{"type": "company"}'
),
contacts AS (
    SELECT id,
           doc ->> 'company_id' AS company_id
           doc ->> 'name' AS contact_name
    FROM couchdata WHERE doc @> '{"type": "contact"}'
)

SELECT company_name,  contact_name FROM companies
LEFT JOIN contacts ON (companies.id=contacts.company_id)
WHERE contact_name ILIKE '%john%'
ORDER BY company_name, contact_name

This would be very hard to do with map/reduce for one off 
searches/queries with couch and take a while to build the views when 
number of docs gets quite large.

We have used this to replaced Elastic Search with Postgres now for our 
apps search needs and are using far less memory/resources compared to ES 
and getting rid of java from our main stack is a nice side bonus.

Take a look at:  https://github.com/sysadminmike/yadms  for some more 
examples of how you can use postgres to query the couch data as that 
should give you some idea how simple it is to use postgres on the couch 
data compared to doing within couch.

Mike.


On 07/03/2016 16:49, Matthew Buckett wrote:
> On 4 March 2016 at 12:03, Mike <mi...@wolman.co.uk> wrote:
>> We have a number of client couchdbs which we query as one.
>>
>> I initially wrote: https://github.com/sysadminmike/couch-to-postgres
>>
>> To help get ad hoc reports from our couch data using postgres.
> Thanks looks interesting. What were the reasons for doing the
> reporting in postgres rather than in couchdb?
>

Re: Reporting against a CouchDB database

[Matthew Buckett <ma...@it.ox.ac.uk>]

On 4 March 2016 at 12:03, Mike <mi...@wolman.co.uk> wrote:
>
> We have a number of client couchdbs which we query as one.
>
> I initially wrote: https://github.com/sysadminmike/couch-to-postgres
>
> To help get ad hoc reports from our couch data using postgres.

Thanks looks interesting. What were the reasons for doing the
reporting in postgres rather than in couchdb?

-- 
  Matthew Buckett, VLE Developer, IT Services, University of Oxford