Learn more in the sources section.
This is the driver type used to connect to the source,
csv, etc. You can specify the type explicitly
sq add, but usually
sq can detect
the driver automatically.
The source location is the URI or file path of the source, such
/Users/neilotoole/sq/xl_demo.xlsx. You specify the source location when
The handle is how
sq refers to a data source, such as
The handle must begin with
@. You specify the handle when adding a source with
The handle can also be used to specify a source group, e.g.
The active source is the source upon which
sq acts if no other source is specified.
sq requires that the first element of a query is the source handle:
$ sq '@sakila | .actor | .first_name, last_name'
But if an active source is set, you can omit the handle:
$ sq '.actor | .first_name, .last_name'
You can use
sq src to get or set the active source.
A SQL source is a source backed by a “real” DB, such as Postgres. Contrast with document source.
A document source is a source backed by a document or file such as CSV or
XLSX. Some functionality
is not available for document sources. For example,
sq doesn’t provide a mechanism to insert query
results into a CSV file. Contrast with SQL source.
The group mechanism organizes sources into groups, based on path-like names.
@dev/test, we have three
sources, but two groups,
dev. See the groups docs.
sq driver ls to view the available drivers.
If a source is
monotable, it means that the source type is really only a single table, such
as a CSV file.
sq always names that single table
data. You access that
@actor_csv | .data.
Note that not all document sources are monotable. For example, XLSX sources have multiple tables, where each worksheet is effectively equivalent to a DB table.
sq inspect returns metadata about a source. At a minimum,
is useful for a quick reminder of table and column names:
sq inspect comes into its own when used with the
--json flag, which outputs voluminous info
on the data source. It is a frequent practice to combine
with jq .
For example, to list the tables of the active source:
$ sq inspect -j | jq -r '.tables | .name' actor address category [...]
See more examples in the cookbook.
Scratch DB refers to the temporary ("scratch") database that
sq uses for under-the-hood
such as converting a document source like CSV to relational
format. By default,
uses an embedded SQLite instance for the Scratch DB.
Schema & catalog
Database implementations have the concept of a schema, which you can think of as a namespace for tables. Some databases go further and support the concept of a catalog, which is a collection of schemas. Often the terms catalog and database are used interchangeably, and, in practice, the various terms are used inconsistently and confusedly.
sqdoesn’t concern itself with clusters.
Here’s what the hierarchy looks like for Postgres (credit):
Each of the
sq DB driver implementations supports the concept of a schema in some way,
but some drivers don’t support the catalog mechanism. Here’s a summary:
|Driver||Default schema||Catalog support?|