MCP Athena Analytics Server
Secure MCP server for querying analytics data through AWS Athena.
Overview
This MCP (Model Context Protocol) server provides Claude and other LLMs with controlled access to analytics data stored in S3/Athena. It implements multiple security layers to prevent data breaches, destructive operations, and excessive resource usage.
Use cases:
- Exploratory data analysis via natural language
- Ad-hoc queries without writing SQL manually
- Template-based reporting with parameter validation
- Data discovery (tables, schemas, available templates)
Architecture
HTTP-based deployment (recommended for production):
┌─────────────────────────────────────────────────────────────┐
│ claude-network (Docker bridge) │
│ │
│ ┌────────────────────────┐ ┌─────────────────────────┐ │
│ │ Claude Code Container │ │ MCP Server Container │ │
│ │ (claude-sandbox) │ │ (mcp-athena-server) │ │
│ │ │ │ │ │
│ │ Claude Code CLI │HTTP│ FastAPI/SSE Server │ │
│ │ ↓ │◄───┤ ↓ │ │
│ │ MCP Client (HTTP) │ │ MCP Protocol Handler │ │
│ │ │ │ ↓ │ │
│ │ 🚫 NO credentials │ │ Athena Tools │ │
│ │ │ │ ↓ │ │
│ └────────────────────────┘ │ boto3 + .env │ │
│ │ ↓ │ │
│ │ ✅ AWS Athena │ │
│ └─────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘
Security benefits:
- ✅ Credentials isolated in MCP server container
- ✅ Claude container has NO access to .env or AWS credentials
- ✅ Bearer token authentication between containers
- ✅ HTTP API as security boundary
- ✅ Network-level isolation via Docker bridge
Alternative: stdio mode (local development only):
- MCP server runs as subprocess of Claude Code
- ⚠️ Claude has access to all credentials (not recommended)
Features
4 MCP Tools
- execute_template - Run template with validated parameters
- execute_query - Execute ad-hoc SELECT query (validated)
- list_tables - Show available Athena tables
- get_table_schema - Get table DDL (CREATE TABLE statement)
Security Layers
1. Query Validation
- Ad-hoc queries: Only SELECT and WITH (CTE) allowed
- Forbidden keywords: DROP, DELETE, CREATE, ALTER, INSERT, TRUNCATE, REPLACE
- Whitelist approach: Query must start with SELECT or WITH
2. Template Blacklist
- DROP_TABLE - Permanently blocked (destructive)
- DROP_TABLE_IF_EXISTS - Permanently blocked (destructive)
- Extensible: Add more templates to blacklist.py as needed
3. Resource Limits
- Max rows: 100,000 (results truncated if exceeded)
- Max timeout: 600 seconds (10 minutes)
- Scan warning: 100 GB (logged but not blocked)
4. Data Sanitization
- Sensitive columns auto-detected: password, token, secret, key, credential, api_key, etc
- Redaction: Values replaced with
***REDACTED*** - Applied to all results before returning to LLM
5. AWS Authentication
- IRSA (IAM Roles for Service Accounts): Temporary credentials via Kubernetes
- No static keys: Credentials refreshed automatically
- Default profile: Sandbox/replica buckets (read-only production data)
Installation
Prerequisites
- Docker & Docker Compose
- AWS credentials (~/.aws/credentials with ATHENA_ANALYTICS_INTERNAL profile)
- Active Athena setup
Setup (HTTP-based, recommended)
# 1. Create .env file in home directory
cp mcp_athena_analytics/.env.example ~/.mcp_athena_analytics.env
# 2. Generate authentication token
openssl rand -hex 32
# 3. Edit .env file and set MCP_AUTH_TOKEN
nano ~/.mcp_athena_analytics.env
# 4. Start claude-sandbox first (creates network)
cd claude-sandbox
docker compose up -d
# 5. Start MCP server container (joins network)
cd ../mcp_athena_analytics
docker compose up -d --build
# 6. Verify server is running
docker ps | grep mcp-athena-server
curl http://localhost:8000/health
# Expected: {"status":"healthy","service":"mcp-athena-analytics"}
# 7. Configure Claude Code CLI
cd ..
cp .mcp.json.example .mcp.json
# 8. Edit .mcp.json and set MCP_AUTH_TOKEN (same as in ~/.mcp_athena_analytics.env)
nano .mcp.json
Note: .env file is stored in home directory (~/.mcp_athena_analytics.env) to keep credentials outside project directory.
Setup (stdio mode, local development only)
Note: stdio mode is deprecated. Use HTTP-based deployment instead.
For local testing without Docker:
# 1. Install dependencies
pip install -r mcp_athena_analytics/requirements.txt
pip install -r app/requirements.txt
# 2. Create .env file
cp mcp_athena_analytics/.env.example ~/.mcp_athena_analytics.env
nano ~/.mcp_athena_analytics.env
# 3. Test server locally (HTTP mode)
PYTHONPATH=. python mcp_athena_analytics/server_http.py
Usage
Claude Code CLI (HTTP mode)
MCP server configuration is in .mcp.json (created from .mcp.json.example):
{
"mcpServers": {
"athena-analytics": {
"type": "sse",
"url": "http://mcp-athena-server:8000/sse",
"headers": {
"Authorization": "Bearer YOUR_TOKEN_HERE"
}
}
}
}
Note: URL uses container name mcp-athena-server for Docker DNS resolution (both containers in claude-network).
Important: Replace YOUR_TOKEN_HERE with the same token from mcp_athena_analytics/.env
Start Claude Code and enable MCP server:
/mcp # In Claude Code CLI
Note: .mcp.json is in .gitignore (local config), .mcp.json.example is tracked.
Testing
Use curl to test HTTP endpoints:
# Health check
curl http://localhost:8000/health
# Test with MCP client (from claude-sandbox container)
docker exec -it claude-sandbox bash
curl http://mcp-athena-server:8000/health
Tool Examples
execute_query
Run ad-hoc SELECT query:
{
"tool": "execute_query",
"arguments": {
"sql_query": "SELECT COUNT(*) FROM provider__actions_alpha WHERE year = 2024"
}
}
execute_template
Run registered template with params:
{
"tool": "execute_template",
"arguments": {
"template_name": "AGG_RTP",
"params": {"year": 2024, "month": 1, "brand": "alpha"}
}
}
Security Best Practices
What's Allowed
✅ SELECT queries (read-only) ✅ WITH (Common Table Expressions) ✅ Registered templates (except blacklisted) ✅ Table metadata queries
What's Forbidden
❌ DROP TABLE (data destruction) ❌ DELETE/INSERT/UPDATE (data modification) ❌ CREATE/ALTER (schema modification)
Data Access
- Athena (S3): ✅ Read-only access via MCP server
- PostgreSQL: ❌ NOT accessible (by design)
- Reason: Direct DB access bypasses audit trail
- Alternative: Use Athena (data replicated to S3)
Troubleshooting
Server won't start
Error: ImportError: No module named 'mcp'
Fix: pip install mcp
Error: ImportError: No module named 'app.lib.aws'
Fix: Set PYTHONPATH:
export PYTHONPATH=/path/to/analytics:$PYTHONPATH
python mcp_athena_analytics/server.py
Error: botocore.exceptions.NoCredentialsError
Fix: Configure AWS credentials (~/.aws/credentials profile ATHENA_ANALYTICS_INTERNAL)
Queries timeout
Symptoms: Queries exceed 600s
Fixes:
- Add partition filters (year, month, day)
- Reduce date range
- Use aggregated tables instead of raw data
Results truncated
Symptoms: "truncated: X rows → 100,000 rows"
Fixes:
- Add LIMIT clause:
SELECT ... LIMIT 10000 - Add WHERE filters to reduce results
- Use GROUP BY aggregation
Sensitive data not redacted
Fix: Add pattern to sanitizer.py:
SENSITIVE_PATTERNS = [
r"\bpassword\b",
r"\buser_api_key\b", # Add your pattern
]
Architecture
File Structure
mcp_athena_analytics/
├── server.py # MCP server entrypoint
├── config.py # Path setup
├── logging_setup.py # Logging configuration
├── athena_tools/
│ ├── registry.py # Tool registry (ToolConfig, TOOL_REGISTRY)
│ ├── execute_query.py # Ad-hoc query tool
│ ├── execute_template.py
│ ├── list_tables.py
│ └── get_table_schema.py
├── blacklist.py # Template blacklist
├── validator.py # Query validation
└── sanitizer.py # Data redaction
Data Flow
Claude Code CLI
↓ (JSON-RPC over stdio)
server.py
↓ (setup_paths, load_env)
TOOL_REGISTRY
↓ (route to tool.execute())
athena_tools/*.py
↓ (validate, check blacklist)
AthenaHelper
↓ (boto3)
AWS Athena → S3
↓ (results)
sanitizer.py (redact PII)
↓ (JSON)
Claude Code CLI
Development
Running Tests
# Test individual modules
python -m mcp_athena_analytics.blacklist
python -m mcp_athena_analytics.validator
python -m mcp_athena_analytics.sanitizer
Adding New Tools
- Create tool module in
athena_tools/my_tool.py:
TOOL_NAME = 'my_tool'
TOOL_DESCRIPTION = 'What this tool does'
TOOL_INPUT_SCHEMA = {...}
def execute(param1: str) -> dict[str, Any]:
"""Implementation."""
return {'result': '...'}
- Add to
athena_tools/registry.py:
from mcp_athena_analytics.athena_tools import my_tool
TOOL_REGISTRY: list[ToolConfig] = [
# ...
ToolConfig(
name=my_tool.TOOL_NAME,
description=my_tool.TOOL_DESCRIPTION,
input_schema=my_tool.TOOL_INPUT_SCHEMA,
execute=my_tool.execute,
),
]
Tool automatically appears in list_tools() and call_tool().
Logging
- stdout: Reserved for MCP protocol (don't pollute!)
- stderr: Error messages and debugging
- File logs:
tmp/mcp_server_YYYYMMDD_HHMMSS.log
License
Internal analytics project.
Support
- Check troubleshooting section above
- Review server logs in
tmp/mcp_server_*.log - Test with MCP Inspector
- Contact analytics team