Import (not restore) from a SQLite file

[danscan]

I'd love if it were possible to additively import data into rqlite using a SQLite file.

For example, imagine you have many SQLite files, each representing a range of time series data. Every hour, you have a new SQLite file representing the data from the past hour.

If I'm not mistaken, beyond e.g. 100mb, uploading a SQLite file and "merging" it with the existing db would be much faster than a bulk insert, if internally it worked like:

ATTACH DATABASE import.sqlite as import;

-- For each table:
INSERT INTO my_table SELECT * FROM import.my_table;

Describe the solution you'd like
A POST /db/import API endpoint and .import shell command to additively import a SQLite file, merging its tables into existing tables in the rqlite database.

Describe alternatives you've considered
Bulk inserts, downloading a backup of the rqlite db, performing the merge locally, and then restoring. Both are much less than ideal at the scale I'm considering (hundreds of mb/hour).

[otoolep]

Thanks for your suggestion, it is interesting.

This implies you are working with very large data sets. Before we discuss this further, are you finding that rqlite is working for your data sets? How large are your SQLite databases getting?

[danscan]

Right now, I'm using SQLite but not yet rqlite (so far, I've only experimented with it on my machine). I'm planning to add rqlite to my app for a small subset of its data this week, so I can come back with updates then.

The SQLite database the app serves query results from (always the biggest of all dbs produced/queried by the app) is currently 1.4GB, including a week's worth of data. But it will grow very quickly once we begin backfilling data.

I'm happy to provide more details if you have any more questions!

[otoolep]

What's the maximum size you've loaded into rqlite? Have you got good hardware? From the docs:

https://rqlite.io/docs/guides/performance/#in-memory-database-limits

rqlite was not designed for very large datasets: While there are no hardcoded limits in the rqlite software, the nature of Raft means that the entire SQLite database is periodically copied to disk, and occasionally copied, in full, between nodes. Your hardware may not be able to process those large data operations successfully. You should test your system carefully when working with multi-GB databases.