Working with CSV files on shell/terminal

Re:Working with CSV files on shell/terminal by wenc on Tuesday June 27, 2023 @11:59PM (#36502107)

For any kind of tabular data, I use DuckDB (parallel CSV reads, works on small to humongous CSVs). It supports full Postgres-like SQL, so you can do arbitrarily complex manipulations on columns and rows. The CSV essentially becomes a dataframe object that can be manipulated in performant ways (DuckDB vectorizes and paralllelizes, so it's way faster than awk on large CSV files). You can even manipulate multiple CSVs in a single statement (JOINs, UNIONs etc).

  % duckdb -c "from 'test.csv'"
  ┌───────┬───────┬───────┐
  │   A   │   B   │   C   │
  │ int64 │ int64 │ int64 │
  ├───────┼───────┼───────┤
  │     1 │     2 │     3 │
  │     4 │     5 │     6 │
  │     7 │     8 │     9 │
  └───────┴───────┴───────┘

  % duckdb -c "select sum(C) from 'test.csv'"
  ┌────────┐
  │ sum(C) │
  │ int128 │
  ├────────┤
  │     18 │
  └────────┘

  % duckdb -c "select sum(A + 2*B + C^2) from 'test.csv'"
  ┌────────────────────────────────┐
  │ sum(((A + (2 * B)) + (C ^ 2))) │
  │             double             │
  ├────────────────────────────────┤
  │                          168.0 │
  └────────────────────────────────┘

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by theLiminator on Wednesday June 28, 2023 @12:41AM (#36502394)

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

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by pyeri on Wednesday June 28, 2023 @01:38AM (#36502696)

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

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by wenc on Wednesday June 28, 2023 @01:49AM (#36502770)

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

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by bombolo on Wednesday June 28, 2023 @07:07AM (#36504806)

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

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by wenc on Wednesday June 28, 2023 @10:36AM (#36507096)

How so?

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by bombolo on Wednesday June 28, 2023 @04:43PM (#36512320)

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.

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by gabagool on Wednesday June 28, 2023 @01:54AM (#36502800)

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

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by RobinL on Wednesday June 28, 2023 @03:12AM (#36503343)

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

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by wenc on Wednesday June 28, 2023 @12:51PM (#36509138)

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

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by hermitcrab on Wednesday June 28, 2023 @03:02AM (#36503271)

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

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by theLiminator on Wednesday June 28, 2023 @04:01PM (#36511801)

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)

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by hermitcrab on Wednesday June 28, 2023 @04:03PM (#36511822)

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

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by theLiminator on Wednesday June 28, 2023 @04:06PM (#36511863)

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

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by hermitcrab on Wednesday June 28, 2023 @04:15PM (#36511955)

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

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by wenc on Wednesday June 28, 2023 @05:10PM (#36512659)

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.

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by up2isomorphism on Wednesday June 28, 2023 @01:36AM (#36502690)

It is kinda sad that one has to go through all the database technologies just in order to read a csv file nowadays.

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by chaxor on Wednesday June 28, 2023 @01:46AM (#36502753)

You don't have to, but you have to admit DuckDB is quite nice for handling csvs quite a bit more intuitively than awk

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by wenc on Wednesday June 28, 2023 @01:53AM (#36502792)

With raw string parsing approaches (awk, perl) you have to handle all kinds of corner cases because CSV is an underspecified spec. CSV is deceptive in that it looks like it parses easily, but the truth is CSV is one of the most difficult formats to reliably parse. I mean, just look at the number of arguments that pandas.read_csv has.

https://pandas.pydata.org/docs/reference/api/pandas.read_csv...

Parsing a CSV by hand is almost always the wrong thing to do unless you also wrote the CSV generating process and can limit the variance.

But if not, instead of working with CSVs at the string level and risk running into issues, I've found that the best practice is to farm the CSV parsing out to a battle-tested engine (I use DuckDB, but sqlite, pandas, polars, clickhouse-local or any number of engines work), and work with the parsed object instead.

Better yet, avoid CSV and use DuckDB + Parquet (paired with visidata for quick vizes) for anything nontrivial.

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by kwoff on Wednesday June 28, 2023 @12:20PM (#36508682)

With Perl, I wouldn't do raw string parsing; I'd use a Text::CSV (a battle-tested engine). For example, this filters a file foo.csv where `some_header` field is positive (you could also use sort or map or whatever instead of grep):

  perl -MText::CSV=csv -e'my $aoh = csv(in=>"foo.csv",headers=>"auto",keep_headers=>\my @h); @$aoh = grep { $_->{some_header} > 0 } @$aoh; csv(in=>$aoh,out=>*STDOUT,headers=>\@h)'

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by bjornasm on Wednesday June 28, 2023 @12:01PM (#36508415)

You don't gave to, but it makes it easier. New tools that helps my work makes me happy.

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by psanford on Wednesday June 28, 2023 @01:45AM (#36502737)

Yeah, sqlite has been my preferred method for working with csv files for a long time. I will say that one nice thing about DuckDB over SQLite is that it does a better job inferring column types than SQLite does.

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by dgsb on Wednesday June 28, 2023 @03:06AM (#36503297)

I do use sqlite too to process csv file, but I either rely on csvkit to generate the schema from the csv or create the table with raw sql before inserting data in the sqlite database.

[ Parent | on HN ]