Skip to content

Athena INSERT via Lambda on Iceberg-backed S3 table fails with “Table not found” and empty database

0

Hello everyone,

I’m trying to use an AWS Lambda function to run an INSERT INTO … VALUES statement against an Iceberg-backed S3 table via Athena’s Python SDK. The table definitely exists in my AWS account—I can query it interactively in the Athena console under the correct catalog and database.

However, when I invoke my Lambda, the Athena query always errors out with: Table my_table not found in database my_database.This query ran against the "" database, unless qualified by the query. See how it says it ran against the "" database? That tells me Athena didn’t pick up my catalog/context settings. Even though I set up my code like this (placeholders used):

import boto3
athena = boto3.client("athena")

def handler(event, context):
    resp = athena.start_query_execution(
        QueryString="""
          INSERT INTO "my_database"."my_table"
          VALUES (1, 'test', 123);
        """,
        QueryExecutionContext={
            "Catalog": "my_custom_catalog",
            "Database": "my_database"
        },
        ResultConfiguration={"OutputLocation": "s3://my-results-bucket/"},
        WorkGroup="primary"
    )
    return {"QueryExecutionId": resp["QueryExecutionId"]}

What I’ve checked so far:

The catalog name and database name in QueryExecutionContext match exactly what appears in the Athena console. My Lambda’s IAM role has Athena and S3 permissions for the query and results bucket. I’ve tried both qualified ("my_db"."my_table") and unqualified (my_table) table names in the SQL. Questions for the community:

Has anyone seen Athena ignore QueryExecutionContext when invoked from Lambda, causing it to run against the default (empty) database? Are there additional IAM permissions or workgroup settings required to bind to a custom Data Catalog from Lambda?

1 Answer
0

The "TABLE_NOT_FOUND" error you're experiencing when running Athena queries from Lambda is a common issue that typically stems from permissions or configuration problems. Based on your description, there are several potential causes to investigate:

  1. IAM Role Permissions: The most likely cause is that your Lambda execution role is missing the necessary Glue permissions. Specifically, your Lambda role needs permissions like glue:GetTable and glue:GetDatabase to access the table metadata in the Glue Data Catalog. This is a common oversight - the role might have Athena permissions but not the required Glue permissions.

  2. Catalog Configuration: When using a custom catalog (not the default AwsDataCatalog), ensure that your Lambda role has the appropriate permissions to access that specific catalog. Your code shows you're using "my_custom_catalog" - verify that this catalog name is exactly correct, including case sensitivity.

  3. Query Execution Context: While your code appears to set the QueryExecutionContext correctly, the error message showing it ran against an empty database ("") suggests the context isn't being applied properly. Double-check that there are no typos in your catalog or database names.

  4. Cross-Account Access: If your Glue catalog and Lambda function are in different AWS accounts, you'll need to set up cross-account permissions.

  5. Workgroup Configuration: Verify that the "primary" workgroup doesn't override your query execution context settings.

To resolve this issue:

  • Add the missing Glue permissions to your Lambda execution role (glue:GetTable, glue:GetDatabase)
  • Verify the exact spelling and case of your catalog and database names
  • Implement more detailed error logging in your Lambda function to capture additional error details
  • Try running a simple SELECT query first to verify connectivity before attempting INSERT operations

This issue is particularly common when the Lambda role has permissions to execute Athena queries but lacks the necessary permissions to access the Glue Data Catalog metadata that Athena needs to locate your tables.
Sources
Athena Query Error using Python | AWS re:Post
AWS HealthLake: TABLE_NOT_FOUND Error in Lambda When Querying Athena | AWS re:Post
TABLE_NOT_FOUND when Running from Lambda but query runs successfully when running from editor | AWS re:Post

answered a year ago

EXPERT

reviewed a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.