DuckDB / SQLite

DuckDB

Point rush at a DuckDB database (.duckdb or .ddb) and every table is read into the dfs list, keyed by table name:

rush run 'names(dfs$shop)' shop.duckdb
#> customers
#> orders

If a database holds a single table, it is also bound to df for convenience, so a one-table database behaves just like any other single input.

Writing to DuckDB

Write to DuckDB with -o output.duckdb. When the result is a named list (e.g. from multiple inputs), each element becomes a separate table:

echo "id,val
1,a
2,b" > a.csv
echo "id,val
3,c
4,d" > b.csv
rush convert -o combined.duckdb a.csv b.csv
rush run 'names(dfs$combined)' combined.duckdb
#> a
#> b

A single file uses the file stem as the table name:

rush convert -o penguins_db.duckdb penguins.csv
rush sql "SELECT species, COUNT(*) AS n FROM penguins_db.penguins GROUP BY species" penguins_db.duckdb
rm -f penguins_db.duckdb
#> species,n
#> Chinstrap,68
#> Gentoo,119
#> Adelie,146

Querying with SQL

A .duckdb file is attached as a schema, and its tables are accessible as schema.table:

rush sql "SELECT c.name, SUM(o.amount) AS total
          FROM shop.customers c JOIN shop.orders o ON c.id = o.cust
          GROUP BY c.name ORDER BY total DESC" shop.duckdb
#> name,total
#> Carol,65
#> Alice,55
#> Bob,10

SQLite

Files ending in .sqlite or .db are read with RSQLite. All tables are loaded into the dfs list, similar to DuckDB:

rush convert -o app.sqlite penguins.csv
rush run 'names(dfs$app)' app.sqlite
rm -f app.sqlite
#> penguins

Writing to SQLite

Like DuckDB, multiple inputs become separate tables:

rush convert -o combined.sqlite a.csv b.csv
rush run 'names(dfs$combined)' combined.sqlite
#> a
#> b

Converting between database formats

Copy all tables from one database to another:

rush convert -o copy.sqlite shop.duckdb
rush run 'names(dfs$copy)' copy.sqlite