Query staged ILOSTAT dataframes
ilostat_dataframe_queryRun a single-statement SELECT against the df_ dataframes staged by ilostat_query_indicator and ilostat_compare_geographies or stored by an earlier register_as. Inspect a dataframe with ilostat_dataframe_describe first; its column schema is what the SQL has to match. Read-only: writes, DDL, DROP, COPY, PRAGMA, ATTACH, file references, file-reading table functions, and functions that read engine settings or name a table from a string are rejected, and system catalogs (information_schema, pg_catalog, sqlite_master, duckdb_*) are denied. Breakdown versions overlap (AGE_YTHADULT_*, AGE_AGGREGATE_*, AGE_10YRBANDS_*, AGE_5YRBANDS_*; ECO_SECTOR_*, ECO_AGGREGATE_*, ECO_ISIC4_*, ECO_ISIC3_*), so filter to one version before summing. Optional register_as stores the result as a new dataframe with a fresh TTL.
Tool call template
Replace each placeholder with a value matching the input schema. Only required parameters are included; add optional parameters as needed.
{
"id": 1,
"jsonrpc": "2.0",
"method": "tools/call",
"params": {
"arguments": {
"sql": "<sql>"
},
"name": "ilostat_dataframe_query"
}
}Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes | One SELECT over df_<id> tables (DuckDB SQL: joins, aggregates, window functions, CTEs), at most 20,000 characters. BIGINT results such as COUNT or SUM of integers serialize as strings; CAST to DOUBLE for inline arithmetic. | |
| preview | No | Rows returned inline; defaults to row_limit, and a larger value is applied as row_limit. Inline rows also stop at a size budget (applied_limits.preview_chars). Set lower when register_as keeps the full result. | |
| row_limit | No | Cap on rows materialized; defaults to 1,000, or the per-call cap when that is lower. The per-call cap is 10,000, or the lower cap the deployment sets; a larger value, up to 1,000,000, is applied as that cap. A query matching more stops at the cap and row_count_capped is true; register_as keeps the full result. | |
| register_as | No | Store the result as a new dataframe under this name (df_XXXXX_XXXXX: letters and digits, five in each part; stored uppercased after df_) with a fresh TTL, to chain analyses. A result over 1,000,000 rows is refused, and storing one can evict the oldest dataframes, which evicted names. |
Output Schema
| Name | Required | Description | Default |
|---|---|---|---|
| cap | No | The row cap that bound: preview when lower than row_limit, else row_limit. | |
| rows | No | Result rows keyed by the names in columns, bounded by preview, row_limit, and an inline size budget; rows stop before the first that would pass the budget, and a value is never clipped. BIGINT values arrive as strings. | |
| error | No | Present when the call failed. Absent on success. | |
| shown | No | Rows returned inline. | |
| notice | No | Guidance when the query returned no rows, a cap withheld some, or a limit was applied below the value sent. | |
| columns | No | Column names in projection order. | |
| evicted | No | Dataframes dropped, oldest first, to keep this tenant within 1,000,000 staged rows and 100 dataframes; present only when storing the result evicted any. | |
| row_count | No | Rows the query produced. With register_as this is the exact count of the stored dataframe, which row_limit does not bound; otherwise at most row_limit, and when row_count_capped is true it is the cap, not a total. | |
| truncated | No | True when a cap withheld rows from this response. | |
| expires_at | No | ISO 8601 expiry of the new dataframe. | |
| registered_as | No | The new dataframe name, when register_as stored the result. | |
| applied_limits | No | The limits this call ran under, after any clamp. | |
| row_count_capped | No | True when the query matched more rows than row_limit; never true with register_as, which stores every row. |