markryanbotha/AthenaMCPServer

AthenaMCPServer

An MCP (Model Context Protocol) server for querying Amazon Athena. This server allows AI assistants to execute SQL queries against AWS Athena and retrieve results in a structured format.

Features

• Execute SQL queries against Amazon Athena
• Authenticate to AWS account using profiles
• Format results as JSON, CSV, or formatted tables
• Configurable database, region, and output location

Setup

# Install dependencies
npm install

Configuration

The server uses the following environment variables for configuration:

• AWS_REGION: AWS region where Athena is located (default: "us-east-1")
• ATHENA_WORKGROUP: Athena workgroup to use (default: "primary")
• ATHENA_OUTPUT_LOCATION: S3 location for query results (default: "s3://aws-athena-query-results/")
• ATHENA_DATABASE: Default database to query (default: "default")
• ATHENA_CATALOG: Default catalog/data source to query (default: "AwsDataCatalog")
• AWS_PROFILE: AWS Profile to use for authentication (default: undefined, and will fallback to using default AWS credentials provider chain)

You can set these environment variables before running the server:

export AWS_REGION=us-west-2
export ATHENA_WORKGROUP=my-workgroup
export ATHENA_OUTPUT_LOCATION=s3://my-bucket/athena-results/
export ATHENA_DATABASE=my_database
export ATHENA_CATALOG=AwsDataCatalog
export AWS_PROFILE=aws_profile_name

Alternatively, you can set these environment variables in the MCP Client configuration (see below).

Configuration for MCP Client

{
  "mcpServers": {
    "athena-mcp": {
      "command": "npm",
      "args": [
        "--prefix",
        "/Users/<alias>/workplace/AthenaMCPServer/src/AthenaMCPServer",
        "start"
      ],
      "disabled": false,
      "env": {
        "AWS_PROFILE": "<aws-profile-for-account-with-athena>",
        "ATHENA_OUTPUT_LOCATION": "s3://<your-athena-query-results-bucket>"
      },
      "autoApprove": []
    }
  }
}

AWS Authentication

The server uses the AWS SDK's default credential provider chain. You can authenticate using any of the following methods:

1. Environment variables (AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY)
2. Shared credentials file (~/.aws/credentials)
3. EC2 instance profile or ECS task role
4. AWS SSO

Using AWS Profiles

You can specify an AWS profile from your shared credentials config file (~/.aws/config) when executing queries:

{
  "query": "SELECT * FROM my_table LIMIT 10",
  "profile": "my-aws-profile"
}

This is useful when you have multiple AWS accounts or roles configured in your credentials file.

For more information, see the AWS SDK documentation.

Usage

Basic example Using MCP Client (e.g. Cline)

Type the following into the MCP Client Window

use the athena mcp server to run a basic query and return in csv

Response:

test
1

You can pass it a query directly, or use other LLMs to generate the SQL for you and ask it to run it using the Athena MCP Server

Starting the Server

# Start the server
npm start

Available Tools

query_athena

Executes SQL queries against Amazon Athena and retrieves results.

Parameters:

• query (required): SQL query to execute
• database (optional): Database to query (uses default if not specified)
• catalog (optional): Catalog (Data source) to query (uses AwsDataCatalog if not specified)
• region (optional): AWS region (uses default if not specified)
• format (optional): Result format (json, table, or csv)
• profile (optional): AWS profile name to use from shared credentials file

Example:

{
  "query": "SELECT * FROM my_table LIMIT 10",
  "database": "my_database",
  "catalog": "AwsDataCatalog",
  "format": "table",
  "profile": "my-aws-profile"
}

Development

Project Structure

athena-mcp/
├── src/
│   ├── core/
│   │   └── aws/
│   │       └── athena-client.ts
│   ├── tools/
│   │   └── query-athena/
│   │       └── tool.ts
│   ├── cli.ts
│   └── index.ts
├── package.json
├── tsconfig.json
└── README.md

Running in Development Mode

# Run in development mode with auto-reload
npm run watch

Testing

# Run tests
npm test