Querying with DuckDB SQL

DuckDB 61,228 queries a Parquet 129 file in place: name the file where a table would go, and there is nothing to load or import. BookNest's buyer wants to know which in-stock books give readers the most pages for their money:

query.sql: price per 100 pages, straight from the Parquet fileSQL

-- Step 3: query the Parquet file in place. Which in-stock books give most pages per dollar?
SELECT title, genre, price, pages,
       round(price / pages * 100, 2) AS usd_per_100_pages
FROM 'books.parquet'
WHERE in_stock
ORDER BY usd_per_100_pages;

Output

┌────────────────────────────┬─────────────────┬──────────────┬───────┬───────────────────┐
│           title            │      genre      │    price     │ pages │ usd_per_100_pages │
│          varchar           │     varchar     │ decimal(6,2) │ int16 │      double       │
├────────────────────────────┼─────────────────┼──────────────┼───────┼───────────────────┤
│ The Clockmaker's Paradox   │ Science Fiction │        16.20 │   344 │              4.71 │
│ The Quiet Harbor           │ Fiction         │        14.99 │   312 │               4.8 │
│ Patterns of the Deep Web   │ Technology      │        39.50 │   428 │              9.23 │
│ Small Steps to Big Summits │ Travel          │        18.75 │   198 │              9.47 │
│ Gardens in Glass           │ Home and Garden │        21.30 │   176 │              12.1 │
└────────────────────────────┴─────────────────┴──────────────┴───────┴───────────────────┘

Run it with duckdb < query.sql. Two details are worth noticing. DuckDB read the declared types from the file (decimal(6,2), int16) without being told. And dividing the decimal price by the integer page count produced a double, so the result shows 4.8 and 12.1 rather than two fixed decimals; cast back with ::DECIMAL(6,2) when exact money formatting matters. Querying Files with DuckDB queries whole folders of such files at once.

MENU