AWS Athena

What is aws-athena-mcp?

aws-athena-mcp is a Model Context Protocol (MCP) server designed to enable AI assistants to interact with AWS Athena. It allows AI assistants to execute SQL queries against AWS Athena databases and retrieve results, effectively bridging the gap between AI and data stored in AWS Athena.

How to use aws-athena-mcp?

To use aws-athena-mcp, you need to follow these steps:

1. Configure AWS credentials: Set up your AWS credentials using AWS CLI configuration, environment variables (AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY), or an IAM role if running on AWS.
2. Add the server to your MCP configuration: Include the aws-athena-mcp server in your MCP configuration file, specifying the OUTPUT_S3_PATH for query results and optionally configuring AWS region, profile, access keys, Athena WorkGroup, query timeout, and retry settings.
3. Utilize provided tools: The server exposes several tools that AI assistants can use to interact with Athena:
   • run_query: Execute SQL queries.
   • get_status: Check the status of a query execution.
   • get_result: Retrieve results for a completed query.
   • list_saved_queries: List all saved (named) queries in Athena.
   • run_saved_query: Run a previously saved query by its ID.

Key Features of aws-athena-mcp

• SQL Query Execution: Allows AI assistants to run SQL queries directly on AWS Athena.
• Query Status Monitoring: Provides functionality to check the status of ongoing Athena queries.
• Result Retrieval: Enables retrieval of full query results once a query is completed.
• Saved Query Management: Supports listing and executing pre-defined saved queries in Athena.
• Configurable AWS Settings: Offers options to configure AWS region, profiles, and access credentials.
• Customizable Query Parameters: Allows setting parameters like maxRows and timeoutMs for queries.

Use Cases of aws-athena-mcp

aws-athena-mcp can be used in various scenarios where AI assistants need to interact with data in AWS Athena, including:

• Data Exploration: AI assistants can be prompted to explore databases, list tables, and retrieve table schemas.
• Data Analysis: Running advanced SQL queries with filtering and aggregation to extract insights from data.
• Automated Reporting: Generating reports by programmatically querying Athena databases.
• Chatbot Integration: Integrating data querying capabilities into chatbots, allowing users to ask natural language questions about their data.
• Business Intelligence: Enabling AI-driven business intelligence by providing real-time access to data in Athena.

FAQ about aws-athena-mcp

• What are the requirements to run aws-athena-mcp?

  • Node.js >= 16
  • AWS credentials with appropriate Athena and S3 permissions
  • An S3 bucket for query results
  • Named queries (optional) must exist in the specified ATHENA_WORKGROUP and AWS_REGION.

• How do I configure AWS credentials?

  • You can configure AWS credentials using AWS CLI configuration, environment variables (AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY), or an IAM role.

• What is the OUTPUT_S3_PATH used for?

  • The OUTPUT_S3_PATH is a required environment variable that specifies the S3 bucket where Athena query results will be stored.

• Can I specify a different Athena WorkGroup?

  • Yes, you can specify the Athena WorkGroup using the ATHENA_WORKGROUP environment variable.

• How do I run a saved query?

  • You can run a saved query using the run_saved_query tool, providing the namedQueryId of the saved query.

• What happens if a query times out?

  • If a query times out, the run_query tool will return only the queryExecutionId for later retrieval of results.

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

Usage

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
      }
    }
  }
}

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