Cookbook
This cookbook demonstrates how to perform various tasks using sq
.
- If not otherwise specified, assume the
@sakila_sl3
data source is present. See here to add that source. - The snippets were created on macOS (using
zsh
). - Some of these examples require jq .
List table names
List the tables of the active source:
$ sq inspect -j | jq -r '.tables[] | .name'
actor
address
category
[...]
List the tables of a named source (@sakila_sl3
in this example):
$ sq inspect @sakila_sl3 -j | jq -r '.tables[] | .name'
actor
address
category
[...]
Export all table data to CSV
With @sakila_sl3
as the active source:
$ sq inspect -j | jq -r '.tables[] | .name' | xargs -I % sq .% --csv --output %.csv
$ ls
actor.csv category.csv country.csv film.csv film_category.csv inventory.csv payment.csv staff.csv
address.csv city.csv customer.csv film_actor.csv film_text.csv language.csv rental.csv store.csv
The above snippet will:
- invoke
sq inspect
on the active source (@sakila_sl3
) and output in JSON - pipe that JSON to jq, and filter for just the table names
- pipe those table names to
xargs
xargs
invokessq .% --csv --output %.csv
for each table name (e.g.sq .actor --csv --output actor.csv
)- thus the content of each table is outputted in CSV format to an individual
.csv
file.
Import JSON Array to database
$ wget https://github.com/neilotoole/sq/raw/master/drivers/json/testdata/actor.jsona
$ cat actor.jsona
[1, "PENELOPE", "GUINESS", "2020-06-11T02:50:54Z"]
[2, "NICK", "WAHLBERG", "2020-06-11T02:50:54Z"]
[3, "ED", "CHASE", "2020-06-11T02:50:54Z"]
[...]
$ sq add actor.jsona
@actor_jsona jsona actor.jsona
# Insert the first 10 rows from that JSONA source into a new table "actor_jsona" in the SQLite source "@sakila_sl3"
$ sq '@actor_jsona.data | .[0:10]' --insert @sakila_sl3.actor_jsona
Inserted 10 rows into @sakila_sl3.actor_jsona
Import JSON Lines to database
$ wget https://github.com/neilotoole/sq/raw/master/drivers/json/testdata/actor.jsonl
$ cat actor.jsonl
{"actor_id": 1, "first_name": "PENELOPE", "last_name": "GUINESS", "last_update": "2020-06-11T02:50:54Z"}
{"actor_id": 2, "first_name": "NICK", "last_name": "WAHLBERG", "last_update": "2020-06-11T02:50:54Z"}
{"actor_id": 3, "first_name": "ED", "last_name": "CHASE", "last_update": "2020-06-11T02:50:54Z"}
[...]
$ sq add actor.jsonl
@actor_jsonl jsonl actor.jsonl
# Insert the first 10 rows from that JSONL source into a new table "actor_jsonl" in the SQLite source "@sakila_sl3"
$ sq '@actor_jsonl.data | .[0:10]' --insert @sakila_sl3.actor_jsonl
Inserted 10 rows into @sakila_sl3.actor_jsonl