Apache Pinot + Microsoft Copilot Studio: Complete MCP Integration
Apache Pinot is a MCP server that MCP server for Apache Pinot real-time analytics database with SQL query capabilities and metadata access.
When integrated with Microsoft Copilot Studio, you can:
- List and inspect Apache Pinot tables
- View and analyze table segments
- Retrieve and analyze table schemas
This guide provides step-by-step instructions to set up Apache Pinot in Microsoft Copilot Studio, including configuration, examples, and troubleshooting.
What You'll Achieve
After completing this setup:
- Apache Pinot will be fully integrated and operational
- You can use Apache Pinot tools directly in Microsoft Copilot Studio
- All Apache Pinot capabilities will be available for your workflows
- Access to 5 different tools
Prerequisites
Before starting, ensure you have:
- Apache Pinot broker hostname
- Apache Pinot broker port
- Microsoft Copilot Studio installed and configured
- Compatible operating system (Web Browser, Microsoft 365)
Installation
Step 1: Install Apache Pinot
Configuration
Step 2: Configure Microsoft Copilot Studio
- Enable Generative Orchestration
Generative Orchestration must be enabled in your Copilot Studio environment to use MCP
Visit: https://learn.microsoft.com/en-us/microsoft-copilot-studio/agent-extend-action-mcp
Note: This is a prerequisite - MCP will not work without it
- Access Copilot Studio
Log in to Microsoft Copilot Studio with appropriate permissions to create and configure agents
Visit: https://copilotstudio.microsoft.com
Note: Requires organizational access or trial subscription
- Navigate to Connectors
In Copilot Studio, go to the Connectors section where you can add new integrations
Note: Location may vary based on interface updates
- Create MCP Connector
Click "Add Connector" and select "Model Context Protocol (MCP)" from the available connector types
Note: Use the YAML schema template provided by Copilot Studio
- Configure {server_name} Connection
Fill in the YAML schema with {server_name} server details including command, arguments, and environment variables
apache pinot
Note: Copilot Studio dynamically reflects changes as tools and resources are updated on the MCP server
- Add Tools to Agent
Once connected, add the MCP server tools and resources to your specific agent through the Copilot Studio UI
Note: Currently supports Tools and Resources (Prompts not yet supported)
- Test Agent Capabilities
Test your agent to verify it can access and use the {server_name} tools correctly
Configuration Details
Connect MCP server to Microsoft Copilot Studio:
Step 1: Create YAML Connector Schema
In Copilot Studio, navigate to the connectors section and create a new MCP connector using the YAML schema template:
# MCP Connector Configuration for Apache Pinot
name: Apache Pinot-mcp-connector
description: MCP server for Apache Pinot real-time analytics database with SQL query capabilities and metadata access
mcp_server:
command: apache pinot
args: "/path/to/mcp-pinot-repo"
env: "PINOT_BROKER_HOST": "localhost",
"PINOT_BROKER_PORT": "9000"
Step 2: Add to Agent
Once connected, add the MCP server's tools and resources to your Copilot Studio agent through the UI.
Examples
Once configured, you can use Apache Pinot in Microsoft Copilot Studio:
List All Tables
Get overview of available Pinot tables
Ask Microsoft Copilot Studio: "Show me all the tables available in the Pinot cluster"
Expected Result: List of table names with basic metadata and schema information
Execute Analytics Query
Run SQL query for real-time analytics
Ask Microsoft Copilot Studio: "SELECT COUNT(*) FROM events WHERE timestamp > now() - 3600000"
Expected Result: Query results showing event count for last hour
Analyze Table Structure
Get detailed schema information for a table
Ask Microsoft Copilot Studio: "Show me the schema and structure of the user_events table"
Expected Result: Table schema with column names, types, and index information
Generate Data Visualization
Create histogram plots from query results
Ask Microsoft Copilot Studio: "Create a histogram showing user activity distribution by hour"
Expected Result: Histogram visualization with data aggregated by time buckets
Testing Your Setup
- Launch Microsoft Copilot Studio
- Verify Apache Pinot is available in the tools list
- Test basic Apache Pinot functionality
Troubleshooting
Common Issues
uv Command Not Found
Symptoms: Command not found error, uv: command not found
Cause: uv package manager not installed
Solution:
- Install uv using: curl -LsSf https://astral.sh/uv/install.sh | sh
- Restart terminal or source shell configuration
- Verify installation with: uv --version
Pinot Connection Failed
Symptoms: Connection refused, Broker unreachable, Network timeout
Cause: Invalid Pinot broker configuration or cluster not running
Solution:
- Verify PINOT_BROKER_HOST and PINOT_BROKER_PORT in .env file
- Check Pinot cluster is running and accessible
- Test connection: telnet <broker_host> <broker_port>
- Ensure firewall allows connections to Pinot broker
Environment Variables Not Loaded
Symptoms: Config missing errors, Default values being used
Cause: .env file not found or incorrectly formatted
Solution:
- Create .env file in repository root directory
- Copy from .env.example and modify values
- Ensure no spaces around = in variable assignments
- Restart MCP server after configuration changes
Claude Desktop Not Recognizing Server
Symptoms: Server not appearing, Connection errors in Claude
Cause: Incorrect claude_desktop_config.json configuration
Solution:
- Verify path to uv binary in command field
- Ensure correct path to mcp-pinot repository
- Restart Claude Desktop after configuration changes
- Check Claude Desktop logs for error messages
Generative Orchestration Not Available
Symptoms: MCP option not visible, Cannot add MCP connectors
Cause: Generative Orchestration is not enabled for your environment
Solution:
- Contact your Microsoft 365 administrator
- Enable Generative Orchestration in tenant settings
- Verify your subscription includes Copilot Studio with MCP support
- Check Microsoft Learn documentation for enablement steps
MCP Connector Connection Failed
Symptoms: Connector shows "Disconnected" status, Tools not available to agent
Cause: MCP server not accessible or configuration error
Solution:
- Verify the MCP server command is correct and accessible
- Check all required environment variables are set
- Test the MCP server independently outside Copilot Studio
- Review YAML schema for syntax errors
- Check network connectivity and firewall rules
Tools Not Appearing in Agent
Symptoms: Connector connected but tools missing, Agent cannot invoke MCP tools
Cause: Tools not added to agent or synchronization issue
Solution:
- Manually add tools to agent through Copilot Studio UI
- Refresh connector to sync latest tools from MCP server
- Verify tools are published on the MCP server
- Check agent configuration includes the MCP connector
MCP Server Tools Update Not Reflected
Symptoms: New tools not appearing, Old tools still visible after removal
Cause: Copilot Studio cache or sync delay
Solution:
- Disconnect and reconnect the MCP connector
- Wait a few minutes for dynamic sync to occur
- Manually refresh the connector in Copilot Studio
- Restart the MCP server if possible
Apache Pinot not appearing in Microsoft Copilot Studio
Symptoms: Server not listed, Tools not available
Cause: Configuration or installation issue
Solution:
- Verify configuration syntax
- Check Apache Pinot installation
- Restart Microsoft Copilot Studio
- Check logs for error messages
Next Steps
Now that Apache Pinot is integrated with Microsoft Copilot Studio:
- Explore all Apache Pinot capabilities through Microsoft Copilot Studio
- Check out other MCP servers that work with Microsoft Copilot Studio
- Join the MCP community for tips and support
- Consider contributing to Apache Pinot development
Need Help?
- Search for Apache Pinot documentation
- Check the Microsoft Copilot Studio MCP guide
- Join the MCP community discussions