4 ms·
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
by wenc 3y ago
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 │
└────────────────────────────────┘
- theLiminator 3y agoiirc it's not possible to read csv in a parallel manner if your parsing true to spec?
- pyeri 3y agoYou 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 agoMy 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
- gabagool 3y agoI didn't know that and my Google fu is lacking, does anyone have a resource I could read to understand that limitation?
- RobinL 3y agoI 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 agoI 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 agoI 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 agoI'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 agoIt is useful to be able to create, view or edit data in a standard text editor though.
- theLiminator 3y agoEdits 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 agoI often have to create test data files a few rows long. Very easy to do for a CSV file.
- wenc 3y agoAbsolutely. 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.
- up2isomorphism 3y agoIt is kinda sad that one has to go through all the database technologies just in order to read a csv file nowadays.
- chaxor 3y agoYou don't have to, but you have to admit DuckDB is quite nice for handling csvs quite a bit more intuitively than awk
- wenc 3y agoWith 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.html 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.
- kwoff 3y agoWith 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)'
- bjornasm 3y agoYou don't gave to, but it makes it easier. New tools that helps my work makes me happy.
- psanford 3y agoYeah, 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.
- dgsb 3y agoI 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.