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

1. execute_template - Run template with validated parameters
2. execute_query - Execute ad-hoc SELECT query (validated)
3. list_tables - Show available Athena tables
4. 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:

1. Add partition filters (year, month, day)
2. Reduce date range
3. Use aggregated tables instead of raw data

Results truncated

Symptoms: "truncated: X rows → 100,000 rows"

Fixes:

1. Add LIMIT clause: SELECT ... LIMIT 10000
2. Add WHERE filters to reduce results
3. 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

1. 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': '...'}

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

1. Check troubleshooting section above
2. Review server logs in tmp/mcp_server_*.log
3. Test with MCP Inspector
4. Contact analytics team

References

• MCP Documentation
• MCP Inspector