Athena Workgroups

Athena Workgroups

Amazon Athena allows you to use workgroups to separate users, teams, applications, or workloads. Workgroups enable you to set limits on the amount of data each query or workgroup can process and help track costs. Additionally, workgroups act as resources, allowing you to apply resource-level identity-based policies to control access to a specific workgroup.

Viewing CloudWatch Metrics

In the Create Tables lab, we enabled CloudWatch metrics for the primary workgroup. Now, let’s review the CloudWatch metrics for the primary workgroup in the Athena console.

  1. Open the Athena console.
  2. Select Workgroups from the left-hand menu. Connect
  3. Click on the primary workgroup from the list of workgroups. Connect
  4. Navigate to the Metrics tab in the Workgroup details screen. You will see several metrics, including: Connect
    • Total data scanned
    • Total successful queries
    • Total failed queries
    • Total execution time Connect

The displayed graphs may have limited data points, as they reflect the usage of your lab account.

Enforcing Cost Constraints

Athena SQL query pricing is based on the amount of data scanned. Workgroups allow you to enforce cost constraints by setting a maximum data scan limit for queries.

We will now configure a workgroup to restrict the maximum data scanned per query.

  1. In the Athena console, select Workgroups from the left-hand menu.
  2. Click WorkgroupA to view its details. Connect
  3. Go to the Data Usage Controls tab and click Manage. Connect
  4. Set the maximum data scanned per query in this workgroup to 15 Megabytes (MB).
  5. Click Save. Connect

Testing the Workgroup Limit

To test the new workgroup limit, log in as a new user. You will first retrieve the login credentials from AWS Secrets Manager.

  1. In the AWS Management Console, search for CloudFormation and select it. Connect

  2. Click the workshop stack name (e.g., athena-workshop). Connect

  3. Navigate to the Outputs tab.

  4. Click the ConsolePassword link to open AWS Secrets Manager. Connect

  5. Click on the Secret Name link. Connect

  6. Click Retrieve secret value and copy the password to your clipboard. Connect

  7. Click on the copy icon to copy the password to clipboard Connect

  8. Return to the CloudFormation Outputs tab and click the ConsoleLogin link to open the AWS login page. Connect

  9. Enter the following details:

  • IAM User Name: userA
  • Password: Paste the copied password. Connect

  1. Click Sign in. Connect Once logged in as UserA:

  2. Open the Athena console by searching for “Athena” in the AWS search bar. Connect

  3. Click Launch Query Editor. Connect

  4. On the Athena dashboard click the “Launch query editor” button. If you get the prompt below click the Acknowledge button Connect

  5. Select WorkgroupA from the Workgroup dropdown. Connect

  6. Copy and paste the following queries into the query editor:

/* This query will fail as it exceeds the 15 MB limit */
SELECT * FROM customers_csv;

/* This query will succeed as it only scans 12 MB of data */
SELECT * FROM customers_parquet;
  1. Highlight the first query and click Run.
    • The query fails with a Bytes scanned limit exceeded error since it surpasses the 15 MB limit. Connect
  2. Highlight the second query and click Run.
    • The query succeeds as it scans less than 15 MB of data. Connect

Using Workgroups to Isolate Queries

Workgroups allow you to isolate queries for different users, teams, or applications and enforce different query limits. IAM policies control access to specific workgroups.

To demonstrate this, attempt to switch workgroups as UserA:

  1. In the Workgroups dropdown, select WorkgroupB.

  2. An Error fetching workgroup message may appear because UserA lacks permissions to access WorkgroupB. Connect

  3. Copy and paste the previous queries into the query editor.

  4. Highlight the first query and click Run.

    • An error appears since UserA does not have permission to execute queries in WorkgroupB. Connect

This demonstrates how Athena Workgroups can be used to isolate queries for different users, teams, and applications. Additionally, workgroup data limits help control query costs effectively.