Problem with where clause with decimal data in AWS Athena.

Question

I am trying to run a query in AWS Athena with a decimal column (6,0), but the data does not return, even though the values exist. In the source database, the same query works.

re:Post Agent · Answer

When working with decimal values in AWS Athena, there are specific considerations for how to properly use them in WHERE clauses.

For your case with a decimal column of type decimal(6,0), the issue might be related to how you're specifying the decimal values in your WHERE clause. In Athena, you should explicitly specify the DECIMAL type when using decimal literals in queries.

Instead of writing:
```
WHERE decimal_column = 123
```

Try using the DECIMAL keyword with the value in single quotes:
```
WHERE decimal_column = DECIMAL '123'
```

This explicit typing helps Athena properly compare the values. If you're using Athena engine version 3, it has stricter type validation compared to version 2, which might explain why your query works in the source database but not in Athena.

Additionally, ensure that your IAM role has the necessary permissions to access both the Athena service and the AWS Glue Data Catalog (specifically the glue:Get permission), as missing permissions can sometimes cause unexpected query behavior.  
**Sources**  
[How to Display Decimal Values with 3 Decimal Places in Athena Table from Integer JSON Data Stored in S3? | AWS re:Post](https://repost.aws/questions/QUVlx90Hz1QD-hE3ZGY0lStw/how-to-display-decimal-values-with-3-decimal-places-in-athena-table-from-integer-json-data-stored-in-s3)  
[Athena Query Error using Python | AWS re:Post](https://repost.aws/questions/QUzbhTHj-uRSa7HZz0HSay1Q/athena-query-error-using-python)  
[Considerations for data types - Amazon Athena](https://docs.aws.amazon.com/athena/latest/ug/data-types-considerations.html)

Problem with where clause with decimal data in AWS Athena.

Add your answer

Relevant content