Live data from Hacker News

Why isn’t there a decent file format for tabular data?

successfulsoftware.net

191–200 of 355 posts

Re: Why isn’t there a decent file format for tabular data?

#191
post #46

Earlier quoted context omitted.

No doubt binary formats like Parquet are the way to go for high performance with multi-GB datasets. Seems like total overkill if you have a few hundred or thousand rows of data though. Being able to create/edit/view stuff in a text editor and easily version it is very useful.

Do people really edit csvs in a text editor? It's horrific, the columns don't line up at all, empty cells are represented by a bunch of commas in a row (which, are you supposed to count all the commas?) And in terms of versioning, I have seen people commit diffs of csvs before, and they're equally unreadable. CSV is a plain text format, but that basically buys you nothing. As long as you're going to be loading it int…

Rainbow CSV in VSCode makes it a lot easier to deal with.

Re: Why isn’t there a decent file format for tabular data?

#193
post #70

SQLite is one of the few file formats that's recommended by the US Library of Congress for archival storage: https://www.loc.gov/preservation/digital/formats/fdd/fdd0004... See also this page on the SQLite website (they're understandably very proud of this): https://www.sqlite.org/locrsf.html I think it's a fantastic format for archiving and distributing data.

I honestly love SQLlite, so many things about this database are refreshingly amazing. The lightweight embedded nature, the extreme ubiquity[1], the single-source-file-to-compile idea[2], the gigantic test suite[3], their commitment to stability and backward compatability[4], etc ...

Simply a modern marvel of engineering. A thing to put on the next Voyager and send to aliens as a representative of what human minds and hands can do.

[1] https://www.sqlite.org/mostdeployed.html

[2]https://www.sqlite.org/amalgamation.html

[3] https://www.sqlite.org/testing.html

[4]https://www.sqlite.org/lts.html

Re: Why isn’t there a decent file format for tabular data?

#194

Seems like the problem here is there is several high quality and well-developed formats, but the author and the commenters here dismiss them because of the different trade-offs they make. csv -- Simple for simple use cases, text-based, however many edge cases, feature lacking etc xlsx -- Works in excel, ubiquitous format with a standard, however complicated and missing scientific features sqlite -- Designed for relat…

Most csv utilities support an alternative delimiter. If I need to edit a file by hand, I'll typically pick an uncommon character for the delimiter (pipe "|" works well since it's uncommon). For me, that pretty much entirely eliminates any of the pain with CSV.

Re: Why isn’t there a decent file format for tabular data?

#195

