Study how to handle realistically large data

[maurolepore]

Study

• [x] https://docs.ropensci.org/arkdb/articles/arkdb.html#package-rationale
• [x] https://docs.ropensci.org/piggyback/articles/cloud_native.html#duckdbfs
• [x] https://www.tidyverse.org/blog/2024/06/nanoparquet-0-3-0/
• [x] https://cboettig.github.io/duckdbfs/

  The mode to create the table in. One of "VIEW" or "TABLE". Creating a VIEW, the default, will execute more quickly because it does not create a local copy of the dataset. TABLE will create a local copy in duckdb's native format, downloading the full dataset if necessary. When using TABLE mode with large data, please be sure to use a conn connections with disk-based storage, e.g. by calling cached_connection(), e.g. cached_connection("storage_path"), otherwise the full data must fit into RAM. Using TABLE assumes familiarity with R's DBI-based interface. -- https://cboettig.github.io/duckdbfs/reference/open_dataset.html#arguments

• [ ] DuckDB video

Conclusions

• A free option to keep the data outside the app is to host the data on github releases and ready it remotely with duckdbfs. This works with .parquet and also .tsv/.csv files. Consider using duckdbfs::open_dataset(urls, mode = "TABLE") as explained above (using a cached_connection("storage_path"). To get the URLs consider using piggyback::pb_download_url()

• An alternative might be to download all assets into the app after deployment, maybe in such a way that it's installed in cache accessible to all apps.

• It's also free to store data in Google Cloud Storage up to 5GB. That should be enough to store all data in a parquet dataset, then read it from inside the app. Else there is a charge but for the amount of data we have it should be super small or free. It then seems possible to mount the data into the app.

• The app may start FROM an image that already has the data so that Google Cloud Run can cache that layer and reuse it as needed, effectively reducing the time to deployment. There seems to be a limit to the image size (5GB?) but parquet files or a duckdb database maybe be small enough

• But maybe DuckDb wins? The same data may take up different size when stored in different formats. parquet files seem like a good fit because they can be compressed to likely smaller size than a DuckDb database and stores data by column. A DuckDb database may take up more storage but also stores data by column and may be faster to query than parquet since it works in-process. Finally the duckdb R package depends only on DBI (compared to arrow which has 16 dependencies)

> pak::pkg_deps_tree("duckdb")
duckdb 1.0.0 [new][dl] (unknown size)
└─DBI 1.2.3 [new][dl] (unknown size)

Key:  [new] new | [dl] download

• Both parquet and DuckDb nicely offer lazy tibbles but that means we can't use DT tables any more and we either need to collect() all the data (probabbly a bad idea) or to provide a control for how many rows to print (e.g. default to 10).

[maurolepore]

I think I have enough ideas. It's now time to try things out.