# 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

```bash
# 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:

```bash
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

```json
{
  "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:

```json
{
  "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](https://docs.aws.amazon.com/sdk-for-javascript/v3/developer-guide/setting-credentials-node.html).

## Usage

### Basic example Using MCP Client (e.g. [Cline](https://cline.bot/))

Type the following into the MCP Client Window

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

Response:

```csv
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

```bash
# 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:**

```json
{
  "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

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

### Testing

```bash
# Run tests
npm test
```