sq
Use the root sq
cmd to execute queries against data sources.
Pipe Data
For file-based sources (such as CSV or XLSX), you can sq add
the source file,
but you can also use the UNIX pipe mechanism:
$ cat ./example.xlsx | sq .Sheet1
Similarly, you can inspect
:
$ cat ./example.xlsx | sq inspect
Output
sq
can output results in many formats.
Reference
sq is a swiss-army knife for wrangling data.
Use sq to query Postgres, SQLite, SQLServer, MySQL, CSV, TSV
and Excel, and output in text, JSON, CSV, Excel, HTML, etc., or
output to a database table.
You can query using sq's own jq-like syntax, or in native SQL.
Execute "sq completion --help" for instructions to install shell completion.
More at https://sq.io
Usage:
sq [command]
Examples:
# pipe an Excel file and output the first 10 rows from sheet1
$ cat data.xlsx | sq '.sheet1 | .[0:10]'
# add Postgres source identified by handle @sakila_pg
$ sq add --handle=@sakila_pg 'postgres://user:pass@localhost:5432/sakila'
# add SQL Server source; will have generated handle @sakila_mssql
$ sq add 'sqlserver://user:pass@localhost?database=sakila'
# list available data sources
$ sq ls
# ping all data sources
$ sq ping all
# set active data source
$ sq src @sakila_pg
# get specified cols from table address in active data source
$ sq '.address | .address_id, .city, .country'
# get metadata (schema, stats etc) for data source
$ sq inspect @sakila_pg
# get metadata for a table
$ sq inspect @pg1.person
# output in JSON
$ sq -j '.person | .uid, .username, .email'
# output in table format (with header)
$ sq -th '.person | .uid, .username, .email'
# output in table format (no header)
$ sq -t '.person | .uid, .username, .email'
# output to a HTML file
$ sq --html '@sakila_sl3.actor' -o actor.html
# join across data sources
$ sq '@my1.person, @pg1.address | join(.uid) | .username, .email, .city'
# insert query results into a table in another data source
$ sq --insert=@pg1.person '@my1.person | .username, .email'
# execute a database-native SQL query, specifying the source
$ sq sql --src=@pg1 'SELECT uid, username, email FROM person LIMIT 2'
# copy a table (in the same source)
$ sq tbl copy @sakila_sl3.actor .actor2
# truncate tables
$ sq tbl truncate @sakila_sl3.actor2
# drop table
$ sq tbl drop @sakila_sl3.actor2
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
Use "sq [command] --help" for more information about a command.