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):

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)

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:

Usage

Claude Code CLI (HTTP mode)

MCP server configuration is in .mcp.json (created from .mcp.json.example):

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:

Note: .mcp.json is in .gitignore (local config), .mcp.json.example is tracked.

Testing

Use curl to test HTTP endpoints:

Tool Examples

execute_query

Run ad-hoc SELECT query:

execute_template

Run registered template with params:

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:

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:

Architecture

File Structure

Data Flow

Development

Running Tests

Adding New Tools

1. Create tool module in athena_tools/my_tool.py:

2. Add to athena_tools/registry.py:

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