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. …

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 ->>…

thanks @samuel , Gonna explore this :+1:

@cliff a couple other tips: 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 Mat…

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]

Hi @cliff . 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 st…

Some more tips and tricks: Make sure your dashboard mockups are complete before starting to write your queries Make sure you understand user access needs for each dashboard Make sure the indicator definitions/calculations are complete & understood before starting Plan your query architecture appro…

helo @samuel @michael 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, do…

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 y…

[image] 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 i…

Community Health Toolkit

Visualizing CHT data using Superset, couch2pg and Postgres

Technical Support

show post in topic

Home
Categories
Guidelines
Terms of Service
Privacy Policy

Powered by Discourse, best viewed with JavaScript enabled