jqnatividad/qsv

qsv: Ultra-fast CSV data-wrangling toolkit

Table of Contents

Hi-ho "Quicksilver" away! logo details

qsv (pronounced "Quicksilver") 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; 36 additional commands; 6 apply subcommands & 35 operations; 5 to subcommands; 3 cat subcommands; and 4 snappy subcommands. See FAQ for more details.

Available commands

Command	Description
apply ✨🚀🧠🤖	Apply series of string, date, math, currency & geocoding transformations to a CSV column. It also has some basic NLP functions (similarity, sentiment analysis, profanity, eudex & language detection).
applydp 🚀	applydp is a slimmed-down version of apply with only Datapusher+ relevant subcommands/operations (qsvdp binary variant only).
behead	Drop headers from a CSV.
cat	Concatenate CSV files by row or by column.
count 📇	Count the rows in a CSV file. (15.82 seconds for 15gb, 27m row NYC 311 dataset without an index. Instantaneous with an index.)
dedup 🤯🚀	Remove duplicate rows (See also extdedup, extsort, sort & sortcheck commands).
describegpt 🌐🤖	Infer extended metadata about a CSV using a GPT model from OpenAI's API.
diff 🚀	Find the difference between two CSVs with ludicrous speed! e.g. compare two CSVs with 1M rows x 9 columns in under 600ms!
enum	Add a new column enumerating rows by adding a column of incremental or uuid identifiers. Can also be used to copy a column or fill a new column with a constant value.
excel	Exports a specified Excel/ODS sheet to a CSV file.
exclude 📇	Removes a set of CSV data from another set based on the specified columns.
explode	Explode rows into multiple ones by splitting a column value based on the given separator.
extdedup	Remove duplicate rows from an arbitrarily large CSV/text file using a memory-mapped, on-disk hash table. Unlike the dedup command, this command does not load the entire file into memory nor does it sort the deduped file.
extsort 🚀	Sort an arbitrarily large CSV/text file using a multithreaded external merge sort algorithm.
fetch ✨🧠🌐	Fetches data from web services for every row using HTTP Get. Comes with HTTP/2 adaptive flow control, jql JSON query language support, dynamic throttling (RateLimit) & caching with optional Redis support for persistent caching.
fetchpost ✨🧠🌐	Similar to fetch, but uses HTTP Post. (HTTP GET vs POST methods)
fill	Fill empty values.
fixlengths	Force a CSV to have same-length records by either padding or truncating them.
flatten	A flattened view of CSV records. Useful for viewing one record at a time. e.g. qsv slice -i 5 data.csv | qsv flatten.
fmt	Reformat a CSV with different delimiters, record terminators or quoting rules. (Supports ASCII delimited data.)
foreach ✨	Loop over a CSV to execute shell commands. (not available on Windows)
frequency 📇😣🏎️	Build frequency tables of each column. Uses multithreading to go faster if an index is present.
generate ✨	Generate test data by profiling a CSV using Markov decision process machine learning.
headers	Show the headers of a CSV. Or show the intersection of all headers between many CSV files.
index	Create an index (📇) for a CSV. This is very quick (even the 15gb, 28m row NYC 311 dataset takes all of 15 seconds to index) & provides constant time indexing/random access into the CSV. With an index, count, sample & slice work instantaneously; random access mode is enabled in luau; and multithreading (🏎️) is enabled for the frequency, split, stats, schema & tojsonl commands.
input	Read CSV data with special quoting, trimming, line-skipping & UTF-8 transcoding rules. Typically used to "normalize" a CSV for further processing with other qsv commands.
join	Inner, outer, right, cross, anti & semi joins. Automatically creates a simple, in-memory hash index to make it fast.
joinp ✨🚀🐻‍❄️	Inner, outer, cross, anti, semi & asof joins using the Pola.rs engine. Unlike join, it can process files larger than RAM, is multi-threaded & its output does not have duplicate columns. However, joinp doesn't have an --ignore-case option & it doesn't support right outer joins.
jsonl	Convert newline-delimited JSON (JSONL/NDJSON) to CSV. See tojsonl command to convert CSV to JSONL.
luau 👑 ✨📇🌐	Create multiple new computed columns, filter rows, compute aggregations and build complex data pipelines by executing a Luau 0.581 expression/script for every row of a CSV file (sequential mode), or using random access with an index (random access mode). Can process a single Luau expression or full-fledged data-wrangling scripts using lookup tables with discrete BEGIN, MAIN and END sections. It is not just another qsv command, it is qsv's Domain-specific Language (DSL) with numerous qsv-specific helper functions to build production data pipelines.
partition	Partition a CSV based on a column value.
pseudo	Pseudonymise the value of the given column by replacing them with an incremental identifier.
py ✨	Create a new computed column or filter rows by evaluating a python expression on every row of a CSV file. Python's f-strings is particularly useful for extended formatting, with the ability to evaluate Python expressions as well.
rename	Rename the columns of a CSV efficiently.
replace	Replace CSV data using a regex. Applies the regex to each field individually.
reverse 🤯	Reverse order of rows in a CSV. Unlike the sort --reverse command, it preserves the order of rows with the same key.
safenames	Modify headers of a CSV to only have "safe" names - guaranteed "database-ready"/"CKAN-ready" names.
sample 📇🌐	Randomly draw rows (with optional seed) from a CSV using reservoir sampling (i.e., use memory proportional to the size of the sample).
schema 📇😣🏎️	Infer schema from CSV data, replete with data type & domain/range validation & output in JSON Schema format. Uses multithreading to go faster if an index is present. See validate command to use the generated JSON Schema to validate if similar CSVs comply with the schema.
search	Run a regex over a CSV. Applies the regex to each field individually & shows only matching rows.
searchset	Run multiple regexes over a CSV in a single pass. Applies the regexes to each field individually & shows only matching rows.
select	Select, re-order, duplicate or drop columns.
slice 📇	Slice rows from any part of a CSV. When an index is present, this only has to parse the rows in the slice (instead of all rows leading up to the start of the slice).
snappy 🚀🌐	Does streaming compression/decompression of the input using Google's Snappy framing format (more info).
sniff 🌐	Quickly sniff & infer CSV metadata (delimiter, header row, preamble rows, quote character, flexible, is_utf8, average record length, number of records, content length & estimated number of records if sniffing a CSV on a URL, number of fields, field names & data types). It is also a general mime type detector.
sort 🚀🤯	Sorts CSV data in alphabetical (with case-insensitive option), numerical, reverse, unique or random (with optional seed) order (See also extsort & sortcheck commands).
sortcheck 📇	Check if a CSV is sorted. With the --json options, also retrieve record count, sort breaks & duplicate count.
split 📇🏎️	Split one CSV file into many CSV files of N chunks. Uses multithreading to go faster if an index is present.
sqlp ✨🚀🐻‍❄️	Run blazing-fast Polars SQL queries against several CSVs - converting queries to fast LazyFrame expressions, processing larger than memory CSV files.
stats 📇🤯🏎️	Compute summary statistics (sum, min/max/range, min/max length, mean, stddev, variance, nullcount, sparsity, quartiles, IQR, lower/upper fences, skewness, median, mode/s, antimode/s & cardinality) & make GUARANTEED data type inferences (Null, String, Float, Integer, Date, DateTime, Boolean) for each column in a CSV. Uses multithreading to go faster if an index is present (with an index, can compile "streaming" stats on NYC's 311 data (15gb, 28m rows) in less than 20 seconds).
table 🤯	Show aligned output of a CSV using elastic tabstops. To interactively view CSV files, qsv pairs well with csvlens.
to ✨🚀	Convert CSV files to PostgreSQL, SQLite, XLSX, Parquet and Data Package.
tojsonl 📇😣🏎️	Smartly converts CSV to a newline-delimited JSON (JSONL/NDJSON). By scanning the CSV first, it "smartly" infers the appropriate JSON data type for each column. See jsonl command to convert JSONL to CSV. Uses multithreading to go faster if an index is present.
transpose 🤯	Transpose rows/columns of a CSV.
validate 📇🚀🌐	Validate CSV data blazingly-fast using JSON Schema Validation & put invalid records into a separate file with an accompanying detailed validation error report file (e.g. up to 350,000 rows/second using NYC's 311 schema generated by the schema command). If no JSON schema file is provided, validates if a CSV conforms to the RFC 4180 standard.

✨: enabled by a feature flag.
📇: uses an index when available.
🤯: loads entire CSV into memory, though dedup, stats & transpose have "streaming" modes as well.
😣: uses additional memory proportional to the cardinality of the columns in the CSV.
🧠: expensive operations are memoized (cached) with available inter-session Redis caching for fetch commands.
🐻‍❄️: command powered by Pola.rs engine.
🤖: command uses Natural Language Processing & General AI techniques.
🏎️: multithreaded when an index (📇) is available.
🚀: multithreaded even without an index.
: has CKAN-aware integration options.
🌐: has web-aware options.

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 and luau features are 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_features

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 --bin qsv --features apply,generate,luau,fetch,foreach,python,to,self_update,polars,feature_capable
# or shorthand
cargo install qsv --locked --bin qsv -F all_features

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

# or to install qsvdp
cargo install qsv --locked --bin qsvdp -F datapusher_plus,luau

Option 4: Compile from Source

Compiling from source also works similarly¹:

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

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 --bin qsv --features apply,generate,luau,fetch,foreach,python,to,self_update,polars,feature_capable
# shorthand
cargo build --release --locked --bin qsv -F all_features

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

# for qsvdp
cargo build --release --locked --bin qsvdp -F datapusher_plus,luau

NOTE: To build with Rust nightly, see Nightly Release Builds.

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 (~13% of the size of qsv)
• qsvdp - optimized for use with DataPusher+ with only DataPusher+ relevant commands; an embedded luau interpreter; applydp, a slimmed-down version of the apply feature; the --progressbar option disabled; and the self-update only checking for new releases, requiring an explicit --update (~12% of the the size of qsv).

Regular Expression Syntax

The --select option and several commands (apply, applydp, schema, search, searchset, select & replace) allow the user to specify regular expressions. We use the regex crate to parse, compile and execute these expressions. ³

Its syntax can be found here and "is similar to Perl-style regular expressions, but lacks a few features like look around and back references. In exchange, all searches execute in linear time with respect to the size of the regular expression and search text."

If you want to test your regular expressions, regex101 supports the syntax used by the regex crate. Just select the "Rust" flavor.

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 following the JSON API 1.1 specification.

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 to validate other CSV files with a similar schema.

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

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

The sqlp command produces query results in CSV, JSON, Parquet & Arrow IPC formats. Polars SQL also supports reading external files directly with its read_ndjson, read_csv, read_parquet & read_ipc table functions.

Snappy Compression/Decompression

qsv supports automatic compression/decompression using the Snappy frame format. Snappy was chosen instead of more popular compression formats like gzip because it was designed for high-performance streaming compression & decompression (up to 2.58 gb/sec compression, 0.89 gb/sec decompression).

For all commands except the index, extdedup & extsort commands, if the input file has an ".sz" extension, qsv will automatically do streaming decompression as it reads it. Further, if the input file has an extended CSV/TSV ".sz" extension (e.g nyc311.csv.sz/nyc311.tsv.sz/nyc311.tab.sz), qsv will also use the file extension to determine the delimiter to use.

Similarly, if the --output file has an ".sz" extension, qsv will automatically do streaming compression as it writes it. If the output file has an extended CSV/TSV ".sz" extension, qsv will also use the file extension to determine the delimiter to use.

Note however that compressed files cannot be indexed, so index-accelerated commands (frequency, schema, split, stats, tojsonl) will not be multi-threaded. Random access is also disabled without an index, so slice will not be instantaneous and luau's random-access mode will not be available.

There is also a dedicated snappy command with four subcommands for direct snappy file operations — a multithreaded compress subcommand (4-5x faster than the built-in, single-threaded auto-compression); a decompress subcommand with detailed compression metadata; a check subcommand to quickly inspect if a file has a Snappy header; and a validate subcommand to confirm if a Snappy file is valid.

The snappy command can be used to compress/decompress ANY file, not just CSV/TSV files.

Using the snappy command, we can compress NYC's 311 data (15gb, 28m rows) to 4.95 gb in 5.77 seconds with the multithreaded compress subcommand - 2.58 gb/sec with a 0.33 (3.01:1) compression ratio. With snappy decompress, we can roundtrip decompress the same file in 16.71 seconds - 0.89 gb/sec.

Compare that to zip 3.0, which compressed the same file to 2.9 gb in 248.3 seconds on the same machine - 43x slower at 0.06 gb/sec with a 0.19 (5.17:1) compression ratio - for just an additional 14% (2.45 gb) of saved space. zip also took 4.3x longer to roundtrip decompress the same file in 72 seconds - 0.20 gb/sec.

RFC 4180 CSV Standard

qsv follows the RFC 4180 CSV standard. However, in real life, 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.

When dealing with "atypical" CSV files, you can use the input command to normalize them to be RFC 4180-compliant.

UTF-8 Encoding

qsv requires UTF-8 encoded input (of which ASCII is a subset).

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 � (U+FFFD REPLACEMENT CHARACTER). 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):

# the following command will produce a UTF16-LE encoded CSV file on Windows
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.

Luau

Luau is a fast, small, safe, gradually typed, statically linked, 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 faster than Python & Luau is even faster still - more so, as qsv precompiles Luau into bytecode. In addition, luau is embedded into qsv, has debug logging, can do aggregations with its --begin & --end options & has no external dependencies unlike the py command.

It also allows mapping of multiple new computed columns, supports random access with indexed CSV files, and has several helper functions to help ease the development of full-fledged data-wrangling scripts.

As date manipulation is often needed, we're also preloading the LuaDate module.

Finally, as qsv's DSL (👑), luau will gain even more features over time compared to the python feature.

Luau 0.581 is currently embedded - qsv's policy is to use the latest stable Luau version at the time of each qsv release.

Python

The python feature is NOT enabled by default on the prebuilt binaries as its dynamically linked to python libraries at runtime, which presents distribution issues, as various operating systems have differing 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.7 and above 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.7), 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 statically linked, has no external dependencies, much faster than py, can do aggregations, supports random access, has a bevy of qsv helper functions, and allows mapping of multiple new columns.

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.

