SQ

swiss-army knife for data

        
        
      

sq is a free/libre open-source swiss-army knife to inspect, query, join, import, and export data. You can think of it as jq for relational data, whether that data is in a document or database.

sq has a rudimentary jq-style query language (formally known as SLQ). This query language is limited in scope. Fortunately, for complex queries, you can always fall back to database-native SQL queries.

Installation

  • mac
  • win
  • deb
  • yum
  • rpm
            $ brew install neilotoole/sq/sq
            $ scoop bucket add sq https://github.com/neilotoole/sq
$ scoop install sq
$ curl -fsSLO https://github.com/neilotoole/sq/releases/latest/download/sq-linux-amd64.deb && sudo apt install -y ./sq-linux-amd64.deb && rm ./sq-linux-amd64.deb $ yum localinstall -y https://github.com/neilotoole/sq/releases/latest/download/sq-linux-amd64.rpm $ sudo rpm -i https://github.com/neilotoole/sq/releases/latest/download/sq-linux-amd64.rpm

See other installation options. Be sure to install shell completion.

For help, $ sq help is your starting point. And then see the docs.

Let's get this out of the way

sq is pronounced like seek. Its query language, SLQ, is pronounced like sleek.

Feature Highlights

Some feature highlights are shown below. For more, see the docs, including the tutorial and cookbook.

Import Excel worksheet into Postgres table

Insert the contents of an Excel XLSX worksheet (from a sheet named actor) into a new Postgres table named xl_actor. Note that the import mechanism is reasonably sophisticated in that it tries to preserve data types.

$ cat ./sakila.xlsx | sq .actor --opts header=true --insert @sakila_pg9.xl_actor

Get names of all columns in a MySQL table

$ sq add mysql://sakila:$PASSWD@192.168.50.136/sakila
$ sq inspect @sakila_my -j | jq -r '.tables[] | select(.name == "actor") | .columns[] | .name'

Even easier, just get the metadata for the table you want:

$ sq inspect @sakila_my.actor -j | jq -r '.columns[] | .name'

View full metadata for a database

The --json flag to sq inspect outputs schema and other metadata in JSON. Note that the output exposes connection string details. Typically the output is piped to jq to select the interesting elements.

$ sq inspect @sakila_my --json

Execute SQL query against SQL Server, insert results to SQLite

$ sq add "sqlserver://sakila:$PASSWD@192.168.50.130?database=sakila"
$ sq add ./sakila.db --handle @sakila_sl3
$ sq sql --src=@sakila_mssql "SELECT * FROM actor WHERE first_name = 'PENELOPE'" --insert @sakila_sl3.actor_copy

Export all database tables to CSV

Get the (JSON) metadata for the active source; pipe that JSON to jq and extract the table names; pipe the table names to xargs, invoking sq once for each table, outputting a CSV file per table.

$ sq inspect -j | jq -r '.tables[] | .name' | xargs -I % sq .% --csv --output %.csv

If you instead wanted to use sql mode:

$ sq inspect -j | jq -r '.tables[] | .name' | xargs -I % sq sql 'SELECT * FROM %' --csv --output %.csv

Source commands

Commands to add, set active, list, ping, or remove sources.

$ sq src                # show active source
$ sq add ./actor.tsv    # add a source
$ sq src @actor_tsv     # set active source
$ sq ls                 # list sources
$ sq ls -v              # list sources (verbose)
$ sq ping --all         # ping all sources
$ sq rm @actor_tsv      # remove a source

Database table commands

Convenient commands that act on database tables.

Note that sq tbl copy only applies within a single database. That is, to copy a table from one database to another, use a query with --insert @target.tbl.

$ sq tbl copy .actor .actor2
$ sq tbl truncate .actor2
$ sq tbl drop .actor2

Query JSONL (e.g. log files)

JSONL output is a row of JSON per line (hence "JSON Lines"). Lots of log output is like this. We can use sq's own log output (typically in ~/.config/sq/sq.log) as an example:

{"level":"debug","time":"06:49:13.751707","caller":"cli/cli.go:100:ExecuteWith","msg":"EXECUTE: ls"}
{"level":"debug","time":"06:49:13.751838","caller":"cli/cli.go:101:ExecuteWith","msg":"Build: v0.15.0-wip cd6d90a8 2021-02-27T06:49:07-0700"}
{"level":"debug","time":"06:49:13.751851","caller":"cli/cli.go:102:ExecuteWith","msg":"Config (cfg version \"v0.14.9-wip\") from: /Users/neilotoole/.config/sq/sq.yml"}
    

Pluck out the level and msg fields from the first three lines of that input:

$ cat ~/.config/sq/sq.log | sq '.data | .[0:3] | .level, .msg'

Supported SQL Databases

sq supports input from and output to common databases.

  • Postgres
  • SQLite
  • SQL Server
  • MySQL

Other Input Formats

In addition to SQL sources, sq can import data from these formats:

  • Excel XLSX
  • CSV
  • TSV
  • JSON (beta)
  • JSONL (JSON Lines)
  • JSONA (JSON Array)
  • XML

Output Formats

sq can output to a plethora of file formats, or insert results directly to a database table.

  • SQL INSERT to Postgres, SQLite, SQL Server, MySQL
  • Excel XLSX
  • CSV
  • TSV
  • JSON
  • JSONL (JSON Lines)
  • JSONA (JSON Array)
  • XML
  • HTML
  • Markdown
  • Raw (bytes)
GitHub Docs

CHANGELOG

v0.15.0

2021/02/21
  • Shell completion
  • Bug fixes

v0.14.9

2021/02/19
  • XLSX import works with empty sheets
  • Bug fixes

v0.14.8

2021/01/03
  • Yet more bug fixes