Tutorials

Create custom MCP server to Query Redshift - without code

H

Hoshang Mehta

Create custom MCP server to Query Redshift - without code

Amazon Redshift is where your massive datasets live—petabytes of clickstream data, financial logs, and user behavior history. But for an AI agent, this data is usually a black box.

Connecting an agent directly to Redshift is dangerous (costly queries, security risks) and difficult (VPC peering, complex drivers). Most teams end up building slow, brittle ETL pipelines just to move a slice of data to a place where an agent can read it.

Pylar solves this.

Pylar allows you to create a secure, read-only MCP (Model Context Protocol) server for your Redshift data warehouse in minutes. No ETL, no code, and no direct access to your raw tables.

Why Pylar for Redshift?

  • Zero-Copy: The agent queries data where it lives. You don't need to move petabytes of data.
  • Cost Control: Use Redshift's WLM (Workload Management) and Pylar's sandboxed views to prevent agents from running expensive queries.
  • Secure Access: Pylar handles the VPC connection logic (via IP whitelisting) and authentication.

Step-by-Step Walkthrough

Step 1: Connect Redshift to Pylar

  1. Get your Endpoint: In AWS Console, copy your Redshift endpoint URL (e.g., my-cluster.abc.us-east-1.redshift.amazonaws.com:5439).
  2. Whitelist IP: In your Redshift Security Group, add an Inbound Rule allowing TCP traffic on port 5439 from Pylar's IP: 34.122.205.142.
  3. In Pylar, go to Connections -> Redshift.
  4. Enter your Endpoint URL (remove the :5439 part), Port (5439), Database Name, Username, and Password.
    • Naming Rule: Use lowercase letters and underscores (e.g., redshift_analytics).

Redshift connection setup

Pylar will test the connection. If it fails, double-check your Security Group settings in AWS.

Step 2: Create a Sandboxed View

You don't want an agent scanning a 10-billion-row table. You want it to see aggregated insights.

In Pylar's SQL IDE, create an aggregated view:

-- view: quarterly_revenue_summary
SELECT 
  quarter,
  region,
  product_category,
  SUM(revenue) as total_revenue,
  COUNT(DISTINCT customer_id) as active_customers
FROM sales_events
GROUP BY 1, 2, 3

Creating a sandboxed view in SQL IDE

Now the agent queries this summary view, which is fast and cheap, instead of the raw sales_events table.

Step 3: Auto-Build the MCP Tool

  1. Select your view (quarterly_revenue_summary) in the right sidebar.
  2. Click "Create MCP Tool" and choose "Create with AI".
  3. Type a prompt: "Create a tool that gets revenue and customer count by region and quarter."
  4. Pylar generates the tool configuration:
    • Name: get_regional_revenue
    • Description: "Returns total revenue and active customers for a specific region and quarter."
    • Parameters: region (string), quarter (string)

Auto-generating MCP tool from view

Step 4: Publish & Connect

  1. Click "Publish" in the right sidebar.
  2. Click "Generate Token".
  3. Copy your MCP HTTP Stream URL (https://mcp.publish.pylar.ai/mcp) and Authorization Bearer Token.

Publishing MCP server

Connect to LangChain (Python):

from langchain_mcp import MCPTool

tool = MCPTool(
    url="https://api.pylar.ai/mcp/v1/server/YOUR_SERVER_ID",
    api_key="YOUR_API_KEY"
)
# The agent can now answer: "How did the APAC region perform in Q3?"

Advanced Use Cases for Redshift Agents

1. Executive Insights Chatbot

Goal: Give the CEO a chatbot that answers "How are we doing?" without waiting for a BI analyst.

  • View: Pre-aggregated KPIs (Revenue, DAU, Churn) joined with targets.
  • Tool Prompt: "Create a tool that compares current metrics against quarterly targets."
  • User Query: "Are we on track to hit our Q4 revenue target?"

2. Marketing Attribution Analysis

Goal: Let marketing managers query campaign performance.

  • View: campaign_spend joined with attributed_conversions.
  • Tool Prompt: "Create a tool that calculates ROAS (Return on Ad Spend) for a given campaign ID."
  • User Query: "What was the ROAS for the 'Summer Sale' campaign?"

Conclusion

Amazon Redshift is one of the most powerful data warehouses available, but giving AI agents direct access to it has always been risky—both from a security and cost perspective. With Pylar, you can safely unlock your Redshift data for AI agents in under 2 minutes—no coding required.

By connecting Pylar to your Redshift cluster (including Redshift Serverless), creating sandboxed views that aggregate data and prevent expensive full-table scans, and auto-generating MCP tools with natural language, you transform your data warehouse into the brain of your AI operations while maintaining complete control.

Whether you're building a marketing analytics agent, a revenue operations dashboard, or any other data-driven AI application, Pylar gives you the governance layer you need. Your views act as cost and security boundaries, preventing agents from running up your AWS bill or accessing unauthorized data. With support for Redshift Spectrum, you can even extend access to your S3 data lake seamlessly.

Stop worrying about runaway queries and security risks. Start unlocking the value of your data warehouse today.

Start building your Redshift MCP server for free at pylar.ai.