I work in data analysis too and SQLite is to me what MS Excel is to other people in the industry. I have development background so I never picked up in MS Excel to same degree as my colleagues from statistics backgrounds. In corporate organisations where DBAs monitor the use of their database with an eagle eye SQLite is a fantastic option to analyse several millions of lines of data on your desktop. I find DB Browser for SQLite (DB4S)[0] is invaluable when using SQLite.
If you like SQLite for data analysis, you might want to check out DuckDB https://github.com/cwida/duckdb which is billed as "SQLite for analytics."
SQLite is a row store, which is best for OLTP (point queries, inserting/updating/deleting one or a few rows at a time), while DuckDB is a column store, which means the data layout has values from the same column stored contiguously, making aggregation queries (GROUP BY) perform much better.
I was wondering the same--I guess because it's fairly new (2018) and it came out of a database research group at a European university, rather than a SV tech firm. Therefore, limited marketing budget.
By the way, here's a YT video of a talk given by one of the DuckDB implementers about why they made it, what it's for, and how it works: https://www.youtube.com/watch?v=PFUZlNQIndo
Thanks for this. I used to be a longtime user of HeidiSQL but drifted away because I landed up in an Oracle environment. At the time I drifted away SQLite was not supported. I look forward to being re-acquainted with HeidiSQL.
Excel is nice as a simple sandbox, and PowerQuery is amazing IMO, the problem is that it's basically impossible to work with large datasets. If you didn't know about it I recommend you to play a bit with it.
Most people don't realize that Excel (like PowerBI) has an in-memory, compressed, column store database inside of it.
Loading hundreds of millions of rows into it takes a while, but given a commensurate amount of RAM and a reasonable data model (single table or star schema), performing aggregations with a pivot table is pretty snappy.
Well, for me with my personal PC with a quad core and 24gb DDR3, it gets unusable for about 1 million rows, but it's true that most of my datasets have around 10 columns or so.
Does SQLiteStudio let you cancel an unexpectedly slow query part way through? DB Browser for SQLite doesn't seem to, and I don't really know what I'm doing so often accidentally set off a query that can't use indices without doing "EXPLAIN QUERY PLAN" first.
The first beta release for 3.12.0 should be out next week. There's not much change in it though (mostly language string changes), as the alpha1 has turned out to be really stable. :)
I used to use this but if you have a JetBrains sub you can open these files directly. Or even better if its a full subscription DataGrip is fantastic and they recently added MongoDB support. Which is not perfect but good enough!
> WARNING: this library is NOT ready for production. Use at your own risk!
How far off do you reckon it is from being "production" ready?
Asking because we've been adding useful SQLite extensions as optional extras in our (sqlitebrowser.org) installer. Can add yours too, if you reckon the code is reasonably cross platform and shouldn't cause (many) weird issues. :)
Basically--it's a struct containing a byte array, weight, scale, and sign, and all the arithmetic operations are implemented in software. So it's really slow, and each RDBMS has to implement this type from scratch, or find a vendor lib that provides a suitable one, because C/C++ standard libraries don't provide one.
My tools of choice are similar: sqlite, DB browser, Jupyter. I've found that DB browser struggles with analytical queries on tables over a few GB though. Still an invaluable exploratory tool.
[0]https://sqlitebrowser.org/