- Newest
- Most votes
- Most comments
-
Run the Original Query:
SELECT id, other_columns FROM your_database.your_table;
Verify that this query returns the expected results without any errors.
-
Create the View with Explicit Column Aliases:
CREATE OR REPLACE VIEW my_view AS SELECT id AS id_column, other_columns FROM your_database.your_table;
-
Verify Column and Table Names:
Ensure that column names used in the SELECT clause match those in the source table.
-
Check Permissions and Refresh Metadata:
Ensure that your user has the necessary permissions and refresh metadata if needed.
I'll circle back on the fully qualified suggestions.
On security/permissions, I see a few suspect non standard roles and policies that were probably activated for a demo or a tutorial including:
AWSAthenaSparkDemoPolicy-cshh55sn AWSAthenaSparkExecutionRole-cshh55sn
an wondering if that could be causing conflict.
answered 2 years ago
Relevant content
asked 2 years ago
asked 6 years ago
- AWS OFFICIALUpdated 2 years ago
Also, consider checking if there are any WITH clauses or complex subqueries within your expression that might need to be explicitly referenced. Sometimes, Athena can have trouble resolving column references in nested or complex SQL without fully qualified column names or explicit aliases.
OK, I'll try these things. One thing you said got me thinking. Is id a keyword in Athena or SQL? Also, what do you mean by refresh metadata? I do think this sounds more like a permissions problem but I'm running as administrator and athena is pretty much in default setup. I wonder if I have corrupted my hive metastore? I'm using hive metastore shared with my databricks instance and many of these tables were created with databricks. But, I can run the query without problems. Its only when I try to save the query as a table or a view when I have the error.