Pinot MCP Server

Table of Contents

• Overview
• Features
• Quick Start
• Configuration Reference
• Docker Build
• Claude Desktop Integration
• Try a Prompt
• Security and Vulnerability Reporting
• Developer Notes

Overview

This project is a Python-based Model Context Protocol (MCP) server for interacting with Apache Pinot. It is built using the FastMCP framework. It is designed to integrate with Claude Desktop to enable real-time analytics and metadata queries on a Pinot cluster.

It allows you to

• List tables, segments, and schema info from Pinot
• Execute read-only SQL queries
• View index/column-level metadata
• Designed to assist business users via Claude integration
• and much more.

Features

• Every tool advertises typed input and output JSON Schemas, MCP risk annotations,

and failure-recovery guidance for agent planning.

• Large query, table, segment-name, and segment-metadata responses use bounded

pages with continuation metadata instead of returning unbounded agent context.

• Read-only SQL is parsed and enforced before execution; validation, permission,

and transient connectivity errors are surfaced as actionable MCP errors.

• Every mutating tool supports `dry_run`; always preview the exact target and

payload before applying. Applying requires the preview's short-lived, one-time

`confirmation_token`, including for table-filter reloads. A preview is not a

guarantee that Pinot will accept the later write.

• Single-purpose schema and table-config inspection tools avoid ambiguous combined

operations: use `get_schema` and `get_table_config` independently.

MCP Tool Contract

Tool names are case-sensitive and use underscores. Version 4 renamed four tools

to make every operation verb-first; clients using the former noun-first names must

update their calls.

For every schema, table-config, or table-filter change, first call the same tool

with `dry_run=true`, present the preview to the user, and call it with

`dry_run=false` and the preview's one-time `confirmation_token` only after

confirmation. Editing a table-filter file after preview invalidates its token.

Pinot performs authoritative validation during table/schema apply calls, so a

write can still fail after a successful preview.

Pinot MCP in Action

See Pinot MCP in action below:

Fetching Metadata

Fetching Data, followed by analysis

Prompt:

Can you do a histogram plot on the GitHub events against time

Sample Prompts

Once Claude is running, click the hammer 🛠️ icon and try these prompts:

• Can you help me analyse my data in Pinot? Use the Pinot tool and look at the list of tables to begin with.
• Can you do a histogram plot on the GitHub events against time

Quick Start

Prerequisites

Install uv (if not already installed)

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.

curl -LsSf https://astral.sh/uv/install.sh | sh

# Reload your bashrc/zshrc to take effect. Alternatively, restart your terminal
# source ~/.bashrc

Installation

# Clone the repository
git clone https://github.com/startreedata/mcp-pinot.git
cd mcp-pinot
uv pip install -e . # Install dependencies

# For development dependencies (including testing tools), use:
# uv pip install -e .[dev]

Configure Pinot Cluster

The 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.

mv .env.example .env

Configuration Reference

The server loads configuration from environment variables and from a `.env` file

found from the current working directory. Process environment variables take

precedence over `.env`, so deployment-time settings cannot be silently replaced

by a checked-out file.

Common Profiles

Pinot Connection

MCP Server

Authentication

An auth provider is required before binding HTTP or HTTPS to a non-loopback host.

Table Filtering

See SECURITY.md for the production exposure checklist and

vulnerability reporting process.

Configure Table Filtering (Optional)

> ⚠️ Security Note: For production access control, use Pinot's native table-level ACLs (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.

Table filtering allows you to control which Pinot tables are visible through the MCP server. This is useful for:

• Reduce Cognitive Load: Focus on relevant tables when your Pinot cluster has hundreds or thousands of tables
• Multi-Tenancy UX: Run multiple MCP server instances against the same Pinot cluster, each showing different table subsets for different teams or use cases
• Environment Separation: Deploy different MCP server instances (dev, staging, prod) that show only environment-specific tables
• Hide System Tables: Filter out internal, test, or deprecated tables from end-user view

When table filtering is enabled, all table operations are filtered to show only the configured tables.

