Concepts

sq #

sq is the command-line utility itself. It is free/libre open-source software, available under the MIT License. The code is available on GitHub. sq was created by Neil O’Toole.

SLQ #

SLQ is the formal name of sq’s query language, similar to jq’s syntax. The Antlr grammar is available on GitHub.

Source #

A source is a data source such as a database instance (SQL source), or an Excel or CSV file (document source). A source has a driver type, location and handle. Some driver types accept options via sq add --opts.

Driver Type #

The type is the driver type used to connect to the source, e.g. postgres, sqlserver, csv, etc. You can specify the type explicitly when invoking sq add, but usually sq can infer the type from the location.

Location #

The source location is the URI or file path of the source, such as postgres://sakila:****@localhost/sakila or /Users/neilotoole/sq/xl_demo.xlsx. You specify the source location when invoking sq add.

Handle #

The handle is how sq refers to a data source, such as @sakila or @customer_csv. A handle must begin with @. You specify the handle when adding a source with sq add.

Active Source #

An active source is the source upon which sq acts if no other source is specified.

By default, 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.

SQL Source #

A SQL Source is a source backed by a “real” DB, such as Postgres. Contrast with document source.

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.

Driver #

A driver is a software component implemented by sq for each data source type. For example, Postgres or CSV.

Use sq driver ls to view the available drivers.

Monotable #

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 table like this: @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.

Metadata #

sq inspect returns metadata about a source. At a minimum, sq inspect is useful for a quick reminder of table and column names:

$ sq inspect
HANDLE      DRIVER    NAME    FQ NAME        SIZE    TABLES  LOCATION
@sakila_pg  postgres  sakila  sakila.public  14.6MB  28      postgres://sakila:xxxxx@localhost/sakila

TABLE                       ROWS   COL NAMES
actor                       200    actor_id, first_name, last_name, last_update
actor_info                  200    actor_id, first_name, last_name, film_info
address                     603    address_id, address, address2, district, city_id, postal_code, phone, last_update

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 sq inspect 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 #

Scratch DB refers to the temporary ("scratch") database that sq uses for under-the-hood activity such as converting a document source like CSV to relational format. By default, sq uses an embedded SQLite instance for the Join DB.

Join DB #

Join DB is similar to Scratch DB, but is used for cross-source joins. By default, sq uses an embedded SQLite instance for the Join DB.