ODK Central includes an OData endpoint that can be accessed by tools and languages that support RESTful web services. Both Tableau and PowerBI support OData, allowing users to download survey submissions for immediate analysis. But what about other scenarios? A common requirement is to retrieve ODK submissions, extract the survey data and load it into a data portal or a relational database. ODK Central supports this use case as well.
The goal of this post is to show how survey data can be processed using an open source data integration tool (Kettle). We retrieve submissions from a survey hosted on the ODK Central sandbox server, transform the JSON document into a set of rows, then load the rows into a dataset stored in a CKAN data portal. The workflow is implemented using a small number of Kettle steps and can be freely modified by anyone in the open source community. Additional scenarios are discussed and documentation links are provided at the end.
Thanks for sharing this, @Dave_Henry! There is so much money spent building custom software that do these sorts of integrations and it's amazing that Central and Kettle can make that happen for no code and no money.
If folks are interested in using Kettle, how can they find out more?
There's a very active Kettle community at https://kettle-community.slack.com/. It's in the process of being opened to the general public. In the interim I'm glad to sponsor invitations - send me a request through the ODK forum or at dave.henry@schemetrica.com.
hey this is really rad! i've been preaching the kettle gospel since the inception of Central but it's really nice to see some concrete material describing how to make it a reality. would you be okay if we linked or adapted it to the odk documentation website?
@issa@Matthew_White would it make sense to include Kettle and this tutorial in the "OData" tab? Maybe also in ODK docs?
Wider discussion: Where would be the best place for a (always kept up to date) collection of ODK Central tutorials? How could maintainers of tutorials keep in the loop of upcoming changes to ODK Central's API?
Its been sometime on this thread, just to confirm if Kettle is stable with ODK Central v1.3, I have a similar scenario where it would be very helpful to have data in Postgresql DB within tables as well.
