AWS Athena query says table does not exists, but it shows on both athena and glue data catalogs

Question

When i ran this query:
SELECT * FROM "myTable" WHERE DATE(CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0'))) BETWEEN DATE('2023/08/01') AND DATE('2023/08/01') AND "myTable"."timestamp" BETWEEN 1690911588 AND 1690912488 ORDER BY "timestamp" DESC

Athena returned this error:
TABLE_NOT_FOUND: line 1:15: Table 'awsdatacatalog.default.myTable' does not exist
This query ran against the "default" database, unless qualified by the query.

I have already double checked that the table exists on both catalogs this was created via aws crawlers, i think this is not a IAM issue since i had a table that used the same IAM role and worked just fine, for now the policy allows to query any table on athena. What could i be missing here?

secondabhi_aws · Accepted Answer

Hi,

Yes, that's right, it doesn't appear to be a permission issue.

Can you make sure that you are running this query in the right database, from the error message it's clear that, this query was run in default database. I understand that myTable does exist but it may not be in default database.

Change the database from left pane dropdown and then run the query in the DB, where this table exists.

Hope you find this helpful.

Comment here if you have additional questions.

Abhishek

AWS Athena query says table does not exists, but it shows on both athena and glue data catalogs

Relevant content