AWS athena database query

The error you're encountering suggests that you're trying to access a table or schema from a different database than the one you're currently using in your Athena query. To overcome this error, you have a few options:

1. Fully qualify your table names: In your query, use the full three-part name for tables, including the catalog, database, and table name. For example:

   SELECT * FROM AwsDataCatalog.nightingale_silver_subscription_prod_us_east_1_db.your_table_name

2. Set the database context: Before running your main query, use the "USE" statement to set the context to the correct database:

   USE nightingale_silver_subscription_prod_us_east_1_db; -- Your main query here

3. Cross-database querying: If you need to query tables from multiple databases in a single query, make sure to fully qualify the table names for tables not in the current database context.

4. Check permissions: Ensure that the IAM role or user executing the query has the necessary permissions to access both databases and their respective tables.

5. Verify database names: Double-check that the database names in your query exactly match those in your Athena/Glue catalog, including case sensitivity.

6. Use a federated query: If the databases are in different AWS accounts or regions, you might need to set up a federated query to access them together.

If the issue persists after trying these solutions, it may be worth reviewing your Athena and Glue configurations to ensure all databases and tables are properly registered and accessible.
Sources
Query fails (inconsistency) after last athena update | AWS re:Post
TABLE_NOT_FOUND when Running from Lambda but query runs successfully when running from editor | AWS re:Post
Athena Query Error using Python | AWS re:Post

Hi,

Agree with the re:post agent, you should fully qualify the database and table name when running the query cross database in Athena query editor.

Sharing sample query example here for reference,

Even after adding catalog.database.tablename I still receive the same error

Hi, Could you please provide more details like, are you able to run the query against the tables successfully if you select the corresponding database in the down drop navigation pane on the left side and only have issues when joining the tables from different databases ?

Is it possible for you to share the screenshot here of running the query with the corresponding database selected on the drop down and the error message when joining the tables ?