What Gets Filtered

Table filtering applies across all MCP operations:

1. Table Listing - Only configured tables appear in table lists

2. Query Execution - SQL queries are checked to ensure all referenced tables (in FROM, JOIN, subqueries, CTEs, etc.) match the configured patterns

3. Table Operations - Direct table access operations filter by table name:

4. Schema Operations - Schema operations filter by schema name:

Setup

Copy the example configuration file:

cp table_filters.yaml.example table_filters.yaml

Edit `table_filters.yaml` to specify which tables to include:

included_tables:
  - production_*        # All tables starting with "production_"
  - analytics_events    # Specific table name
  - metrics_*          # All tables starting with "metrics_"

Configure the filter file path in your `.env`:

PINOT_TABLE_FILTER_FILE=table_filters.yaml

Pattern Matching

The filter supports glob-style patterns using standard Unix filename pattern matching:

• `exact_table_name` - Matches exactly this table
• `prefix_*` - Matches all tables starting with "prefix_"
• `*_suffix` - Matches all tables ending with "_suffix"
• `*pattern*` - Matches all tables containing "pattern"
• `sharded_table_?` - Matches tables with exactly one character after the underscore (e.g., `sharded_table_1`, `sharded_table_a`)

Query Filtering

When filtering is enabled, SQL queries are checked before execution:

• Supported SQL Features: FROM clauses, JOIN clauses (INNER, LEFT, RIGHT, OUTER, CROSS), subqueries, CTEs (WITH), and comma-separated table lists
• Quoted Identifiers: Supports both double-quoted (`"table name"`) and backtick-quoted (`` `table_name` ``) table names
• Schema Prefixes: Handles schema-qualified table names (e.g., `database.schema.table`)
• Comments: Removes SQL comments before checking

Example filtered query:

SELECT * FROM allowed_table
JOIN other_table ON allowed_table.id = other_table.id

Error: `Query references unauthorized tables: other_table. Allowed tables: allowed_table, prod_*`

Configuration Features

Fail-Fast Validation:

• ⚠️ If `PINOT_TABLE_FILTER_FILE` is configured but the file doesn't exist, the server will fail to start with a `FileNotFoundError`
• This prevents accidentally showing all tables due to misconfiguration
• Empty filter files or missing `included_tables` key will show all tables (no filtering)

Comprehensive Filtering:

• All MCP tools that access tables apply filtering before execution
• Consistent filtering across all table access points
• Clear error messages indicate which tables don't match the configured patterns

Disabling Table Filtering

To disable table filtering, either:

1. Remove the `PINOT_TABLE_FILTER_FILE` environment variable, or

2. Don't configure it in your `.env` file

When not configured, all tables in the Pinot cluster are visible.

When a filter file supplies both `allow_all: true` and a non-empty

`included_tables`, the explicit allow-list takes precedence and the server logs a

warning. Applying a reload requires the token from an unchanged dry-run candidate.

Read-only Query Enforcement

The `read_query` tool always validates SQL before forwarding it to Pinot. It

accepts one statement only, and that statement must be a read-only `SELECT` or

`WITH ... SELECT` query. SQL comments are stripped, semicolon-stacked statements

are rejected, and write/DDL/admin keywords are blocked.

Configure OAuth Authentication (Optional)

To enable OAuth authentication, set the following environment variables in your `.env` file:

Required variables (when `OAUTH_ENABLED=true`):

• `OAUTH_CLIENT_ID`: OAuth client ID
• `OAUTH_CLIENT_SECRET`: OAuth client secret
• `OAUTH_BASE_URL`: Your MCP server base URL
• `OAUTH_AUTHORIZATION_ENDPOINT`: OAuth authorization endpoint URL
• `OAUTH_TOKEN_ENDPOINT`: OAuth token endpoint URL
• `OAUTH_JWKS_URI`: JSON Web Key Set URI for token verification
• `OAUTH_ISSUER`: Token issuer identifier

Optional variables:

