Best way of managing AWS Athena queries is by using Workgroups

Overview

AWS Athena workgroups can be used to separate users, teams, applications, or workloads and to set limits on amount of data each query or the entire workgroup can process, and to track their respective costs. Because workgroups act as resources, you can use resource-level identity-based policies to control access to a specific workgroup. You can also view query-related metrics in Amazon Cloudwatch, control costs by configuring limits on the amount of data scanned, create thresholds, and trigger actions, such as Amazon SNS, when these thresholds are breached.

Benefits of using Athena

Isolate users, teams, applications, or workloads using workgroups

• Each workgroup has its own distinct query history and a list of saved queries which you can easily view in the history tab of the Athena console. These queries are saved and segregated as per the workgroups defined
• For all queries in the workgroup, you can choose to configure workgroup settings. They include an Amazon S3 location for storing query results, and encryption configuration.

Define and enforce cost constraints

You can set two types of cost constraints for queries in a workgroup:

• Per-query limit is a threshold for the amount of data scanned for each query. Athena cancels queries when they exceed the specified threshold. The limit applies to each running query within a workgroup. You can set only one per-query limit and update it if required.
• Per-workgroup limit is a threshold you can set for each workgroup for the amount of data scanned by queries in the workgroup. Breaching a threshold activates an Amazon SNS alarm that triggers an action of your choice, such as sending an email to a specified user. You can set multiple per-workgroup limits for each workgroup.

Track query-related metrics for workgroups in Cloudwatch

• For each query that runs in a workgroup, you can configure the workgroups to publish the Athena metrics to AWS Cloudwatch.
• You can view the query metrics for each of your workgroups within the Athena console itself. On top of that in Cloudwatch, you can create custom dashboards, and set thresholds and alarms on these metrics which can send you email notifications when these thresholds are extended

Characteristic of Workgroups

1. By default, each account has a primary workgroup and the default permissions allow all authenticated users access to this workgroup. The primary workgroup cannot be deleted.
2. Each workgroup that you create shows saved queries and query history only for queries that ran in it, and not for all queries in the account. This separates your queries from other queries within an account and makes it more efficient for you to locate your own saved queries and queries in history.
3. Disabling a workgroup prevents queries from running in it, until you enable it. Queries sent to a disabled workgroup fail, until you enable it again.
4. You can set up workgroup-wide settings and enforce their usage by all queries that run in a workgroup. The settings include query results location in Amazon S3 and encryption configuration.

How to Setup Athena Workgroups?

1. Decide which workgroups you want to create. For example, you can decide the following:

• Who can run queries in each workgroup, and who owns workgroup configuration. This determines IAM policies you create.
• Which locations in Amazon S3 will be used to store the query results for queries that run in each workgroup. A location must exist in Amazon S3 before you can specify it for the workgroup query results. All users who use a workgroup must have access to this location.
• Which encryption settings is required, and which workgroups have queries that must be encrypted. It is recommended that you create separate workgroups for encrypted and non-encrypted queries. That way, you can enforce encryption for a workgroup that applies to all queries that run in it.

AWS Athena Workgroup Console

2. Create workgroups as needed, and then add tags to them. Open the Athena console, choose the Workgroup tab, and then choose Create workgroup to create a new workgroup

Create Workgroup Form Page — 1

3. Create IAM policies for your users, groups, or roles to enable their access to workgroups. The policies establish the workgroup membership and access to actions on a workgroup resource.

4. Set workgroup settings and specify a location in Amazon S3 for query results and encryption settings, if needed. You can enforce workgroup settings.

Create Workgroup Form Page — 2

5. Further you can configure cost control limits, also known as data usage control limits, for queries and workgroups. To notify you when a threshold is breached, you can create an Amazon SNS topic and configure subscriptions

Limitation of Workgroups

• You can create maximum up to 1000 workgroups per Region in your AWS account.
• The primary workgroup cannot be deleted and its the default workgroup if you do not create any custom workgroups
• You can open up to ten query tabs within each workgroup. When you switch between workgroups, your query tabs remain open for up to three workgroups.

References

1. Controlling Athena Query Access and Cost using Workgroups : https://docs.aws.amazon.com/athena/latest/ug/manage-queries-control-costs-with-workgroups.html
2. IAM policies for accessing Workgroups : https://docs.aws.amazon.com/athena/latest/ug/workgroups-iam-policy.html
3. Managing Workgroups : https://docs.aws.amazon.com/athena/latest/ug/workgroups-create-update-delete.html
4. Athena Workgroup APIs : https://docs.aws.amazon.com/athena/latest/ug/workgroups-api-list.html
5. Troubleshooting Workgroups : https://docs.aws.amazon.com/athena/latest/ug/workgroups-troubleshooting.html
6. Controlling Costs and Monitoring Queries with CloudWatch Metrics and Events : https://docs.aws.amazon.com/athena/latest/ug/control-limits.html