@lishenxydlgzs/aws-athena-mcp

A Model Context Protocol (MCP) server for running AWS Athena queries. This server enables AI assistants to execute SQL queries against your AWS Athena databases and retrieve results.

Features:

• Execute SQL queries via AWS Athena

• Support for both stdio (local) and Lambda + API Gateway (remote) deployment

• OAuth 2.0 authentication via AWS Cognito (Lambda deployment)

• Async query execution with status polling

• Named query support

Deployment Options

Option 1: Local (stdio) - For MCP Clients

Use with Claude Desktop, Cline, or other MCP clients:

Option 1: Local (stdio) - For MCP Clients

Use with Claude Desktop, Cline, or other MCP clients:

1. Configure AWS credentials using one of the following methods:

   • AWS CLI configuration

   • Environment variables (AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY)

   • IAM role (if running on AWS)

2. Add the server to your MCP configuration:

{
  "mcpServers": {
    "athena": {
      "command": "npx",
      "args": ["-y", "@lishenxydlgzs/aws-athena-mcp"],
      "env": {
        // Required
        "OUTPUT_S3_PATH": "s3://your-bucket/athena-results/",
        
        // Optional AWS configuration
        "AWS_REGION": "us-east-1",                    // Default: AWS CLI default region
        "AWS_PROFILE": "default",                     // Default: 'default' profile
        "AWS_ACCESS_KEY_ID": "",                      // Optional: AWS access key
        "AWS_SECRET_ACCESS_KEY": "",                  // Optional: AWS secret key
        "AWS_SESSION_TOKEN": "",                      // Optional: AWS session token
        
        // Optional server configuration
        "ATHENA_WORKGROUP": "default_workgroup",      // Optional: specify the Athena WorkGroup
        "QUERY_TIMEOUT_MS": "300000",                 // Default: 5 minutes (300000ms)
        "MAX_RETRIES": "100",                         // Default: 100 attempts
        "RETRY_DELAY_MS": "500"                       // Default: 500ms between retries
      }
    }
  }
}

Option 2: Lambda + API Gateway - For Remote Access

Deploy as a serverless API with OAuth 2.0 authentication:

# 首次部署（交互式配置）
./deploy.sh

# 或快速部署（使用已有配置）
./deploy-quick.sh

部署脚本会自动：

• 构建 TypeScript 代码

• 使用 SAM 部署到 AWS

• 创建 Cognito User Pool 和 App Client

• 配置 API Gateway OAuth 认证

• 输出完整的 OAuth 配置信息（包括 Client Secret）

• 保存配置到 .env.oauth 文件

部署后输出示例：

================================================
🎉 部署配置信息
================================================

📡 API 端点:
   https://xxxxx.execute-api.us-east-1.amazonaws.com/prod/mcp

🔐 OAuth 认证配置:
   Client ID:     xxxxxxxxxxxxxxxxxxxxx
   Client Secret: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   Token URL:     https://xxxxx.auth.us-east-1.amazoncognito.com/oauth2/token
   Scopes:        athena-mcp-api/read athena-mcp-api/write

🏢 AWS 资源:
   User Pool ID:  us-east-1_xxxxx
   Function ARN:  arn:aws:lambda:us-east-1:xxxxx:function:xxxxx

测试部署：

# 测试 OAuth 认证
./test-cognito-auth.sh

# 测试查询执行
./test-oauth-query.sh "SELECT * FROM my_table LIMIT 5" "my_database"

手动获取配置（如果需要）：

# Get Client ID and Token URL from CloudFormation outputs
aws cloudformation describe-stacks --stack-name aws-athena-mcp-stack \
  --query "Stacks[0].Outputs"

# Get Client Secret
aws cognito-idp describe-user-pool-client \
  --user-pool-id <USER_POOL_ID> \
  --client-id <CLIENT_ID> \
  --query "UserPoolClient.ClientSecret" \
  --output text

Client Integration:

// See examples/oauth-client-example.ts for full implementation
import { AthenaMcpClient } from './examples/oauth-client-example';

