jqnatividad / qsv

qsv: Ultra-fast CSV data-wrangling toolkit

	Table of Contents
logo details	qsv is a command line program for indexing, slicing, analyzing, filtering, enriching, validating & joining CSV files. Commands are simple, fast & composable. * Available Commands * Installation Options * Whirlwind Tour * Cookbook * FAQ * Changelog * Performance Tuning * Benchmarks * Environment Variables * Feature Flags * Testing * NYC School of Data 2022 slides * Sponsor

ℹ️ NOTE: qsv is a fork of the popular xsv utility, merging several pending PRs since xsv 0.13.0's May 2018 release. On top of xsv's 20 commands, it adds numerous new features; 31 additional commands; 6 apply subcommands & 35 operations; and 5 to subcommands (for a total of 97). See FAQ for more details.

Available commands

❇️: enabled by a feature flag on qsv. Not available on qsvlite. qsvdp has luau & applydp pre-enabled.
📇: uses an index when available. join creates its own in-memory index automatically.
🗜️: loads entire CSV into memory, though dedup, stats & transpose have "streaming" modes as well.
🧠: expensive operations are memoized (cached) with available inter-session Redis caching for fetch commands.
🏎️: multithreaded when an index is available.
🚀: multithreaded even without an index.

Installation Options

Option 1: Download Prebuilt Binaries

Full-featured prebuilt binary variants of the latest qsv version for Windows, Linux & macOS are available for download, including binaries compiled with Rust Nightly (more info).

Option 2: Homebrew

For macOS and Linux (64-bit), you can quickly install qsv with Homebrew. However, only the apply feature is enabled.

brew install qsv

Option 3: Install with Rust

If you have Rust installed, you can also install from source using Rust's cargo command¹:

cargo install qsv --locked --features all_full

The binary will be installed in ~/.cargo/bin.

To install different variants and enable optional features, use cargo --features (see Feature Flags for more info):

# to install qsv with all features enabled
cargo install qsv --locked --features apply,generate,luau,fetch,foreach,python,to,self_update,full
# or shorthand
cargo install qsv --locked -F all_full

# or to install qsvlite
cargo install qsv --locked -F lite

# or to install qsvdp
cargo install qsv --locked -F datapusher_plus

Option 4: Compile from Source

Compiling from source also works similarly[^1}]:

git clone https://github.com/jqnatividad/qsv.git
cd qsv
cargo build --release --locked --features all_full

The compiled binary will end up in ./target/release/.

To compile different variants and enable optional features:

# to compile qsv with all features enabled
cargo build --release --locked --features apply,generate,luau,fetch,foreach,python,to,self_update,full
# shorthand
cargo build --release --locked -F all_full

# for qsvlite
cargo build --release --locked -F lite

# for qsvdp
cargo build --release --locked -F datapusher_plus

Variants

There are three binary variants of qsv:

• qsv - feature-capable(❇️), with the prebuilt binaries enabling all applicable features except Python ²
• qsvlite - all features disabled (~33% of the size of qsv)
• qsvdp - optimized for use with DataPusher+ with only DataPusher+ relevant commands; applydp, a slimmed-down version of the apply feature; embedded luau interpreter; the --progressbar option disabled; and the self-update only checking for new releases, requiring an explicit --update (~33% of the the size of qsv).

Minimum Supported Rust Version

qsv's MSRV policy is to require the latest Rust version that is supported by Homebrew.

Tab Completion

qsv's command-line options are quite extensive. Thankfully, since it uses docopt for CLI processing, we can take advantage of docopt.rs' tab completion support to make it easier to use qsv at the command-line (currently, only bash shell is supported):

# install docopt-wordlist
cargo install docopt

# IMPORTANT: run these commands from the root directory of your qsv git repository
# to setup bash qsv tab completion
echo "DOCOPT_WORDLIST_BIN=\"$(which docopt-wordlist)"\" >> $HOME/.bash_completion
echo "source \"$(pwd)/scripts/docopt-wordlist.bash\"" >> $HOME/.bash_completion
echo "complete -F _docopt_wordlist_commands qsv" >> $HOME/.bash_completion

File formats

