sq add
Use sq add
to add a data source. The source can be a SQL database, or a document
such as a CSV or Excel file. This action will add an entry to sq
’s
config file.
If you later want to change the source, generally the easiest solution is to
sq rm @handle
and then sq add
again. However, you can also directly edit
the config file (e.g. vi ~/.config/sq/sq.yml
).
A data source has three main elements:
driver type
: such aspostgres
, orcsv
. You may also see this referred to as the source type or simply type.handle
: such as@sakila_pg
. A handle always starts with@
. The handle is used to refer to the data source.location
: such aspostgres://user:p_ssW0rd@@localhost/sakila
. For a document source, location may just be a file path, e.g./Users/neilotoole/sakila.csv
.
The format of the command is:
sq add [--handle HANDLE] [--driver DRIVER] [--active] [--opts OPTS] LOCATION
For example, to add a postgres data source:
$ sq add postgres://sakila:p_ssW0rd@localhost/sakila
@sakila_pg postgres sakila@localhost/sakila
Note that flags can generally be omitted. If --handle
is omitted,
sq
will generate a handle. In the example above, the generated handle
is @sakila_pg
. Usually --driver
can also be omitted, and sq
will determine the driver type. The --active
flag immediately sets
the newly-added source as the active source (this also happens regardless if there is
not currently an active source).
To add a document source, you can generally just add the file path:
sq add ~/customers.csv
Password visibility
In the Postgres example above, the location string includes the database password. This is a
security hazard, as the password value is visible on the command line, and in
shell history etc. You can use the --password
/ -p
flag to be prompted
for the password.
$ sq add 'postgres://user@localhost/sakila' -p
Password: ****
You can also read the password from a file or a shell variable. For example:
# Add a source, but read password from an environment variable
$ export PASSWORD='open:;"_Ses@me'
$ sq add 'postgres://user@localhost/sakila' -p <<< $PASSWORD
# Same as above, but instead read password from file
$ echo 'open:;"_Ses@me' > password.txt
$ sq add 'postgres://user@localhost/sakila' -p < password.txt
Reference
Add data source specified by LOCATION, optionally identified by @HANDLE.
Usage:
sq add [--handle @HANDLE] LOCATION [flags]
Examples:
When adding a data source, LOCATION is the only required arg.
# Add a postgres source with handle "@sakila_pg"
$ sq add -h @sakila_pg 'postgres://user:pass@localhost/sakila'
The format of LOCATION varies, but is generally a DB connection string, a
file path, or a URL.
DRIVER://USER:PASS@HOST:PORT/DBNAME
/path/to/local/file.ext
https://sq.io/data/test1.xlsx
If flag --handle is omitted, sq will generate a handle based
on LOCATION and the source driver type.
It's a security hazard to expose the data source password via
the LOCATION string. If flag --password (-p) is set, sq prompt the
user for the password:
$ sq add 'postgres://user@localhost/sakila' -p
Password: ****
However, if there's input on stdin, sq will read the password from
there instead of prompting the user:
# Add a source, but read password from an environment variable
$ export PASSWD='open:;"_Ses@me'
$ sq add 'postgres://user@localhost/sakila' -p <<< $PASSWD
# Same as above, but instead read password from file
$ echo 'open:;"_Ses@me' > password.txt
$ sq add 'postgres://user@localhost/sakila' -p < password.txt
Flag --opts sets source-specific options. Generally, opts are relevant
to document source types (such as a CSV file). The most common
use is to specify that the document has a header row:
$ sq add actor.csv --opts=header=true
Use query string encoding for multiple options, e.g. "--opts a=b&x=y".
If flag --driver is omitted, sq will attempt to determine the
type from LOCATION via file suffix, content type, etc.. If the result
is ambiguous, explicitly specify the driver type.
$ sq add --driver=tsv ./mystery.data
Available source driver types can be listed via "sq drivers". At a
minimum, the following drivers are bundled:
sqlite3 SQLite
postgres PostgreSQL
sqlserver Microsoft SQL Server / Azure SQL Edge
mysql MySQL
csv Comma-Separated Values
tsv Tab-Separated Values
json JSON
jsona JSON Array: LF-delimited JSON arrays
jsonl JSON Lines: LF-delimited JSON objects
xlsx Microsoft Excel XLSX
More examples:
# Add a source, but prompt user for password
$ sq add 'postgres://user@localhost/sakila' -p
Password: ****
# Explicitly set flags
$ sq add --handle=@sakila_pg --driver=postgres 'postgres://user:pass@localhost/sakila'
# Same as above, but with short flags
$ sq add -h @sakila_pg --d postgres 'postgres://user:pass@localhost/sakila'
# Add a SQL Server source; will have generated handle @sakila_mssql or similar
$ sq add 'sqlserver://user:pass@localhost?database=sakila'
# Add a sqlite db
$ sq add ./testdata/sqlite1.db
# Add an Excel spreadsheet, with options
$ sq add ./testdata/test1.xlsx --opts=header=true
# Add a CSV source, with options
$ sq add ./testdata/person.csv --opts=header=true
# Add a CSV source from a URL (will be downloaded)
$ sq add https://sq.io/testdata/actor.csv
Flags:
-d, --driver string Explicitly specify the data source driver to use
--opts string Driver-dependent data source options
-h, --handle string Handle for the source
-p, --password Read password from stdin or prompt
--skip-verify Don't ping source before adding it
-j, --json Output JSON
--help help for add
Global Flags:
-M, --monochrome Don't colorize output
-v, --verbose Print verbose output, if applicable