Dealing with 250k entry limit for admin dashboard

[JGreenlee]

@Tvpower

We have a 250k entries limit for DB queries. If program admins have a lot of data, they can hit more than 250k location entries in the range they are trying to query for, which can make it hard or impossible for them to download all the data they need to perform their own analysis.

We need to implement chunking (and ideally caching) to work around this limit.

Two strategies we can employ:

1. Do the chunking/caching on the client-side (ie in op-admin-dashboard)
2. Do the chunking/caching on the server-side (ie in e-mission-server) and send over a compressed archive

(2) seems more efficient but we need to make changes to e-mission-server and test them on op-admin-dashboard. This is tricky because op-admin-dashboard runs over a dockerized instance of e-mission-server.

Is there an easier way to test changes to e-mission-server on the dashboard?

[JGreenlee]

At least for now, can make changes to e-mission-server and test the requests separately from op-admin-dashboard. Hooking it up to the op-admin-dashboard UI could be a separate step.

[JGreenlee]

If we think that the phone app could benefit from downloading archives like this, we could add a new endpoint to the API (cfc_webapp.py) which is for querying an "archive" / "zip file" instead of the raw entries.
It will be more robust than /datastreams/find_entries/<time_type> and able to handle the 250k entry limit, and once it has gathered all the entries it will send back a compressed zip or tar

But if this is just for the dashboard, we don't necessarily need to do that. We can just make the changes in op-admin-dashboard db_utils.py

[shankari]

(2) seems more efficient but we need to make changes to e-mission-server and test them on op-admin-dashboard. This is tricky because op-admin-dashboard runs over a dockerized instance of e-mission-server.

I don't think we need to make changes to e-mission-server for this. op-admin-dashboard is its own python container and has access to all of the same python wrappers in the server code. I would suggest creating a callback called generate_zip_file or similar, that will chunk the data and potentially save it to temporary file(s) before zipping it all up and returning the zipped file.

[shankari]

e.g. https://community.plotly.com/t/create-and-download-zip-file/53704 (in particular, https://community.plotly.com/t/create-and-download-zip-file/53704/9)

[shankari]

If we think that the phone app could benefit from downloading archives like this, we could add a new endpoint to the API (cfc_webapp.py) which is for querying an "archive" / "zip file" instead of the raw entries.

Hm. that's a good point. Let's brainstorm a bit; I can see a benefit to having the API if we are going to ever implement the browser-based diary. But even if we did have the API, I think that there should be a separate "library" that is invoked from both the API and the dashboard; the current architecture is that the admin dashboard interacts with the database directly and does not make any server calls.

block-beta
    columns 3
    space db[("db")] space
    space:3
    space common_zipping_implementation space
    space:3
    block:impl:3
        op_admin_dash
        REST_API
    end

    common_zipping_implementation --> db
    REST_API --> common_zipping_implementation
    op_admin_dash --> common_zipping_implementation

Loading

[Tvpower]

@JGreenlee

I am going to to overcome the 250k entry limit by breaking large database queries into smaller time based chunks collecting all the results and then zipping this data in memory to allow users to download a single compressed file of all requested trajectories when they press the download button on the dashboard.

[JGreenlee]

I think that we can implement the chunking/caching in op-admin-dashboard for now but make sure it is modular/reusable enough that we could easily extract it to a common_zipping_implementation later (I assume that the common_zipping_implementation in this example would be part of e-mission-server). Either way, it will be python

I am going to to overcome the 250k entry limit by breaking large database queries into smaller time based chunks collecting all the results and then zipping this data in memory to allow users to download a single compressed file of all requested trajectories when they press the download button on the dashboard.

This approach is good, except we could run out of memory if collecting a huge number of results.
When we fetch a batch of entries from the db (which will be <=250k), I think we should save it in a temp file and free up memory for the next batch. Then the zipping process would just be combining temp files. This would be slower with more filesystem i/o, but safer