Hacker News Reader

Comment by CJefferson | Hacker News Reader

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.

View on HN

"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

1: https://sqlite.org/affcase1.html

For 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.

One 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).

SQLite 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.

Much 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.

So 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.

An 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.

An sqlite db file is probably as light as your json or csv AND better formed/typed

It’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).

Can but probably won't.

You 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.

Not an SQL expert but pretty sure two tables joined together is extremely normal and well represented in RDBMS

Yep, 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.

Speaking of which, I've not grokked jq's functional approach to composing queries. Can anyone suggest a good approach to understanding jq please?

Two tables: Person: {id, name} and Hobbies: {person_id, name}.

Then you can "SELECT person.name, array_agg(hobbies.name) FROM person JOIN hobbies ON (person.id = hobbies.person_id)" to get your json representation back (at least with postgresql, "array_agg" isn't in standard SQL)

That's a pretty simple join, any real database layout I've seen goes much more complicated and much deeper than that.

Your version is denormalized. It is faster, but also more likely to have misspellings and duplicates. It really depends what is more important.

The JSON example that it was compared to was also denormalized.

Exactly. You could put it in one table if you really wanted to (not that it would be ideal by any means).

I'd argue that the more large/complicated/nested the JSON structure, the more it would benefit from using a "real" database instead of a JSON file sitting on disk. If not SQLite due to document-relational mismatch, then LevelDB, RocksDB, or one of the other embedded key-value stores.

Of course, SQLite can also emulate a key-value store quite well, with a table with 'key' and 'value' columns.

Also the JSON1 extension.

https://www.sqlite.org/json1.html

Most SQL databases are much more like JSON than CSV: deeply nested and interconnected. It's not unusual to join three different tables together to get something you want, which is effectively the same as nested JSON (though a bit more flexible, because it allows for circular dependencies and the like).

`imbrications`: overlapping, as in roof tiles

(thought it was a typo, now I’m not sure)

Sibling comments are on target but also the "relational" advantages of storing your data in a database.

I 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.

I never claimed to show advantages over excel. Excel is fine, enjoy it!

sounds wasteful and slow for anything larger than a few MB compared to the awesome (really) sqlite

and why SQLite and not something newer like datomic

Reply on news.ycombinator.com