Sources
A source is an individual data source, such as a database connection, or a CSV or Excel document.
Overview
A source has three main elements:
- driver: a driver type such as
postgres
, orcsv
. - handle: such as
@sakila_pg
. A handle always starts with@
. The handle is used to refer to the data source. A handle can also specify a group, e.g.@prod/sakila
. - location: such as
postgres://user:p_ssW0rd@@localhost/sakila
. For a document source, location may just be a file path, e.g./Users/neilotoole/sakila.csv
.
sq
prints a location containing security credentials (such as the password in the
postgres string above), the credentials are redacted. Thus, that location string
would be printed as postgres://user:xxxxx@@localhost/sakila
.sq
provides a set of commands to add, list, rename
and remove sources.
Add
To add a source, use sq add
. The command packs in a log of functionality:
see the docs for detail.
# Add a postgres database
$ sq add postgres://sakila:p_ssW0rd@localhost/sakila
@sakila_pg postgres sakila@localhost/sakila
# Add a CSV source, specifying the handle.
$ sq add ./actor.csv -h @actors
List sources
Use sq ls
to list sources.
$ sq ls
@dev/customer csv customer.csv
@dev/sales csv sales.csv
@prod/customer csv customer.csv
@prod/sales csv sales.csv
In practice, colorization makes things a little easier to parse.
Note that the @dev/sales
source is highlighted. This means that it’s
the active source (you can get the active source
at any time via sq src
).
Pass the -v
(--verbose
) flag to see more detail:
$ sq ls -v
HANDLE ACTIVE DRIVER LOCATION OPTIONS
@dev/customer csv /Users/neilotoole/sakila-csv/customer.csv
@dev/sales active csv /Users/neilotoole/sakila-csv/sales.csv
@prod/customer csv /Users/neilotoole/sakila-csv/customer.csv
@prod/sales csv /Users/neilotoole/sakila-csv/sales.csv
sq ls
operates on the active group. By default, this is the /
root group.
So, when the default group is /
, then sq ls
is equivalent to sq ls /
.
But just like the UNIX ls
command, you can supply an argument to sq ls
to
list the sources in that group.
# List sources in the "prod" group.
$ sq ls prod
@prod/customer csv customer.csv
@prod/sales csv sales.csv
List groups
Use sq ls -g
(--group
) to list groups instead of sources.
$ sq ls -g
/
dev
prod
See more detail by adding -v
:
Like the plain sq ls
command, you can pass an argument to ls -g
to
see just the subgroups of the argument.
$ sq ls -gv prod
GROUP SOURCES TOTAL SUBGROUPS TOTAL ACTIVE
prod 2 2
Active source
The 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 be 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'
Use sq src
to get or set the active source.
# Get active source
$ sq src
@sakila_sl3 sqlite3 sakila.db
# Set active source
$ sq src @sakila_pg12
@sakila_pg12 postgres sakila@192.168.50.132/sakila
If no active source is set (like when you first start using sq
), and
you sq add
a source, that source becomes the active source.
When you sq rm
the active source, there will no longer be an active source.
Like the active source, there is an active group. Use
the equivalent sq group
command to get or set the active group.
Remove
Use sq rm
to remove a source (or group of sources).
sq rm
only removes the reference to the source
from sq
’s configuration.
It doesn’t do anything destructive to the source itself.# Remove a single source.
$ sq rm @sakila_pg
# Remove multiple sources at once.
$ sq rm @sakila_pg @sakila_sqlite
# Remove all sources in the "dev" group.
$ sq rm dev
# Remove a mix of sources and groups.
$ sq rm @prod/customer staging
Move
Use sq mv
to move (rename) sources and groups. sq mv
works analogously
to the UNIX mv
command, where source handles are equivalent to files,
and groups are equivalent to directories.
# Rename a source
$ sq mv @dev/sales @dev/europe/sales
@dev/europe/sales csv sales.csv
# Move a source into a group (the group need not exist beforehand).
$ sq mv @dev/customer dev/europe
@dev/europe/customer csv customer.csv
# Rename a group (and by extension, rename all of the group's sources).
$ sq mv dev/europe dev/europa
dev/europa
Ping
Use sq ping
to check the connection health of your sources.
If invoked without argumetns, sq ping
pings the active source. Otherwise, supply
a list of sources or groups to ping.
# Ping the active source.
$ sq ping
# Ping all sources.
$ sq ping /
# Ping @sakila_my, and sources in the "prod" and "staging" groups
$ sq ping @sakila_my prod staging
Groups
If you find yourself dealing large numbers of sources, sq
provides a
simple mechanism to structure groups of sources. A typical handle looks like @sales
.
But if you use a path structure in the handle like @prod/sales
,
sq
interprets that prod
path as a group.
For example, let’s say you had two databases, customer
and sales
, and two
environments, dev
and prod
. You might naively add sources @dev_customer
,
@dev_sales
, @prod_customer
, and @prod_sales
.
# This example is using a CSV data source, but it could be postgres, mysql, etc.
$ sq ls
@dev_customer csv customer.csv
@dev_sales csv sales.csv
@prod_customer csv customer.csv
@prod_sales csv sales.csv
Now, if you have dozens (or hundreds) of sources, interacting with them
becomes burdensome. Enter the groups mechanism. Let’s add these sources instead:
@dev/customer
, @dev/sales
, @prod/customer
, @prod/sales
.
$ sq ls
@dev/customer csv customer.csv
@dev/sales csv sales.csv
@prod/customer csv customer.csv
@prod/sales csv sales.csv
So, the _
char has been replaced with /
… what’s the big difference you ask?
sq
interprets /
-separated path values in the handle as groups. By default,
you start out in the root group, represented by /
. Use sq group
to see
the active group:
$ sq group
/
Now, let’s set the active group to dev
, and note the different behavior of sq ls
:
# Set the active group to "dev".
$ sq group dev
dev
# Now "sq ls" will only list the sources under "dev".
$ sq ls
@dev/customer csv customer.csv
@dev/sales csv sales.csv
You can use sq group /
to reset the active group to the root group. But you can
also list the sources in a group without changing the active group:
$ sq ls prod
@prod/customer csv customer.csv
@prod/sales csv sales.csv
If you want to list the groups (as opposed to listing sources), use ls -g
:
# Equivalent to "sq ls --group"
$ sq ls -g
/
dev
prod
As you can see above, there are three groups: /
(the root group), dev
, and prod
.
You’re not restricted to one level of grouping. A handle such as @mom_corp/prod/europe/sales
is perfectly valid, and the commands work intuitively. For example, to list all the subgroups
of mom_corp/prod
:
$ sq ls -g mom_corp/prod
mom_corp/prod/europe
mom_corp/prod/na
mom_corp/prod/africa
When you have lots of sources and groups, use sq ls -gv
(--group --verbose
)
to see more detail on the hierarchical structure.
$ sq ls -gv
GROUP SOURCES TOTAL SUBGROUPS TOTAL ACTIVE
/ 4 2 2
dev 2 2 active
prod 2 2
Here’s a real-world example:
If you want to get really crazy, try the JSON output (sq ls -gj
).
You may have noticed by now that groups are implicit. A group “exists” when there exists a source that has the group’s path in the handle. Thus, there is no command to “add a group”. However, you can rename (move) and remove groups.
# Rename a group, i.e. rename all the sources in the group.
$ sq mv old/group new/group
# Remove a group, i.e. remove all the sources in the group.
$ sq rm my/group
These commands are effectively batch operations on the sources in each group.