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

311–320 of 355 posts

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

#311

Post by rawgabbit »

Tostino wrote: post #299
tantalor wrote: post #146

Earlier quoted context omitted.

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.…

Databases are measured in gigabytes and terabytes. If you put only a portion of it in source control, how do you back up the rest of your database? Furthermore, static data is a minor subset of a database. Data by its nature is volatile. Transactions make up 80% of the data. A projection or snapshot/summary is a the summarization of the daily/hourly/minute transactions. If you want a golden copy to bootstrap new environments, I would argue you are better off backing up that golden copy and restoring it using native database tools. Mashing together two very different paradigms will lead to long term disaster. There was a thread about a year ago, when a DevOps engineer was surprised when his "nonproduction" configuration code deleted his production database. If your database is a terabyte in size, by the time you restore the database -- you are already fired.

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

#312

Post by stavrospap »

human_person wrote: post #37

Have you see tiledb? https://tiledb.com/data-types/dataframes My team is currently transitioning from HDF5 to tiledb for genomics data.

Hi folks, Stavros from TileDB here. Here are my two cents on tabular data. TileDB (Embedded) is a very serious competitor to Parquet, the only other sane choice IMO when it comes to storing large volumes of tabular data (especially when combined with Arrow). Admittedly, we haven’t been advertising TileDB’s tabular capabilities, but that’s only because we were busy with much more challenging applications, such as genomics (population and single-cell), LiDAR, imaging and other very convoluted (from a data format perspective) domains.

Similar to Parquet:

* TileDB is columnar and comes with a lot of compressors, checksum and encryption filters.

* TileDB is built in C++ with multi-threading and vectorization in mind

* TileDB integrates with Arrow, using zero-copy techniques

