Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

beets.io

Post reply on HN
Print view View original

71–80 of 125 posts

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#71

Post by BjoernKW »

Someone1234 wrote: post #47
wenc wrote: post #35

This is just a thought: SQLite is a really good file format. Why aren't we replacing CSV with it, especially for big data applications? CSV can be difficult and ambiguous to parse correctly (because there's no real standard) and isn't extremely performant. The only thing it has going for it is its universality. SQLite is lightweight, structured, supports indexing for performance and is extremely easy to use.

CSV's biggest competitor is XML. But both XML and SQLite have the same issue: They give you just enough rope to hang yourself with. While SQLite is a fantastic micro-database engine and a file format, it isn't a very good universal B2B format because there are too many features you'd have to support to interoperate. I'd argue CSV's biggest strength is that it is easy to parse correctly, because the format is so simpl…

> that it is easy to parse correctly

While that's not exactly correct - CSV can be quite hard to parse if you want to cover each and every variation and edge case - for all practical intents and purposes that statement is true in my opinion.

CSV is something of a lowest common denominator, a compromise between well-defined, structured data and portability / accessibility.

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#73

Post by pdabbadabba »

wenc wrote: post #35

This is just a thought: SQLite is a really good file format. Why aren't we replacing CSV with it, especially for big data applications? CSV can be difficult and ambiguous to parse correctly (because there's no real standard) and isn't extremely performant. The only thing it has going for it is its universality. SQLite is lightweight, structured, supports indexing for performance and is extremely easy to use.

I agree that SQLite-as-file-format is a great idea. But CSV still has some significant advantages for certain use cases. One big one that I don't think others have mentioned is that CSVs are human readable with any old text editor. That's a big help when working with unfamiliar datasets, debugging, etc.

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#74

Post by rhizome »

wenc wrote: post #35

This is just a thought: SQLite is a really good file format. Why aren't we replacing CSV with it, especially for big data applications? CSV can be difficult and ambiguous to parse correctly (because there's no real standard) and isn't extremely performant. The only thing it has going for it is its universality. SQLite is lightweight, structured, supports indexing for performance and is extremely easy to use.

SQLite has no type safety on its fields; CSV is just text. You can create an integer field and save the value "wenc" in a SQLite record without it complaining, something it calls "dynamic typing."

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#75

Post by wenc »

diogofranco wrote: post #68
wenc wrote: post #35

This is just a thought: SQLite is a really good file format. Why aren't we replacing CSV with it, especially for big data applications? CSV can be difficult and ambiguous to parse correctly (because there's no real standard) and isn't extremely performant. The only thing it has going for it is its universality. SQLite is lightweight, structured, supports indexing for performance and is extremely easy to use.

> Why aren't we replacing CSV with it, especially for big data applications? Big data applications tend to use other structured binary formats like parquet and avro, which any big data tool can typically parse.

Parquet is a great columnar format, and Avro is great for schema evolution. But they're not that easy to work with directly. To this day there isn't a decent Parquet viewer to do adhoc data viewing.

SQLite on the other hand has a lightweight SQL REPL that can be invoked from the command line.

Spark can work with SQLite via JDBC, though obviously it isn't as native as Parquet. Between SQLite and Parquet, I might pick Parquet under most circumstances.

But it seems to me SQLite ought to at least be a better option than CSV for Spark jobs (less work needed to do type inference, predicate pushdowns are trivial, etc.)

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#76

Post by infogulch »

Someone1234 wrote: post #47
wenc wrote: post #35

This is just a thought: SQLite is a really good file format. Why aren't we replacing CSV with it, especially for big data applications? CSV can be difficult and ambiguous to parse correctly (because there's no real standard) and isn't extremely performant. The only thing it has going for it is its universality. SQLite is lightweight, structured, supports indexing for performance and is extremely easy to use.

CSV's biggest competitor is XML. But both XML and SQLite have the same issue: They give you just enough rope to hang yourself with. While SQLite is a fantastic micro-database engine and a file format, it isn't a very good universal B2B format because there are too many features you'd have to support to interoperate. I'd argue CSV's biggest strength is that it is easy to parse correctly, because the format is so simpl…

> CSV's biggest strength is that it is easy to parse correctly

(goes on to say a bunch of applications don't parse it correctly; lets add mssql to the pile)

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#77

Post by pbreit »

wenc wrote: post #35

This is just a thought: SQLite is a really good file format. Why aren't we replacing CSV with it, especially for big data applications? CSV can be difficult and ambiguous to parse correctly (because there's no real standard) and isn't extremely performant. The only thing it has going for it is its universality. SQLite is lightweight, structured, supports indexing for performance and is extremely easy to use.

