Skip to main content
Glama

NEM Explorer (ScadaMiner)

execute_sql

Read-onlyIdempotent

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

NameRequiredDescriptionDefault
sqlYesRead-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.
timeNoThe time range when the SQL uses the `$__time…` macros: `{"default": "now-30d", "min_interval": "5m", "max_span": "1y"}`.
paramsNoDefaults 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_idNoUUID 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.*`.

Schema Changelog

Changes observed during successful MCP inspections.

  1. First observed

TDQS

A4.9/5.0
Behavior5/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

Goes well beyond the annotations (which only cover read-only/idempotent/open-world) by disclosing the 10,000-row return cap versus scan scope, the 100-row/20 KB preview threshold that parks a result server-side for 24h with a result_id/result_url, the meaning of truncated=true, and the resource cap that rejects whole-table date scans. It also explains that a live_url re-executes the query with no API key and that anyone with the link can read it — a consequential access-model disclosure.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness4/5

Is the description appropriately sized, front-loaded, and free of redundancy?

Despite its length, it is front-loaded with the core purpose and then partitioned into labelled sections (RESULT SHAPE, LIVE URL, VARIABLES), so an agent can scan to the part it needs. It is verbose and occasionally repetitive ('NEVER re-run a query to see more of it'), and some routing detail could live in sibling definitions, but nearly every sentence carries actionable constraint information.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness5/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

There is no output schema, and the description fully compensates by specifying the response ordering and fields (row_count, preview_rows, time_windows, rows), truncation behavior, and the parked-result/CSV-download paths. For a high-complexity tool with four parameters and session-scoped artifacts, nothing an agent needs before calling it is missing.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters5/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema coverage is already 100%, but the description still adds real meaning about how params behave: `{name:Type}` placeholders must have defaults in `params`, those defaults also populate the live_url query string, values are type-checked and bound by ClickHouse rather than interpolated into SQL, and `$__timeFilter`/`$__timeGroup` route to from/to. That interactive contract is not inferable from the schema alone.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

Opens with a specific verb and resource: "Execute a read-only ClickHouse SQL query and return the results." It immediately separates itself from siblings by naming fetch_result_rows, build_chart, store_result, get_table_schema, search_columns, get_data_freshness, and list_tables with the conditions under which each is the wrong alternative here.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines5/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

Explicit routing in both directions: send the whole analysis as ONE statement, reach for store_result only to reuse an expensive intermediate, use fetch_result_rows for specific rows, build_chart to chart everything, get_table_schema/search_columns for schema discovery instead of information_schema, and get_data_freshness/list_tables for coverage questions instead of hand-rolled min/max scans. It also states a hard prerequisite (time-column WHERE filter on every analytic query).

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

Try in Browser

Glama MCP Gateway

Add one secure layer between your agents and this server.