6 ms·
I feel this article misses out on "why". I tend to store stuff as csv or json, then slurp it into python to operate on it. It's not clear what benefit putting y
by CJefferson 9y ago
I feel this article misses out on "why". I tend to store stuff as csv or json, then slurp it into python to operate on it. It's not clear what benefit putting your csv into sqlite gets you, from this article.
- untog 9y agoFor one, memory usage. You either have to load your entire CSV file into memory or read it line-by-line in order to get the result you're looking for. SQLite intelligently uses disk, memory and indexes for that purpose. Plus, you know, the whole SQL thing.
- liveoneggs 9y agosounds wasteful and slow for anything larger than a few MB compared to the awesome (really) sqlite
- mannykannot 9y agoAn explanation of the considerable benefits that relational databases bring to a wide class of problems is beyond the scope of an article on one particular implementation, and also beyond the scope of an HN comment.
- paulsutter 9y agoSQLite is when the data is a little too big for your approach, but not worth the hassle of installing/running/managing a database process. The flat files method may lead to partial or empty files in certain failure cases (crash during writes) that may or may not be more gracefully handled by SQLite.
- c_shu 9y agoSo you can insert/delete/update records without rewriting the whole file. And you get many other features like transaction, spill cache pages, fflush & fsync behind the scenes.
- mythrwy 9y agoSibling comments are on target but also the "relational" advantages of storing your data in a database.
- zackelan 9y ago"SQLite As An Application File Format" [0] and "What If OpenDocument Used SQLite?" [1], both by the author of SQLite, may help to answer why you'd use it over other file serialization formats, such as CSV, JSON, Python's pickle, etc. 0: https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html 1: https://sqlite.org/affcase1.html https://sqlite.org/affcase1.html
- Avshalom 9y agoAn sqlite db file is probably as light as your json or csv AND better formed/typed
- hk__2 9y agoIt’s easier to compare it to CSV than JSON, because the former is tabular while the latter can have deep imbrications that aren’t well represented in SQL(ite).
- Avshalom 9y agoCan but probably won't.
- hk__2 9y agoYou don’t need to go deep: `{"name": "Bob", "hobbies": ["soccer", "cinema", "music"]}`. That’s 3 tables in SQL(ite): one for the people; one for the hobbies; and one to join both.
- Avshalom 9y agoNot an SQL expert but pretty sure two tables joined together is extremely normal and well represented in RDBMS
- voltagex_ 9y agoYep, I can write an INNER JOIN faster than I can remember jq's arcane syntax. If you needed it all in one flat table for some reason, SQLite supports views.
- bklaasen 9y agoSpeaking of which, I've not grokked jq's functional approach to composing queries. Can anyone suggest a good approach to understanding jq please?
- labster 9y agoI think this comment misses out on "why". I tend to store stuff in xlsx, then use Excel to operate on it. It's not clear what benefit Python or JSON gets you, from this post.
- CJefferson 9y agoI never claimed to show advantages over excel. Excel is fine, enjoy it!
- danso 9y agoOne advantage that may seem too obvious to mention: using SQLite allows you to work with data using SQL, which is far, far easier to learn (and be powerful with) than trying to learn Python or even R if you are new to languages. Even as an experienced programmer, I find SQL to almost always more elegant for expressing logic than Pandas or R. My workflow is usually having a script execute a SQL statement to do the heavy data work, and then importing the results of that SQL statement into another programming environment (e.g. R/ggplot2 for visualizations).
- ww520 9y agoMuch easier to work on data with SQL. I can cleanse, deduplicate, fill in default, update, merge, join, and generate reports with a few SQL. I had an interest rate app where the backend data collection process consists of a shell script to run curl to download rates from various places as CSV, import CSV into Sqlite, runs a few SQL to clean up/merge/fix default/dedup the data. The process is idempotent and can add new data to existing data. At the end I got a long history of clean up-to-date interest rate data in a Sqlite table.
- geokon 9y agoand why SQLite and not something newer like datomic