4 ms·

Consider how inserts, updates, single-point lookups, indexes etc work with your analytical system. You use SQLite to back and operate an application. SQLite is

by jpau 6y ago

Consider how inserts, updates, single-point lookups, indexes etc work with your analytical system. You use SQLite to back and operate an application. SQLite is a wonderfully lightweight transactional database; it compares more to e.g. MySQL than with OLAP systems like Spark. SQLite isn't competing for analytical use cases :)

mlthoughts2018 6y ago
No, I meant using eg pandas even for that. We built an data annotation processing system at my last job like this. Literally customer specific csv files that got booted up and then an online flask service received restful updates of data annotations transactionally written back to the backing csv. Managing it as separate csvs per customer allowed some incredible optimizations for fanning out processing and performing reporting and dashboarding. The process running pandas allowed us to do much nicer aggregations, pivots, filters, etc., and by not writing it in SQL, we had so much more flexibility in application code and especially in unit & integration testing code. If data size per customer was going to grow substantially larger, we would have needed to migrate the workload to a backing SQL database, likely Postgres, but the nature of the problem meant this axis of data size was not a problem (every separate csv represented a completely isolated advertising campaign from a customer, with only up to a few million records per campaign). Using a flask server program to do this in pandas was an aspect that really, really paid off for us.
- Groxx 6y ago
  One of the reasons to prefer SQLite even in simple cases is that "filesystems are hard" and "transactionally written back to the backing csv" has a ridiculous number of failure modes, many of which SQLite handles correctly: https://danluu.com/file-consistency/ https://danluu.com/file-consistency/ But in many cases, yeah, simple file use is good enough, as long as important stuff is backed up somewhere / a human can easily re-upload and repair anything needed. It's a <0.1% optimization, it really only saves you noticeable effort when you're doing like millions of those operations per day.
- justsomeuser 6y ago
  SQLite would give you these benefits over CSV: - The parsing code for SQLite is exactly the same C code in every language, making it impossible to make mistakes in reading/writing. - SQLite likely has stronger transactional writing ability than what your application created. - Any SQL GUI will allow you to inspect and iterate on queries during development/production. - SQL is a good first step to writing “join/filtering” queries and is backed by C code/indexes so should be fast for simple stuff. Of course if you do not need any of those and are able to spend the extra time to write out your “queries” in pandas/Python that will work well too - just another way of doing the same thing. Personally I like SQL as a first stop for prototyping, with the hope I do not need to use other tools. Joins, transactions and using the disk for state are all ”good enough” starting points, and I can take those techniques to any language I use.

by jpau 6y ago

mlthoughts2018 6y ago

No, I meant using eg pandas even for that. We built an data annotation processing system at my last job like this. Literally customer specific csv files that got booted up and then an online flask service received restful updates of data annotations transactionally written back to the backing csv. Managing it as separate csvs per customer allowed some incredible optimizations for fanning out processing and performing reporting and dashboarding. The process running pandas allowed us to do much nicer aggregations, pivots, filters, etc., and by not writing it in SQL, we had so much more flexibility in application code and especially in unit & integration testing code. If data size per customer was going to grow substantially larger, we would have needed to migrate the workload to a backing SQL database, likely Postgres, but the nature of the problem meant this axis of data size was not a problem (every separate csv represented a completely isolated advertising campaign from a customer, with only up to a few million records per campaign). Using a flask server program to do this in pandas was an aspect that really, really paid off for us.

Groxx 6y ago
One of the reasons to prefer SQLite even in simple cases is that "filesystems are hard" and "transactionally written back to the backing csv" has a ridiculous number of failure modes, many of which SQLite handles correctly: https://danluu.com/file-consistency/ https://danluu.com/file-consistency/ But in many cases, yeah, simple file use is good enough, as long as important stuff is backed up somewhere / a human can easily re-upload and repair anything needed. It's a <0.1% optimization, it really only saves you noticeable effort when you're doing like millions of those operations per day.
justsomeuser 6y ago
SQLite would give you these benefits over CSV: - The parsing code for SQLite is exactly the same C code in every language, making it impossible to make mistakes in reading/writing. - SQLite likely has stronger transactional writing ability than what your application created. - Any SQL GUI will allow you to inspect and iterate on queries during development/production. - SQL is a good first step to writing “join/filtering” queries and is backed by C code/indexes so should be fast for simple stuff. Of course if you do not need any of those and are able to spend the extra time to write out your “queries” in pandas/Python that will work well too - just another way of doing the same thing. Personally I like SQL as a first stop for prototyping, with the hope I do not need to use other tools. Joins, transactions and using the disk for state are all ”good enough” starting points, and I can take those techniques to any language I use.