faostat-mcp-server

faostat-mcp-server: dataframe query

faostat_dataframe_query

Read-onlyIdempotent

Run a single-statement SELECT against canvas tables staged by faostat_query_observations and faostat_commodity_profile (faostat_xxxxxxxx). Use DuckDB joins, aggregates, window functions, and CTEs for rankings and time-series analysis. Item groups can overlap their members even with matching units; do not sum them together, and resolver kind=null does not establish safe-to-sum. Separate source methodologies and respect bounded values and multi-year periods. Read-only: writes, DDL, DROP, COPY, PRAGMA, ATTACH, and external-file functions are rejected; system catalogs are denied. List tables and columns with faostat_dataframe_describe. Preserve data-quality flags in projections: commonly A=Official, B=time-series break, E=Estimated, I=Imputed, M=Missing, T=Unofficial, X=international organization; unrecognized flags never establish official status.

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": "faostat_dataframe_query"
  }
}

Input Schema

Name	Required	Description
`sql`	Yes	Single-statement read-only SELECT against staged faostat_<id> tables. Columns: area_code, area, item_code, item, element_code, element, year, unit, value, flag — plus domain (QCL or TCL) on tables staged by faostat_commodity_profile.
`canvas_id`	No	Optional canvas ID as returned by a prior faostat_query_observations / faostat_commodity_profile call — exactly 10 characters of letters, digits, hyphens, and underscores. Omit to query the tables staged in this session (the common case).
`row_limit`	No	Hard cap on rows in the response. Default 1000, max 10000.

Output Schema

Name	Required	Description
`rows`	No	Materialized result rows, bounded by row_limit.
`error`	No	Present when the call failed. Absent on success.
`notice`	No	Guidance when the query returned no rows or when results were capped.
`columns`	No	Column names in projection order.
`row_count`	No	Rows returned in this response — the materialized count, equal to rows.length. When truncated is true this is NOT the full result total (this path computes no exact total); page or aggregate to reach the rest.
`truncated`	No	True when row_limit capped the result and more rows exist than were returned. To reach them: page with ORDER BY + SQL LIMIT/OFFSET, raise row_limit (max 10000), or aggregate with GROUP BY.

Schema Changelog

Changes observed during successful MCP inspections.

Changed1 schema field changedSep 30, 2026

changedInput schema / properties / sql / description

Previous value: -"Single-statement read-only SELECT against staged faostat_<id> tables. Columns: area_code, area, item_code, item, element_code, element, year, unit, value, flag. CAST(value AS DOUBLE) for arithmetic."New value: +"Single-statement read-only SELECT against staged faostat_<id> tables. Columns: area_code, area, item_code, item, element_code, element, year, unit, value, flag — plus domain (QCL or TCL) on tables staged by faostat_commodity_profile."

Changed2 schema fields changedSep 19, 2026

changedInput schema / properties / canvas_id / description

Previous value: -"Optional canvas ID from a prior faostat_query_observations / faostat_commodity_profile call. Omit to query the tables staged in this session (the common case)."New value: +"Optional canvas ID as returned by a prior faostat_query_observations / faostat_commodity_profile call — exactly 10 characters of letters, digits, hyphens, and underscores. Omit to query the tables staged in this session (the common case)."

addedInput schema / properties / canvas_id / pattern
```
Added value: +"^[A-Za-z0-9_-]{10}$"
```

Changed6 schema fields changedAug 25, 2026

changedInput schema / $schema

Previous value: -"http://json-schema.org/draft-07/schema#"New value: +"https://json-schema.org/draft/2020-12/schema"

addedInput schema / additionalProperties
```
Added value: +false
```

changedOutput schema / $schema

Previous value: -"http://json-schema.org/draft-07/schema#"New value: +"https://json-schema.org/draft/2020-12/schema"

addedOutput schema / anyOf

Added value: +[
+  {
+    "not": {
+      "required": [
+        "error"
+      ]
+    },
+    "required": [
+      "columns",
+      "row_count",
+      "rows",
+      "truncated"
+    ]
+  },
+  {
+    "required": [
+      "error"
+    ]
+  }
+]

addedOutput schema / properties / error

