execute_sql
Execute a read-only ClickHouse SQL query and return the results. Send the whole analysis as ONE statement — arbitrarily complex read-only SQL is supported and preferred: multiple CTEs (WITH), self-joins, window functions, UNION ALL across horizons, and subqueries all run in a single call. There is no statement-complexity limit, and you do NOT need store_result/scratch tables to build up a multi-step result; reach for those only when you want to reuse an expensive intermediate across several later queries. Results are capped at 10000 rows returned (the cap applies to the rows handed back, not to how much the query may scan or aggregate). RESULT SHAPE: the response always leads with metadata — row_count (the FULL result size), preview_rows, and time_windows (the min/max of every time column across ALL rows) — followed by the rows. Results over the preview size (100 rows or 20 KB, whichever comes first, so wide rows show fewer) return only their first rows inline plus a result_id and result_url: the full result is parked server-side for 24h. NEVER re-run a query to see more of it. To aggregate/filter further, write new SQL (cheapest). For specific rows, fetch_result_rows(result_id, offset, limit). To chart EVERYTHING, pass result_id to build_chart — never retype rows. If you can execute code, download result_url (CSV) to a file and analyse it there — the data then costs you no context at all. When truncated=true the underlying query hit the row cap and even the parked result is incomplete: aggregate to daily/weekly/monthly. LIVE URL: every query over consolidated/semantic/calculated also returns a live_url. A plain GET on it re-runs THIS query and returns the latest rows as JSON — no API key, the id is the access. Point client-side chart code at it and the chart keeps updating wherever it is pasted (a static HTML file, an embedded widget, the user's own dashboard). It lasts 24h; pin_live_query makes it durable. Hand it over whenever a user wants a chart that stays current, and tell them anyone with the link can read it. VARIABLES: put {name:Type} placeholders in the SQL ({region:String}, {min_mw:Float64}, {regions:Array(String)}, {day:Date}) and give each a default in params — the query runs with those, and the live_url then accepts any value for each as a query string (?region=VIC1), checked against its type and bound by ClickHouse, never pasted into the SQL. $__timeFilter(ts) / $__timeGroup(ts) make it take from/to too. When a user wants a link they can point at other regions, units or windows, parameterise it this way rather than handing them one fixed query. For schema discovery do NOT query information_schema or system.* — use get_table_schema (one table's columns with types/units/notes) or search_columns (find columns by keyword); information_schema is not available on this connection. For data-coverage / date-range / latest-date questions ("how fresh is X", "what range does table Y cover", "earliest/latest date") do NOT hand-roll a scan — min/max(SETTLEMENTDATE) or DISTINCT date over a consolidated.* table reads the whole table and is rejected by a per-query resource cap. Use get_data_freshness (latest interval + NEM date) or list_tables (per-table data_available_from / data_available_to) instead. Every analytic query needs a time-column WHERE filter so ClickHouse can prune the primary key. The full NEM SQL reference (warehouse model, table mapping, dispatch conventions, formulas, ClickHouse syntax gotchas, join rules) is in the server's initial instructions — consult it before composing SQL.
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": "execute_sql"
}
}Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes | Read-only ClickHouse SQL. Must query only `consolidated.*`, `semantic.*`, or system lookup tables. Mutations are blocked at the database level. Follow the NEM SQL rules in your system prompt — especially index-safe date filters and the ClickHouse-specific syntax requirements. | |
| time | No | The time range when the SQL uses the `$__time…` macros: `{"default": "now-30d", "min_interval": "5m", "max_span": "1y"}`. | |
| params | No | Defaults for the variables the SQL reads. A variable is any ClickHouse placeholder in it — `{region:String}`, `{min_mw:Float64}`, `{regions:Array(String)}`, `{day:Date}` — and needs a default here: `{"region": "SA1", "min_mw": 100, "regions": ["SA1", "VIC1"]}`. The query runs with these, and its `live_url` then accepts any value for each as a query string (`?region=VIC1`), checked against its type and bound by ClickHouse — never pasted into the SQL. A value may also be an object with `default`, `options` (the only values allowed), `label` and `description`. The time macros (`$__timeFilter(ts)`, `$__timeGroup(ts)`) need no default: they read the link's `from`/`to` (last 7 days unless `time` says otherwise). | |
| question_id | No | UUID returned by an earlier `start_question` call. When provided, this tool call is attributed to that question; it is also **required to read your scratch tables** — pass it whenever the SQL references `scratch.*`. |