[db65edfc7996]

I have grown fond of using miller[0] to handle command line data processing. Handles the standard tabular formats (csv, tsv, json) and has all of the standard data cleanup options. Works on streams so (most operations) are not limited by memory.

[0]: https://github.com/johnkerl/miller

[llimllib]

xsv does similar stuff for CSV, and very rapidly: https://github.com/BurntSushi/xsv

https://miller.readthedocs.io/en/latest/why/ has a nice section on "why miller":

> First: there are tools like xsv which handles CSV marvelously and jq which handles JSON marvelously, and so on -- but I over the years of my career in the software industry I've found myself, and others, doing a lot of ad-hoc things which really were fundamentally the same except for format. So the number one thing about Miller is doing common things while supporting multiple formats: (a) ingest a list of records where a record is a list of key-value pairs (however represented in the input files); (b) transform that stream of records; (c) emit the transformed stream -- either in the same format as input, or in a different format.

[nsteel]

I was using xsv a lot at work (it is so much faster than csvkit) but I've recently jumped to qsv, a fork with more features.

https://github.com/jqnatividad/qsv

[cb321]

Out of curiosity, I tried these two on that same data & computer as https://news.ycombinator.com/item?id=31356573 , mlr --c2t cat takes 2.96 seconds while xsv cat rows to /dev/null takes 0.434 seconds. So, 14.8X and 2.17X slower than that c2tsv Nim program to do exactly (and only) that conversion. But, yes, yes I am sure perf varies depending on quoting/escaping/column/etc. densities.

[mattewong]

This is not an outlier. `mlr` is quite slow, literally off-the-charts slow for our purposes when we benchmarked vs xsv and zsv (see https://github.com/liquidaty/zsv. disclaimer: I'm one of its authors)

[cb321]

For completeness, just one CPU/machine, but a recent checkout of zsv 2tsv (built with -O3 -march=native) on that same file/same computer seems to take 0.380 sec - almost 2X longer than c2tsv's 0.20 sec (built with -mm:arc -d:danger, gcc-11), but zsv 2tsv does seem a little faster than xsv cat rows.

OTOH, zsv count only takes 0.114 sec for me (but of course, as I'm sure you know, that also only counts rows not columns which some might complain about). { EDIT: and I've never tried to time a "parse only" mode for c2tsv. }

[mattewong]

BTW does c2tsv handle multibyte UTF8, \r\n vs \n, regular escapes e.g. embedded dbl-quote/nl/lf/comma, as well as CSV garbage that doesn't exist in theory but is abundant in real world (e.g. dbl-quote inside a cell that did not start w dbl-quote, malformed UTF8, etc)? Handling those in the same way Excel does added considerable overhead to zsv (and is the reason it could only perform a subset of the processing in SIMD and had to use regular branch code for other)

[cb321]

It handles most cases, but maybe not arbitrary garbage that humans might be able to guess, but I don't think rfc4180 includes all those anyway. c2tsv is UTF8/binary agnostic. It just keys off ASCII commas, newlines, etc. Beats me how one ensures you handle anything the "same" way Excel does without actually running Excel's code somehow. { Maybe today, but next year or 10 years ago? } The little state machine could be extended, but it's hard to guess what the speed impact might be until you actually write said extensions.

From a performance perspective, strictly delimiter-separated values { again, ironically redundant ;-) } can be parsed with memchr. On Linux, memchr should be SIMD vectorized at least on x86_64 glibc via ELF 'i' symbols. So, while you give up SIMD on the "messy part" with a byte-at-a-time DFA, you regain it on the other side. (I have no idea if Apple gives you SIMD-vectorized memchr.)

Send to a file and segmentation (for parallel handling of segments) is also a simple application of memchr rather than needing an index of where rows start. You just split by bytes and find the next newline char. (Roughly). This can get you 16..128X speed-ups (today, anyway, on just one host) depending upon what you do.

Conversion to something properly byte-delimited basically restores whatever charm you might have thought ?SV had. I can only imagine a few corner cases where running directly off a complex format like quoted CSV makes sense ("tiny" data, "cannot/will not spend 2X space+must save input", "cannot/will not spend time to recompress", "running on a network fileysystem shared with those who refuse simplicity".) These cases are not common (for me). When they do happen, perf is usually limited by other things like network IO, start-up overheads, etc. Usually that little extra bit to write buffers out to a pipeline will either not matter or be outright immediately repaid in parallelism, parsing simplicity, or both.

Converting from any ASCII to even faster binary formats has a similar story, but usually with even more perf improvement (depending..) and more "choices" like how to represent strings [1]. Fully pre-parsed, the performance of conversion matters much less. (Whatever the ratio of processings per initial parse is.) Between both parallelism and ASCII->binary, however fast you make your serial zsv parser/ETL stuff, actual data analysis may still run 10,000 times slower than it could be on just 1 CPU (depending upon what throttles your workloads..you may only get 10000x for CPU local L1 resident nested loop stuff). { But we veer now toward trying to cram a databases course into an HN comment. :) And I'm probably repeating myself/others. Direct email from here may work better. }

[1] https://github.com/c-blake/nio

[mattewong]

thanks for mentioning. will try out. did you use the default build settings for zsv (i.e. just plain old "make install")? also do you have copy or location for the dataset you used to test on? also what hardware / os if I may ask?

[pstuart]

Just starred the repo -- thanks for the tip!