# 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)
```bash
# 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:
```bash
# 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`):
```json
{
"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:
```bash
/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:
```bash
# 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:
```json
{
"tool": "execute_query",
"arguments": {
"sql_query": "SELECT COUNT(*) FROM provider__actions_alpha WHERE year = 2024"
}
}
```
### execute_template
Run registered template with params:
```json
{
"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:
```bash
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`:
```python
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
```bash
# 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`:
```python
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`:
```python
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](https://modelcontextprotocol.io/)
- [MCP Inspector](https://github.com/modelcontextprotocol/inspector)
