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.
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
Hi @cliff. You can create views and materialized views 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:
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';
Every time couch2pg it runs, it calls the refresh_matviews() 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 so be mindful of dependencies between materialized views (ie one materialized view probably shouldn’t depend on another one)
PostgreSQL is where you have your data transformation while Superset is where you have your 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 created a materialized view of data from a form with
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
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.
