Live data from Hacker News

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

successfulsoftware.net

291–300 of 355 posts

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

#291

Earlier quoted context omitted.

> - Can be extended as needed with new type specifiers. That's often not a good thing, at least if that allowance is given to third parties, as it wildly opens up the format to effective incompatibilities (fragmenting the ecosystem) and is a never ending source of security issues.

This assumes that if you don't include such capability, that applications won't find a way to extend it on their own regardless of the spec, and that's empirically false. There's a reason the vast majority of file formats do have some capacity for extension. By providing this capability you avoid much worse hacks (the devil you know and all that...) and you can ensure certain invariants are upheld (such as applicatio…

> This assumes that if you don't include such capability, that applications won't find a way to extend it on their own regardless of the spec, and that's empirically false.

Except it's true. For a trivial example, json which does not offer these capabilities doesn't suffer from incompatibilities or security issues anywhere close to how much yaml does. Because a json file which is not POD requires a lot of additional documentation and code.

> There's a reason the vast majority of file formats do have some capacity for extension.

Yes, naïvety (if not outright brain damage): for a long long time it's been assumed that flexibility and extensibility were innocuous, and while we've known that was not at all the case for at least a generation, these ideas remain attractive nuisances.

More modern formats have tended to stay away from these, and those few which have not (like, once again, yaml) are case studies in why they should have.

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

#292
In my view you can't have it both ways - if you want efficiency it's not reasonable to expect to be able to hand edit the data in a text editor. Especially since at the scale that efficiency matters your text editor is going to struggle.

If you don't care that much about efficiency then CSV, JSON, XML all provide perfectly good solutions and they're actually more easily editable in a text editor than what is being suggested. It's also much more sensible if you are hand-editing tabular data to just slurp it in to a jupyter notebook and do the edits there and shoot it back out because it means you've got a straight forward record of what changes you've had to make and can re-run those changes at a click of a button.

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

#293

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…

> GDBM, Kyoto Cabinet, etc -- Useful if you are programming in late 1990s

Hold your horses:

https://charlesleifer.com/blog/completely-un-scientific-benc...

While I would never consider GDBM for any new project, I wouldn't dismiss its performance for small/locally accessed data stores.

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

#294
post #264

Earlier quoted context omitted.

Yeah, true. Still, one or more bytes of overhead per cell and row/column seems wasteful.

Fixed width formats are only efficient when everything in a column is a similar length. You only need a single 1000 char data value in a column to mean either the whole column is 1000 chars or the data is truncated.

Space efficient, that is.

Fixed width formats are pretty efficient for quick random access.

Btw, you can have a mostly fixed width format, but with an escape that puts larger value somewhere else. That's more or less what some relational databases are doing, I think.

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

#295

Earlier quoted context omitted.

> but not the lack of a date type. i've also found this to be truly bizarre. even more bizarre than not actually respecting (via coercing or error) to the specified type...why even have types, then?

What's so special about having a named type for datetime? User will still need to call functions to manipulate the dates. If only for the default display and import?

Having a standardised format for data from different sources. If there's no standard people will use their OS specific format. That makes it harder to compare datasets from different sources

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

#296
post #234

Earlier quoted context omitted.

They are re learning those lessons slowly. I.e. OpenAPI and json schema are pretty much poor re implementations of SOAP and XSD but for json. I don't want to be that get off my lawn guy but it's laughable how equivalent they are for 99% of daily use cases.

Every time I hear someone talking about validating JSON I just think about how, despite its flaws, XSD is actually pretty decent despite being 20 years old.

XML and its associated formats were just so complex. I remember considering getting a book on XML and it was 4 inches thick. Just for a text-based data storage format...

This is just prohibitively complex. Formats like JSON and YAML thrive because they don't have the complexity of trying to fit every possible scenario ever. The KISS principle still works.

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

#297

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…

And DuckDB (DuckDB.org) is a lightweight and super fast library/CLI for working with Parquet.

It’s SQLite for column formats, and for Python users it’s only a pip install away. I use it on the command line to inspect and work with Parquet. It’s aliso Pandas compatible and actually more performant than Pandas.

No need to use Spark, which is heavy and has tons of boilerplate.

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

#298
post #219

> There doesn’t seem to be anything that is reasonably space efficient, simple and quick to parse and text based (not binary) so you can view and edit it with a standard editor. > XML and Javascript are tree structures and not suitable for efficiently storing tabular data (plus other issues). You can certainly be efficient with json(net). See: Notice how they are separate objects: {'name': 'foo', 'age': 2} {'name': '…

XML can be more efficient, since you do not have to surround the key with quotes. It saves two characters at the foo/cat each:

   
   

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

#299
post #145

Earlier quoted context omitted.

None of them seem all that conducive to source control or merging. Any good format for that?

Don't put data in source control; use a database.

What if you are doing both, as in trying to source control your database. A lot of tools (liquibase for one) can use CSV files as the basis of read-mostly lookup tables for example. E.g. if I had a list of "product categories" that I wanted to be part of my database when I bootstrap a new environment to run my software. Liquibase can turn that CSV file into a set of upserts that will run if the file hash has changed. So you can maintain that file with just regular edit file > git commit, and it will ensure that table is always consistent with what you want and gives you a much easier to understand commit history for your database, especially as things inevitably get complicated.

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

#300

Earlier quoted context omitted.

Code may be data but data isn't code.

Data is code that prints/evaluates to itself.

The higher-ups might be unhappy if you use that excuse after committing a gigabyte of customer data.
Post reply on HN