Skip to main content
Glama
ZackFairTS

AWS Athena MCP Server

by ZackFairTS

@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.


Related MCP server: Redshift MCP Server

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

Available Tools

5 tools
get_resultA

Get results for a completed query. Returns error if query is still running.

ParametersJSON Schema
NameRequiredDescriptionDefault
queryExecutionIdYesThe query execution ID
maxRowsNoMaximum number of rows to return (default: 1000)

TDQS

A3.5/5.0
Behavior3/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

With no annotations provided, the description carries the full burden of behavioral disclosure. It adds value by stating the error condition ('Returns error if query is still running'), which is useful context beyond the input schema. However, it lacks details on permissions, rate limits, response format, or pagination behavior (e.g., how maxRows affects output). For a tool with no annotations, this is a moderate disclosure, scoring a 3.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is extremely concise and front-loaded: two sentences that directly state the tool's function and a key behavioral constraint. Every sentence earns its place by providing essential information without waste, making it efficient and well-structured for an AI agent.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness3/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Given the tool's moderate complexity (2 parameters, no output schema, no annotations), the description is somewhat complete but has gaps. It covers the basic purpose and an error condition, but lacks details on return values (since no output schema exists), authentication, or how it integrates with sibling tools. For a result-retrieval tool, this is minimally adequate, scoring a 3.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema description coverage is 100%, so the input schema fully documents both parameters (queryExecutionId and maxRows). The description does not add any parameter-specific semantics beyond what the schema provides (e.g., it doesn't explain the format of queryExecutionId or how maxRows interacts with query results). According to the rules, with high schema coverage (>80%), the baseline is 3 even with no param info in the description, which applies here.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose4/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly states the tool's purpose: 'Get results for a completed query.' It specifies the verb ('Get') and resource ('results'), and distinguishes it from siblings like 'get_status' (which checks query status) and 'run_query' (which executes queries). However, it doesn't explicitly differentiate from 'list_saved_queries' or 'run_saved_query' in terms of result retrieval, making it a 4 rather than a 5.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines3/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description provides implied usage guidance: it should be used only for completed queries ('Returns error if query is still running'), suggesting an alternative might be 'get_status' to check completion first. However, it doesn't explicitly state when to use this tool versus alternatives like 'get_status' for status checks or 'run_query' for execution, nor does it mention prerequisites (e.g., needing a queryExecutionId from a prior run). This is adequate but has gaps, scoring a 3.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

get_statusC

Get the current status of a query execution

ParametersJSON Schema
NameRequiredDescriptionDefault
queryExecutionIdYesThe query execution ID

TDQS

C2.9/5.0
Behavior2/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

With no annotations provided, the description carries the full burden of behavioral disclosure. It states the action is to 'Get' status, implying a read-only operation, but doesn't specify whether this requires authentication, has rate limits, or details the return format (e.g., pending, completed, error). The description is minimal and misses key behavioral traits for a tool interacting with query executions.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is a single, clear sentence with no wasted words, making it highly concise and front-loaded. It directly states the tool's purpose without unnecessary elaboration, which is efficient for an AI agent to parse and understand.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness2/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Given the complexity of query execution tools and the lack of annotations and output schema, the description is incomplete. It doesn't explain what the status includes (e.g., progress indicators, error messages) or how it relates to sibling tools like 'get_result'. For a tool that likely returns dynamic execution state information, more context is needed to guide effective use.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

The input schema has 100% description coverage, with the single parameter 'queryExecutionId' documented as 'The query execution ID'. The description doesn't add any meaning beyond this, such as explaining where to obtain this ID or its format. Given the high schema coverage, a baseline score of 3 is appropriate, as the schema adequately handles parameter documentation.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose4/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly states the verb 'Get' and the resource 'current status of a query execution', making the purpose specific and understandable. However, it doesn't explicitly distinguish this tool from its siblings like 'get_result' or 'run_query', which might also relate to query execution status or results.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines2/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description provides no guidance on when to use this tool versus alternatives like 'get_result' or 'run_query'. It lacks context about prerequisites, such as needing a query execution ID from a previous operation, and doesn't mention any exclusions or specific scenarios for its use.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

list_saved_queriesA

List all saved (named) Athena queries available in your AWS account.

ParametersJSON Schema
NameRequiredDescriptionDefault

No parameters

TDQS

A3.7/5.0
Behavior2/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

With no annotations provided, the description carries full burden but only states what it does, not how it behaves. It doesn't disclose whether this requires specific IAM permissions, how results are paginated, if there are rate limits, or what the return format looks like.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

Single sentence with zero waste - every word contributes to understanding the tool's purpose. Front-loaded with the core action ('List all saved queries') followed by clarifying details.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness3/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

For a read-only listing tool with no parameters and no output schema, the description is adequate but minimal. It doesn't address behavioral aspects like authentication requirements, result format, or limitations that would help an agent use it correctly.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters4/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

The tool has 0 parameters with 100% schema description coverage, so the baseline is 4. The description appropriately doesn't discuss parameters since none exist, which is correct for this case.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly states the specific action ('List all saved queries') and identifies the target resource ('named Athena queries in your AWS account'). It distinguishes from siblings like run_saved_query (executes) and get_result/status (retrieves query results/metadata).

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines3/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description implies usage for retrieving available saved queries, but doesn't explicitly state when to use this versus alternatives like run_saved_query or when not to use it. No prerequisites or exclusions are mentioned.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

run_queryA

Execute a SQL query using AWS Athena. Returns full results if query completes before timeout, otherwise returns queryExecutionId.

ParametersJSON Schema
NameRequiredDescriptionDefault
databaseYesThe Athena database to query
queryYesSQL query to execute
maxRowsNoMaximum number of rows to return (default: 1000)
timeoutMsNoTimeout in milliseconds (default: 60000)

TDQS

A3.7/5.0
Behavior3/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

With no annotations provided, the description carries the full burden. It discloses key behavioral traits: it executes queries, returns full results if completed before timeout, otherwise returns a queryExecutionId. However, it lacks details on permissions, error handling, rate limits, or what 'full results' entails (e.g., format, pagination).

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is two sentences, front-loaded with the core purpose and followed by critical behavioral detail. Every word earns its place with no redundancy or fluff, making it highly efficient and well-structured.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness3/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Given no annotations, no output schema, and 4 parameters, the description is moderately complete. It covers the core action and timeout behavior but misses details like result format, error cases, or integration with sibling tools (e.g., how queryExecutionId relates to get_result). For a query execution tool, more context would be helpful.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema description coverage is 100%, so the schema fully documents all parameters. The description does not add meaning beyond what the schema provides (e.g., it doesn't explain parameter interactions or usage nuances). Baseline 3 is appropriate as the schema handles parameter documentation.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly states the specific action ('Execute a SQL query using AWS Athena') and the resource ('Athena'), distinguishing it from siblings like get_result (which retrieves results), get_status (checks status), list_saved_queries (lists saved queries), and run_saved_query (runs saved queries). It precisely defines what this tool does.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines3/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description implies usage by mentioning timeout behavior, but it does not explicitly state when to use this tool versus alternatives like run_saved_query or get_result. No explicit exclusions or prerequisites are provided, leaving usage context somewhat vague.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

run_saved_queryC

Execute a saved (named) Athena query by its query ID.

ParametersJSON Schema
NameRequiredDescriptionDefault
namedQueryIdYesAthena NamedQueryId
databaseOverrideNoOptional database override
maxRowsNoMaximum number of rows to return (default: 1000)
timeoutMsNoTimeout in milliseconds (default: 60000)

TDQS

C2.9/5.0
Behavior2/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

With no annotations provided, the description carries full burden for behavioral disclosure. It states the tool executes a query but doesn't mention what happens during execution (e.g., query runs on Athena, may take time, returns results directly or via reference), potential side effects, authentication needs, rate limits, or error handling. This is inadequate for a tool that likely involves computational resources and timeouts.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is a single, efficient sentence that front-loads the core purpose without unnecessary words. Every part earns its place by specifying the action, resource type, and key identifier, making it easy to parse quickly.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness2/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Given the complexity of executing Athena queries (involving timeouts, row limits, and potential async behavior), no annotations, and no output schema, the description is insufficient. It doesn't explain what the tool returns (e.g., results, status ID, error details), how to handle large results, or integration with sibling tools like 'get_status' for monitoring. This leaves critical gaps for an agent to use it effectively.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema description coverage is 100%, so the schema already documents all parameters thoroughly. The description doesn't add any parameter-specific information beyond implying 'namedQueryId' is required. It doesn't explain relationships between parameters (e.g., how databaseOverride interacts with saved query settings) or provide usage examples, so it meets the baseline but doesn't enhance understanding.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose4/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly states the action ('Execute') and resource ('saved (named) Athena query by its query ID'), making the purpose immediately understandable. It doesn't explicitly differentiate from sibling tools like 'run_query' or 'get_result', but the focus on saved/named queries provides some implicit distinction.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines2/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description provides no guidance on when to use this tool versus alternatives like 'run_query' (for ad-hoc queries) or 'get_result' (for retrieving results). It mentions saved/named queries but doesn't clarify prerequisites (e.g., needing a saved query ID from 'list_saved_queries') or when this is preferred over other execution methods.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

  1. 5 tool updatesv1.0.0
    • First observedget_result
    • First observedget_status
    • First observedlist_saved_queries
    • First observedrun_query
    • First observedrun_saved_query

TDQS

A3.7/5.0

Scored across 5 tools

Disambiguation5/5

Each tool has a distinct purpose: get_result retrieves query results, get_status checks query status, list_saved_queries lists saved queries, run_query executes a new SQL query, and run_saved_query executes a saved query. There is no overlap or ambiguity between these functions.

Naming Consistency5/5

All tool names follow a consistent verb_noun pattern (e.g., get_result, get_status, list_saved_queries, run_query, run_saved_query). The naming is uniform and predictable across all tools.

Tool Count5/5

With 5 tools, the server is well-scoped for AWS Athena operations. Each tool serves a clear purpose in the query lifecycle, from execution to result retrieval, without being overly complex or sparse.

Completeness5/5

The tool set covers the full CRUD/lifecycle for Athena queries: creating/executing queries (run_query, run_saved_query), reading results and status (get_result, get_status), and listing saved queries (list_saved_queries). There are no obvious gaps for the domain.

Maintenance

ActivityInactive
ResponsivenessNo issues

Related MCP Connectors

Related MCP Servers

  • A
    license
    Not graded
    quality
    B
    maintenance
    Enables execution of SQL queries against AWS Athena databases with schema discovery, query status management, and result retrieval through a standardized Model Context Protocol interface.
    23
    MIT
  • A
    license
    Not graded
    quality
    C
    maintenance
    Enables AI agents to query S3 data lakes using natural language, with support for CSV, JSON, Parquet and tools for data discovery, analysis, and metadata exploration.
    1
    MIT