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

A love letter to the CSV format

github.com

Post reply on HN
Print view View original

691–700 of 711 posts

Re: A love letter to the CSV format

#691

Post by mjw_byrne »

recursive wrote: post #643
mjw_byrne wrote: post #531

Earlier quoted context omitted.

I used to be a data analyst at a Big 4 management consultancy, so I've seen an awful lot of this kind of thing. One thing I never understood is the inverse correlation between "cost of product" and "ability to do serialisation properly". Free database like Postgres? Perfect every time. Big complex 6-figure e-discovery system? Apparently written by someone who has never heard of quoting, escaping or the difference bet…

"Enterprise software" has been defined as software that is purchased based on the decisions of people that will not use it. I think that explains a lot.

Yep, we had a constant tug of war between techies who wanted to use open-source tools that actually work (Linux, Postgres, Python, Go etc.) and bigwigs who wanted impressive-sounding things in Powerpoint decks and were trying to force "enterprise" platforms like Palantir and IBM BigInsights on us.

Any time we were allowed to actually test one of the "enterprise" platforms, we'd break it in a few minutes. And I don't mean by being pathologically abusive, I mean stuff like "let's see if it can correctly handle a UTF-8 BOM...oh no, it can't".

Re: A love letter to the CSV format

#692

Post by mjw_byrne »

dietr1ch wrote: post #612
mjw_byrne wrote: post #508

Earlier quoted context omitted.

Exactly. "Use a delimiter that's not in the data" is not real serialisation, it's fingers-crossed-hope-for-the-best stuff. I have in the past does data extractions from systems which really can't serialise properly, where the only option is to concat all the fields with some "unlikely" string like @#~!$ as a separator, then pick it apart later. Ugh.

> Exactly. "Use a delimiter that's not in the data" is not real serialisation, it's fingers-crossed-hope-for-the-best stuff. It's not doing just this, you pick something that's likely not in the data, and then escape things properly. When writing strings you can write a double quote within double quotes with \", and if you mean to type the designated escape character you just write it twice, \\. The only reason you g…

I agree that it's best to pick "unlikely" delimiters so that you don't have to pepper your data with escape chars.

But some people (plenty in this thread) really do think "pick a delimiter that won't be in the data" - and then forget quoting and/or escaping - is a viable solution.

Re: A love letter to the CSV format

#693

Post by mjw_byrne »

crazygringo wrote: post #350
mjw_byrne wrote: post #14

CSV is ever so elegant but it has one fatal flaw - quoting has "non-local" effects, i.e. an extra or missing quote at byte 1 can change the meaning of a comma at byte 1000000. This has (at least) two annoying consequences: 1. It's tricky to parallelise processing of CSV. 2. A small amount of data corruption can have a big impact on the readability of a file (one missing or extra quote can bugger the whole thing up).…

I'm not clear why quotes prevent parallel processing? I mean, you don't usually parallelize reading a file in the first place, only processing what you've already read and parsed. So read each record in one process and then add it to a multiprocessing queue for multiple processes to handle. And data corruption is data corruption. If a movie I'm watching has a corrupted bit I don't mind a visual glitch and I want it t…

> I'm not clear why quotes prevent parallel processing?

Because of the "non-local" effect of quotes, you can't just jump into the middle of a file and start reading it, because you can't tell whether you're inside a quoted section or not. If (big if) you know something about the structure of the data, you might be able to guess. So that's why I said "tricky" instead of "impossible".

Contrast to my escaping-only strategy, where you can jump into the middle of a file and fully understand your context by looking one char on either side.

> Do you really have a use case where reading itself is the performance bottleneck and you need to parallelize reading by starting at different file offsets? I know that multiple processes can read faster from certain high-end SSD's than just one process, but that's a level of performance optimization that is pretty extraordinary. I'm kind of curious what it is!

I used to be a data analyst at a management consultancy. A very common scenario would be that I'm handed a multi-gigabyte CSV and told to "import the data". No spec, no schema, no nothing. Data loss or corruption is totally unacceptable, because we were highly risk-sensitive. So step 1 is to go through the whole thing trying to determine field types by testing them. Does column 3 always parse as a timestamp? Great, we'll call it a timestamp. That kind of thing. In that case, it's great to be able to parallelise reading.

> And data corruption is data corruption

Agreed, but I prefer data corruption which messes up one field, not data corruption which makes my importer sit there for 5 minutes thinking the whole file is a 10GB string value and then throw "EOF in quoted field".

Re: A love letter to the CSV format

#694

Post by IanCal »

recursive wrote: post #645
IanCal wrote: post #403

Earlier quoted context omitted.

Have you had to work with csv files from the wild much? I'm not being snarky but what you're talking about is night and day to what I've experienced over the years. There aren't vast numbers of different JSON formats. There's practically one and realistically maybe two. Headers are in each line, utf8 has never been an issue for me and quoting and escaping are well defined and obeyed. This is because for datasets, alm…

