python - boto3 和 AWS Athena 权限

Question

我正在尝试使用 boto3, v. 1.7.4，通过以下脚本与 AWS Athena 交互:

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'})

以我账户当前的权限，读取s3_input内容的测试效果很好。我也可以通过 db_query 创建数据库，但表创建失败并显示以下错误消息:

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: [...])

如果我从控制台运行 table_query 命令，console.aws.amazon.com/athena/home , 使用相同的帐户，没有问题，表已正确创建。

权限是

{
   "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": "*"
       }
   ]
}

我很乐意了解我在这里缺少的东西。提前致谢。

Answer 1

这里是为需要从 Boto3 运行 athena 查询的用户创建策略的方法。

-- 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": "*"
        }
    ]
}

这是我为自动化所做的博客:https://www.sqlgossip.com/automate-aws-athena-create-partition-on-daily-basis/