Query, aggregate & SQL
Read rows with filters, compute grouped aggregates, or run raw SQL - the three read paths and when to use each.
Three ways to read a table, in escalating power:
| Need | Tool |
|---|---|
| Read with filters, sort, paginate | query_rows (the default) |
| Count / sum / avg with grouping | aggregate |
| Joins, subqueries, CTEs, windows | execute_sql |
Query rows
query_rows is the default way to read data: pick columns, filter, sort, and
paginate.
eddytor query "SELECT product_id, name, price FROM \`eddytor\`.\`sales\`.\`orders\` WHERE status = 'active' ORDER BY price DESC" --limit 20eddytor query uses Flight SQL - set eddytor config set-flight-url first.
{
"table": "eddytor.cfg_xxx.<uuid>_products",
"columns": ["product_id", "name", "price"],
"filter": "status = 'active' AND price > 50",
"order_by": "price DESC",
"limit": 20
}- Filter - standard SQL
WHERE:=,!=,LIKE,IN (...),IS NULL,BETWEEN. - Order -
"price DESC"or"category ASC, price DESC".
Heads up
"name = 'Widget'"), numbers
don't ("price > 50"). For NULLs use IS NULL / IS NOT NULL, never = NULL.
Dates are ISO: "order_date > '2026-01-15'".Aggregate
aggregate computes COUNT / SUM / AVG / MIN / MAX with optional grouping and
filtering - without writing SQL:
{
"table": "eddytor.cfg_xxx.<uuid>_products",
"aggregations": ["COUNT(*)", "AVG(price)"],
"group_by": ["category"],
"filter": "status != 'deleted'"
}Returns one row per group with the requested aggregates. The filter uses the
same SQL WHERE syntax as query_rows.
Heads up
aggregate operates on rows within one table - it can't
aggregate across tables or count tables. To count tables use
list_tables; for
cross-table work use raw SQL with a join (below).Raw SQL
execute_sql runs raw SQL (powered by DataFusion) for anything query_rows and
aggregate can't express - joins, subqueries, CTEs, window functions.
eddytor query "SELECT category, COUNT(*) AS cnt FROM \`eddytor\`.\`sales\`.\`orders\` GROUP BY category" --limit 100SELECT p.`category`, COUNT(*) AS cnt
FROM `eddytor`.`cfg_xxx`.`<uuid>_products` p
WHERE p.`status` = 'active'
GROUP BY p.`category`
HAVING COUNT(*) > 5Heads up
execute_sql is passed straight to the engine untransformed, so an unquoted
dot is a parse error. Correct: FROM `eddytor`.`cfg_xxx`.`products`. Wrong:
FROM eddytor.cfg_xxx.products. Get exact names from
list_tables. (Over
Flight SQL / ADBC, identifiers use double quotes instead:
"catalog"."schema"."table".)execute_sql is read-oriented analysis - for mutations use
merge_rows.