> There aren't vast numbers of different JSON formats. Independent variations I have seen: * Trailing commas allowed or not * Comments allowed or not * Multiple kinds of date serialization conventions * Divergent conventions about distinguishing floating point types from integers * Duplicated key names tolerated or not * Different string escaping policies, such as, but not limited to "\n" vs "\x0a" There are bazillio…

There are always many, but in comparison to csv I've received almost no differences. Json issues were rare but csv issues it was common to have a brand new issue per client.

Typically the big difference is there are different parsers that are less tolerant of in spec values. Clickhouse had a more restrictive parser, and recently I've dealt with matrix.

Maybe I've been lucky for json and unlucky for csv.

Re: A love letter to the CSV format

#695

Post by recursive »

thayne wrote: post #679
recursive wrote: post #645

Earlier quoted context omitted.

> There aren't vast numbers of different JSON formats. Independent variations I have seen: * Trailing commas allowed or not * Comments allowed or not * Multiple kinds of date serialization conventions * Divergent conventions about distinguishing floating point types from integers * Duplicated key names tolerated or not * Different string escaping policies, such as, but not limited to "\n" vs "\x0a" There are bazillio…

> Trailing commas allowed or not The json spec does not allow commas. Although there are jsom supersets that do. > Comments allowed or not The json spec does not allow comments. Although there are jsom supersets that do. > Multiple kinds of date serialization conventions Json spec doesn't say anything about dates. That is dependent on your application schema. > Divergent conventions about distinguishing floating poin…

And CSV parsers and serializers compliant with RFC 4180 are similarly reliable.

Re: A love letter to the CSV format

#696

Post by thayne »

recursive wrote: post #695
thayne wrote: post #679

Earlier quoted context omitted.

> Trailing commas allowed or not The json spec does not allow commas. Although there are jsom supersets that do. > Comments allowed or not The json spec does not allow comments. Although there are jsom supersets that do. > Multiple kinds of date serialization conventions Json spec doesn't say anything about dates. That is dependent on your application schema. > Divergent conventions about distinguishing floating poin…

And CSV parsers and serializers compliant with RFC 4180 are similarly reliable.

But many, perhaps most, parsers and serializers for CSV are not compliant with RFC 4180.

RFC 4180 is not an official standard. The text of the RFC itself states:

> This memo provides information for the Internet community. It does > not specify an Internet standard of any kind.

CSVs existed long before that RFC was written, and it is more a description of CSVs that are somewhat portable, not a definitive specification.

Re: A love letter to the CSV format

#697

Post by thayne »

mjw_byrne wrote: post #690
thayne wrote: post #668

Earlier quoted context omitted.

So have a way to escape those control characters.

Right, but the original point I was responding to is that control characters are disallowed in the data and therefore don't need to be escaped. If you're going to have an escaping mechanism then you can use "normal" characters like comma as delimiters, which is better because they can be read and written normally.

But a comma is much more likely to need to be escaped.

Re: A love letter to the CSV format

#698

Post by mjw_byrne »

thayne wrote: post #697
mjw_byrne wrote: post #690

Earlier quoted context omitted.

Right, but the original point I was responding to is that control characters are disallowed in the data and therefore don't need to be escaped. If you're going to have an escaping mechanism then you can use "normal" characters like comma as delimiters, which is better because they can be read and written normally.

But a comma is much more likely to need to be escaped.

It's good for a delimiter to be uncommon in the data, so that you don't have to use your escaping mechanism too much.

This is a different thing altogether from using "disallowed" control characters, which is an attempt to avoid escaping altogether - an attempt which I was arguing is doomed to fail.

Re: A love letter to the CSV format

#699

Post by IanCal »

recursive wrote: post #695
thayne wrote: post #679

Earlier quoted context omitted.

> Trailing commas allowed or not The json spec does not allow commas. Although there are jsom supersets that do. > Comments allowed or not The json spec does not allow comments. Although there are jsom supersets that do. > Multiple kinds of date serialization conventions Json spec doesn't say anything about dates. That is dependent on your application schema. > Divergent conventions about distinguishing floating poin…

And CSV parsers and serializers compliant with RFC 4180 are similarly reliable.

That RFC doesn't even support utf8.

It is, and accepts it is, codifing best practices rather than defining an authoritative standard.

Re: A love letter to the CSV format

#700

Post by craftkiller »

thayne wrote: post #667
craftkiller wrote: post #599

Earlier quoted context omitted.

So in addition to losing human readability, we are also throwing away the ability to nest (pseudo-)CSVs? With comma delimiters, I can take an entire CSV document and put it in 1 column, but with 0x1C-0x1F delimiters and banning non-text valid utf-8 in columns I no longer can. This continues to be a step backwards.

No reason you can't escape those special characters.

Then we're back to my original response of it doesn't solve anything: https://news.ycombinator.com/item?id=43495217
Post reply on HN