Amazon Redshift Model Context Protocol Gateway

This Python-based implementation adheres to the Model Context Protocol (MCP) specification, providing a standardized interface for AI agents to interact programmatically with Amazon Redshift clusters.

Overview

Redshift MCP Gateway serves as the bridge, enabling intelligent systems to perform complex data operations within a Redshift environment, such as:

• Enumerating database schemas and associated tables.
• Fetching the structural DDL (Data Definition Language) for any given table.
• Retrieving detailed operational statistics for data objects.
• Sending and receiving results from arbitrary SQL execution.
• Initiating table analysis procedures to update internal statistics.
• Obtaining detailed execution plans, including runtime metrics, for specific queries.

Deployment Prerequisites

To successfully deploy this service, ensure the following dependencies are met:

• Python runtime environment, version 3.13 or newer is required.
• Access to an operational Amazon Redshift cluster.
• Valid authentication credentials (cluster endpoint, port, access key, secret key, and target database name).

Source Code Integration

Obtain the source files via Git and install necessary Python packages:

bash

Obtain repository code

git clone https://github.com/Moonlight-CL/redshift-mcp-server.git cd redshift-mcp-server

Install dependencies using uv

uv sync

Configuration Variables

The service mandates the following environment variables for establishing a connection to the target Redshift instance:

RS_HOST=your-redshift-cluster.region.redshift.amazonaws.com RS_PORT=5439 RS_USER=your_login_name RS_PASSWORD=your_secret_key RS_DATABASE=target_data_warehouse RS_SCHEMA=default_search_path # Optional; defaults to 'public'

Configuration can be supplied via shell exports or a local .env file.

Operation

Launching the Service

Execute the server process using the following commands:

bash

Launch via uv, including dotenv support

uv run --with mcp python-dotenv redshift-connector mcp mcp run src/redshift_mcp_server/server.py

AI Assistant Integration

Configure your MCP-aware AI system using the following JSON snippet within its settings:

{ "mcpServers": { "redshift": { "command": "uv", "args": ["--directory", "src/redshift_mcp_server", "run", "server.py"], "env": { "RS_HOST": "your-redshift-cluster.region.redshift.amazonaws.com", "RS_PORT": "5439", "RS_USER": "your_login_name", "RS_PASSWORD": "your_secret_key", "RS_DATABASE": "target_data_warehouse", "RS_SCHEMA": "default_search_path" } } } }

Capabilities

Data Access Resources

Access endpoints are provided for metadata retrieval:

• rs:///schemas - Retrieves a comprehensive list of all available schemas.
• rs:///{schema}/tables - Lists all tables contained within the specified schema.
• rs:///{schema}/{table}/ddl - Fetches the complete Data Definition Language script for a selected table.
• rs:///{schema}/{table}/statistic - Returns current statistical metrics associated with the specified table.

Functionality Tools

Specific computational or analytical actions are exposed via these tools:

• execute_sql - Executes any provided SQL command against the Redshift cluster.
• analyze_table - Triggers an analytical scan on a table to refresh its statistics.
• get_execution_plan - Returns the optimized execution path and operational data for a given SQL statement.

Illustrative Examples

Schema Enumeration

access_mcp_resource("redshift-mcp-server", "rs:///schemas")

Table Listing (Public Schema)

access_mcp_resource("redshift-mcp-server", "rs:///public/tables")

DDL Retrieval

access_mcp_resource("redshift-mcp-server", "rs:///public/users/ddl")

Running a Query

use_mcp_tool("redshift-mcp-server", "execute_sql", {"sql": "SELECT user_id, name FROM public.users WHERE status = 'active' LIMIT 10"})

Table Analysis Invocation

use_mcp_tool("redshift-mcp-server", "analyze_table", {"schema": "public", "table": "users"})

Query Plan Retrieval

use_mcp_tool("redshift-mcp-server", "get_execution_plan", {"sql": "SELECT COUNT(*) FROM public.transactions WHERE transaction_date > '2024-01-01'"})

Development Structure

Directory Layout

redshift-mcp-server/ ├── src/ │ └── redshift_mcp_server/ │ ├── init.py │ └── server.py ├── pyproject.toml └── README.md

Required Packages

• mcp[cli]>=1.5.0 - Model Context Protocol Software Development Kit.
• python-dotenv>=1.1.0 - Utility for managing environment variables from dotfiles.
• redshift-connector>=2.1.5 - Official Python driver for interfacing with Amazon Redshift.