mcp-pinot

# Pinot MCP Server
<!-- mcp-name: io.github.startreedata/mcp-pinot -->

[![Build and Test](https://github.com/startreedata/mcp-pinot/actions/workflows/build-and-test.yml/badge.svg)](https://github.com/startreedata/mcp-pinot/actions/workflows/build-and-test.yml)
[![PyPI version](https://img.shields.io/pypi/v/mcp-pinot-server.svg)](https://pypi.org/project/mcp-pinot-server/)
[![Python versions](https://img.shields.io/pypi/pyversions/mcp-pinot-server.svg)](https://pypi.org/project/mcp-pinot-server/)
[![License: Apache 2.0](https://img.shields.io/badge/License-Apache_2.0-blue.svg)](LICENSE)

## Table of Contents

- [Overview](#overview)
- [Features](#features)
- [Quick Start](#quick-start)
- [Configuration Reference](#configuration-reference)
- [Docker Build](#docker-build)
- [Claude Desktop Integration](#claude-desktop-integration)
- [Try a Prompt](#try-a-prompt)
- [Security and Vulnerability Reporting](#security-and-vulnerability-reporting)
- [Developer Notes](#developer-notes)

## Overview

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

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.

| Tool | Purpose |
|---|---|
| `test_connection` | Diagnose broker, controller, and query connectivity. |
| `list_tables` | List visible Pinot table names. |
| `get_schema` | Get one table's column schema. |
| `get_table_config` | Get one table's indexing and ingestion configuration. |
| `get_table_size` | Get reported and estimated storage size for one table. |
| `list_segments` | List exact segment names for one table. |
| `list_segment_metadata` | Page through metadata for a table's segments. |
| `get_segment_index_metadata` | Inspect per-column indexes for one exact segment. |
| `read_query` | Run one read-only Pinot SQL query. |
| `create_schema` / `update_schema` | Preview or apply schema changes. |
| `create_table_config` / `update_table_config` | Preview or apply table-config changes. |
| `reload_table_filters` | Preview or apply the configured table-filter YAML. |

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.

<a href="https://glama.ai/mcp/servers/@startreedata/mcp-pinot">
  <img width="380" height="200" src="https://glama.ai/mcp/servers/@startreedata/mcp-pinot/badge" alt="StarTree Server for Apache Pinot MCP server" />
</a>

## Pinot MCP in Action

See Pinot MCP in action below:

### Fetching Metadata
![Pinot MCP fetching metadata](assets/pinot-mcp-in-action.png)

### Fetching Data, followed by analysis

Prompt:
Can you do a histogram plot on the GitHub events against time
![Pinot MCP fetching data and analyzing table](assets/github-events-analysis.png)

### 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](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.

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

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


### Installation
```bash
# 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.
```bash
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

| Use case | Required settings | Notes |
|---|---|---|
| Claude Desktop | `MCP_TRANSPORT=stdio` | Default and recommended for local desktop use; no HTTP listener is started. |
| Local HTTP | `MCP_TRANSPORT=http`, `MCP_HOST=127.0.0.1` | Explicit local web profile. Accessible only from the same machine. |
| Remote HTTP/HTTPS | `MCP_TRANSPORT=http`, `MCP_HOST=0.0.0.0`, `AUTH_PROVIDER=oauth`\|`static` | The server refuses non-loopback HTTP/HTTPS binds unless an auth provider is active. Use TLS directly or an authenticated reverse proxy. |
| 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. |

### Pinot Connection

| Variable | Default | Description |
|---|---|---|
| `PINOT_CONTROLLER_URL` | `http://localhost:9000` | Pinot controller endpoint used for metadata and table/schema operations. |
| `PINOT_BROKER_URL` | `http://localhost:8000` | Pinot broker endpoint used for SQL queries. |
| `PINOT_BROKER_HOST` | Parsed from `PINOT_BROKER_URL` | Optional host override for the broker connection. |
| `PINOT_BROKER_PORT` | Parsed from `PINOT_BROKER_URL` | Optional port override for the broker connection. |
| `PINOT_BROKER_SCHEME` | Parsed from `PINOT_BROKER_URL` | Optional scheme override, usually `http` or `https`. |
| `PINOT_USERNAME` / `PINOT_PASSWORD` | unset | Basic authentication for Pinot. |
| `PINOT_TOKEN` | unset | Bearer or raw token for Pinot; takes precedence over `PINOT_TOKEN_FILENAME`. |
| `PINOT_TOKEN_FILENAME` | unset | File containing a Pinot token. A missing or empty file logs a warning and continues without token auth. |
| `PINOT_DATABASE` | empty | Optional database header for multi-database Pinot deployments. |
| `PINOT_USE_MSQE` | `false` | Enables Pinot multi-stage query engine query option. |
| `PINOT_REQUEST_TIMEOUT` | `60` | HTTP request timeout in seconds. |
| `PINOT_CONNECTION_TIMEOUT` | `60` | HTTP connection timeout in seconds. |
| `PINOT_QUERY_TIMEOUT` | `60` | SQL query timeout in seconds. |

### MCP Server

| Variable | Default | Description |
|---|---|---|
| `MCP_TRANSPORT` | `stdio` | Transport mode. Use `stdio` for desktop clients and `http` for Streamable HTTP clients. |
| `MCP_HOST` | `127.0.0.1` | HTTP bind host. Set `0.0.0.0` only with an auth provider enabled. |
| `MCP_PORT` | `8080` | HTTP listen port. |
| `MCP_PATH` | `/mcp` | MCP HTTP path. |
| `MCP_SSL_KEYFILE` | unset | TLS private key path. Requires `MCP_SSL_CERTFILE`. |
| `MCP_SSL_CERTFILE` | unset | TLS certificate path. Requires `MCP_SSL_KEYFILE`. |
| `MCP_LOG_LEVEL` | `INFO` | Application log level: `DEBUG`, `INFO`, `WARNING`, `ERROR`, or `CRITICAL`. Logs go to stderr so STDIO protocol output remains valid. |
| `MCP_RATE_LIMIT_RPS` / `MCP_RATE_LIMIT_BURST` | `10` / `20` | Per-principal (authenticated) or per-peer (loopback HTTP) tool-call rate and burst limits. |
| `MCP_RATE_LIMIT_MAX_CLIENTS` | `10000` | Maximum in-memory client buckets; least-recently-used buckets are evicted. |
| `MCP_RATE_LIMIT_IDLE_TTL_SECONDS` | `600` | Idle time before a rate-limit bucket can be evicted. |
| `MCP_CONFIRMATION_TTL_SECONDS` | `300` | Confirmation-token lifetime, constrained to 30–3600 seconds. Tokens are process-bound and intentionally fail after restart. |

### Authentication

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

| Variable | Default | Description |
|---|---|---|
| `AUTH_PROVIDER` | unset | Active auth provider: `none` (default), `oauth`, or `static`. Some provider is required before a non-loopback bind. |
| `MCP_STATIC_TOKEN` | empty | Shared bearer secret for `AUTH_PROVIDER=static` — a service-to-service caller sends it as `Authorization: Bearer <token>`. Required when the static provider is active. |
| `MCP_STATIC_SCOPES` | `pinot:read pinot:write pinot:admin` | Space- or comma-separated sco