aws-athena-mcp-server

AWS Athena MCP Server

A Model Context Protocol (MCP) server for AWS Athena, designed to integrate with n8n AI agents.

Overview

This MCP server provides a standardized way for n8n AI agents to query AWS Athena and retrieve data. It implements the Model Context Protocol, allowing n8n agents to:

1. List available databases and tables
2. Get table schemas and metadata
3. Execute SQL queries and retrieve results

Features

• Simple API for interacting with Athena from n8n AI agents
• Built-in health checks and monitoring
• Configurable through environment variables
• Containerized for easy deployment
• Kubernetes-ready with sample EKS manifests

Prerequisites

• AWS account with Athena access
• AWS credentials with appropriate permissions
• Docker for local development/testing
• Kubernetes/EKS for production deployment

Environment Variables

The server is configured using the following environment variables:

Available Tools

The MCP server provides the following tools to n8n AI agents:

execute_query

Execute SQL queries against Athena and retrieve results.

Parameters:

• query (string, required): SQL query to execute
• database (string, optional): Database name
• catalog (string, optional): Catalog name
• output_location (string, optional): S3 location for results
• workgroup (string, optional): Workgroup name
• max_results (integer, optional): Maximum number of results to return
• max_wait_seconds (integer, optional): Maximum time to wait for query completion

list_databases

List available databases in a catalog.

Parameters:

• catalog (string, optional): Catalog name

list_tables

List tables in a specific database.

Parameters:

• database (string, required): Database name
• catalog (string, optional): Catalog name

get_table_metadata

Get metadata for a specific table, including column definitions.

Parameters:

• table (string, required): Table name
• database (string, required): Database name
• catalog (string, optional): Catalog name

Local Development

Installation

1. Clone the repository:

git clone https://github.com/yourusername/aws-athena-mcp-server.git
cd aws-athena-mcp-server

2. Install dependencies:

pip install -r requirements.txt

3. Configure AWS credentials:

export AWS_ACCESS_KEY_ID=your_access_key
export AWS_SECRET_ACCESS_KEY=your_secret_key
export AWS_REGION=your_region
export ATHENA_OUTPUT_LOCATION=s3://your-bucket/folder/

4. Run the server:

python main.py

The server will be available at http://localhost:8050.

Docker Setup

Building the Docker Image

docker build -t athena-mcp-server:latest .

Running the Docker Container

docker run -p 8050:8050 \
  -e AWS_REGION=us-east-1 \
  -e ATHENA_OUTPUT_LOCATION=s3://your-bucket/folder/ \
  -e AWS_ACCESS_KEY_ID=your_access_key \
  -e AWS_SECRET_ACCESS_KEY=your_secret_key \
  athena-mcp-server:latest

Deploying to AWS EKS

Prerequisites

• AWS CLI configured with appropriate permissions
• kubectl installed and configured to connect to your EKS cluster
• Docker installed for building images

Deployment Steps

1. Build and push the Docker image to ECR:

# Create ECR repository if it doesn't exist
aws ecr create-repository --repository-name athena-mcp-server

# Get ECR login command
aws ecr get-login-password --region us-east-1 | docker login --username AWS --password-stdin ${AWS_ACCOUNT_ID}.dkr.ecr.us-east-1.amazonaws.com

# Build and tag the image
docker build -t ${AWS_ACCOUNT_ID}.dkr.ecr.us-east-1.amazonaws.com/athena-mcp-server:latest .

# Push the image to ECR
docker push ${AWS_ACCOUNT_ID}.dkr.ecr.us-east-1.amazonaws.com/athena-mcp-server:latest

2. Create IAM role for the EKS service account:

# Create IAM policy
aws iam create-policy \
  --policy-name AthenaQueryPolicy \
  --policy-document file://k8s/aws-policy.json

# Create IAM role for service account (IRSA)
eksctl create iamserviceaccount \
  --name athena-mcp-server \
  --namespace default \
  --cluster your-eks-cluster \
  --attach-policy-arn arn:aws:iam::${AWS_ACCOUNT_ID}:policy/AthenaQueryPolicy \
  --approve \
  --override-existing-serviceaccounts

3. Update Kubernetes manifest placeholders with actual values:

export AWS_ACCOUNT_ID=$(aws sts get-caller-identity --query Account --output text)
export AWS_REGION=us-east-1
export ATHENA_DEFAULT_DATABASE=your_database
export ATHENA_WORKGROUP=primary
export ATHENA_OUTPUT_LOCATION=s3://your-bucket/athena-results/

# Replace variables in YAML files
envsubst < k8s/deployment.yaml > k8s/generated/deployment.yaml
envsubst < k8s/serviceaccount.yaml > k8s/generated/serviceaccount.yaml

4. Apply Kubernetes manifests:

kubectl apply -f k8s/generated/serviceaccount.yaml
kubectl apply -f k8s/generated/deployment.yaml
kubectl apply -f k8s/generated/service.yaml

5. Verify deployment:

kubectl get pods -l app=athena-mcp-server
kubectl get service athena-mcp-server

6. (Optional) Create an ingress resource if you need external access.

Integration with n8n

To use this MCP server with n8n:

1. Deploy the MCP server to your infrastructure
2. Configure the n8n AI Agent node with the appropriate System Prompt (see docs/n8n_system_prompt_template.md)
3. Connect to the MCP server endpoint from n8n

n8n AI Agent System Prompt Template

Use the template in docs/n8n_system_prompt_template.md for the n8n AI Agent node System Prompt. This template guides the AI in:

• Understanding database schema
• Formulating efficient SQL queries
• Executing queries via the MCP server
• Presenting results clearly

Customize the template with your specific Athena database structure and common query patterns.

Documentation

Additional documentation can be found in the docs/ directory:

• n8n_system_prompt_template.md: Template for n8n AI Agent system prompt
• implementation_guide.md: Detailed implementation guide
• architecture.md: Architecture diagram and explanation
• troubleshooting.md: Solutions for common issues
• n8n_integration_example.md: Example n8n workflow
• examples/: Directory with example prompts for specific use cases

License

MIT

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.