15

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": "*"
        }
    ]
}
NHD
  • 435
  • 1
  • 6
  • 17
  • Experiencing the exact same problem. Why does AWS allow such complexity? There is simply no reason to allow this in the console but not in the CLI. – Steve Gon Apr 20 '21 at 19:59
  • Any update on this, are you able to fix this issue, I am facing a similar one. – Prakash Jul 28 '21 at 12:05

2 Answers2

13

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

Hannes R.
  • 1,379
  • 16
  • 23
  • 1
    This worked for me as well! – Nikunj Dec 23 '21 at 05:32
  • Thanks - this recently started affecting me - after over a year of using the same exact IAM configuration with Athena v2 workgroup - suddenly in the past couple of weeks queries started failing sporadically due to the stated error, and adding this permission seems to help. – mbafford Apr 24 '23 at 13:50
9

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/

Hansraj Das
  • 209
  • 4
  • 7