{
"markdown": "# Pinot MCP Server\n\n\n[](https://github.com/startreedata/mcp-pinot/actions/workflows/build-and-test.yml)\n[](https://pypi.org/project/mcp-pinot-server/)\n[](https://pypi.org/project/mcp-pinot-server/)\n[](LICENSE)\n\n## Table of Contents\n\n- [Overview](#overview)\n- [Features](#features)\n- [Quick Start](#quick-start)\n- [Configuration Reference](#configuration-reference)\n- [Docker Build](#docker-build)\n- [Claude Desktop Integration](#claude-desktop-integration)\n- [Try a Prompt](#try-a-prompt)\n- [Security and Vulnerability Reporting](#security-and-vulnerability-reporting)\n- [Developer Notes](#developer-notes)\n\n## Overview\n\nThis project is a Python-based [Model Context Protocol (MCP)](https://github.com/anthropic-ai/mcp) server for interacting with Apache Pinot. It is built using the [FastMCP framework](https://github.com/jlowin/fastmcp). It is designed to integrate with Claude Desktop to enable real-time analytics and metadata queries on a Pinot cluster.\n\nIt allows you to\n- List tables, segments, and schema info from Pinot\n- Execute read-only SQL queries\n- View index/column-level metadata\n- Designed to assist business users via Claude integration\n- and much more.\n\n## Features\n\n- Every tool advertises typed input and output JSON Schemas, MCP risk annotations,\n and failure-recovery guidance for agent planning.\n- Large query, table, segment-name, and segment-metadata responses use bounded\n pages with continuation metadata instead of returning unbounded agent context.\n- Read-only SQL is parsed and enforced before execution; validation, permission,\n and transient connectivity errors are surfaced as actionable MCP errors.\n- Every mutating tool supports `dry_run`; always preview the exact target and\n payload before applying. Applying requires the preview's short-lived, one-time\n `confirmation_token`, including for table-filter reloads. A preview is not a\n guarantee that Pinot will accept the later write.\n- Single-purpose schema and table-config inspection tools avoid ambiguous combined\n operations: use `get_schema` and `get_table_config` independently.\n\n## MCP Tool Contract\n\nTool names are case-sensitive and use underscores. Version 4 renamed four tools\nto make every operation verb-first; clients using the former noun-first names must\nupdate their calls.\n\n| Tool | Purpose |\n|---|---|\n| `test_connection` | Diagnose broker, controller, and query connectivity. |\n| `list_tables` | List visible Pinot table names. |\n| `get_schema` | Get one table's column schema. |\n| `get_table_config` | Get one table's indexing and ingestion configuration. |\n| `get_table_size` | Get reported and estimated storage size for one table. |\n| `list_segments` | List exact segment names for one table. |\n| `list_segment_metadata` | Page through metadata for a table's segments. |\n| `get_segment_index_metadata` | Inspect per-column indexes for one exact segment. |\n| `read_query` | Run one read-only Pinot SQL query. |\n| `create_schema` / `update_schema` | Preview or apply schema changes. |\n| `create_table_config` / `update_table_config` | Preview or apply table-config changes. |\n| `reload_table_filters` | Preview or apply the configured table-filter YAML. |\n\nFor every schema, table-config, or table-filter change, first call the same tool\nwith `dry_run=true`, present the preview to the user, and call it with\n`dry_run=false` and the preview's one-time `confirmation_token` only after\nconfirmation. Editing a table-filter file after preview invalidates its token.\nPinot performs authoritative validation during table/schema apply calls, so a\nwrite can still fail after a successful preview.\n\n\n ![\"StarTree](\"https://glama.ai/mcp/servers/@startreedata/mcp-pinot/badge\")
\n\n\n## Pinot MCP in Action\n\nSee Pinot MCP in action below:\n\n### Fetching Metadata\n\n\n### Fetching Data, followed by analysis\n\nPrompt:\nCan you do a histogram plot on the GitHub events against time\n\n\n### Sample Prompts\nOnce Claude is running, click the hammer 🛠️ icon and try these prompts:\n\n- Can you help me analyse my data in Pinot? Use the Pinot tool and look at the list of tables to begin with.\n- Can you do a histogram plot on the GitHub events against time\n\n\n## Quick Start\n\n### Prerequisites\n\n#### Install uv (if not already installed)\n[uv](https://github.com/astral-sh/uv) is a fast Python package installer and resolver, written in Rust. It's designed to be a drop-in replacement for pip with significantly better performance.\n\n```bash\ncurl -LsSf https://astral.sh/uv/install.sh | sh\n\n# Reload your bashrc/zshrc to take effect. Alternatively, restart your terminal\n# source ~/.bashrc\n```\n\n\n### Installation\n```bash\n# Clone the repository\ngit clone https://github.com/startreedata/mcp-pinot.git\ncd mcp-pinot\nuv pip install -e . # Install dependencies\n\n# For development dependencies (including testing tools), use:\n# uv pip install -e .[dev] \n```\n\n### Configure Pinot Cluster\nThe MCP server expects a uvicorn config style `.env` file in the root directory to configure the Pinot cluster connection. This repo includes a sample `.env.example` file that assumes a pinot quickstart setup.\n```bash\nmv .env.example .env\n```\n\n## Configuration Reference\n\nThe server loads configuration from environment variables and from a `.env` file\nfound from the current working directory. Process environment variables take\nprecedence over `.env`, so deployment-time settings cannot be silently replaced\nby a checked-out file.\n\n### Common Profiles\n\n| Use case | Required settings | Notes |\n|---|---|---|\n| Claude Desktop | `MCP_TRANSPORT=stdio` | Default and recommended for local desktop use; no HTTP listener is started. |\n| Local HTTP | `MCP_TRANSPORT=http`, `MCP_HOST=127.0.0.1` | Explicit local web profile. Accessible only from the same machine. |\n| Remote HTTP/HTTPS | `MCP_TRANSPORT=http`, `MCP_HOST=0.0.0.0`, `MCP_ALLOWED_HOSTS=`, `AUTH_PROVIDER=oauth`\\|`static`\\|`oauth+static` | The server refuses non-loopback HTTP/HTTPS binds unless an auth provider is active, and a wildcard bind requires an explicit Host allowlist. Use `oauth+static` to serve interactive users and one trusted backend at once. Use TLS directly or an authenticated reverse proxy. |\n| Helm exposure | `service.enabled=true`, `mcp.host=0.0.0.0`, `mcp.oauth.enabled=true` | Helm defaults are local-only and render no Service unless exposure is explicitly enabled. |\n\n### Pinot Connection\n\n| Variable | Default | Description |\n|---|---|---|\n| `PINOT_CONTROLLER_URL` | `http://localhost:9000` | Pinot controller endpoint used for metadata and table/schema operations. |\n| `PINOT_BROKER_URL` | `http://localhost:8000` | Pinot broker endpoint used for SQL queries. |\n| `PINOT_BROKER_HOST` | Parsed from `PINOT_BROKER_URL` | Optional host override for the broker connection. |\n| `PINOT_BROKER_PORT` | Parsed from `PINOT_BROKER_URL` | Optional port override for the broker connection. |\n| `PINOT_BROKER_SCHEME` | Parsed from `PINOT_BROKER_URL` | Optional scheme override, usually `http` or `https`. |\n| `PINOT_USERNAME` / `PINOT_PASSWORD` | unset | Basic authentication for Pinot. |\n| `PINOT_TOKEN` | unset | Bearer or raw token for Pinot; takes precedence over `PINOT_TOKEN_FILENAME`. |\n| `PINOT_TOKEN_FILENAME` | unset | File containing a Pinot token. A missing or empty file logs a warning and continues without token auth. |\n| `PINOT_DATABASE` | empty | Optional database header for multi-database Pinot deployments. |\n| `PINOT_USE_MSQE` | `false` | Enables Pinot multi-stage query engine query option. |\n| `PINOT_REQUEST_TIMEOUT` | `60` | HTTP request timeout in seconds. |\n| `PINOT_CONNECTION_TIMEOUT` | `60` | HTTP connection timeout in seconds. |\n| `PINOT_QUERY_TIMEOUT` | `60` | SQL query timeout in seconds. |\n\n### MCP Server\n\n| Variable | Default | Description |\n|---|---|---|\n| `MCP_TRANSPORT` | `stdio` | Transport mode. Use `stdio` for desktop clients and `http` for Streamable HTTP clients. |\n| `MCP_HOST` | `127.0.0.1` | HTTP bind host. Set `0.0.0.0` only with an auth provider enabled. |\n| `MCP_PORT` | `8080` | HTTP listen port. |\n| `MCP_PATH` | `/mcp` | MCP HTTP path. |\n| `MCP_ALLOWED_HOSTS` | exact host[:port] of a concrete bind | Comma-separated Host authorities accepted at the MCP endpoint. A wildcard bind (`0.0.0.0`, `::`) has no inferable public authority, so it defaults to empty and **the server exits at startup** until you list the names clients use, e.g. `mcp.example.com,mcp.example.com:443`. |\n| `MCP_ALLOWED_ORIGINS` | unset | Comma-separated browser `Origin` values accepted. Empty rejects requests that send `Origin` while still allowing clients that omit it. |\n| `MCP_SSL_KEYFILE` | unset | TLS private key path. Requires `MCP_SSL_CERTFILE`. |\n| `MCP_SSL_CERTFILE` | unset | TLS certificate path. Requires `MCP_SSL_KEYFILE`. |\n| `MCP_LOG_LEVEL` | `INFO` | Application log level: `DEBUG`, `INFO`, `WARNING`, `ERROR`, or `CRITICAL`. Logs go to stderr so STDIO protocol output remains valid. |\n| `MCP_RATE_LIMIT_RPS` / `MCP_RATE_LIMIT_BURST` | `10` / `20` | Per-principal (authenticated) or per-peer (loopback HTTP) tool-call rate and burst limits. |\n| `MCP_RATE_LIMIT_MAX_CLIENTS` | `10000` | Maximum in-memory client buckets; least-recently-used buckets are evicted. |\n| `MCP_RATE_LIMIT_IDLE_TTL_SECONDS` | `600` | Idle time before a rate-limit bucket can be evicted. |\n| `MCP_CONFIRMATION_TTL_SECONDS` | `300` | Confirmation-token lifetime, constrained to 30–3600 seconds. Tokens are process-bound and intentionally fail after restart. |\n\n### Authentication\n\nAn auth provider is required before binding HTTP or HTTPS to a non-loopback host.\n\n| Variable | Default | Description |\n|---|---|---|\n| `AUTH_PROVIDER` | unset | Active auth provider: `none` (default), `oauth`, `static`, or `oauth+static`. Some provider is required before a non-loopback bind. |\n| | | `oauth+static` accepts both an OIDC login and the shared token on one deployment — the usual hosted case, where people use a browser and one trusted backend cannot. Either spelling works; the shared secret is checked first, and each credential keeps its own scopes (`MCP_STATIC_SCOPES` vs `OAUTH_GRANTED_SCOPES`). |\n| `MCP_STATIC_TOKEN` | empty | Shared bearer secret for `AUTH_PROVIDER=static` — a service-to-service caller sends it as `Authorization: Bearer `. Required when the static provider is active. |\n| `MCP_STATIC_SCOPES` | `pinot:read pinot:write pinot:admin` | Space- or comma-separated scopes granted to the static principal. Use `pinot:read` for a read-only service. |\n| `OAUTH_ENABLED` | `false` | Legacy flag; `true` is equivalent to `AUTH_PROVIDER=oauth`. Enables OAuth authentication. |\n| `OAUTH_CLIENT_ID` | empty | OAuth client ID. |\n| `OAUTH_CLIENT_SECRET` | empty | OAuth client secret. |\n| `OAUTH_BASE_URL` | `http://localhost:8080` | Public base URL for this MCP server. |\n| `OAUTH_AUTHORIZATION_ENDPOINT` | empty | Upstream authorization endpoint. |\n| `OAUTH_TOKEN_ENDPOINT` | empty | Upstream token endpoint. |\n| `OAUTH_JWKS_URI` | empty | JWKS URI used for token verification. |\n| `OAUTH_ISSUER` | empty | Expected token issuer. |\n| `OAUTH_AUDIENCE` | canonical MCP resource URI | Audience tokens are validated against. Defaults to `OAUTH_BASE_URL` (without a trailing slash) plus `MCP_PATH`, which is what RFC 9728 metadata advertises. Set it explicitly when the provider issues a different `aud` — many (Dex among them) set it to the client ID; the server logs a warning and honours your value. |\n| `OAUTH_GRANTED_SCOPES` | `pinot:read pinot:write pinot:admin` | Pinot scopes granted to every principal this provider authenticates, unioned onto the scopes the token already carries. Needed because general-purpose OIDC providers issue a fixed scope catalog and cannot mint `pinot:*`, so without a grant every tool call from a valid user would be denied. Set to `pinot:read` for a read-only deployment. |\n| `OAUTH_EXTRA_AUTH_PARAMS` | unset | Optional JSON object with additional authorization parameters. |\n\n### Table Filtering\n\n| Variable | Default | Description |\n|---|---|---|\n| `PINOT_TABLE_FILTER_FILE` | unset | YAML file with `included_tables` glob patterns. If configured and missing, startup fails. |\n\nSee [SECURITY.md](SECURITY.md) for the production exposure checklist and\nvulnerability reporting process.\n\n### Configure Table Filtering (Optional)\n\n> ⚠️ **Security Note:** For production access control, use [Pinot's native table-level ACLs](https://docs.pinot.apache.org/operators/operating-pinot/access-control) (available since Pinot 0.8.0+). Table filtering in this MCP server is a convenience feature for organizing tables and improving UX, not a security boundary. It uses best-effort SQL parsing and should not be relied upon for security.\n\nTable filtering allows you to control which Pinot tables are visible through the MCP server. This is useful for:\n- **Reduce Cognitive Load**: Focus on relevant tables when your Pinot cluster has hundreds or thousands of tables\n- **Multi-Tenancy UX**: Run multiple MCP server instances against the same Pinot cluster, each showing different table subsets for different teams or use cases\n- **Environment Separation**: Deploy different MCP server instances (dev, staging, prod) that show only environment-specific tables\n- **Hide System Tables**: Filter out internal, test, or deprecated tables from end-user view\n\nWhen table filtering is enabled, **all table operations** are filtered to show only the configured tables.\n\n#### What Gets Filtered\n\nTable filtering applies across **all MCP operations**:\n\n1. **Table Listing** - Only configured tables appear in table lists\n2. **Query Execution** - SQL queries are checked to ensure all referenced tables (in FROM, JOIN, subqueries, CTEs, etc.) match the configured patterns\n3. **Table Operations** - Direct table access operations filter by table name:\n - Get table details, size, and metadata\n - Get table segments and segment metadata\n - Get index/column details\n - Get/update table configurations\n4. **Schema Operations** - Schema operations filter by schema name:\n - Get/create/update schemas\n - Create table configurations\n\n#### Setup\nCopy the example configuration file:\n```bash\ncp table_filters.yaml.example table_filters.yaml\n```\n\nEdit `table_filters.yaml` to specify which tables to include:\n```yaml\nincluded_tables:\n - production_* # All tables starting with \"production_\"\n - analytics_events # Specific table name\n - metrics_* # All tables starting with \"metrics_\"\n```\n\nConfigure the filter file path in your `.env`:\n```bash\nPINOT_TABLE_FILTER_FILE=table_filters.yaml\n```\n\n#### Pattern Matching\nThe filter supports glob-style patterns using standard Unix filename pattern matching:\n- `exact_table_name` - Matches exactly this table\n- `prefix_*` - Matches all tables starting with \"prefix_\"\n- `*_suffix` - Matches all tables ending with \"_suffix\"\n- `*pattern*` - Matches all tables containing \"pattern\"\n- `sharded_table_?` - Matches tables with exactly one character after the underscore (e.g., `sharded_table_1`, `sharded_table_a`)\n\n#### Query Filtering\nWhen filtering is enabled, SQL queries are checked before execution:\n\n- **Supported SQL Features**: FROM clauses, JOIN clauses (INNER, LEFT, RIGHT, OUTER, CROSS), subqueries, CTEs (WITH), and comma-separated table lists\n- **Quoted Identifiers**: Supports both double-quoted (`\"table name\"`) and backtick-quoted (`` `table_name` ``) table names\n- **Schema Prefixes**: Handles schema-qualified table names (e.g., `database.schema.table`)\n- **Comments**: Removes SQL comments before checking\n\n**Example filtered query:**\n```sql\nSELECT * FROM allowed_table\nJOIN other_table ON allowed_table.id = other_table.id\n```\n**Error:** `Query references unauthorized tables: other_table. Allowed tables: allowed_table, prod_*`\n\n#### Configuration Features\n\n**Fail-Fast Validation:**\n- ⚠️ If `PINOT_TABLE_FILTER_FILE` is configured but the file doesn't exist, the server will **fail to start** with a `FileNotFoundError`\n- This prevents accidentally showing all tables due to misconfiguration\n- Empty filter files or missing `included_tables` key will show all tables (no filtering)\n\n**Comprehensive Filtering:**\n- All MCP tools that access tables apply filtering before execution\n- Consistent filtering across all table access points\n- Clear error messages indicate which tables don't match the configured patterns\n\n#### Disabling Table Filtering\n\nTo disable table filtering, either:\n1. Remove the `PINOT_TABLE_FILTER_FILE` environment variable, or\n2. Don't configure it in your `.env` file\n\nWhen not configured, all tables in the Pinot cluster are visible.\n\nWhen a filter file supplies both `allow_all: true` and a non-empty\n`included_tables`, the explicit allow-list takes precedence and the server logs a\nwarning. Applying a reload requires the token from an unchanged dry-run candidate.\n\n### Read-only Query Enforcement\n\nThe `read_query` tool always validates SQL before forwarding it to Pinot. It\naccepts one statement only, and that statement must be a read-only `SELECT` or\n`WITH ... SELECT` query. SQL comments are stripped, semicolon-stacked statements\nare rejected, and write/DDL/admin keywords are blocked.\n\n### Configure OAuth Authentication (Optional)\nTo enable OAuth authentication, set the following environment variables in your `.env` file:\n\n**Required variables (when `OAUTH_ENABLED=true`):**\n- `OAUTH_CLIENT_ID`: OAuth client ID\n- `OAUTH_CLIENT_SECRET`: OAuth client secret\n- `OAUTH_BASE_URL`: Your MCP server base URL\n- `OAUTH_AUTHORIZATION_ENDPOINT`: OAuth authorization endpoint URL\n- `OAUTH_TOKEN_ENDPOINT`: OAuth token endpoint URL\n- `OAUTH_JWKS_URI`: JSON Web Key Set URI for token verification\n- `OAUTH_ISSUER`: Token issuer identifier\n\n**Optional variables:**\n- `OAUTH_AUDIENCE`: audience tokens are validated against. Defaults to the canonical MCP resource URI (`OAUTH_BASE_URL` + `MCP_PATH`). Set it when your provider issues a different `aud` — for example an IdP that puts the client ID there.\n- `OAUTH_GRANTED_SCOPES`: Pinot scopes granted to authenticated principals (default all three). Use `pinot:read` to make the deployment read-only for every OIDC caller.\n- `OAUTH_REQUIRED_SCOPES`: baseline scopes an access token must already carry (default: none enforced).\n- `OAUTH_EXTRA_AUTH_PARAMS`: Additional authorization parameters as JSON object (e.g., `{\"scope\": \"openid profile\"}`)\n\nTool-level authorization uses `pinot:read` / `pinot:write` / `pinot:admin`. General-purpose OIDC providers issue a fixed scope catalog and cannot mint resource scopes like these, so `OAUTH_GRANTED_SCOPES` is what makes an authenticated user able to call anything — narrow it rather than leaving tools ungated.\n\nExample configuration:\n```bash\nOAUTH_ENABLED=true\nOAUTH_CLIENT_ID=client-id\nOAUTH_CLIENT_SECRET=client-secret\nOAUTH_BASE_URL=http://localhost:8000\nOAUTH_AUTHORIZATION_ENDPOINT=https://example.com/oauth/authorize\nOAUTH_TOKEN_ENDPOINT=https://example.com/oauth/token\nOAUTH_JWKS_URI=https://example.com/.well-known/jwks.json\nOAUTH_ISSUER=https://example.com\nOAUTH_AUDIENCE=http://localhost:8000/mcp\nOAUTH_EXTRA_AUTH_PARAMS={\"scope\": \"openid profile\"}\n```\n\n### Run the server\n\n```bash\nuv --directory . run mcp_pinot/server.py\n```\nYou should see logs indicating that the server is running.\n\n> Security notes:\n> - STDIO is the default. When HTTP is selected it binds to `127.0.0.1`; set `MCP_HOST=0.0.0.0` only with OAuth or static-token authentication plus TLS or an authenticated reverse proxy.\n> - The server refuses to start when HTTP is bound to a non-loopback host without an auth provider (`AUTH_PROVIDER=oauth` or `static`, or the legacy `OAUTH_ENABLED=true`).\n> - `read_query` enforces a single read-only SQL statement before execution. This is a guardrail, not a replacement for Pinot authentication and authorization.\n> - The supported `mcp[cli]` dependency includes DNS rebinding protections for the Streamable HTTP server.\n> - Confirmation replay state and rate-limit buckets are process-local. Run exactly one server process/Helm replica. The chart rejects `replicas != 1`; horizontal scaling requires a shared state-store implementation.\n> - `/readyz` reports MCP process readiness, not Pinot cluster health. Use `test_connection` to diagnose Pinot dependencies.\n\n### Launch Pinot Quickstart (Optional)\n\nStart Pinot QuickStart using docker:\n\n```bash\ndocker run --name pinot-quickstart -p 2123:2123 -p 9000:9000 -p 8000:8000 -d apachepinot/pinot:1.5.1 QuickStart -type batch\n```\n\nQuery MCP Server\n\n```bash\nuv --directory . run examples/example_client.py\n```\n\nThis quickstart just checks all the tools and queries the airlineStats table.\n\n## Claude Desktop Integration\n\n### Open Claude's config file\n```bash\nvi ~/Library/Application\\ Support/Claude/claude_desktop_config.json\n```\n\n### Add an MCP server entry\n```json\n{\n \"mcpServers\": {\n \"pinot_mcp\": {\n \"command\": \"/path/to/uv\",\n \"args\": [\n \"--directory\",\n \"/path/to/mcp-pinot-repo\",\n \"run\",\n \"mcp_pinot/server.py\"\n ],\n \"env\": {\n // You can also include your .env config here\n }\n }\n }\n}\n```\nReplace `/path/to/uv` with the absolute path to the uv command, you can run `which uv` to figure it out.\n\nReplace `/path/to/mcp-pinot` with the absolute path to the folder where you cloned this repo.\n\nNote: you must use stdio transport when running your server to use with Claude desktop.\n\nYou could also configure environment variables here instead of the `.env` file, in case you want to connect to multiple pinot clusters as MCP servers.\n\n### Restart Claude Desktop\n\nClaude will now auto-launch the MCP server on startup and recognize the new Pinot-based tools.\n\n## Using the MCP Bundle\n\nThe release workflow publishes a Claude Desktop MCP Bundle (`.mcpb`). Its UV\nruntime installs the locked dependencies for the user's platform, so one small\nbundle works across macOS, Linux, and Windows. To build one locally:\n\n```bash\nnpm install -g @anthropic-ai/mcpb@2.1.2\nmcpb validate manifest.json\nmcpb pack\n```\n\nOpen the resulting `.mcpb` file to install it in Claude Desktop.\n\n## Security and Vulnerability Reporting\n\nSee [SECURITY.md](SECURITY.md) for vulnerability reporting instructions,\nsecurity categories, and the checklist for safely exposing the MCP HTTP\nendpoint.\n\n## Developer\n\n- MCP tool definitions live in `mcp_pinot/server.py`; Pinot HTTP/DB operations\n live in `mcp_pinot/pinot_client.py`.\n\n### Build\nBuild the project with\n\n```bash\nuv sync --frozen\n```\n\n### Test\nTest the repo with:\n```bash\nuv run pytest --cov=mcp_pinot\n```\n\n### Build the Docker image\n```bash\ndocker build -t mcp-pinot .\n```\n\n### Run the container\n```bash\ndocker run --rm -i -v \"$(pwd)/.env:/app/config/.env:ro\" mcp-pinot\n```\n\nThis uses the default STDIO transport. For HTTP/Kubernetes deployments, configure\nan inbound auth provider before binding to a non-loopback address; see the\nconfiguration and Helm sections above.\n",
"bytes": 23554,
"sha": "7fa3a97cc0470d36a4581dedd65eef738fa62e75e087936697ff50817c4c873b",
"repo_slug": "startreedata/mcp-pinot",
"fonte": "repo",
"truncated": false,
"api": "https://agentalog.com/api/listings/mcp_io_github_startreedata_mcp_pinot_175205ac/readme"
}