Overview
sq
is the missing tool for wrangling data. A swiss-army knife for data.
sq
provides a jq
-style syntax to query, join, migrate, and export data from a variety of data sources,
such as Postgres, SQLite, SQL Server, MySQL, Excel or CSV, with the ability to fall back
to actual SQL for trickier work. In essence, sq
treats every data source as if it were a SQL database.
sq
also provides several handy commands such as inspect
, ping
, or tbl copy
.
Key Concepts
sq
is the command-line utility itself.- SLQ is the formal name of
sq
’s language, similar tojq
syntax. - Source is a specific data source such as a database instance, or Excel or CSV file etc.. A
source
has a: - Active Source is the default source upon which
sq
acts if no other source is specified. sq inspect
returns metadata about your source, such as table names or number of rows.
Read more in Concepts.
Quick start
- Install
sq
. - Add a data source. We’ll use an Excel file.
$ sq add --handle "@demo" https://sq.io/testdata/xl_demo.xlsx @demo xlsx xl_demo.xlsx
- Inspect the source:
$ sq inspect @demo HANDLE DRIVER NAME SIZE TABLES LOCATION @demo xlsx xl_demo.xlsx 9.7KB 2 https://sq.io/testdata/xl_demo.xlsx TABLE ROWS COL NAMES person 8 A, B, C, D address 3 A, B, C, D, E, F
- Run a query, getting the first three rows of the
person
table.:$ sq '@demo.person | .[0:3]' A B C D uid username email address_id 1 neilotoole neilotoole@apache.org 1 2 ksoze kaiser@soze.org 2
- Run the query again, but output in a different format:
$ sq '@demo.person | .[0:3]' --jsonl {"A": "uid", "B": "username", "C": "email", "D": "address_id"} {"A": "1", "B": "neilotoole", "C": "neilotoole@apache.org", "D": "1"} {"A": "2", "B": "ksoze", "C": "kaiser@soze.org", "D": "2"}
Next, read the tutorial.
Commands
Use sq help
to list the available commands, or consult the reference
for each command.
Available Commands:
sql Execute DB-native SQL query or statement
src Get or set active data source
add Add data source
ls List data sources
rm Remove data source
inspect Inspect data source schema and stats
ping Ping data sources
version Print sq version
driver List or manage drivers
tbl Useful table actions (copy, truncate, drop)
completion Generate shell completion script
help Show sq help
Flags:
-o, --output string Write output to <file> instead of stdout
-j, --json Output JSON
-A, --jsona Output LF-delimited JSON arrays
-l, --jsonl Output LF-delimited JSON objects
-t, --table Output text table
-X, --xml Output XML
-x, --xlsx Output Excel XLSX
-c, --csv Output CSV
-T, --tsv Output TSV
-r, --raw Output each record field in raw format without any encoding or delimiter
--html Output HTML table
--markdown Output Markdown
-h, --header Print header row in output (default true)
--pretty Pretty-print output (default true)
--insert string Insert query results into @HANDLE.TABLE. If not existing, TABLE will be created.
--src string Override the active source for this query
--driver string Explicitly specify the data source driver to use when piping input
--opts string Driver-dependent data source options when piping input
--version Print sq version
--help Show sq help
-M, --monochrome Don't colorize output
-v, --verbose Print verbose output, if applicable
Issues
File any bug reports or other issues here. When filing a bug report, submit a log file. For example, this:
SQ_LOGFILE=./sq.log sq [some command]
will create sq.log
in the current dir. That sq.log
file should be submitted with the bug report.
Config
By default, sq
stores its config in ~/.config/sq/sq.yml
.
Logging
By default, sq
debug logging is disabled. For one-time logging, this will
generate a sq.log
file in the current dir:
SQ_LOGFILE=./sq.log sq [some command]
To enable logging generally, add this line to your .bashrc
or .zshrc
etc..
export SQ_LOGFILE=~/.config/sq/sq.log
For Windows, set the SQ_LOGFILE
environment variable per the usual mechanism.