CSV is fine for tabular data which is by far the biggest use case. When the data gets even slightly more complicated, people are not importing and exporting it all that much.

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#78

Post by Someone1234 »

wenc wrote: post #60
Someone1234 wrote: post #47

Earlier quoted context omitted.

CSV's biggest competitor is XML. But both XML and SQLite have the same issue: They give you just enough rope to hang yourself with. While SQLite is a fantastic micro-database engine and a file format, it isn't a very good universal B2B format because there are too many features you'd have to support to interoperate. I'd argue CSV's biggest strength is that it is easy to parse correctly, because the format is so simpl…

> I'd argue CSV's biggest strength is that it is easy to parse correctly Not sure I resonate with that. I work with large CSVs of varying provenance every day (I work in big data) and there's always some CSV edge case that stymies my analysis pipeline. Timestamp parsing is extremely hard if it's not ISO-8601, as well as handling of unicode encoding, missing data, type inference, European usage of , as a decimal point…

> Excel's CSV parsing isn't the greatest, but does a surprisingly decent job considering how ill-defined CSV is.

No, it really doesn't. Save this as a CSV, open it in Excel, hit save, and then review the raw CSV:

"1000000000012345", "Hello",

"2000000000067890", "World",

Here's what Excel (O365) does to it for me:

1E+15, Hello,

2E+15, World,

That data is now permanently lost. And this corruption occurs for almost all EAN/UPCs. There are many ways to get around this in Excel, but the default experience is data corruption and has been for most of my lifetime. It is a terrible application for CSV. If they followed the standard every column would be what it is: Text.

> XML isn't really on anyone's radar in the big data world.

But is a central theme in the business to business back-end systems world. I'm talking about ordering, invoicing, remittances, hospital records, hospital billing, and so on. No clue what "big data" is, we deal with billions of transactions a year, but that's likely not what you're referring to.

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#79

Post by Analemma_ »

kodablah wrote: post #38
Analemma_ wrote: post #33

Earlier quoted context omitted.

Why? I know this is kind of a snarky response, but seriously... why? This would take developer resources away from real feature work, introduce boatloads of complexity, add more points of failure, make testing and validation harder and make it harder to reason about the entire system end-to-end. Not that that isn't sometimes necessary, but you need a compelling reason, not just jumping into it because it sounds like…

> Why? To allow others to impl the data store they want and to avoid having to write blog posts like these. It's not that much real work to quickly pull out an iface on a stable system. Assuming it would be just mostly pass-through to your main impl anyways and this is a common approach in refactorization. You don't need some huge system, just drop it back a layer and abstract it. Often, what you end up learning abou…

> To allow others to impl the data store they want and to avoid having to write blog posts like these.

That's not a good enough reason. Those others need to first justify what the specific benefits of switching would be. I have a strong feeling that a lot of these people don't actually know why this change should be made beyond "I heard that SQLite 'isn't a real DB' but I don't know what that means". The customer is not always right; sometimes people making feature requests need to be told "No".

> It's not that much real work to quickly pull out an iface on a stable system

Maybe we've worked on different things, but this has not been my experience. IME, replacing a DBMS is agony, and adding an abstraction layer so you can use multiple DBMS's is agony exponentially magnified. Some of that is due to bad choices you made in the original design, but a lot of it is just because these systems will never behave identically, no matter what promises they make about SQL standards. Don't forget, also, that when you target multiple DBMS's, you're locking yourself into hitting the lowest common denominator of the intersection between their feature sets. You might end up making performance worse because you can no longer use DBMS-specific optimizations!

But this is all immaterial: no matter how much or how little work it is, it's all unnecessary work. If there's no benefit to switching, why do any work at all, especially when it has all the downsides I mentioned above.

> Often, what you end up learning about your system when you do this minimal work (again, nothing big) is that you screwed up and buried SQL and string concatenation and a bunch of other hard-to-review pieces....

I agree! So go through the app and fix those and then see if you still need to swap out the storage layer (which is actually what the author is recommending). Don't just do it because it's faddish before you've even established that it would actually add value.

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#80

Post by [deleted] »

Boulth wrote: post #42
jimktrains2 wrote: post #34

Earlier quoted context omitted.

Do you have a write-heavy workload? How do you handle contention? Do you have multiple servers? How do you handle real-time filesystem sync? If you only have one server, how do you handle its inevitable failure? Do you loose any data collected between last backup and failure? (I'm not saying SQLite isn't good, because it's f-ing amazing. I'm just not sold on it as a multi-process, multi-user database.) (If you have a…

> Do you have a write-heavy workload? How do you handle contention? I'd also be interested in that. Last time I wanted to use Sqlite opening twice the same file for writing either would not succeed or could time out.

[deleted]
Post reply on HN