geoparquet_run_sql
Run ad-hoc read-only SQL on remote GeoParquet datasets to answer questions no typed tool supports, such as joins, HAVING, and window functions.
Instructions
Run one read-only SELECT against the datasets in scope, for questions the other tools do not have a shape for.
WHEN TO USE IT. Last, not first. The typed tools push their filters into the remote Parquet file by construction; an ad-hoc query pushes down only what its WHERE clause happens to express, so a query that forgets a bbox predicate can read gigabytes to answer something geoparquet_aggregate_attribute would have answered in kilobytes. Reach for it for genuine gaps: a join between two datasets, a HAVING clause, a window function, a self-join.
WHAT YOU CAN QUERY. Each dataset in scope is a table named exactly as the dataset is — overture_places, overture_divisions, overture_buildings — and those are the only tables that exist. There is no way to name a file: table functions such as read_parquet are refused, and so is anything that is not a single SELECT. That is a perimeter, not a lint rule.
WRITING A FAST ONE. Constrain bbox explicitly, as four comparisons on its members, because that is the form Parquet statistics can prune on:
SELECT categories.primary AS category, count(*) AS n FROM overture_places WHERE bbox.xmin <= 2.40 AND bbox.xmax >= 2.30 AND bbox.ymin <= 48.88 AND bbox.ymax >= 48.85 GROUP BY 1 ORDER BY n DESC
Writing that filter with a geometry function instead would be correct and would read the entire file, because the Parquet reader cannot see through it. Select named columns rather than *, for the same reason: unread columns are unfetched.
PARAMETERS. sql: one SELECT statement. max_rows: row ceiling, applied as an outer LIMIT. Hard, and capped at 1000. max_bytes: byte ceiling. Reported, not pre-emptive — see below.
WHAT COMES BACK. rows, tables_read, the executed_sql actually run, the scan block, and byte_budget_exceeded. That last one is a verdict after the fact, not a brake: DuckDB cannot abort a scan on bytes already transferred, so the rows are returned — they have been paid for — and the flag tells you the query was too expensive and the next one should be narrower. The row ceiling, by contrast, is enforced.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes | ||
| max_rows | No | ||
| max_bytes | No |
Output Schema
| Name | Required | Description | Default |
|---|---|---|---|
No arguments | |||