query_resource
Run read-only SQL queries on cached open data resources, including CSV, TSV, XLSX, JSON, and ODS files. Supports SELECT statements with row limits for safe analysis.
Instructions
Run an ad-hoc read-only SQL query against a cached resource via DuckDB.
Power-user escape hatch when filter_resource / aggregate_resource don't cover the case. The cached resource is exposed as the in-memory table 'data'. SQL is DuckDB dialect — see https://duckdb.org/docs/sql/introduction. Supports CSV, TSV, XLSX, XLS, JSON, and ODS (auto-converted to CSV).
Safety:
Only SELECT/WITH statements (CTEs allowed); multi-statement blocked.
DDL/DML keywords (INSERT/UPDATE/DELETE/DROP/CREATE/ALTER/COPY/EXPORT/ IMPORT/TRUNCATE/GRANT/REVOKE/PRAGMA/SET/LOAD/INSTALL/ATTACH/DETACH/ VACUUM/ANALYZE) rejected outright.
Sandboxed: the resource is materialized in memory and external access is disabled, so table functions (read_text/read_csv/glob/...) cannot read local files or reach the network.
Row cap always applied via outer wrapper.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| url | Yes | Direct URL to the file (CKAN resource 'url' field). | |
| format | Yes | Format declared in CKAN. Accepts: csv, tsv, xlsx, xls, json, ods. | |
| sql | Yes | Read-only SQL query against table 'data'. Only SELECT/WITH allowed; DDL/DML rejected. The query is wrapped in 'SELECT * FROM (<your sql>) LIMIT <limit>' so a row cap is always enforced. Example: "SELECT Estatus, COUNT(*) c FROM data WHERE Año=2026 AND Mes='Abril' GROUP BY Estatus ORDER BY c DESC" | |
| limit | No | Hard cap on returned rows (1-1000). |
Output Schema
| Name | Required | Description | Default |
|---|---|---|---|
| error | No | ||
| hint | No | ||
| source_url | No | ||
| format | No | ||
| cache | No | ||
| sql_executed | No | ||
| rows_returned | No | ||
| columns | No | ||
| rows | No |