qsv recognizes UTF-8/ASCII encoded, CSV (.csv) & TSV files (.tsv & .tab). CSV files are assumed to have "," (comma) as a delimiter, and TSV files, "\t" (tab) as a delimiter. The delimiter is a single ascii character that can be set either by the --delimiter command-line option or with the QSV_DEFAULT_DELIMITER environment variable or automatically detected when QSV_SNIFF_DELIMITER is set.

When using the --output option, qsv will UTF-8 encode the file & automatically change the delimiter used in the generated file based on the file extension - i.e. comma for .csv, tab for .tsv & .tab files.

JSONL/NDJSON files are also recognized & converted to/from CSV with the jsonl and tojsonl commands respectively.

The fetch & fetchpost commands also produces JSONL files when its invoked without the --new-column option & TSV files with the --report option.

The excel, safenames, sniff, sortcheck & validate commands produce JSON files with their JSON options.

The schema command produces a JSON Schema Validation (Draft 7) file with the ".schema.json" file extension, which can be used with the validate command.

The excel command recognizes Excel & Open Document Spreadsheet(ODS) files (.xls, .xlsx, .xlsm, .xlsb & .ods files).

The to command produces produces .xlsx, Parquet & Data Package files, and populates PostgreSQL and SQLite databases.

RFC 4180

qsv validates against the RFC 4180 CSV standard. However IRL, CSV formats vary significantly & qsv is actually not strictly compliant with the specification so it can process "real-world" CSV files. qsv leverages the awesome Rust CSV crate to read/write CSV files.

Click here to find out more about how qsv conforms to the standard using this crate.

UTF-8 Encoding

The following commands require UTF-8 encoded input (of which ASCII is a subset) - dedup, exclude, fetch, fetchpost, frequency, join, schema, sort, stats & validate.

For these commands, qsv checks if the input is UTF-8 encoded by scanning the first 8k & will abort if its not unless QSV_SKIPUTF8_CHECK is set. On Linux & macOS, UTF-8 encoding is the default.

This was done to increase performance of these commands, as they make extensive use of from_utf8_unchecked so as not to pay the repetitive utf-8 validation penalty, no matter how small, even for already utf-8 encoded files.

Should you need to re-encode CSV/TSV files, you can use the input command to transcode to UTF-8. It will replace all invalid UTF-8 sequences with �. Alternatively, there are several utilities you can use to do so on Linux/macOS & Windows.

Windows Usage Note

Unlike other modern operating systems, Microsoft Windows' default encoding is UTF16-LE. This will cause problems when redirecting qsv's output to a CSV file & trying to open it with Excel (which ignores the comma delimiter, with everything in the first column):

qsv stats wcp.csv > wcpstats.csv

Which is weird, since you would think Microsoft's own Excel would properly recognize UTF16-LE encoded CSV files. Regardless, to create a properly UTF-8 encoded file on Windows, use the --output option instead:

# so instead of redirecting stdout to a file
qsv stats wcp.csv > wcpstats.csv

# do this instead
qsv stats wcp.csv --output wcpstats.csv

Interpreters

For complex data-wrangling tasks, you can use Luau and Python scripts. The qsv binary variant can embed luau and python interpreters, enabled by identically named feature flags. The qsvdp binary variant has the luau interpreter embedded by default.

Luau

Luau is a fast, small, safe, gradually typed embeddable scripting language derived from Lua. It lies at the heart of Roblox technology - powering all it's user generated content, with Roblox's own internal code having more than 2 millions lines of Luau.

It has sandboxing, type-checking, additional operators & increased performance while maintaining compatibility with Lua.

Lua is much faster than Python, and Luau is even faster still - more so, as qsv precompiles Luau scripts into bytecode. In addition, luau is embedded into qsv, has debug logging, can do aggregations with its --prologue & --epilogue options & has no external dependencies unlike the py command.

Python

The python feature is NOT enabled by default on the prebuilt binaries, as doing so requires it to dynamically link to python at runtime, which presents distribution issues, as various operating systems have differing bundled Python versions.

If you wish to enable the python feature - you'll just have to install/compile from source, making sure you have the development libraries for the desired Python version (Python 3.6 to 3.11 are supported) installed when doing so.

