5 ms·
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 an
by wenc 9y ago
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.
- bachmeier 9y ago> Why aren't we replacing CSV with it, especially in big data applications? I'm inclined to say we are. R, for instance, includes an embedded sqlite, and it seems to be used a lot. Related link: https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html
- consto 9y agoUniversality is key. Everything reads it, everything writes it, everyone can read or edit it by virtually any piece of software. Compare that with SQLite.
- ju-st 9y agoI used it. SQLite can even import directly from CSV (as long as you're only have ASCII characters). Joining several CSVs and the first data cleaning steps took a second instead of 30 minutes with pandas.
- benoror 9y agoDon't underestimate the ability to see right away CSV contents using Excel/Numbers/Google Spreadsheets. Non-coders technical users
- noir_lord 9y agoI'm a coder and I still find the ability to eyeball a file useful, also csv can be munged about with the shell easily (cut, grep, sed etc) which can be surprisingly useful.
- shabbyrobe 9y agoOr Miller: https://github.com/johnkerl/miller https://github.com/johnkerl/miller
- ams6110 9y agocsv can also be queried by tools like q[1] if sed, grep, etc. are not quite up to the task. I find this very useful to quickly summarize, filter, reorder/rename columns etc. in csv files. [1] https://harelba.github.io/q/ https://harelba.github.io/q/
- rossdavidh 9y ago...or even, in most cases, Notepad.
- JustSomeNobody 9y ago> Why aren't we replacing CSV with it, especially in big data applications? Simple, I can edit this with only a text editor if need be.
- Someone1234 9y agoCSV'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 simple and the standard is largely the superset. The biggest problem for CSV is that the most popular desktop application for view CSV (Microsoft Excel) sucks at it, and causes data corruption on save (and has for at least twenty years). If people wanted a relational database as a file, I can think of nothing better than SQLite, but in B2B you want automated tooling that can parse the format without human involvement. If you need some structure there are some simpler XML-based formats which accommodate that, but still offer far fewer features you'd have to support than SQLite does. Ajax might become popular in this space, but I'm yet to see it.
- debaserab2 9y agoI'd actually argue that CSV's biggest competitor specifically is xlsx. Which is kind of XML...
- Someone1234 9y agoI'm talking about business to business transactions, as in automated transactions on VANs and similar data interchanges. The industry doesn't really use XLSX or XLS because they're unreliable to parse without Microsoft's Office libraries which require Windows licenses, and the two formats have a huge amount of overhead compared to XML formats and CSV (which costs money on VANs). This is a space where plain text files and binary is still king. Formats like EDI are still massive. In many ways CSV and XML are the new kids on the block. Only end users are sending Excel formatted files (and PDFs) to one another.
- merb 9y ago> The industry doesn't really use XLSX or XLS because they're unreliable to parse without Microsoft's Office libraries XLS yes, however XLSX is just easy to parse if you document only contains simple types. of course with embedded mathml/wordml it gets a little bit wierd.
- tomtimtall 9y agoYour costumer just sent you a slightly malformed cab, there’s a couple of column names that need to change and some of the rows need to be excluded. 10min tops. Vs your consumer just sent you a slightly mallformed sqlite dB... which tables need to be merged with which tables? What values are actually indexes into other tables? What assumptions are there on the id’s of this table compared to this table? 4days and 20 emails later you still haven’t fed the data into your model. csv has problems that are trivial to solve live. SQLite is just way overkill to be handed over from party to party lightly.
- diogofranco 9y ago> 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.
- wenc 9y agoParquet 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.)
- diogofranco 9y agocsv is notoriously bad with spark - in fact you couldn't write to csv for the longest time without external libraries like the databricks one, so I don't contest your last point. SQlite is usually a single file representing a database though, I don't know how it would work with partitioning and stuff, and then how to handle the schema evolving across sqlite files.
- drej 9y agoAlso, the now native Spaek CSV export doesn’t follow RFC 4180 with default settings, it escapes double quotes with a backlash. So odd.
- AutoEngineer 9y agoSpeaking for an Engineer unfortunately often data is simple CSV. Parsing this data robustly and visualizing it without scripting is still something that hasn't been solved. If you have any idea how to do it, I'd be more than glad to hear about it.
- pdabbadabba 9y agoI 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.
- rhizome 9y agoSQLite 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."
- wenc 9y agoGood point. Type affinity via dynamic typing [1] isn't strong typing. But I think it's still slightly better than CSV's no-typing. [1] https://www.sqlite.org/faq.html#q3 https://www.sqlite.org/faq.html#q3
- pbreit 9y agoCSV 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.
- neves 9y agoBecause it is human readable. I can inspect it without any tool installed. I can grep it. I can awk it. I can understand it.
- simonw 9y agoReplacing CSV with SQLite is one of my goals for the combination of https://github.com/simonw/datasette https://github.com/simonw/datasette and https://github.com/simonw/csvs-to-sqlite https://github.com/simonw/csvs-to-sqlite
- hajile 9y agoWhy aren't we replacing CSV with json? You can express CSV as an array of arrays (with guaranteed semantics) or do something more complex if needed.
- megaman22 9y agoCan you open a SQLite database in Excel, without programming skills? There's your reason.