• `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.
• `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.
• `OAUTH_REQUIRED_SCOPES`: baseline scopes an access token must already carry (default: none enforced).
• `OAUTH_EXTRA_AUTH_PARAMS`: Additional authorization parameters as JSON object (e.g., `{"scope": "openid profile"}`)

Tool-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.

Example configuration:

OAUTH_ENABLED=true
OAUTH_CLIENT_ID=client-id
OAUTH_CLIENT_SECRET=client-secret
OAUTH_BASE_URL=http://localhost:8000
OAUTH_AUTHORIZATION_ENDPOINT=https://example.com/oauth/authorize
OAUTH_TOKEN_ENDPOINT=https://example.com/oauth/token
OAUTH_JWKS_URI=https://example.com/.well-known/jwks.json
OAUTH_ISSUER=https://example.com
OAUTH_AUDIENCE=http://localhost:8000/mcp
OAUTH_EXTRA_AUTH_PARAMS={"scope": "openid profile"}

Run the server

uv --directory . run mcp_pinot/server.py

You should see logs indicating that the server is running.

> Security notes:

> - 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.

> - 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`).

> - `read_query` enforces a single read-only SQL statement before execution. This is a guardrail, not a replacement for Pinot authentication and authorization.

> - The supported `mcp[cli]` dependency includes DNS rebinding protections for the Streamable HTTP server.

> - 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.

> - `/readyz` reports MCP process readiness, not Pinot cluster health. Use `test_connection` to diagnose Pinot dependencies.

Launch Pinot Quickstart (Optional)

Start Pinot QuickStart using docker:

docker run --name pinot-quickstart -p 2123:2123 -p 9000:9000 -p 8000:8000 -d apachepinot/pinot:1.5.1 QuickStart -type batch

Query MCP Server

uv --directory . run examples/example_client.py

This quickstart just checks all the tools and queries the airlineStats table.

Claude Desktop Integration

Open Claude's config file

vi ~/Library/Application\ Support/Claude/claude_desktop_config.json

Add an MCP server entry

{
  "mcpServers": {
      "pinot_mcp": {
          "command": "/path/to/uv",
          "args": [
              "--directory",
              "/path/to/mcp-pinot-repo",
              "run",
              "mcp_pinot/server.py"
          ],
          "env": {
            // You can also include your .env config here
          }
      }
  }
}

Replace `/path/to/uv` with the absolute path to the uv command, you can run `which uv` to figure it out.

Replace `/path/to/mcp-pinot` with the absolute path to the folder where you cloned this repo.

Note: you must use stdio transport when running your server to use with Claude desktop.

You could also configure environment variables here instead of the `.env` file, in case you want to connect to multiple pinot clusters as MCP servers.

Restart Claude Desktop

Claude will now auto-launch the MCP server on startup and recognize the new Pinot-based tools.

Using the MCP Bundle

The release workflow publishes a Claude Desktop MCP Bundle (`.mcpb`). Its UV

runtime installs the locked dependencies for the user's platform, so one small

bundle works across macOS, Linux, and Windows. To build one locally:

npm install -g @anthropic-ai/mcpb@2.1.2
mcpb validate manifest.json
mcpb pack

Open the resulting `.mcpb` file to install it in Claude Desktop.

Security and Vulnerability Reporting

See SECURITY.md for vulnerability reporting instructions,

security categories, and the checklist for safely exposing the MCP HTTP

endpoint.

Developer

• MCP tool definitions live in `mcp_pinot/server.py`; Pinot HTTP/DB operations

live in `mcp_pinot/pinot_client.py`.

Build

Build the project with

uv sync --frozen

Test

Test the repo with:

uv run pytest --cov=mcp_pinot

Build the Docker image

docker build -t mcp-pinot .

Run the container

docker run --rm -i -v "$(pwd)/.env:/app/config/.env:ro" mcp-pinot

This uses the default STDIO transport. For HTTP/Kubernetes deployments, configure

an inbound auth provider before binding to a non-loopback address; see the

configuration and Helm sections above.