StartQueryExecution operation: Unable to verify/create output bucket

Question

I am trying to execute query on Athena using python.

Sample code

   client = boto3.client(
        'athena', 
        region_name=region,
        aws_access_key_id=AWS_ACCESS_KEY_ID,
        aws_secret_access_key=AWS_SECRET_ACCESS_KEY
    )
    execution = client.start_query_execution(
        QueryString=query,
        QueryExecutionContext={
            'Database': database
        },
        WorkGroup=workgroup,
        ResultConfiguration={
            'OutputLocation': S3_OUTPUT_LOCATION
        }
    )

This is working code, But I got an unusual scenario.

• One day it throws an InvalidRequestException error Error

InvalidRequestException: An error occurred (InvalidRequestException) when calling the StartQueryExecution operation: Unable to verify/create output bucket <BUCKET NAME>

• As per the DevOps application have all the permission, It should work.
• We try to execute the same query on the AWS Athena console(Query editor). There it is working.
• Then we re-run the python script, it is not throwing any error.
• But on the next day, the python script start's throwing the same InvalidRequestException error.
• Then we execute the same query on the AWS Athena console(Query editor) and re-run the python script, it started working.

We observed this scenario for a few days, Every 24 hours python script throws the error then we execute the query on the Athena console(Query editor) and re-run the python script. I don't understand why it is happening, is there any permission issue.

Permissions:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "athena:GetWorkGroup",
                "athena:StartQueryExecution",
                "athena:ListDatabases",
                "athena:StopQueryExecution",
                "athena:GetQueryExecution",
                "athena:GetQueryResults",
                "athena:GetDatabase",
                "athena:GetDataCatalog",
                "athena:ListQueryExecutions",
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::<BUCKET NAME>",
                "arn:aws:s3:::<BUCKET NAME>/*",
            ]
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:ListBucket",
                "athena:UpdateWorkGroup",
            ],
            "Resource": [
                "arn:aws:s3:::<BUCKET NAME>/*",
                "arn:aws:s3:::<BUCKET NAME>",
                "arn:aws:athena:*:<BUCKET NAME>/<PATH>",
            ]
        },
        {
            "Sid": "VisualEditor2",
            "Effect": "Allow",
            "Action": [
                "athena:ListDataCatalogs",
                "s3:ListAllMyBuckets"
            ],
            "Resource": "*"
        }
    ]
}

Answer 1

I also faced same error today and found that execution role requires s3:GetBucketLocation permission also, AWS doc: https://aws.amazon.com/premiumsupport/knowledge-center/athena-output-bucket-error/

Answer 2

I was experiencing the same issue - random failures. The issue turned out to be s3:GetBucketLocation policy being configured wrong. It was bundled with the same cluster as other s3 actions where the resource points to the s3 bucket, including path. It does not work this way.

I fixed it as below, works now.

- Effect: Allow
  Action:
    - s3:GetBucketLocation
  Resource:
    - arn:aws:s3:::*
- Effect: Allow
  Action:
    - s3:PutObject
    - s3:GetObject
  Resource:
    - arn:aws:s3:::<BUCKET NAME>/<PATH>/*

See documentation: https://docs.aws.amazon.com/AmazonS3/latest/userguide/using-with-s3-actions.html