What permissions are needed for Athena queries?

[andy-han]

In the documentation for making read_sql_query requests, it says that if you choose to use ctas_approach=True it requires create/delete table permissions on Glue. If I also set ctas_database_name, why do I still need permissions on the database?

For example:

df = wr.athena.read_sql_query(
            SELECT * FROM my_table LIMIT 10;",
            database="temp1",
            ctas_database_name="temp2",
            boto3_session=session,
        )

is erroring unless I give my role permission to CreateTable and DeleteTable on 'temp1'. Can you provide a list of iam and LakeFormation permissions or more verbose examples of how to use "ctas_database_name"?

I want wrangler to have no access to create/delete tables in my main db but full access to do whatever it wants in the ctas_database_name database for any temporary tables

[jaidisido]

Thank you for raising this @andy-han, I believe it's a bug on our end. Specifically this line where the temporary table is deleted once the submitted query has completed. At the moment it always attempts to delete a table in the original database, regardless of whether a ctas_database_name parameter was provided or not. Modifying that line to:

catalog.delete_table_if_exists(database=ctas_database_name or database, table=name, boto3_session=boto3_session)

should solve the issue. I will raise a PR to mitigate this.

[jaidisido]

This has now been merged to main and can be tested:

pip uninstall awswrangler -y
pip install git+https://github.com/awslabs/aws-data-wrangler.git@main

until it's available in the next release.

[andy-han]

Thanks!