4 ms·

When you have "format wars", the best idea is usually to have a converter program change to the easiest to work with format - unless this incurs a space explosi

by cb321 4y ago

When you have "format wars", the best idea is usually to have a converter program change to the easiest to work with format - unless this incurs a space explosion as per some image/video formats. With CSV-like data, bulk conversion from quoted-escaped RFC4180 CSV to a simpler-to-parse format is the best plan for several reasons. First, it may "catch on", help Microsoft/R/whoever embrace the format and in doing so squash many bugs written by "data analyst/scientist coders". Second, in a shell "a|b" runs programs a & b in parallel on multi-core and allow things like csv2x|head -n10000|b or popen("csv2x foo.csv"). Third, bulk conversion to a random access file where literal delimiters cannot occur as non-delimiters allows trivial file segmentation to be nCores times faster (under often satisfied assumptions). There are some D tools for this bulk convert in https://github.com/eBay/tsv-utils https://github.com/eBay/tsv-utils and a much smaller stand-alone Nim tool https://github.com/c-blake/nio/blob/main/utils/c2tsv.nim https://github.com/c-blake/nio/blob/main/utils/c2tsv.nim . Optional quoting was always going to be a PITA due to its non-locality. What if there is no quote anywhere? Fourth, by using a program as the unit of modularity in this case, you make things programming language agnostic. Someone could go to town and write a pure SIMD/AVX512 converter in assembly even and solve the problem "once and for all" on a given CPU. The problem is actually just simple enough that this smells possible. I am unaware of any "document" that "standardizes" this escaped/lossless TSV format. { Maybe call it "DSV" for delimiter separated values where "delimiters actually separate"? Ironically redundant. ;-) } Someone want to write an RFC or point to one? It can be just as "general/lossless" (see https://news.ycombinator.com/item?id=31352170 https://news.ycombinator.com/item?id=31352170). Of course, if you are going to do a lot of data processing against some data, it is even better to parse all the way to down to binary so that you never have to parse again (well, unless you call CPUs loading registers "parsing") which is what database systems have been doing since the 1960s.

hedora 4y ago
Someone linked a wikipedia format guide for TSV, but the world seems to have settled on using the escape codes \\, \t, \n with their obvious meanings, then allowing arbitrary binary. That should be parallelism friendly, even with UTF-8, where an ascii tab or newline byte always mean tab and newline.
- deleted 4y ago
  [deleted]
- deleted 4y ago
  [deleted]
- cb321 4y ago
  That someone was me. I don't think of "could change at any time Wikipedia" as "as authoritative" as the "document" should be. :-) { EDIT: and I very much agree it is friendlier in almost any thinkable way except maybe Excel might not support it. Or maybe it does? You do need to unescape binary fields at a "higher level" of usage, of course, when delimiting is no longer an issue. Also, merged my posts. } A fast streaming converter into my suggested "DSV" can also be faster end-to-end. These kinds of things can vary a lot based upon how many columns rows have. I could not find "huge.csv". So, to be specific/possibly reproducible, using the 151492068 bytes of data from here: http://burntsushi.net/stuff/worldcitiespop.csv put into /dev/shm and making a symlink to huge.csv and then using the csvbench.sh in the goawk distro, I got (best of 3 elapsed times): Python 2.88user 0.01system 0:02.91elapsed 99%CPU (9460maxresident)k Goawk 1.15user 0.07system 0:01.11elapsed 110%CPU (8460maxresident)k Go 0.86user 0.04system 0:00.84elapsed 107%CPU (7300maxresident)k Go vs. Goawk time ratios were similar to Ben's article but inverted. Probably a number of columns effect. frawk failed to compile for me because my Rust was not new enough, according to the error messages. On the same data: c2tsv+gawk 0.22user 0.04system 0:00.57elapsed 46%CPU (2680maxresident)k (as *c2tsv<huge.csv|gawk -F'\t' '{nfs+=NF} END{print NR, nfs}'*) c2tsv+mawk 0.62user 0.09system 0:00.46elapsed 155%CPU (2780maxresident)k (as *c2tsv<huge.csv|mawk -F'\t' '{nfs+=NF} END{print NR, nfs}'*) c2tsv V2 0.19user 0.07system 0:00.23elapsed 115%CPU (2680maxresident)k (as *c2tsv<huge.csv|wc -l*, very similar to c2tsv<huge.csv>/dev/null) c2tsv V3 0.19user 0.00system 0:00.20elapsed 99%CPU (2632maxresident)k (as *c2tsv<huge.csv>/dev/null*) A little 100 line Nim program combined with a standard utility seems to be about 4x faster (0.84/0.23) than Go results even though said program writes out all the data again. How can this be? Well, my pipe IO is usually around 4.4 GB/s (as assessed by a dd piped to a read-only sink) while 151e6/.2=only 755 MB/s. So it need only use ~17% of available pipe BW.

by cb321 4y ago

hedora 4y ago

Someone linked a wikipedia format guide for TSV, but the world seems to have settled on using the escape codes \\, \t, \n with their obvious meanings, then allowing arbitrary binary. That should be parallelism friendly, even with UTF-8, where an ascii tab or newline byte always mean tab and newline.

deleted 4y ago
[deleted]
deleted 4y ago
[deleted]
cb321 4y ago
That someone was me. I don't think of "could change at any time Wikipedia" as "as authoritative" as the "document" should be. :-) { EDIT: and I very much agree it is friendlier in almost any thinkable way except maybe Excel might not support it. Or maybe it does? You do need to unescape binary fields at a "higher level" of usage, of course, when delimiting is no longer an issue. Also, merged my posts. } A fast streaming converter into my suggested "DSV" can also be faster end-to-end. These kinds of things can vary a lot based upon how many columns rows have. I could not find "huge.csv". So, to be specific/possibly reproducible, using the 151492068 bytes of data from here: http://burntsushi.net/stuff/worldcitiespop.csv put into /dev/shm and making a symlink to huge.csv and then using the csvbench.sh in the goawk distro, I got (best of 3 elapsed times): Python 2.88user 0.01system 0:02.91elapsed 99%CPU (9460maxresident)k Goawk 1.15user 0.07system 0:01.11elapsed 110%CPU (8460maxresident)k Go 0.86user 0.04system 0:00.84elapsed 107%CPU (7300maxresident)k Go vs. Goawk time ratios were similar to Ben's article but inverted. Probably a number of columns effect. frawk failed to compile for me because my Rust was not new enough, according to the error messages. On the same data: c2tsv+gawk 0.22user 0.04system 0:00.57elapsed 46%CPU (2680maxresident)k (as *c2tsv<huge.csv|gawk -F'\t' '{nfs+=NF} END{print NR, nfs}'*) c2tsv+mawk 0.62user 0.09system 0:00.46elapsed 155%CPU (2780maxresident)k (as *c2tsv<huge.csv|mawk -F'\t' '{nfs+=NF} END{print NR, nfs}'*) c2tsv V2 0.19user 0.07system 0:00.23elapsed 115%CPU (2680maxresident)k (as *c2tsv<huge.csv|wc -l*, very similar to c2tsv<huge.csv>/dev/null) c2tsv V3 0.19user 0.00system 0:00.20elapsed 99%CPU (2632maxresident)k (as *c2tsv<huge.csv>/dev/null*) A little 100 line Nim program combined with a standard utility seems to be about 4x faster (0.84/0.23) than Go results even though said program writes out all the data again. How can this be? Well, my pipe IO is usually around 4.4 GB/s (as assessed by a dd piped to a read-only sink) while 151e6/.2=only 755 MB/s. So it need only use ~17% of available pipe BW.