You can't read SAME csv in a parallel manner but you can still read two different csvs in two separate threads? I think that's what they meant by "parallel csv".

My understanding is that the CSV is read in chunks, and then each chunk is parsed in a different thread.

This is the description in the experimental PR (parallel CSV has since merged into the main branch)

https://github.com/duckdb/duckdb/pull/5194

Sounds like a good way to fail to parse all the possible files properly.

How so?

Because you read a \n or a comma but you don't know if there was an open quote before the block you're currently reading.

I didn't know that and my Google fu is lacking, does anyone have a resource I could read to understand that limitation?

I think the fundamental issue is that a single 'cell' (value) can contain newlines, so you can't just assume each line is a 'row' and trivially parallelize.

The problem is perhaps most obvious when you consider that the value of a single cell could itself be a CSV, and this could be recursive

I haven’t worked out if the BNF is unambiguous (RFC 4180) but if you have a CSV in a cell you would escape any ambiguous characters.

An unescaped cell within a CSV will break most CSV parsers in the world even with recursion handling.

https://datatracker.ietf.org/doc/html/rfc4180

I think you can use a heuristic approach. E.g. pick a comma in the middle of the CSV, assume it is a delimiter (not inside data) and parse from there in a separate thread. If you find it doesn't parse correctly (perhaps because it is inside data) you might have to throw the results of that thread away. Kind of ugly but would probably allow faster parsing in 99% of cases.

CSVs are a bit of a mess. Non-trivial to parse properly, due to escaping, and the standard (such as it is) is poorly followed. E.g. sometimes people use semi-colon or pipe instead of comma.

See also: 'Why isn’t there a decent file format for tabular data?' https://news.ycombinator.com/item?id=31220841

I've found that for tabular data parquet can't be beat, the only time textual formats are superior are for human readabilty (which imo can be solved by tooling)

It is useful to be able to create, view or edit data in a standard text editor though.

Edits might be a little tricky, there's no concept of writing in place in parquet afaict. But there's absolutely no reason why reads/creation can't be supported in text editors (I'm sure some do).

I often has to create small test data files a few rows long. Very easy to do for a CSV file.

Absolutely.

This is also my use case where I have GBs of data in S3 as Parquet and a small CSV file that I have to join them to.

And DuckDB reads CSVs and Parquet and SQLite and others. I can join all these heterogeneous data types in a single SQL statement and have the assurance that it’ll be done correctly.

I believe clickhouse-local can do the same.