const client = new AthenaMcpClient({
  clientId: process.env.COGNITO_CLIENT_ID!,
  clientSecret: process.env.COGNITO_CLIENT_SECRET!,
  tokenUrl: process.env.COGNITO_TOKEN_URL!,
  apiEndpoint: process.env.API_ENDPOINT!,
});

await client.initialize();
const result = await client.runQuery('my_db', 'SELECT * FROM my_table LIMIT 10');

For detailed OAuth setup instructions, see OAUTH-SETUP-GUIDE.md.

Available Tools

The server provides the following tools:

• run_query: Execute a SQL query using AWS Athena

  • Parameters:

    • database: The Athena database to query

    • query: SQL query to execute

    • maxRows: Maximum number of rows to return (default: 1000, max: 10000)

  • Returns:

    • If query completes within timeout: Full query results

    • If timeout reached: Only the queryExecutionId for later retrieval

• get_status: Check the status of a query execution

  • Parameters:

    • queryExecutionId: The ID returned from run_query

  • Returns:

    • state: Query state (QUEUED, RUNNING, SUCCEEDED, FAILED, or CANCELLED)

    • stateChangeReason: Reason for state change (if any)

    • submissionDateTime: When the query was submitted

    • completionDateTime: When the query completed (if finished)

    • statistics: Query execution statistics (if available)

• get_result: Retrieve results for a completed query

  • Parameters:

    • queryExecutionId: The ID returned from run_query

    • maxRows: Maximum number of rows to return (default: 1000, max: 10000)

  • Returns:

    • Full query results if the query has completed successfully

    • Error if query failed or is still running

• list_saved_queries: List all saved (named) queries in Athena.

• Returns:

  • An array of saved queries with id, name, and optional description

  • Queries are returned from the configured ATHENA_WORKGROUP and AWS_REGION

• run_saved_query: Run a previously saved query by its ID.

• Parameters:

  • namedQueryId: ID of the saved query

  • databaseOverride: Optional override of the saved query's default database

  • maxRows: Maximum number of rows to return (default: 1000)

  • timeoutMs: Timeout in milliseconds (default: 60000)

• Returns:

  • Same behavior as run_query: full results or execution ID

Usage Examples

Show All Databases

Message to AI Assistant: List all databases in Athena

MCP parameter:

{
  "database": "default",
  "query": "SHOW DATABASES"
}

List Tables in a Database

Message to AI Assistant: Show me all tables in the default database

MCP parameter:

{
  "database": "default",
  "query": "SHOW TABLES"
}

Get Table Schema

Message to AI Assistant: What's the schema of the asin_sitebestimg table?

MCP parameter:

{
  "database": "default",
  "query": "DESCRIBE default.asin_sitebestimg"
}

Table Rows Preview

Message to AI Assistant: Show some rows from my_database.mytable

MCP parameter:

{
  "database": "my_database",
  "query": "SELECT * FROM my_table LIMIT 10",
  "maxRows": 10
}

Advanced Query with Filtering and Aggregation

Message to AI Assistant: Find the average price by category for in-stock products

MCP parameter:

{
  "database": "my_database",
  "query": "SELECT category, COUNT(*) as count, AVG(price) as avg_price FROM products WHERE in_stock = true GROUP BY category ORDER BY count DESC",
  "maxRows": 100
}

Checking Query Status

{
  "queryExecutionId": "12345-67890-abcdef"
}

Getting Results for a Completed Query

{
  "queryExecutionId": "12345-67890-abcdef",
  "maxRows": 10
}

Listing Saved Queries

{
  "name": "list_saved_queries",
  "arguments": {}
}

Running a Saved Query

{
  "name": "run_saved_query",
  "arguments": {
    "namedQueryId": "abcd-1234-efgh-5678",
    "maxRows": 100
  }
}

Requirements

• Node.js >= 16

• AWS credentials with appropriate Athena and S3 permissions

• S3 bucket for query results

• Named queries (optional) must exist in the specified ATHENA_WORKGROUP and AWS_REGION

License

MIT

Repository

GitHub Repository