Reporting against a CouchDB database

[Matthew Buckett]

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

[Mike]

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.

[Matthew Buckett]

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?

[Mike]

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.