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

121–130 of 355 posts

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

#121

Post by thayne »

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

Parquet is also column-major which is great for many use cases, but bad for others, where row-major is more useful. For example, if you want to get just the first x rows.

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

#122

Post by thayne »

prepend wrote: post #58
quesera wrote: post #40

Earlier quoted context omitted.

I think CSV is crappy because commas are so common in real data. For almost all scenarios I've had to work with, I'd have been perfectly happy with TSV where literal Tab was a disallowed character. No escaping histrionics required.

I prefer commas because I can see them over tabs. I spend zero time escaping commas because the libraries and read and write with (usually pandas but pretty much everything) escape for me. So unless I’m manually building csvs myself it’s a non-issue and certainly not histrionics.

Newlines are pretty common in text too, depending on your data. What do you do about those?

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

#123

Post by losvedir »

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…

I've been pretty impressed with parquet lately. One thing I've missed is a way to group tables. Is there a standard for that? While parquet is generally column oriented it has support for metadata about tables of multiple columns. However, I'm not aware of any format that groups the tables, short of just zipping a bunch of files.

For context, this would be for an application that passes sqlite files around. So naturally it has good support for the database level of storage. But parquet is so fast for some applications as well as so compressed.

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

#124

Post by tbrownaw »

UncleEntity wrote: post #107
tbrownaw wrote: post #91

Earlier quoted context omitted.

> Removing the ability to embed record delimiters, for example, means you can process the records in parallel. That’s a massive improvement all by itself. I can process records from a csv in parallel just fine. Lexing isn't slow. .csv stream -> tokens -> records (-> aggregate into ~100ms blocks) -> work queue with N workers

You lex the whole file, separate it into records and then process them? Doesn’t sound like you’re processing the file in parallel to me. TFA’s way would allow you to start at any arbitrary file position, just advance until you find a record separator then start parsing from there without having to worry about starting in the middle of a quoted string.

Not like sequential steps, a whole bunch of foreach / yield return loops until the last one dumps things onto a thread-safe queue.

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

#125

Post by naikrovek »

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.

yeah the "CSV is fine" bit scared me. people think they know what everyone is doing and why; in reality (where these problems actually need to be solved) no one knows.

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

#126

Post by asperous »

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 relational data, somewhat ubiquitous, types defined but not enforced

parquet / hdf5 / apache feather / etc -- Designed for scientific use cases, robust, efficient, less ubiquitous

capn proto, prototype buffers, avro, thrift -- Has specific features for data communication between systems

xml -- Useful if you are programming in early 2000s

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

Pick your poison I guess. Engineering is about trade offs.

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

#127

Post by d3mcfadden »

out_of_protocol wrote: post #68
synack wrote: post #2

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

You can just use sqlite then. Very compact, highly popular (in different role though). Seen it used for large datasets - map tiles (millions of jpeg files). Much smaller size than zip or tar archive, indexed, fast. P.S. sqlite> .mode csv sqlite> .import city.csv cities

Cool, I didn't know about .mode and .import. Super handy tip.

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

#128

Post by dfdz »

I think CSV is a decent file format for tabular data. The author claims that CSV files are

> difficult to parse efficiently using multiple cores, due to the quoting (you can’t start parsing from part way through a file).

But I do not see why this is the case.

Step 1: loop over file (in parallel) to determine indices of quote characters

Step 2: loop over indices outside quote regions (in parallel) to determine indices of comma and return characters

Step 3: return two dimensional integer array with pointers to cells of table

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

#129

Post by zzo38computer »

asperous wrote: post #126

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…

The latest version of SQLite has a STRICT command to enforce the data types. This option is set per table, but even in a STRICT table you can specify the type of some columns as ANY if you want to allow any type of data in that column (this is not the same meaning of ANY in non-strict tables).
Post reply on HN