If you plan to distribute your manually built qsv with the python feature, qsv will look for the specific version of Python shared libraries (libpython* on Linux/macOS, python*.dll on Windows) against which it was compiled starting with the current directory & abort with an error if not found, detailing the Python library it was looking for.

Note that this will happen on qsv startup, even if you're not running the py command.

When building from source - PyO3 - the underlying crate that enables the python feature, uses a build script to determine the Python version & set the correct linker arguments. By default it uses the python3 executable. You can override this by setting PYO3_PYTHON (e.g., PYO3_PYTHON=python3.6), before installing/compiling qsv. See the PyO3 User Guide for more information.

Consider using the luau command instead of the py command if the operation you're trying to do can be done with luau - as luau is faster than py and can do aggregations.

The py command cannot do aggregations because PyO3's GIL-bound memory limitations will quickly consume a lot of memory (see issue 449 for details). To prevent this, the py command processes CSVs in batches (default: 30,000 records), with a GIL pool for each batch, so no globals are available across batches.

Environment Variables

Several dependencies also have environment variables that influence qsv's performance & behavior:

• Memory Management (mimalloc)
  When incorporating qsv into a data pipeline that runs in batch mode, particularly with very large CSV files using qsv commands that load entire CSV files into memory, you can fine-tune Mimalloc's behavior using its environment variables.
• Network Access (reqwest)
  qsv uses reqwest for its fetch, validate & --update functions & will honor proxy settings set through the HTTP_PROXY, HTTPS_PROXY & NO_PROXY environment variables.

ℹ️ NOTE: To get a list of all active qsv-relevant environment variables, run qsv --envlist. Relevant env vars are defined as anything that starts with QSV_ & MIMALLOC_ & the proxy variables listed above.

Feature Flags

qsv has several features:

• mimalloc (default) - use the mimalloc allocator (see Memory Allocator for more info).

• apply - enable apply command. This swiss-army knife of CSV transformations is very powerful, but it has a lot of dependencies that increases both compile time and binary size.

• fetch - enables the fetch & fetchpost commands.

• foreach - enable foreach command (not valid for Windows).

• generate - enable generate command.

• luau - enable luau command. Embeds a Luau interpreter into qsv. Luau has type-checking, sandboxing, additional language operators, increased performance & other improvements over Lua.

• python - enable py command. Note that qsv will look for the shared library for the Python version (Python 3.6 & above supported) it was compiled against & will abort on startup if the library is not found, even if you're not using the py command. Check Python section for more info.

• to - enables the to command.

• self_update - enable self-update engine, checking GitHub for the latest release. Note that if you manually built qsv, self-update will only check for new releases. It will NOT offer the choice to update itself to the prebuilt binaries published on GitHub. You need not worry that your manually built qsv will be overwritten by a self-update.

• full - enable to build qsv binary variant which is feature-capable.

• all_full - enable to build qsv binary variant with all features enabled (apply,fetch,foreach,generate,luau,python,to,self_update).

• lite - enable to build qsvlite binary variant with all features disabled.

• datapusher_plus - enable to build qsvdp binary variant - the DataPusher+ optimized qsv binary.

• nightly - enable to turn on nightly/unstable features in the rand, regex, hashbrown, parking_lot & pyo3 crates when building with Rust nightly/unstable.

ℹ️ NOTE: qsvlite, as the name implies, always has non-default features disabled. qsv can be built with any combination of the above features using the cargo --features & --no-default-features flags. The prebuilt qsv binaries has all applicable features valid for the target platform².

Testing

qsv has ~1,020 tests in the tests directory. Each command has its own test suite in a separate file with the convention test_<COMMAND>.rs. Apart from preventing regressions, the tests also serve as good illustrative examples, and are often linked from the usage text of each corresponding command.

To test each binary variant:

# to test qsv
cargo test --feature all_full

# to test qsvlite
cargo test --feature lite

# to test qsvdp
cargo test --feature datapusher_plus

# to test a specific command
# here we test only stats and use the
# -F shortcut for --feature
cargo test stats -F all_full

License

Dual-licensed under MIT or the UNLICENSE.

Sponsor

qsv was made possible by
Standards-based, best-of-breed, open source solutions to make your Data Useful, Usable & Used.

Naming Collision

This project is unrelated to Intel's Quick Sync Video.