Run qsv validate before qsv count?

[mhkeller]

In the docs for stats it says that stats assumes the csv is validate and suggests running qsv validate first. Is the same true for qsv count? If I want to run 1) make sure the csv is valid, 2) get its row count and schema (using the --typesonly flag for stats), are these three commands the best or is there another way that I'm overlooking?

[jqnatividad]

Yes. qsv validate ensures you have a valid CSV for qsv count.

Before you count, you may also want to index after validate.

Doing so enables:

• instant counts, as it gets the row count from the index
• much faster stats --typesonly as the index enables stats parallel processing.

[jqnatividad]

Actually, if you're doing stats --typesonly, you can skip the count and the index altogether as the latest qsv also adds "dataset stats" to the --typesonly output - (qsv__rowcount, qsv__columncount, qsv_filesize_bytes and qsv_fingerprint_hash).

You may want to consider setting the env_var QSV_AUTOINDEX_SIZE though. It will automatically create an index if the size of the input file is more than the specified size (in bytes).

[mhkeller]

Great good to know. So I think I'll just keep it as 1) run validate and 2) run stats. Do you have any advice on what a good autoindex size is? Is there a size at which the benefits of the index become apparent when running stats? I'll only ever be running these two commands.

Related, it's been a while since I've built a custom binary. Is building the lite version the best way to go if all I need is validate and stats?

cargo build --release --locked --bin qsvlite -F lite

[jqnatividad]

That really depends on your typical dataset size. But even then, it's so cheap to create an index and it unlocks so much speed that it almost always worth it.

Even if you set it to a "low" value (like 50 mb), the indexing "tax" is so low that the main decision is if you want to clean up the index files, which the --cache-threshold option can even take care of for you.

For example, the 500 mb, 41-column, 1M row NYC 311 benchmark data, here's what I get on my 2024 MacBook Air (M3 with 24 gb):

% target/release/qsvlite --version
qsvlite 3.0.0-mimalloc--8-8;19.20 GiB-0 B-0 B-24.00 GiB (aarch64-apple-darwin compiled with Rust 1.85) compiled

# ========= NO INDEX ========
% hyperfine 'target/release/qsvlite stats /tmp/NYC_311_SR_2010-2020-sample-1M.csv --typesonly --force -o /tmp/sto1.csv'
Benchmark 1: target/release/qsvlite stats /tmp/NYC_311_SR_2010-2020-sample-1M.csv --typesonly --force -o /tmp/sto1.csv
  Time (mean ± σ):      1.019 s ±  0.009 s    [User: 0.938 s, System: 0.078 s]
  Range (min … max):    1.009 s …  1.037 s    10 runs

# with --infer-dates
% hyperfine 'target/release/qsvlite stats /tmp/NYC_311_SR_2010-2020-sample-1M.csv --typesonly --infer-dates --force -o /tmp/sto1-dates.csv'
Benchmark 1: target/release/qsvlite stats /tmp/NYC_311_SR_2010-2020-sample-1M.csv --typesonly --infer-dates --force -o /tmp/sto1-dates.csv
  Time (mean ± σ):      4.600 s ±  0.053 s    [User: 4.502 s, System: 0.091 s]
  Range (min … max):    4.483 s …  4.665 s    10 runs
 
# ==== WITH AN INDEX ========
% hyperfine 'target/release/qsvlite index /tmp/NYC_311_SR_2010-2020-sample-1M.csv'                                                         
Benchmark 1: target/release/qsvlite index /tmp/NYC_311_SR_2010-2020-sample-1M.csv
  Time (mean ± σ):     393.1 ms ±  10.9 ms    [User: 349.3 ms, System: 40.4 ms]
  Range (min … max):   380.9 ms … 420.9 ms    10 runs

% hyperfine 'target/release/qsvlite stats /tmp/NYC_311_SR_2010-2020-sample-1M.csv --typesonly --force -o /tmp/sto2.csv'
Benchmark 1: target/release/qsvlite stats /tmp/NYC_311_SR_2010-2020-sample-1M.csv --typesonly --force -o /tmp/sto2.csv
  Time (mean ± σ):     138.6 ms ±   5.3 ms    [User: 801.1 ms, System: 56.9 ms]
  Range (min … max):   132.1 ms … 154.1 ms    19 runs

# with --infer-dates
% hyperfine 'target/release/qsvlite stats /tmp/NYC_311_SR_2010-2020-sample-1M.csv --typesonly --infer-dates --force -o /tmp/sto2-dates.csv'
Benchmark 1: target/release/qsvlite stats /tmp/NYC_311_SR_2010-2020-sample-1M.csv --typesonly --infer-dates --force -o /tmp/sto2-dates.csv
  Time (mean ± σ):     856.4 ms ±  18.2 ms    [User: 5816.7 ms, System: 91.8 ms]
  Range (min … max):   840.6 ms … 890.8 ms    10 runs

And yes, you got the build command for qsvlite right, and that's all you need for your validate and stats.

[mhkeller]

Hm how does --cache-threshold interact with it? If I have --cache-threshold=0 on the stats call, would that avoid the creation of an index file while still calculating stats with the benefits of the index – and thus obviate the need to cleaning up the index file?

[jqnatividad]

Set --cache-threshold to -5:

% hyperfine 'target/release/qsvlite stats /tmp/NYC_311_SR_2010-2020-sample-1M.csv --typesonly --infer-dates --force -o /tmp/sto1-dates.csv'
Benchmark 1: target/release/qsvlite stats /tmp/NYC_311_SR_2010-2020-sample-1M.csv --typesonly --infer-dates --force -o /tmp/sto1-dates.csv
  Time (mean ± σ):      4.480 s ±  0.060 s    [User: 4.371 s, System: 0.092 s]
  Range (min … max):    4.376 s …  4.566 s    10 runs
 
% hyperfine 'target/release/qsvlite stats /tmp/NYC_311_SR_2010-2020-sample-1M.csv --typesonly --infer-dates --force --cache-threshold -5 -o /tmp/sto1-dates.csv'
Benchmark 1: target/release/qsvlite stats /tmp/NYC_311_SR_2010-2020-sample-1M.csv --typesonly --infer-dates --force --cache-threshold -5 -o /tmp/sto1-dates.csv
  Time (mean ± σ):      1.253 s ±  0.023 s    [User: 6.175 s, System: 0.122 s]
  Range (min … max):    1.228 s …  1.296 s    10 runs

% ls -lh /tmp/NYC_311_SR_2010-2020-sample-1M.csv.idx
ls: NYC_311_SR_2010-2020-sample-1M.csv.idx: No such file or directory

% ls -lh /tmp/NYC_311_SR_2010-2020-sample-1M.*      
-rw-r--r--@ 1 joelnatividad  wheel   514M Feb 22 14:50 NYC_311_SR_2010-2020-sample-1M.csv

Setting --cache-threshold to -5 tells qsv to automatically create an index if the file size is greater than 5 bytes, and delete the index and the stats cache files after running stats.

Per stats's usage text:

-c, --cache-threshold <arg>  Controls the creation of stats cache files.
                                - when greater than 1, the threshold in milliseconds before caching
                                  stats results. If a stats run takes longer than this threshold,
                                  the stats results will be cached.
                                - 0 to suppress caching. 
                                - 1 to force caching.
                                - a negative number to automatically create an index when
                                  the input file size is greater than abs(arg) in bytes.
                                  If the negative number ends with 5, it will delete the index
                                  file and the stats cache file after the stats run. Otherwise,
                                  the index file and the cache files are kept.
                              [default: 5000]