3 ms·

iirc it's not possible to read csv in a parallel manner if your parsing true to spec?

pyeri 3y ago
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".
wenc 3y ago
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 https://github.com/duckdb/duckdb/pull/5194
- bombolo 3y ago
  Sounds like a good way to fail to parse all the possible files properly.
  - wenc 3y ago
    How so?
    - bombolo 3y ago
      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.
      - deleted 3y ago
        [deleted]
gabagool 3y ago
I didn't know that and my Google fu is lacking, does anyone have a resource I could read to understand that limitation?
- RobinL 3y ago
  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
  - deleted 3y ago
    [deleted]
  - wenc 3y ago
    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 https://datatracker.ietf.org/doc/html/rfc4180
hermitcrab 3y ago
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 https://news.ycombinator.com/item?id=31220841
- theLiminator 3y ago
  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)
  - hermitcrab 3y ago
    It is useful to be able to create, view or edit data in a standard text editor though.
    - theLiminator 3y ago
      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).
      - hermitcrab 3y ago
        I often have to create test data files a few rows long. Very easy to do for a CSV file.
        wenc 3y ago
        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.

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

wenc 3y ago

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 https://github.com/duckdb/duckdb/pull/5194

bombolo 3y ago
Sounds like a good way to fail to parse all the possible files properly.
- wenc 3y ago
  How so?
  - bombolo 3y ago
    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.
    - deleted 3y ago
      [deleted]

gabagool 3y ago

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

RobinL 3y ago
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
- deleted 3y ago
  [deleted]
- wenc 3y ago
  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 https://datatracker.ietf.org/doc/html/rfc4180

hermitcrab 3y ago

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 https://news.ycombinator.com/item?id=31220841

theLiminator 3y ago
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)
- hermitcrab 3y ago
  It is useful to be able to create, view or edit data in a standard text editor though.
  - theLiminator 3y ago
    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).
    - hermitcrab 3y ago
      I often have to create test data files a few rows long. Very easy to do for a CSV file.
      - wenc 3y ago
        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.