Parquet is a wonderful file format and is a dream to work with compared to CSV. Parquet embeds the schema in the footer metadata, so the query engines don't need to guess what the column names / data types are. Parquet used to be poorly supported, but now it's well supported by almost all languages. You can even view Parquet files in text editors now, but that's not something I've ever needed ( https://blog.jetbrains…

I've recently been working on a WebAssembly version of Parquet, to bring Parquet to the Web too!

[0]: https://github.com/kylebarron/parquet-wasm

[1]: https://observablehq.com/@kylebarron/geoparquet-on-the-web

Re: Why isn’t there a decent file format for tabular data?

#196

Seems like the problem here is there is several high quality and well-developed formats, but the author and the commenters here dismiss them because of the different trade-offs they make. csv -- Simple for simple use cases, text-based, however many edge cases, feature lacking etc xlsx -- Works in excel, ubiquitous format with a standard, however complicated and missing scientific features sqlite -- Designed for relat…

I've often wondered what would happen if there was a standard text editor plugin for dealing with parquet and co.

It seems like these formats are disliked, as they are difficult to inspect - but there really isn't any reason UTF-8 bytes arranged in a large sequence (aka CSV) should be any easier to read except for editor support. Sure writes would be slower, but I'd expect most users wouldn't care on modern hardware.

Re: Why isn’t there a decent file format for tabular data?

#197

Seems like the problem here is there is several high quality and well-developed formats, but the author and the commenters here dismiss them because of the different trade-offs they make. csv -- Simple for simple use cases, text-based, however many edge cases, feature lacking etc xlsx -- Works in excel, ubiquitous format with a standard, however complicated and missing scientific features sqlite -- Designed for relat…

ndjson is actually a really pragmatic choice here that should not be overlooked.

Tabular formats break down when the data stops being tabular. This comes up a lot. People love spread sheets as editing tools but they then end up doing things like putting comma separated values in a cell. I've also seen business people use empty cells to indicate hierarchical 'inheritance". An alternate interpretation of that is that that data has some kind of hierarchy and isn't really row based. People just shoehorn all sorts of stuff into spreadsheets because they are there.

With ndjson, every line is a json object. Every cell is a named field. If you need multiple values, you can use arrays for the fields. Json has actual types (int, float, strings, boolean). So you can have both hierarchical and multivalued data in a row. The case where all the fields are simple primitives is just the simple case. It has an actual specification too: https://github.com/ndjson/ndjson-spec. I like it because I can stream process it and represent arbitrarily complex objects/documents instead of having to flatten it into columns. The parsing overhead makes it more expensive to use than tsv though. The file size is fine if you use e.g. gzip compression. It compresses really well generally.

But I also use tab separated values quite often for simpler data. I mainly like it because google spread sheets provides that as an export option and is actually a great editor for tabular data that I can just give to non technical people.

Both file formats can be easily manipulated with command line tools (jq, csvkit, sed, etc.). Both can be processed using mature parsers in a wide range of languages. If you really want, you can edit them with simple text editors, though you probably should be careful with that. Tools like bat know how to format and highlight these files as well. Etc. Tools like that are important because you can use them and script them together rather than reinventing wheels.

Formats like parquet are cumbersome mainly because none of the tools I mention support it. No editors. Not a lot of command line tools. No formatting tools. If you want to inspect the data, you pretty much have to write a program to do it. I guess this would be fixable but people seem to be not really interested in doing that work. Parquet becomes nice when you need to process data at scale and in any case use a lot of specialized tooling and infrastructure. Not for everyone in other words.

Character encoding is not an issue with either tsv or ndjson if you simply use UTF-8, always. I see no good technical reason why you should use anything else. Anything else should be treated as a bug or legacy. Of course a lot of data has encoding issues regardless. Shit in, shit out basically. Fix it at the source, if you can.

The last point is actually key because all of the issues with e.g. csv usually start with people just using really crappy tools to produce source data. Switching to a different file format won't fix these issues since you still deal with the same crappy tools that of course do not support this file format. Anything else you could just fix to not suck to begin with. And if you do, it stops being an issue. The problem is when you can't.

Nothing wrong with tsv if you use UTF-8 and a some nice framework that generates properly escaped values and does all the right things. The worst you can say about it is that there are a bit too many choices here and people tend to improvise their own crappy data generation tools with escaping bugs and other issues. Most of the pain is self inflicted. The reason csv/tsv are popular is that you don't need a lot of frameworks / tools. But of course the flipside is that DYI leads to people introducing all sorts of unnecessary issues. Try not to do that.

Re: Why isn’t there a decent file format for tabular data?

#198

I literally don't get why JSON is bad: [{row1}, {row2}, {row3}] The fact that it can do more is in no way a negative. Can even make a limited JSON parser with reduced capabilities. And with JSON can do more definitions like header names vs column names vs just arrays of arrays.

This table has one column, one row, and thus one cell. What is its value? [{"col1":"val1", "col1":"val2"}]

If you're really trying to replace csv with json, it would probably look like

    ["header1", "header2"]
    ["val1", "val2"]
    ...
The entire file isn't valid JSON, but if you load each line independent of the next (jsonl), then you're fine.

Re: Why isn’t there a decent file format for tabular data?

#199
post #2

HDF5 is often used in scientific computing for this. https://en.wikipedia.org/wiki/Hierarchical_Data_Format

Single Cell RNA Seq data is often stored in Loom which is a HDF5 format.

https://linnarssonlab.org/loompy/

Its a little weird at first but its a great format and has libraries in a lot of major languages. It stores a sparse matrix which cuts the size down a lot.

https://linnarssonlab.org/loompy/format/index.html

Re: Why isn’t there a decent file format for tabular data?

#200
post #45

I literally don't get why JSON is bad: [{row1}, {row2}, {row3}] The fact that it can do more is in no way a negative. Can even make a limited JSON parser with reduced capabilities. And with JSON can do more definitions like header names vs column names vs just arrays of arrays.

The repetition of the keys seems like the sore point here. I could see a specialized form of JSON using jagged arrays {"Header": ["Name", "Street Address", "City"] "Body": [ ["Alice", "123 Fake St", "Faketon"], ["Bob", "987 Any Pl", "Anytown"], ]} in that way the keys aren't repeated. It wouldn't be coherent useful JS objects when deserialized, but it would be trivial to convert js table object into a true array of J…

Not great because you can't just append records without modifying the entire "body". Better to use JSONL!
Post reply on HN