Added value: +{
+  "additionalProperties": {},
+  "description": "Present when the call failed. Absent on success.",
+  "properties": {
+    "code": {
+      "description": "JSON-RPC error code for this failure.",
+      "maximum": 9007199254740991,
+      "minimum": -9007199254740991,
+      "type": "integer"
+    },
+    "data": {
+      "additionalProperties": {},
+      "properties": {
+        "reason": {
+          "description": "Machine-readable failure mode. Declared by this tool: `canvas_disabled`: The DataCanvas service is not configured for this deployment. `canvas_not_found`: An explicit canvas_id does not resolve to a live canvas — unknown, expired, or owned by another tenant. `missing_table`: The SQL references a faostat_<id> table that has expired or was never staged. `system_catalog_access`: The SQL references a denied system catalog (information_schema, sqlite_master, duckdb_*). `invalid_sql`: The SQL has a syntax or execution error, or is not a single read-only SELECT. Other values are possible when a failure originates below the handler.",
+          "examples": [
+            "canvas_disabled",
+            "canvas_not_found",
+            "missing_table",
+            "system_catalog_access",
+            "invalid_sql"
+          ],
+          "type": "string"
+        },
+        "recovery": {
+          "additionalProperties": {},
+          "description": "Actionable next step for the caller.",
+          "properties": {
+            "hint": {
+              "type": "string"
+            }
+          },
+          "required": [
+            "hint"
+          ],
+          "type": "object"
+        },
+        "retryable": {
+          "description": "Whether retrying may succeed.",
+          "type": "boolean"
+        }
+      },
+      "type": "object"
+    },
+    "message": {
+      "description": "Human-readable description of what went wrong.",
+      "type": "string"
+    }
+  },
+  "required": [
+    "code",
+    "message"
+  ],
+  "type": "object"
+}

removedOutput schema / required

Removed value: -[
-  "columns",
-  "row_count",
-  "rows",
-  "truncated"
-]

Changed3 schema fields changedJul 13, 2026

changedOutput schema / properties / row_count / description

Previous value: -"Total rows the query produced (may exceed rows.length when capped)."New value: +"Rows returned in this response — the materialized count, equal to rows.length. When truncated is true this is NOT the full result total (this path computes no exact total); page or aggregate to reach the rest."

addedOutput schema / properties / truncated

Added value: +{
+  "description": "True when row_limit capped the result and more rows exist than were returned. To reach them: page with ORDER BY + SQL LIMIT/OFFSET, raise row_limit (max 10000), or aggregate with GROUP BY.",
+  "type": "boolean"
+}

changedOutput schema / required

Previous value: -[
-  "columns",
-  "row_count",
-  "rows"
-]New value: +[
+  "columns",
+  "row_count",
+  "rows",
+  "truncated"
+]

First observedJun 16, 2026

TDQS

A4.3/5.0

Behavior5/5

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

Annotations already declare readOnlyHint and idempotentHint, yet the description adds substantial context beyond them: the exact rejected statement classes (writes, DDL, DROP, COPY, PRAGMA, ATTACH, external-file functions) and denial of system catalogs. It also discloses semantic hazards an agent would otherwise miss — overlapping item groups with matching units, distinct source methodologies, bounded values, and multi-year periods.

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?

Front-loaded with the core action and staging dependency, then organized into safety, discovery, and flag semantics. It is dense and several sentences are semicolon-chained, and the full flag glossary is arguably longer than needed, but nearly every clause carries operational meaning.

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

Completeness4/5

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

For a complex SQL-execution tool, the description covers the sandbox boundaries, table naming, sibling for schema discovery, and data-quality flag semantics; the output schema carries return values. It does not address error/failure reporting or how row_limit interacts with large results, leaving a small gap.

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

Parameters3/5

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

Schema description coverage is 100%, so sql, canvas_id, and row_limit are already documented in the schema, including the omit-to-use-session-tables behavior. The description adds only the faostat_xxxxxxxx table naming pattern and does not elaborate on row_limit or SQL constraints beyond what the schema states. Baseline 3 applies when the schema does the heavy lifting.

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?

States a specific verb and resource: 'Run a single-statement SELECT against canvas tables staged by faostat_query_observations and faostat_commodity_profile (faostat_xxxxxxxx)'. The staging dependency and table-naming convention distinguish it cleanly from siblings such as faostat_dataframe_describe and the staging tools themselves.

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

Usage Guidelines4/5

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

Gives concrete usage context — DuckDB joins, aggregates, window functions and CTEs for rankings and time-series — and routes schema discovery to faostat_dataframe_describe. It also supplies analytical guardrails (don't sum overlapping item groups, resolver kind=null is not safe-to-sum). It stops short of an explicit when-not-to-use statement relative to faostat_query_observations, so it is strong but not exhaustive.

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

All faostat-mcp-server tools Inspect

Try in Browser

One endpoint for every MCP toolRayrun runs every MCP server behind one URL and holds the credentials. Each call is decided per client, tool, and argument, then recorded.ray.runAd

Glama MCP Gateway

Add one secure layer between your agents and this server.