Troubleshoot workgroup errors - Amazon Athena

Troubleshoot workgroup errors

Use the following tips to troubleshoot workgroups.

  • Check permissions for individual users in your account. They must have access to the location for query results, and to the workgroup in which they want to run queries. If they want to switch workgroups, they too need permissions to both workgroups. For information, see Use IAM policies to control workgroup access.

  • Pay attention to the context in the Athena console, to see in which workgroup you are going to run queries. If you use the driver, make sure to set the workgroup to the one you need. For information, see Specify a workgroup for queries.

  • If you use the API or the drivers to run queries, you must specify the query results location using one of the following ways: for individual queries, use OutputLocation (client-side). In the workgroup, use WorkGroupConfiguration. If the location is not specified in either way, Athena issues an error at query runtime.

  • If you override client-side settings with workgroup settings, you may encounter errors with query result location. For example, a workgroup's user may not have permissions to the workgroup's location in Amazon S3 for storing query results. In this case, add the necessary permissions.

  • Workgroups introduce changes in the behavior of the API operations. Calls to the following existing API operations require that users in your account have resource-based permissions in IAM to the workgroups in which they make them. If no permissions to the workgroup and to workgroup actions exist, the following API actions throw AccessDeniedException: CreateNamedQuery, DeleteNamedQuery, GetNamedQuery, ListNamedQueries, StartQueryExecution, StopQueryExecution, ListQueryExecutions, GetQueryExecution, GetQueryResults, and GetQueryResultsStream (this API action is only available for use with the driver and is not exposed otherwise for public use). For more information, see Actions, resources, and condition keys for Amazon Athena in the Service Authorization Reference.

    Calls to the BatchGetQueryExecution and BatchGetNamedQuery API operations return information only about queries that run in workgroups to which users have access. If the user has no access to the workgroup, these API operations return the unauthorized query IDs as part of the unprocessed IDs list. For more information, see Use Athena workgroup APIs.

  • If the workgroup in which a query will run is configured with an enforced query results location, do not specify an external_location for the CTAS query. Athena issues an error and fails a query that specifies an external_location in this case. For example, this query fails, if you override client-side settings for query results location, enforcing the workgroup to use its own location: CREATE TABLE <DB>.<TABLE1> WITH (format='Parquet', external_location='s3://amzn-s3-demo-bucket/test/') AS SELECT * FROM <DB>.<TABLE2> LIMIT 10;

You may see the following errors. This table provides a list of some of the errors related to workgroups and suggests solutions.

Workgroup errors
Error Occurs when...
query state CANCELED. Bytes scanned limit was exceeded. A query hits a per-query data limit and is canceled. Consider rewriting the query so that it reads less data, or contact your account administrator.
User: arn:aws:iam::123456789012:user/abc is not authorized to perform: athena:StartQueryExecution on resource: arn:aws:athena:us-east-1:123456789012:workgroup/workgroupname A user runs a query in a workgroup, but does not have access to it. Update your policy to have access to the workgroup.
INVALID_INPUT. WorkGroup <name> is disabled. A user runs a query in a workgroup, but the workgroup is disabled. Your workgroup could be disabled by your administrator. It is possible also that you don't have access to it. In both cases, contact an administrator who has access to modify workgroups.
INVALID_INPUT. WorkGroup <name> is not found. A user runs a query in a workgroup, but the workgroup does not exist. This could happen if the workgroup was deleted. Switch to another workgroup to run your query.
InvalidRequestException: when calling the StartQueryExecution operation: No output location provided. An output location is required either through the Workgroup result configuration setting or as an API input.

A user runs a query with the API without specifying the location for query results. You must set the output location for query results using one of the two ways: either for individual queries, using OutputLocation (client-side), or in the workgroup, using WorkGroupConfiguration.

The Create Table As Select query failed because it was submitted with an 'external_location' property to an Athena Workgroup that enforces a centralized output location for all queries. Please remove the 'external_location' property and resubmit the query.

If the workgroup in which a query runs is configured with an enforced query results location, and you specify an external_location for the CTAS query. In this case, remove the external_location and rerun the query.
Cannot create prepared statement prepared_statement_name. The number of prepared statements in this workgroup exceeds the limit of 1000. The workgroup contains more than the limit of 1000 prepared statements. To work around this issue, use DEALLOCATE PREPARE to remove one or more prepared statements from the workgroup. Alternatively, create a new workgroup.