agree with this view of sqlite. will also add that it can handle tens to hundreds of gigabytes of data without problems. I’ve done things like plop decades of census data at the individual level into sqlite. I also used it for my dissertation where I stored all the data from molecular Dynamics simulations for later analysis.
it’s useful to read up a bit on SQL. there are many good books for this purpose. choose one focused on data analysis and not transaction processing
How does SQLite compare to HDF5 and raw binary files in terms of performance for chunked compressed storage for large and growing arrays, that does not fit in memory and should be read/write chunk-by chunk?
I heard (didn’t test myself) that it is inefficient to stoge large objects (images, arrays and so on) in SQL databases, is it true or not?
TLDR: You have to use what you know and what you like
And Benchmark first !
imho: SQLite can be important if the Interoperability is a high requirement. (using data from Mobile phone to HPC )
You can use SQLite - for reading/writing extra large data … like any SQL … it will work!
SQLite is used by mobile phone apps also; You can store the Full Earth - OpenStreetMap vector data ( > 60GB ) preprocessed and splitted in SQLite3 format( inside GoogleProtobuff) … and use by any low profile Android mobile phone … see MBTiles format later )
imho: it is possible to combine the both of the worlds …
( special compression + SQL + data splitting + indexes + views … )
you can add/use special - extra - compression with BLOB format;
so you can use as a container format.
MBTiles " MBTiles is a specification for storing tiled map data in SQLite databases for immediate usage and for transfer. MBTiles files, known as tilesets , MUST implement the specification below to ensure compatibility with devices."
It can be faster than storing a large number of individual files, especially if the files aren’t too big. So if you have a few thousand 1MB blobs one per file, you’re probably better off putting them as a few thousand rows with blobs in one SQLite file, because the overhead of opening the individual files and reading the data is higher than the overhead for SQLite to use its already open file to grab different rows.
You’d have to test in your specific use case, but I promise you SQLite is much more usable by your friends that only deal with Excel spreadsheets than HDF5 is… (access via ODBC from Excel)
Here’s the thing about HDF5 vs SQLite… both of them use B-Trees to create a “filesystem like” container object. But SQLite comes with a fully functional standardized query language and works well for both meta-data and data itself. It has a blob format so you can blob in enormous binary objects if you want. SQLite is far far more widely used. There are over 1 Trillion SQLite files in existence. https://www.sqlite.org/mostdeployed.html
SQLite might seem like a toy thing to people used to dealing with supercomputers, but in real world use, it’s way way more battle tested than HDF5 and very much actively maintained.
Also note that there are multiple “modes” of operation for SQLite… for example if you’re planning to read and write lots of data, using the “WAL format” https://www.sqlite.org/wal.html rather than the default format. Basically you run the SQL command
and you have a WAL database.
EDIT: The “lite” part of SQLite comes from the fact that it doesn’t require you to install a database server process and create database users with access controls, and it’s not designed for a typical SQL workflow where hundreds or thousands of clients each read and write a few rows at a time to a big table (like for people who process credit card transactions). In many ways it should be called SQLHeavy, because it’s ideal for HEAVY analysis of data. it’s better for data analysis, because it doesn’t have all the overhead of worrying about hundreds of people inserting rows in your table while you’re joining it to another table… so it does data analysis tasks quite quickly. For example if you have the entire Census ACS at the microdata level and want to join the households table (millions of rows) to the person table (tens of millions of rows) and extract a randomized subset that meets some requirements… it will do this faster than most SQL servers would.
If the only thing you work with is a specialized kind of binary data blob that goes in and out of a special FEM solver or something… then obviously SQLite isn’t necessarily of interest. But if you have hundreds of FEM solves and need to ship them all to a colleague along with meta-data about the conditions that are being simulated, and the experimental data taken off the aerodynamic wind tunnel physical model for comparison… I’d be looking at trying out SQLite. Any time you want to “bundle” a lot of related data together it’s a good candidate.
For those of us without background in databases: Could probably one of the experts post a simple SQLite/Julia usage example? Like e.g. saving/reading a 2d array of Float64 with an accompanying UTF8 text.
As much as I like SQLite, I don’t think the overhead is major either way, especially with modern filesystems operating in a reasonable hardware environment.
There are lot of other differences between various options (eg package support, fixed cost of setup, portability, concurrent access, data integrity, long run support for the data format) that may be more significant in practical applications.
run the pragma statement I mention above to switch to WAL mode before doing your timings.
I tried some timings on my machine, which is not at all super fast, but I was on my /tmp directory which is a tmpfs (in memory) filesystem so at least disk access is not the main issue. In WAL mode, writing the database took about 2.5 seconds. But doing select * from taxi took on average 10 to 11 seconds. which is I think way too long. So I’m guessing there’s some inefficiency in the way in which the Julia SQLite interface interacts with the database.
In particular, the output of
@btime dfx = DBInterface.execute(db, "select * from taxi") |> DataFrame
10.192 s (22607532 allocations: 521.60 MiB)
450,627 rows × 20 columns (omitted printing of 15 columns)
Compare to reading the same table in R:
> db = SQLite()
> dbc = dbConnect(db,"test_out.db")
> t = proc.time()
> df = dbReadTable(dbc,"taxi")
user system elapsed
1.853 0.017 2.396
> df = dbGetQuery(dbc,"select * from taxi")
user system elapsed
1.611 0.020 2.165
So there is definitely some Julia specific implementation slowness as Julia is 5x as slow as R.
@quinnj seems to be the latest committer to the Julia SQLite implementation, perhaps there is some obvious explanation he can offer?
If absolute speed is your main concern, because you have to read and write large datasets all day long, Feather is going to be the best choice. Its “read” implementation is basically just mmap the disk file into memory, done… Write is basically flush raw memory to disk.
However its one-table one-file and not exactly something people are going to read into Excel easily.
Usually compressed JSONL (JSON3.jl) which are inflated and uploaded to a PostgreSQL database (LibPQ.jl). Most of the transformations are done in SQL and then ones the data is ready for analysis / viz (StatsMakie.jl) I pull those to Julia again.
Two DateTime columns were saved in BLOBs in SQLite, causing the large file size. When I convert them to string before writing to SQLite, I get the same file size as you.
Actually, there is already an issue for this:
I worked mostly with Oracle and Postgres before and was not aware that there is no dedicated datetime type in SQLite
I really like sqlite and i like the sqlite module, and have been using it for a lot of engineering applications.
however, you’ve inadvertently pointed out the biggest problem with it, it can be hard to figure out how to do easy things. Your example of creating a schema, in Julia, is something i didn’t even know you could do.
Now, that i know, i went back through the docs and still don’t understand how the docs tell you that it’s possible. I would never have figured it out without your example, and yet such an example is not in the docs.
And it’s a REALLY useful thing otherwise you do what i do, which is to manage the schema through sqlite3 directly.
I think the docs are quite thorough and yet i have a lot of difficulty figuring out how to do easy things.
My point here is not to complain, but to say, be patient, it’s worth the effort. I find it to be a great way to handle datasets for measurements. I’ve created DBs about 20M in size and accessing them is very fast.
edit: and ask for help ! the list is great, many power users of the sqlite module