Best practices for incremental updates after datasette publish fly?

[jayljohnson]

I have a sqlite database in low GB size and the command dataset publish fly works seamlessly. But my internet connection has low upload bandwidth and I'd like to be able to remote in to the sqlite db on fly.io and insert the incremental data on a regular basis.

The data is in csv format and I used csvs-to-sqlite locally to backfill all of the historical data into the sqlite db. Incremental data is also in csv format and I'd be pushing that to the remote database from local at first, and later running this as a cron job. The data for both historical backfill and incremental data is fetched from a 3rd party API, cleaned and flattened, and saved to csv files.

Would this approach work okay? Any alternatives or better ways to do it?

1. Install csvs-to-sqlite plugin during the initial dataset publish fly, like this

datasette publish fly \
sqlite.db \
--app your-application-name \
--create-volume 1 \
--install csvs-to-sqlite

2. Save incremental data to fly.io volume as /mnt/1/files/2023-02-04.csv
3. Somehow run this from the fly.io host: csvs-to-sqlite -t existing_table /mnt/1/files/2023-02-04.csv sqlite.db
4. Delete 2023-02-04.csv after successfully loading the data

Appreciate any feedback on this before I jump into a solution. I'm happy to share the exact steps once it's working. Or if anyone knows of a good writeup please let me know too, I searched but couldn't find one.

[jayljohnson]

Thinking about this some more, it may be nice if datasette publish fly could directly load the incremental data. To abstract away the steps described above.

Something like this:

datasette publish fly \
sqlite.db \
--append-file-source ~/2023-02-04.csv
--append-file-target some_table_name

Or, can the --install arg support running a command after the install step? Something like this might work too, with the command that was passed into the arg running on a remote host on the fly.io side:

datasette publish fly \
sqlite.db \
--static /mnt/1/:~/2023-02-04.csv
--install csvs-to-sqlite "-t existing_table /mnt/1/files/2023-02-04.csv sqlite.db"

[cldellow]

I think a wrinkle is that when you do datasette publish fly database.db, you're baking database.db into the docker image, so it's not meant to be modified.

The proposals you have sound like interesting things, but I think there's nothing quite like that yet.

So, what can you do?

One solution might look something like this:

1. Use Fly's persistent volumes to get a place where your DB can live and persist between deploys. https://simonwillison.net/2022/Sep/5/laion-aesthetics-weeknotes/ documents how to do this, scroll down to "Deploying it to Fly"

2. Figure out a way to run csvs-to-sqlite on your fly instance.

I think you can achieve (2) in either of two ways:

Option A: ssh to your Fly instance. The default docker image will likely be missing a bunch of tools, but you can apt-get install them. Install everything you need to run csvs-to-sqlite. Fetch your file. Run csvs-to-sqlite.

Option B: Add csvs-to-sqlite as an --install dependency for your Datasette. Write a one-off plugin that runs on startup. That plugin detects if there is a CSV that has not yet been applied. If so, it uses the subprocess module to run csvs-to-sqlite with the appropriate arguments. Whenever you need to ingest a new CSV, you do a new fly deploy, passing the new CSV via the --static argument.

They both feel a little bit Rube Goldberg-y, so it's a matter of taste. Option A will be a pain because the default docker image is missing a bunch of tools. Option B is a pain because you have to write some code.

If it were me, I'd probably take a stab at doing option B -- you could prototype it all locally.

[jayljohnson]

Thank you! I like option B but I realized some inefficiencies with my current approach, and thinking to go in a different direction that combines parts of A and B. Instead of using CSV's I would directly load the main sqlite.db file from python scripts. Then for changed data, the same python logic and sqlite.db table schemas would be used but only containing the changed data,

Finally, using SQL-based merge logic like this example it would bring the changed data into the main database. Ideally it can use fly sftp to transfer the changed data sqlite.db file to the fly volume. Then run a script to do the merge logic via fly ssh console -c /path/to/script/.

This avoids extra i/o from API result -> python flattening logic -> CSV -> sqlite both for the backfill and incremental data. CSV is cut out of the flow completely. And if I wanted to keep the history of changed data it's easily available and queryable.

tbd if I'll be able to do this via a plugin but either way I can share the final scripts once it's working. I'm excited to get this working because it can act like an inexpensive, simple, and fast file-based ETL/ELT backend. I'm at low GB scale and performance is good enough with CSV's, and will be even better without the extra CSV 'hop'