* TileDB has numerous optimized APIs (C, C++, C#, Python, R, Java, Go)

* TileDB pushes compute down to storage, similar to what Arrow does

Better than Parquet:

* TileDB is multi-dimensional, allowing rapid multi-column conditions

* TileDB builds versioning and time-traveling into the format (no need for Delta Lake, Iceberg, etc)

* TileDB allows for lock-free parallel writes / parallel reads with ACID properties (no need for Delta Lake, Iceberg, etc)

* TileDB can handle more than tables, for example n-dimensional dense arrays (e.g., for imaging, video, etc)

Useful links:

* Github repo (https://github.com/TileDB-Inc/TileDB)

* TileDB Embedded overview (https://tiledb.com/products/tiledb-embedded/)

* Docs (https://docs.tiledb.com/)

* Webinar on why arrays as a universal data model (https://tiledb.com/blog/why-arrays-as-a-universal-data-model)

Happy to hear everyone’s thoughts.

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

#313

Post by chubot »

earthboundkid wrote: post #137
chubot wrote: post #101

Related thread: CSVs: The Good, the Bad, and the Ugly (2020) https://news.ycombinator.com/item?id=25014721 FWIW I have designed an upgrade to TSV as part of https://www.oilshell.org . Oil actually emits it now from 'pp proc' to pretty print "functions" and their docstrings as a table. It will be used in many parts of Oil, most of which aren't implemented yet. It's called QTT -- Quoted, Typed Tables (formerly QTSV) It…

You should make the connection of QTT and QSN to Rust more clear by coming up with a backronym for RSON.

Hm I wasn't aware of RSON. https://github.com/rson-rs/rson

QSN isn't intended to be tied to Rust in any way (and isn't), while RSON says it uses the Serde data model.

This gets at an issue I have been having a hard time explaining, mentioned here:

http://www.oilshell.org/blog/2022/03/backlog-arch.html

That is, narrow waists are necessarily a COMPROMISE. JSON is a compromise, and Rust users will be equally unhappy as Lua or Erlang users. That is a feature and not a bug for something meant of interoperability. You are "stuck with" the lowest common denominator, but that's what enables interop.

(The lowest common denominator of them all is Unix byte streams.)

I contrast "monoglot" serialization formats like Python pickle an Go .gob with language-independent formats like JSON, TSV, and HTML. The wisdom of JSON is that Crockford specified it independently of JavaScript.

But both are useful.

It's not clear if RSON is meant to be monoglot or polyglot, but it's a huge difference and it seems more monoglot. QSN on the other hand is definitely a polyglot design like JSON, despite being derived from Rust.

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

#314

Post by euroderf »

simonw wrote: 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.

All I'd want is a CLI tool that can dump an SQLite file so that it actually looks like a table (using "|", "-", "+", etc.), and maybe also accept grep-style filter specs (per specified column[s] of course).

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

#315

Post by bryanrasmussen »

petepete wrote: post #285
zo1 wrote: 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.

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 were a comma separated list of ids.

This structure is of course easy to represent with XPath, probably via Schematron. But there is no real way to represent this kind of context dependent structure in the first version of XSD (I have not kept up for reasons that shall become clear).

Which led me to declare that XSD sucks. Although I have to admit that Henry Thompson is a great programmer, and one of the most worthwhile speakers on technical issues I have ever had the pleasure to here, and while his model of XSD validation as a finite state machine is also elegant it still does not make it suck any less because the standard could not validate many common markup structures.

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

#316

Post by hedora »

gnulinux wrote: post #158
hedora wrote: post #50

Earlier quoted context omitted.

CSV is fine. If you care about edge cases, implement RFC 4180: https://www.rfc-archive.org/getrfc.php?rfc=4180 If you don't, then split each line on ",". Problem solved. If you find tab delimited easier to read (as I do), then check out the IANA spec for TSV files: https://www.iana.org/assignments/media-types/text/tab-separa... It's easier to parse than CSV. Unfortunately, you have to decide how to handle newlines an…

> If you don't, then split each line on ",". Problem solved. What? Real data contains '"' quote character too. That doesn't work.

Read the RFC I linked. It handles all the characters.

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

#317

Post by hedora »

makeworld wrote: post #89
hedora wrote: post #50

Earlier quoted context omitted.

CSV is fine. If you care about edge cases, implement RFC 4180: https://www.rfc-archive.org/getrfc.php?rfc=4180 If you don't, then split each line on ",". Problem solved. If you find tab delimited easier to read (as I do), then check out the IANA spec for TSV files: https://www.iana.org/assignments/media-types/text/tab-separa... It's easier to parse than CSV. Unfortunately, you have to decide how to handle newlines an…

> If you don't, then split each line on ",". Problem solved. And the millionth bad CSV parser is born.

More than the millionth, I'm sure. If you know your input doesn't have commas (for example, because you generated it) and the code is throwaway, then splitting on comma is fine.

But, yeah, I prefer tab delimited, since "no commas" is painful, and data that contains quotes leads to scenarios where the crappy CSV I generated can't be parsed by RFC 4180 parsers.

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

#318

Post by jayd16 »

wtetzner wrote: post #149
jayd16 wrote: post #145

Earlier quoted context omitted.

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

CSV should be pretty easy to merge, given that it’s line based.

Although it's the easiest, same row cell changes still conflict unnecessarily.

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

#319

Post by hedora »

kall wrote: post #73
prepend wrote: post #38

I think it’s because csv is good enough. All the standards I’ve seen haven’t been worth the effort to implement. So since csv, with all its flaws, is good enough it crowds out other open standards. People complain about it, but it’s not really much of a challenge to use csv. I’d also prefer it over the crap (rdf, xml, even schemad json) proposed by people who value more structure. It’s easier for me to just make clea…

Yeah I agree. Just the combination of opening in excel on double click and being dead simple to spit out from any software makes it a winner. It helps that it's usable in plain text too. If it would have used ANYTHING other than half the world's decimal separator as a value separator, it would be a no brainer. If it had been .psv (pipe separated values) the edge cases would be so few that we could just ignore them.

Gnumeric (an open source spreadsheet) has a great dialog box that pops up and lets you tell it exactly how to parse the tab delimited / csv file you just handed it. It even has a preview of what your parsing options will do to the file you're opening. Also, "gnumeric foo.csv" does the right thing from the command line.

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

#320

Post by jayd16 »

civilized wrote: post #167
jayd16 wrote: post #145

Earlier quoted context omitted.

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

Why would we expect data to be amenable to version control systems designed for source code?

I don't _expect_ it but I want it. Surely you can understand the power of storing a config file as tabular data.
Post reply on HN