[ERROR] [AthenaConnector::query] Error: Table offers_dataset not found in database ro_reporting while querying s3 tables with Athena

Question

[ERROR] [AthenaConnector::query] Error: Table offers_dataset not found in database ro_reporting
I have the following error while querying an s3 table in a table bucket with https://www.npmjs.com/package/athena-express.

Query looks like this: MERGE INTO "s3tablescatalog/my-bucket".ro_reporting.offers_dataset AS target

I have added the following policy to the ecs execution role:

{
        "Action": "s3tables:*",
        "Effect": "Allow",
        "Resource": [
          "arn:aws:s3tables:eu-central-1:accId:bucket/my-bucket",
          "arn:aws:s3tables:eu-central-1:accId:bucket/my-bucket/tables/*"
        ]
      }

is it any other policy i need to add? Note that when i ran the same code locally using saml2aws and credentials file it works, so it must be an issue with permissions. 
I also set some permissions in lake formation for this execution role with Super on database permissions.

Andreea · Accepted Answer

There was either an s3tables permissions or catalog permission.
either   "s3tables:ListTables" or  "glue:GetTables"

re:Post Agent · Answer

Based on your error message, it appears you're encountering a "Table not found" error when trying to query an S3 table using Athena. Since the query works locally with your credentials but fails when using the ECS execution role, this is indeed likely a permissions issue.

While you've added the s3tables:* permissions to your ECS execution role, you may be missing some required Lake Formation permissions. Even though you mentioned setting "Super" permissions on the database in Lake Formation, there are a few additional things to check:

1. Make sure your ECS execution role has the necessary AWS Glue permissions to access the table metadata. You likely need to add permissions like `glue:Get*` to your role.

2. Verify that you've properly registered the S3 table bucket catalog as a data source in Athena.

3. Ensure that you've granted specific Lake Formation permissions on the S3 table to your ECS execution role. The "Super" database permissions might not be sufficient - you may need to explicitly grant permissions on the table itself.

4. Check that you're using the correct catalog in your query. S3 Tables use a separate catalog from the default AWS Glue Data Catalog, which appears to be what you're doing with "s3tablescatalog/eos-ads-analytics-all-prod".

5. If you're using an opt-in region, make sure it's enabled for your account.

The most common resolution for this type of error is ensuring that the role has both the appropriate S3 permissions and the required Glue/Lake Formation permissions to access the table metadata.  
**Sources**  
[Athena Query Error using Python | AWS re:Post](https://repost.aws/questions/QUzbhTHj-uRSa7HZz0HSay1Q/athena-query-error-using-python)  
[S3 Tables - Unable to query using Athena | AWS re:Post](https://repost.aws/questions/QUKVZfbxcJRXSjyhr_-Qs5PQ/s3-tables-unable-to-query-using-athena)  
[Querying Amazon S3 tables with Athena - Amazon Simple Storage Service](https://docs.aws.amazon.com/AmazonS3/latest/userguide/s3-tables-integrating-athena.html)

[ERROR] [AthenaConnector::query] Error: Table offers_dataset not found in database ro_reporting while querying s3 tables with Athena

Add your answer

Relevant content