- Newest
- Most votes
- Most comments
The error you're encountering is due to a mismatch between the data types supported by AWS Glue and those supported by Athena. While AWS Glue supports the 'varchar' data type, Athena does not directly support it when querying tables.
Athena uses Presto as its query engine, which has a different set of supported data types. In Athena, the equivalent of 'varchar' is typically 'string'. This is why your query is successful when you use 'string' as the data type for all columns.
To resolve this issue, you have a few options:
-
Use 'string' instead of 'varchar' when defining your columns in the AWS Glue Data Catalog. This is the simplest solution and will work well with Athena queries.
-
If you need to maintain the 'varchar' data type in Glue for other purposes, you may need to create a separate table definition specifically for Athena use, where you map 'varchar' to 'string'.
-
You could also consider using AWS Glue ETL jobs to transform your data and create a new table with compatible data types for Athena.
It's important to note that while 'varchar' and 'string' are functionally similar, 'string' in Athena doesn't have a length limit, whereas 'varchar' in some systems allows you to specify a maximum length. If you need to enforce length constraints, you may need to handle this in your application logic or through data validation processes.
When working with AWS Glue, Athena, and other AWS services, it's always a good practice to consult the documentation for the specific data types supported by each service to ensure compatibility across your data pipeline.
Sources
Data types - AWS Glue DataBrew
