# Visualizing CHT data using Superset, couch2pg and Postgres

**URL:** <https://forum.communityhealthtoolkit.org/t/visualizing-cht-data-using-superset-couch2pg-and-postgres/1783>\
**Category:** Technical Support\
**Created:** [March 17, 2022, 6:20pm UTC](https://forum.communityhealthtoolkit.org/t/visualizing-cht-data-using-superset-couch2pg-and-postgres/1783 "2022-03-17T18:20:48Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![cliff](https://communityhealthtoolkit.b-cdn.net/user_avatar/forum.communityhealthtoolkit.org/cliff/32/1638_2.png) [@cliff](https://forum.communityhealthtoolkit.org/u/cliff)\
**Post date:** [March 17, 2022, 6:20pm UTC](https://forum.communityhealthtoolkit.org/t/visualizing-cht-data-using-superset-couch2pg-and-postgres/1783/1 "2022-03-17T18:20:48Z")

</div>

Hello community

We have been able to use cht-couch2pg docker-compose file to replicate remote CHT data to postgres and connected superset postgres though the data was exported from couchdb as a json file and imported into Postgres as a one column table with all the data still formatted with JSON.

 ![image (1)](https://communityhealthtoolkit.b-cdn.net/uploads/default/original/2X/c/cc971d7974a1c395985e9679d149f0dbc1f8d41c.png)  
 ![Screenshot from 2022-03-17 21-14-24](https://communityhealthtoolkit.b-cdn.net/uploads/default/original/2X/c/c7b6aa477e1543d8d0bdf192b79bf074f7ce946d.png)

With data in this format its hard to do some metrics ,  
Is there a way to change this data when it gets into postgres in a relational table like form with columns and rows ?  
Thanks  
cc @mrjones @michael @diana @gareth

---

<div class="post-metadata">

**Author:** ![samuel](https://communityhealthtoolkit.b-cdn.net/user_avatar/forum.communityhealthtoolkit.org/samuel/32/43_2.png) [@samuel](https://forum.communityhealthtoolkit.org/u/samuel)\
**Post date:** [March 17, 2022, 7:08pm UTC](https://forum.communityhealthtoolkit.org/t/visualizing-cht-data-using-superset-couch2pg-and-postgres/1783/2 "2022-03-17T19:08:39Z")

</div>

Hi @cliff. You can [create views](https://www.postgresql.org/docs/14/sql-createview.html) and [materialized views](https://www.postgresql.org/docs/14/sql-creatematerializedview.html) to help you structure the data in a more relational way. Below is an example of how to create a materialized view that gets pregnancy registrations:

```auto
CREATE MATERIALIZED VIEW useview_pregnancy AS 
  SELECT 
    doc ->> '_id' AS uuid, 
    doc ->> 'form' AS form, 
    to_timestamp((NULLIF(doc ->> 'reported_date', '')::bigint / 1000)::double precision) AS reported, 
    COALESCE((doc #>> '{fields, patient_uuid}'), '') AS patient_uuid, 
    COALESCE((doc #>> '{fields, inputs, source}'), (doc #>> '{fields, source}'), '') AS source, 
    COALESCE((doc #>> '{fields, inputs, source_id}'), (doc #>> '{fields, source_id}'), '') AS source_id, 
    doc #>> '{fields, patient_age_in_years}' AS patient_age_in_years,
    doc #>> '{fields, lmp_date}' AS lmp_date, 
    doc #>> '{fields, edd_8601}' AS edd, 
    doc #>> '{fields, weeks_since_lmp}' AS weeks_since_lmp
  FROM 
    couchdb
    WHERE
      doc ->> 'form' = 'pregnancy';

```

---

<div class="post-metadata">

**Author:** ![cliff](https://communityhealthtoolkit.b-cdn.net/user_avatar/forum.communityhealthtoolkit.org/cliff/32/1638_2.png) [@cliff](https://forum.communityhealthtoolkit.org/u/cliff)\
**Post date:** [March 17, 2022, 7:10pm UTC](https://forum.communityhealthtoolkit.org/t/visualizing-cht-data-using-superset-couch2pg-and-postgres/1783/3 "2022-03-17T19:10:42Z")

</div>

thanks @samuel ,  
Gonna explore this 👍

---

<div class="post-metadata">

**Author:** ![michael](https://communityhealthtoolkit.b-cdn.net/user_avatar/forum.communityhealthtoolkit.org/michael/32/319_2.png) [@michael](https://forum.communityhealthtoolkit.org/u/michael)\
**Post date:** [March 18, 2022, 1:10pm UTC](https://forum.communityhealthtoolkit.org/t/visualizing-cht-data-using-superset-couch2pg-and-postgres/1783/4 "2022-03-18T13:10:52Z")

</div>

@cliff a couple other tips:

- Every time `couch2pg` it runs, it calls the [refresh\_matviews()](https://github.com/medic/cht-couch2pg/blob/45ea783908928cd025ef22ac5870920c0817a04d/libs/analytics/migrations/201606200952.do.2318-prepareContacts.sql#L79-L98) function so data in materialized views is kept up to date
- Depending on how much data you have and how performant the query is that defines your materialized view, refreshing the matviews can take some time
- Materialized views are [_not refreshed in any particular order_](https://forum.communityhealthtoolkit.org/t/are-materialized-views-with-dependencies-refreshed-in-order-by-couch2pg/1459) so be mindful of dependencies _ **between** _ materialized views (ie one materialized view probably shouldn’t depend on another one)
- Since the [matviews are refreshed `CONCURRENTLY`](https://github.com/medic/cht-couch2pg/blob/45ea783908928cd025ef22ac5870920c0817a04d/libs/analytics/migrations/201606200952.do.2318-prepareContacts.sql#L93), you _MUST_ have a `UNIQUE` index defined for every materialized view.

---

<div class="post-metadata">

**Author:** ![cliff](https://communityhealthtoolkit.b-cdn.net/user_avatar/forum.communityhealthtoolkit.org/cliff/32/1638_2.png) [@cliff](https://forum.communityhealthtoolkit.org/u/cliff)\
**Post date:** [April 12, 2022, 6:28am UTC](https://forum.communityhealthtoolkit.org/t/visualizing-cht-data-using-superset-couch2pg-and-postgres/1783/5 "2022-04-12T06:28:25Z")

</div>

hello @michael @samuel  
With superset where can one create the views and materialized views from ?  
TJe SQL editor only allows `SELECT` statements

 ![Screenshot from 2022-04-12 09-11-08](https://communityhealthtoolkit.b-cdn.net/uploads/default/original/2X/3/330fd4d12251848f0d5e63bcf860ee002fe08cf4.png)

---

<div class="post-metadata">

**Author:** ![samuel](https://communityhealthtoolkit.b-cdn.net/user_avatar/forum.communityhealthtoolkit.org/samuel/32/43_2.png) [@samuel](https://forum.communityhealthtoolkit.org/u/samuel)\
**Post date:** [April 12, 2022, 11:12am UTC](https://forum.communityhealthtoolkit.org/t/visualizing-cht-data-using-superset-couch2pg-and-postgres/1783/6 "2022-04-12T11:12:24Z")

</div>

Hi @cliff.

PostgreSQL is where you have your [data transformation](https://docs.communityhealthtoolkit.org/core/overview/data-flows-for-analytics/#2-data-transformation) while Superset is where you have your [data use](https://docs.communityhealthtoolkit.org/core/overview/data-flows-for-analytics/#3-data-use). The views and materialized views would be written in PostgreSQL. The queries you write in Superset would only read the data from the views and materialized views.

A rough idea of the steps you would follow after setting up cht-couch2pg:

- Create materialized views on PostgreSQL
- Create views on PostgreSQL that read from materialized views and any other tables or functions
- Configure your database connector on Superset
- Write SQL SELECT statements on Superset that will retrieve the data that will feed into your dashboards

I hope this helps.

---

<div class="post-metadata">

**Author:** ![michael](https://communityhealthtoolkit.b-cdn.net/user_avatar/forum.communityhealthtoolkit.org/michael/32/319_2.png) [@michael](https://forum.communityhealthtoolkit.org/u/michael)\
**Post date:** [April 12, 2022, 1:23pm UTC](https://forum.communityhealthtoolkit.org/t/visualizing-cht-data-using-superset-couch2pg-and-postgres/1783/7 "2022-04-12T13:23:21Z")

</div>

Some more tips and tricks:

1. Make sure your dashboard mockups are complete before starting to write your queries
2. Make sure you understand user access needs for each dashboard
3. Make sure the indicator definitions/calculations are complete & understood before starting
4. Plan your query architecture approach to minimize duplication
5. To the extent possible, do calculations in PostgreSQL, NOT Superset
6. Queries should always explicitly include field names in the SELECT (ie don’t use SELECT \*)
7. Never write the same calculation twice
8. Use parameterized functions where useful
9. “Shape” your query results in a way that will make it easier to work with in Superset
10. Be mindful of query performance and the amount of data it will return to Superset

---

<div class="post-metadata">

**Author:** ![cliff](https://communityhealthtoolkit.b-cdn.net/user_avatar/forum.communityhealthtoolkit.org/cliff/32/1638_2.png) [@cliff](https://forum.communityhealthtoolkit.org/u/cliff)\
**Post date:** [May 11, 2022, 1:27pm UTC](https://forum.communityhealthtoolkit.org/t/visualizing-cht-data-using-superset-couch2pg-and-postgres/1783/8 "2022-05-11T13:27:44Z")

</div>

helo @samuel @michael

i created a materialized view of data from a form with

```auto
CREATE MATERIALIZED VIEW care_form_view AS 
  SELECT 
    doc ->> '_id' AS uuid, 
    doc ->> 'form' AS form, 
    doc #>> '{fields, care, medical}' AS medical,
    doc #>> '{fields, care, adherence}' AS adherence,
    doc #>> '{fields, care, mental_health}' AS mental_health,
    doc #>> '{fields, care, support_system}' AS support_system,
    doc #>> '{fields, care, transportation}' AS transportation,
    doc #>> '{fields, care, living_situation}' AS living_situation,
    doc #>> '{fields, care, knowledge_disease}' AS knowledge_disease,
    doc #>> '{fields, care, communication_skills}' AS communication_skills
  FROM 
    couchdb
    WHERE
      doc ->> 'form' = 'care';

```

however on viewing the materilazed view in superset i get` Error: permission denied for materialized view care_form_view`

 ![Screenshot from 2022-05-11 16-26-32](https://communityhealthtoolkit.b-cdn.net/uploads/default/original/2X/2/2aa72b6c0a43fe3f861a79417d2e495d6b2c9ee9.png)

Wondering if you encountered this before ?  
Thanks

---

<div class="post-metadata">

**Author:** ![michael](https://communityhealthtoolkit.b-cdn.net/user_avatar/forum.communityhealthtoolkit.org/michael/32/319_2.png) [@michael](https://forum.communityhealthtoolkit.org/u/michael)\
**Post date:** [May 11, 2022, 2:28pm UTC](https://forum.communityhealthtoolkit.org/t/visualizing-cht-data-using-superset-couch2pg-and-postgres/1783/9 "2022-05-11T14:28:19Z")

</div>

Sounds like the `user` in your connection details from Superset does not have the permission to query the new view you created in PostgreSQL. Are you able to query other tables or views in PostgreSQL from Superset? If yes, I’d recommend looking in PostgreSQL to see who the `owner` is of the new view you created and compare that to the other views that you are able to query from Superset.

You might need to [REASSIGN OWNED BY](https://www.postgresql.org/docs/current/sql-reassign-owned.html) abc TO xyz

---

<div class="post-metadata">

**Author:** ![cliff](https://communityhealthtoolkit.b-cdn.net/user_avatar/forum.communityhealthtoolkit.org/cliff/32/1638_2.png) [@cliff](https://forum.communityhealthtoolkit.org/u/cliff)\
**Post date:** [May 11, 2022, 6:41pm UTC](https://forum.communityhealthtoolkit.org/t/visualizing-cht-data-using-superset-couch2pg-and-postgres/1783/10 "2022-05-11T18:41:41Z")

</div>

> [@michael](#):
>
> Sounds like the `user` in your connection details from Superset does not have the permission to query the new view you created in PostgreSQL. Are you able to query other tables or views in PostgreSQL from Superset? If yes, I’d recommend looking in PostgreSQL to see who the `owner` is of the new view you created and compare that to the other views that you are able to query from Superset.

thanks @michael

i altered the view owner to the one accepted in superset and works fine now

---

<div class="post-metadata">

**Author:** ![mrjones](https://communityhealthtoolkit.b-cdn.net/user_avatar/forum.communityhealthtoolkit.org/mrjones/32/681_2.png) [@mrjones](https://forum.communityhealthtoolkit.org/u/mrjones)\
**Post date:** [August 29, 2025, 6:54pm UTC](https://forum.communityhealthtoolkit.org/t/visualizing-cht-data-using-superset-couch2pg-and-postgres/1783/11 "2025-08-29T18:54:08Z")

</div>

Split this topic on August 29, 2025, 6:54pm UTC

A post was split to a new topic: [Existing SuperSet dashboards to build upon](https://forum.communityhealthtoolkit.org/t/existing-superset-dashboards-to-build-upon/5318)
