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

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

successfulsoftware.net

Post reply on HN
Print view View original

331–340 of 355 posts

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

#331

Post by petepete »

bryanrasmussen wrote: post #315
petepete wrote: post #285

Earlier quoted context omitted.

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.

The first time I used XSD was in 2001 I think, for a format we were developing to do human rights violation reporting. In one part of the document would be a list of people committing violations, another part a list of people witnessing violations, and then in another part violations. The violations part would have attributes saying what people had taken part in the violation, and who had witnessed. These attributes…

Yeah I also hit the XSD complexity ceiling on occasion. Most of what we used it for back in the early-mid 00s was quite simple though and it did make much of our work simpler.

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

#332

Post by cestith »

jltsiren wrote: post #135
psfried wrote: post #119

> But it is binary, so can’t be viewed or edited with standard tools, which is a pain. I've heard this sentiment expressed multiple times before, and a minor quibble I have with it is that the fact that it's binary has nothing to do with whether or not it's a pain. It's a pain because the tools aren't ubiquitous, so you can't count on them always being installed everywhere. But I'd argue that sqlite _is_ ubiquitous a…

JSON is good for structured data, but I prefer TSV for simple human-readable tabular data. In situations where it's the right choice, a TSV file consists of data and whitespace and nothing else. You can view and edit it with any imaginable tool, and there is no overhead in the form of delimeters and encodings distracting you from the data.

I really like LTSV. (That stands for labeled tab-separated values.)

LTSV is basically equivalent to a JSON object per line. You have columns consisting of a label, a colon, then a value. The columns are then separated by tabs. The value can be quoted. If you need a tab in the value, it goes inside the quotes.

As the http://ltsv.org/ suggests, I use it for logging, too, so that a log line is easily parseable and a log file is basically a table. Notice there are parsers for many languages, and there are several tools supporting it including fluentd.

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

#334

Post by jph »

kbouck wrote: post #282
Brometheus wrote: post #255

Earlier quoted context omitted.

ASCII Code 29: Group Separator ASCII Code 30: Record Separator ASCII Code 31: Unit Separator https://theasciicode.com.ar/ascii-control-characters/record-...

this doesn't directly address the ease-of-editing concern, but if you have csv (or tsv) and want ascii (0x1f, 0x1e) or unicode(U+241F, U+241E) separated (or vice versa), 'miller' [0] is a command line tool that supports converting among these formats with these one-liners: mlr --icsv --oasv cat input.csv # csv to asv mlr --iasv --ocsv cat input.asv # asv to csv [0] https://miller.readthedocs.io/en/latest/ [1] https:/…

You're right, Miller is great for this, and it turns out that Miller already offers Unicode Separated Values (USV).

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

#335

Post by bryanrasmussen »

petepete wrote: post #331
bryanrasmussen wrote: post #315

Earlier quoted context omitted.

The first time I used XSD was in 2001 I think, for a format we were developing to do human rights violation reporting. In one part of the document would be a list of people committing violations, another part a list of people witnessing violations, and then in another part violations. The violations part would have attributes saying what people had taken part in the violation, and who had witnessed. These attributes…

Yeah I also hit the XSD complexity ceiling on occasion. Most of what we used it for back in the early-mid 00s was quite simple though and it did make much of our work simpler.

That XSD is complex is one thing, some things are by their nature complex, ok, that it was not able to validate common XML structures was another thing, a third thing was cross validation tool support sucked.

Actually this combination of difficulties actually made it sort of enjoyable for me to solve problems stemming from it's usage, and looking smart for doing so, while still feeling like I was using a deficient tool.

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

#336

Post by bryanrasmussen »

_abox wrote: post #296
petepete wrote: post #285

Earlier quoted context omitted.

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.

Several things though -

1. tech books tend to be too big

2. These XML books tended to have section on XML and well formedness, namespaces, UTF-8, examples of designing a format - generally a book or address format - all this stuff probably came in to approximately 80-115 pages. Which was what you needed to understand the basis of XML.

3. Then would come the secondary stuff to understand, XPath and XSLT. I would say this would be another 100 - 150 pages, so a query language and a programming DSL to manipulating the data/document format. All this together 265 pages.

4. Then validation and entities in DTDs noting that this was old stuff from SGML days and you didn't need it and there was going to be some other way to validate really soon. Another 60 pages? (and then when that validation language came it sucked, as I noted elsewhere)

5. Then because tech books need to be thick and a 300 page book is not big enough a bunch of stuff that never amounted to anything, like Xlink or some breathless stuff about some XML formats, maybe a talk about SVG and VML, XSL-FO blah blah blah. Another 300 pages of unnecessary stuff.

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

#337

Post by o_nate »

neves wrote: post #147
MrPowers wrote: post #114

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…

It's it possible to diff a parquet file?

Its possible but you can't just diff the file bytes. Because you will get spurious differences due to metadata, etc.

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

#338

Post by habitue »

noxvilleza wrote: post #96
habitue wrote: post #51

There is a decent file format for tabular data, and the author dismisses it: parquet. It's compact, encodes all the common data types well, does int/float distinction (thanks for teaching us about how important that is json), stores null records with a mask instead of a special value, row major order, has compression, speedy random access... it has it all. And it isn't bogged down with legacy cruft (yet). Since you n…

Unsure if it was added after your comment, but there is a brief comment on parquet (in that it's binary - so it can be annoying to view/edit).

Yeah, he mentioned it quickly, but kind of dismisses it for bad reasons imo.

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

#339

Post by habitue »

emef wrote: post #74
habitue wrote: post #51

There is a decent file format for tabular data, and the author dismisses it: parquet. It's compact, encodes all the common data types well, does int/float distinction (thanks for teaching us about how important that is json), stores null records with a mask instead of a special value, row major order, has compression, speedy random access... it has it all. And it isn't bogged down with legacy cruft (yet). Since you n…

parquet is great but it's not particularly easy to read or write. the libraries that do exist to work with it are few and far between, and those that do either have a hundred dependencies or depend on native code (e.g. libarrow). certainly an important dimension in an ideal file format should be the ease of parsing/writing it, and parquet gets an extremely low score on that front imo

Sure, but any new format is going to have the same problems. I think you're right that implementation complexity needs to be considered, but it's not like Word or Excel files or something where you need to replicate bug for bug a format accreted over decades.

Parquet isn't trivial to parse / write but that's probably good imo. CSV is really easy to write, and... that just means everybody does it slightly differently. Being somewhat difficult to interact with encourages people to use a library to centralize a bit, but it's not so complex that someone motivated couldn't write a new implementation in a reasonable amount of time.

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

#340

Post by hermitcrab »

devit wrote: post #157

The article's proposal seems worse than tab separated. Not having escaping is unacceptable of course, and once you add escaping you might as well use the standard tab and newline characters as delimiters (and the \t, \n, \\ escapes plus \N for SQL NULL), resulting in a file that is properly formatted by the terminal and text editors and just works with Unix tools and most spreadsheet/database importers unlike a file…

>Not having escaping is unacceptable of course

How often do you use the ASCII US and RS characters in your data (apart from binary data blobs, which it wouldn't be a good fit for)?

Post reply on HN