rush sql "SELECT species, bill_length_mm, body_mass_g FROM penguins WHERE body_mass_g > 5500 ORDER BY body_mass_g DESC" penguins.csv
#> species,bill_length_mm,body_mass_g
#> Gentoo,49.2,6300
#> Gentoo,59.6,6050
#> Gentoo,48.8,6000
#> Gentoo,51.1,6000
#> Gentoo,49.8,5950
#> Gentoo,45.2,5950
#> Gentoo,55.1,5850
#> Gentoo,48.4,5850
#> Gentoo,49.3,5850
#> Gentoo,48.6,5800
#> Gentoo,49.5,5800
#> Gentoo,50.4,5750
#> Gentoo,49.8,5700
#> Gentoo,49.6,5700
#> Gentoo,50.2,5700
#> Gentoo,50,5700
#> Gentoo,50,5700
#> Gentoo,54.3,5650
#> Gentoo,49.5,5650
#> Gentoo,47.8,5650
#> Gentoo,50.8,5600
#> Gentoo,55.9,5600
#> Gentoo,49,5550
#> Gentoo,50.4,5550
#> Gentoo,50.7,5550
#> Gentoo,50.5,5550
#> Gentoo,50,5550
#> Gentoo,52.1,5550rush sql
The sql command runs a DuckDB query directly against your files, with no import step. Each file becomes a relation named after the file, so you can reference it right in the query.
Usage
rush sql [options] [<query>] [--] [<file>...]Basic queries
Run a SQL query against one or more files:
Aggregations
DuckDB’s full SQL is available, including aggregations, window functions, and CTEs:
rush sql "SELECT species,
COUNT(*) AS n,
ROUND(AVG(body_mass_g)) AS avg_mass,
MIN(bill_length_mm) AS min_bill,
MAX(bill_length_mm) AS max_bill
FROM penguins
GROUP BY species
ORDER BY avg_mass DESC" penguins.csv
#> species,n,avg_mass,min_bill,max_bill
#> Gentoo,119,5092,40.9,59.6
#> Chinstrap,68,3733,40.9,58
#> Adelie,146,3706,32.1,46Querying Parquet files
Parquet files are registered via DuckDB’s read_parquet, so large-file queries run without loading everything into memory:
rush sql "SELECT carrier, COUNT(*) AS flights, ROUND(AVG(arr_delay), 1) AS avg_delay
FROM flights
GROUP BY carrier
ORDER BY avg_delay DESC" flights.parquet
#> carrier,flights,avg_delay
#> WN,1,50
#> AA,3,33.7
#> DL,3,7
#> UA,3,-4.3Querying DuckDB databases
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,10Querying standard input
Use - to read CSV from stdin as a relation named stdin:
echo "item,price,qty
apple,1.20,10
banana,0.80,25
cherry,3.50,5" | rush sql "SELECT item, price * qty AS total FROM stdin ORDER BY total DESC" -
#> item,total
#> banana,20
#> cherry,17.5
#> apple,12Joining multiple files
Pass multiple files and each becomes a relation you can join:
echo "species,habitat
Adelie,coastal
Gentoo,subantarctic
Chinstrap,rocky" > habitat.csv
rush sql "SELECT p.species, h.habitat, ROUND(AVG(p.body_mass_g)) AS avg_mass
FROM penguins p JOIN habitat h ON p.species = h.species
GROUP BY p.species, h.habitat
ORDER BY avg_mass DESC" penguins.csv habitat.csv
rm -f habitat.csv
#> species,habitat,avg_mass
#> Gentoo,subantarctic,5092
#> Chinstrap,rocky,3733
#> Adelie,coastal,3706Saving SQL results
Pipe a SQL result to Parquet or another file:
rush sql -o efficient.parquet "SELECT * FROM penguins WHERE body_mass_g > 4000" penguins.csv
rush run 'nrow(df)' efficient.parquet
#> 167Piping with SQL
The result of a query is an ordinary data frame, so everything composes:
rush run 'head(df, 5)' penguins.csv | rush sql "SELECT species, body_mass_g FROM stdin WHERE body_mass_g > 4000" -
#> species,body_mass_gDry run
See the generated script that runs the query:
rush sql -n "SELECT COUNT(*) FROM penguins" penguins.csv
#> #!/usr/bin/env -S ir run
#> #| packages:
#> #| - rush
#>
#> rush::init(
#> dpi = 300L
#> )
#>
#> con <- DBI::dbConnect(duckdb::duckdb())
#> on.exit(DBI::dbDisconnect(con, shutdown = TRUE), add = TRUE)
#> invisible(DBI::dbExecute(con, "CREATE VIEW \"penguins\" AS SELECT * FROM read_csv_auto('penguins.csv')"))
#> result <- DBI::dbGetQuery(con, "SELECT COUNT(*) FROM penguins")
#>
#> rush::write(result)Help
rush sql -h
#> rush: Query files with SQL (DuckDB)
#>
#> Usage:
#> rush sql [options] [<query>] [--] [<file>...]
#>
#> Arguments:
#> <query> DuckDB SQL query to run. Its result is printed or
#> written out.
#> <file> File(s) to expose to the query, each as a DuckDB
#> relation named after the file's base name (lowercased,
#> non-alphanumeric chars become underscores; names
#> starting with a digit are prefixed with 'x'). The
#> relation is double-quoted in the generated SQL, so
#> refer to it as e.g. SELECT * FROM "x2024". CSV files
#> are read with read_csv_auto, Parquet with read_parquet,
#> and a '.duckdb' database is attached so its tables are
#> addressed as name.table. Use '-' to read CSV from
#> standard input as a relation named 'stdin'. Files with
#> the same base name in different directories collide.
#>
#> Setup options:
#> -l, --library <name> Libraries to load.
#> -t, --tidyverse Enter the Tidyverse.
#>
#> Saving options:
#> --dpi <int> Plot resolution [default: 300].
#> --head <int> Limit output rows.
#> --height <num> Plot height.
#> -o, --output <str> Output file.
#> -D, --output-delimiter <str> Output delimiter (overrides -d).
#> -O, --output-format <format> Output format [default: auto].
#> --output-indent <int> Indentation level (JSON, YAML) [default: 2].
#> --output-record <str> Record element name (XML, TOML) [default: record].
#> --output-root <str> Root element name (XML) [default: root].
#> --output-sheet <str> Excel sheet name to write.
#> --units <str> Plot size units [default: in].
#> -w, --width <num> Plot width.
#>
#> General options:
#> -n, --dry-run Only print generated script.
#> -h, --help Show this help.
#> -I, --no-ir Run with Rscript, not ir.
#> -R, --no-rush Inline all code (no rush dep).
#> --seed <int> Seed random number generator.
#> -v, --verbose Be verbose.
#> --version Show version.