dsq

dsq uses DataStation as a library, which in turn uses SQLite to power SQL queries on arbitrary structured data.

Because it relies on SQLite, dsq provides a well-tested and well-documented SQL engine. It also includes numerous useful functions on top of SQLite builtins, such as:

• Best-effort date parsing
• URL parsing and extraction
• Statistics functions

For complex queries, store the SQL in a file and use the --file or -f flag to specify its path instead of providing the query as a command-line argument.

$ dsq data1.csv data2.csv -f query.sql

You can pass multiple files to dsq. Each file is treated as a table that can be accessed using the 0-based index {N} (e.g., {0}, {1}). You can also assign aliases to these tables using standard SQL syntax.

# Joining CSV and JSON using indices
$ dsq testdata/join/users.csv testdata/join/ages.json \
  "select {0}.name, {1}.age from {0} join {1} on {0}.id = {1}.id"

# Joining using aliases
$ dsq testdata/join/users.csv testdata/join/ages.json \
  "select u.name, a.age from {0} u join {1} a on u.id = a.id"

By default, dsq outputs compact JSON. To get more readable output, you can either:

1. Pipe the output to jq for pretty-printed JSON.
2. Use the -p or --pretty flag to display results in an ASCII table.

You can install dsq on macOS using the Homebrew package manager.

$ brew install dsq

Enter an interactive mode to run multiple SQL queries sequentially using the -i or --interactive flag.

$ dsq some-large-file.json -i

To pipe data into dsq, you must use the -s flag to specify the file extension or MIME type.

Note: Piping data is not supported on Windows.

$ cat testdata.csv | dsq -s csv "SELECT * FROM {} LIMIT 1"
$ cat testdata.parquet | dsq -s parquet "SELECT COUNT(1) FROM {}"

Use the --cache or -C flag to store imported data on disk. dsq calculates a SHA1 sum of the input files; if the files haven't changed, subsequent queries will reuse the existing SQLite database instead of re-importing, making them significantly faster.

You can also enable this permanently by setting the DSQ_CACHE=true environment variable.

$ dsq some-large-file.json --cache 'SELECT COUNT(1) FROM {}'

Since dsq is built on SQLite, you can use the REGEXP operator for filtering. dsq uses Go's regexp implementation.

SELECT * FROM {} WHERE name REGEXP 'A.*'

To install the pre-compiled binaries on macOS, Linux, or WSL, you can use the following script to download, unzip, and move the binary to your /usr/local/bin directory. Note that this example uses version v0.23.0.

$ VERSION="v0.23.0"
$ FILE="dsq-$(uname -s | awk '{ print tolower($0) }')-x64-$VERSION.zip"
$ curl -LO "https://github.com/multiprocessio/dsq/releases/download/$VERSION/$FILE"
$ unzip $FILE
$ sudo mv ./dsq /usr/local/bin/dsq