How can I resolve the "View is stale; it must be re-created" error in Athena?
Last updated: 2021-03-30
When I run a view query in Amazon Athena, I get a "stale view" error like this: "SYNTAX_ERROR: line 1:15: View 'awsdatacatalog.mydatabase.myview' is stale; it must be re-created."
Athena reports a stale view under one of or more of the following conditions:
- The table or database that's specified in the view query doesn't exist.
- You tried to modify the table definition after you create the view.
- You tried to update the schema or metadata in a referenced table while recreating or updating the table.
For example, if you create a view and then run an ALTER TABLE ADD COLUMNS statement on the same table, then your view query fails with a "stale view" error. The stale view error also happens if you run an AWS Glue crawler to update the table definition. For more information, see Considerations for views.
To prevent "stale view" errors, recreate the view whenever you modify the underlying table or databases. You can also replace the view by adding the OR REPLACE clause in the CREATE VIEW statement. This clause lets you update the existing view by replacing it.