9 ms·
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
by mmsimanga 6y ago
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.
[0]https://sqlitebrowser.org/ https://sqlitebrowser.org/
- iagovar 6y agoExcel 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. And check SQLiteStudio for Windows, is nice too.
- nojito 6y agoI handle hundreds of millions of rows in excel trivially. PowerPivot is extremely amazing.
- speedgoose 6y agoPowerBI is quite nice too.
- kyllo 6y agoMost 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.
- iagovar 6y agoWell, 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.
- nojito 6y agoYou should be using PowerPivot in Excel. Here's a video showing rapid pivots of 100m row dataset https://youtu.be/0WwFJ0Zg3d8?t=2285 https://youtu.be/0WwFJ0Zg3d8?t=2285
- iagovar 6y agoMmm, I was using PowerQuery and then use the editor to do the transformations (don't remember exact names sorry), and I just couldn't cope with it.
- mkl 6y agoDoes 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.
- googie 6y agoIt does.
- justinclift 6y ago> DB Browser for SQLite If it helps, our development builds for quite a few months have: https://nightlies.sqlitebrowser.org/latest/ https://nightlies.sqlitebrowser.org/latest/ Our recent 3.12.0-alpha1 release does as well: https://nightlies.sqlitebrowser.org/latest/ https://nightlies.sqlitebrowser.org/latest/ 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. :)
- time0ut 6y agoMy 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.
- iagovar 6y agoCheck SQLiteStudio. DBeaver also supports SQLite as far as I remember, but it's a bit more cumbersome and really geared towards mysql etc.
- giancarlostoro 6y agoI 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!
- deleted 6y ago[deleted]
- edwinyzh 6y agoFor Windows also check HeidiSQL (open source, very feature-rich)
- mmsimanga 6y agoThanks 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.
- fauigerzigerk 6y agoI have a lot of Excel-like use cases for which SQLite would be a great fit if only it had a decimal type.
- cordite 6y agoAs in like Java Bigdecimal, with a BigInteger on the left side, and a fixed precision on the right?
- fauigerzigerk 6y agoAs in other database systems such as Postgres. Even a C# style decimal floating point type would be sufficient.
- kyllo 6y agoBasically--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.
- darksaints 6y agoIt does have a decimal type.
- fauigerzigerk 6y agoNo, that's just an alias for numeric type affinity which results in a binary floating point type if there's a decimal point. There's also a long tradition of "faking" decimals with integers. I have always found that to be extremely tedious and error prone.
- lifepillar 6y agoI have developed SQLiteDecimal: http://chiselapp.com/user/lifepillar/repository/sqlite3decimal/index http://chiselapp.com/user/lifepillar/repository/sqlite3decim... Still very rough, but usable.
- kyllo 6y agoIf you like SQLite for data analysis, you might want to check out DuckDB https://github.com/cwida/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.
- pachico 6y agoThis is great! Thanks for sharing!
- erichocean 6y agoWow, why isn't DuckDB not more widely known!?!?! Looks incredible!!!
- kyllo 6y agoI 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 https://www.youtube.com/watch?v=PFUZlNQIndo
- nojvek 6y agoSQLite is started by some Aussie devs right ?
- kyllo 6y agoSQLite was started by Dwayne Richard Hipp from North Carolina. 20 years ago.
- justinclift 6y ago> Aussie devs Not SQLite itself. One of the DB Browser for SQLite dev's is an Aussie though. :)