boto3 and AWS Athena permission

boto3 athena
boto3 athena waiter
python boto3 athena example
boto3 athena query example
python athena example
boto3 athena get query results
boto3 athena client
athena s3 permissions

I am trying to use boto3, v. 1.7.4, to interact with AWS Athena through the following script:

import boto3
import botocore

# Test access to the input bucket
bucket = boto3.resource('s3').Bucket('s3_input')
print(list(bucket.objects.all())

client = boto3.client('athena', region_name='us-east-1')

# Create a new database
db_query = 'CREATE DATABASE IF NOT EXISTS france;'
response = client.start_query_execution(
    QueryString=db_query,
    ResultConfiguration={'OutputLocation': 's3_output'})

# Create a new table
table_query = '''
CREATE EXTERNAL TABLE IF NOT EXISTS france.by_script (`content` string ) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ('separatorChar' = ',')
LOCATION 's3_input';'''

response = client.start_query_execution(
    QueryString=table_query,
    ResultConfiguration={'s3_output'},
    QueryExecutionContext={'Database': 'france'})

With the current permissions of my account, the test to read the content of s3_input works well. I can also create the database through the db_query but the table creation fails with the following error message:

Your query has the following errors:FAILED: Execution Error, return
code 1 from org.apache.hadoop.hive.ql.exec.DDLTask.
MetaException(message:Got exception: java.io.IOException
com.amazon.ws.emr.hadoop.fs.shaded.com.amazonaws.services.s3.model.AmazonS
Exception: Access Denied (Service: Amazon S3; Status Code: 403; Error Code:
AccessDenied; Request ID: [...]), S3 Extended Request ID: [...])

If I run the table_query command from the console, console.aws.amazon.com/athena/home, using the same account, there is no problem and the table is properly created.

The permissions are

{
   "Version": "2012-10-17",
   "Statement": [
       {
           "Sid": "VisualEditor0",
           "Effect": "Allow",
           "Action": "s3:GetObject",
           "Resource": "s3_input"
       },
       {
           "Sid": "VisualEditor1",
           "Effect": "Allow",
           "Action": [
               "s3:ListAllMyBuckets",
               "s3:HeadBucket"
           ],
           "Resource": "*"
       }
   ]
}

I would be happy to understand what I am missing here. Thanks in advance.


It turns out that the following permissions make it work

{
   "Version": "2012-10-17",
   "Statement": [
       {
           "Effect": "Allow",
           "Action": [
               "s3:Get*",
               "s3:List*"
           ],
           "Resource": "*"
       }
   ]
}

Athena, To stream query results successfully, the IAM principal with permission to call GetQueryResults also must have permissions to the Amazon S3 GetObject action for  IAM principals with permission to the Amazon S3 GetObject action for the query results location are able to retrieve query results from Amazon S3 even if permission to the GetQueryResults action is denied. To restrict user or role access, ensure that Amazon S3 permissions to the Athena query location are denied.


Here is the way to create policy for the user who needs to run athena query from Boto3.

-- S3 files bucket: sqladmin-cloudtrail
-- S3 output bucket: aws-athena-query-results-XXXXXXXXXX-us-east-1

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "s3:ListBucket",
            "Resource": [
                "arn:aws:s3:::aws-athena-query-results-XXXXXXXXXX-us-east-1",
                "arn:aws:s3:::sqladmin-cloudtrail"
            ]
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": "s3:PutObject",
            "Resource": "arn:aws:s3:::aws-athena-query-results-XXXXXXXXXXXXXXXX-us-east-1/*"
        },
        {
            "Sid": "VisualEditor2",
            "Effect": "Allow",
            "Action": [
                "s3:GetObjectAcl",
                "s3:GetObject",
                "s3:GetObjectTagging",
                "s3:GetBucketPolicy"
            ],
            "Resource": [
                "arn:aws:s3:::sqladmin-cloudtrail",
                "arn:aws:s3:::sqladmin-cloudtrail/*"
            ]
        },
        {
            "Sid": "VisualEditor3",
            "Effect": "Allow",
            "Action": [
                "athena:StartQueryExecution",
                "athena:CreateNamedQuery",
                "athena:RunQuery"
            ],
            "Resource": "*"
        }
    ]
}

Here is my blog I did for an automation: https://www.sqlgossip.com/automate-aws-athena-create-partition-on-daily-basis/

Resolve "Access Denied" Errors When Running Amazon Athena , If you change the default location of the results bucket (aws-athena-query-results​-*), be sure that the IAM user has permission to read and write  This allows for an efficient, easy setup connection to Athena using the Boto3 SDK as a driver. NOTE: Before using RAthena you must have an aws account or have access to aws account with permissions allowing you to use Athena.


I ran into the same problem as above, but in addition to the permissions mentioned by Flavien in the answer above my process (a Lambda function) needed to add also s3:PutObject and s3:AbortMultipartUpload.

Athena apparently creates objects named like folderName_$Folder$ in the SOURCE data folders, so it needs to have PutObject permission to that (not just read-only). Don't ask me why the AbortMultipartUpload is needed... but it comes straight from Athena docs at https://docs.aws.amazon.com/athena/latest/ug/access.html

The entire statement for your IAM policy looks like this:

        {
            "Effect": "Allow",
            "Action": [
                "s3:GetBucketLocation",
                "s3:GetObject",
                "s3:ListBucket",
                "s3:ListBucketMultipartUploads",
                "s3:ListMultipartUploadParts",
                "s3:AbortMultipartUpload",
                "s3:CreateBucket",
                "s3:PutObject"
            ],
            "Resource": [
                "arn:aws:s3:::your-source-data-bucket-name*"
            ]
        }

"Unable to Verify/Create Output Bucket" Error in Amazon Athena, Note: The s3:CreateBucket permission isn't required if the bucket already exists. The arn:aws:s3:::aws-athena-query-results-* permission isn't  Hi, Here is what I am trying to get . I have an application writing to AWS DynamoDb-> A Keinesis writing to S3 bucket. I use an ATHENA to query to the Data from S3 based on monthly buckets/Daily buckets to create a table on clean up data


Cross-account Access - Amazon Athena, Granting access to an AWS KMS-encrypted bucket in Account A to a user in Account B requires the following permissions: The bucket policy in Account A must  Introduction. In this article I will be demonstrating the use of Python along with the Boto3 Amazon Web Services (AWS) Software Development Kit (SDK) which allows folks knowledgeable in Python programming to utilize the intricate AWS REST API's to manage their cloud resources.


Lambda function to execute an query on ATHENA and store the , AWS ATHENA does not allow INSERT_INTO/INSERT_OVERWRITE to modify the table contents. s3_output = "s3://xxxx-results/" client = boto3.client('athena') It was simplest case of not having proper IAM permissions. The S3 location provided to save your query results is invalid. Please check your S3 location is correct and is in the same region and try again.. Since it works when you use the console, it is likely the bucket is in a different region than the one you are using in Boto3.


Run Amazon Athena's queries with AWS Lambda, Receive key data when an Event published and AWS lambda is executed. Run query at Amazon Athena and get the result from execution. If you want to delete output file # s3 client client = boto3.client('s3') # created s3  Boto3 generates the client from a JSON service definition file. The client’s methods support every single type of interaction with the target AWS service. Resources, on the other hand, are generated from JSON resource definition files. Boto3 generates the client and the resource from different definitions.