How to import ODK data from Aggregate to Google BigQuery

Hi ggalmazor

I'm pretty new in the ODK/GCP world. For my project I am considering to import ODK data from Aggregate to Google BigQuery. ODK Aggregate is installed on Google App Engine. As you write in your post, it should be possible to import data to BigQuery via Google Cloud Storage. Right now, I'd like to figure out 1) how to export the ODK Forms and Submissions from Aggregate to a Google Cloud Storage Bucket (e.g. as JSON-Files) and 2) How to export and import media files to Google BigQuery. Do you have any experience doing that?

Any help would be greatly appreciated. Thank you!


1 Like

Hi, @schivsan!

The information you're asking for is already in the ODK Aggregate data wrangling for App Engine forum post:

  • First, export a selection of entities from your Aggregate DataStore to a Google Cloud Storage bucket

    You should follow the instructions from the official support article Exporting and Importing Entities. Here's the example command you should use:

    gcloud datastore export --kinds="KIND1,KIND2" --namespaces="NAMESPACE1,NAMESPACE2" gs://${BUCKET}

    Remember that a form stored in Aggregate can have more than one related entity. This docs page explains Aggregate's database structure.

  • Once you've completed exporting your entities, you should follow this guide that explains how to query data in BigQuery.

1 Like