Memory Management

qsv supports three memory allocators - mimalloc (default), jemalloc and the standard allocator.
See Memory Allocator for more info.

It also has Out-of-Memory prevention, with two modes - NORMAL (default) & CONSERVATIVE.
See Out-of-Memory Prevention for more info.

Environment Variables & dotenv file support

qsv supports an extensive list of environment variables and supports .env files to set them.

For details, see Environment Variables and the dotenv.template.yaml file.

Feature Flags

qsv has several features:

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

• jemallocator - use the jemalloc 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.

• polars - enables all Polars-powered commands (currently, joinp and sqlp). Note that Polars is a very powerful library, but it has a lot of dependencies that drastically increases both compile time and binary size.

• python - enable py command. Note that qsv will look for the shared library for the Python version (Python 3.7 & 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. Note that enabling this feature will also noticeably increase both compile time and binary size.

• 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.

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

• all_features - enable to build qsv binary variant with all features enabled (apply,fetch,foreach,generate,luau,polars,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 & 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².

Minimum Supported Rust Version

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

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

Testing

qsv has ~1,110 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 --features all_features

# to test qsvlite
cargo test --features lite

# to test qsvdp
cargo test --features datapusher_plus,luau

# to test a specific command
# here we test only stats and use the
# t alias for test and the -F shortcut for --features
cargo t stats -F all_features

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.