Hi Friends,
I am trying to visualise Athena table data in QuickSight.
When I try to see the data in QuickSight, I get difference in decimal places.
results from Athena query-
select col0,
SUM(CAST(col8 AS DECIMAL(18, 8))) AS total_col8
from daily
where accountid = '*******'
and month = '06'
and col2 = 'AmazonGuardDuty'
group by col0
| Date |
Value |
| 30/6/24 |
1.16788816 |
| 29/6/24 |
1.14775233 |
| 28/6/24 |
1.12271908 |
| 27/6/24 |
1.12252700 |
| 26/6/24 |
1.09848388 |
| 25/6/24 |
1.10528968 |
| 24/6/24 |
1.15987429 |
result from QuickSight dashboard-
| Date |
cost |
| Jun 30, 2024 |
1.16776559 |
| Jun 29, 2024 |
1.14763031 |
| Jun 28, 2024 |
1.12258474 |
| Jun 27, 2024 |
1.12241922 |
| Jun 26, 2024 |
1.09836680 |
| Jun 25, 2024 |
1.10517135 |
| Jun 24, 2024 |
1.15973471 |
so the data is matching only to three decimal places.
I have attempted these things-
- Created fresh Data Source
- Changed data type Decimal-fixed, Decimal-Float
- Changed Query Mode from “Spice” to “Direct Query”
The result is same.
Could you please help.
Thank you
Amit Raj
In your Athena query, you cast col8 to DECIMAL(18, 8) at the row level before applying the SUM function. Have you tried aggregating with SUM first and then casting the result to DECIMAL(18, 8)?
You might need to apply the cast at both the row and aggregated levels. Additionally, using the ROUND function in QuickSight can help ensure that the results are displayed with the same precision as in Athena.
Hi @robdhondt
Thank you for replying. I tried aggregating with SUM first. but this results in error-
This is because col8 is a string type in Athena
FUNCTION_NOT_FOUND: line 4:7: Unexpected parameters (varchar) for function sum. Expected: sum(double), sum(real), sum(bigint), sum(interval day to second), sum(interval year to month), sum(decimal(p,s))
we convert col8 to float type in QuickSight under datasets.
I was thinking to create a Calculated Custom Field that does the same thing as my SQL query in Athena.
I was looking for functions for CAST but I do not find any function in QuickSight.
how can we CAST in QuickSight?
I tried with parseDecimal but this does not allowed decimal point precision as an argument.
I tried to use ROUND function but that also did not work.
| Date |
whats in athena |
What I get in QuickSight(Spice DB, col8 float type) |
What I get with round({col8}, 2) |
What I get with round({col8}, 3 |
| Jun 30, 2024 |
1.16788816 |
1.16776559 |
1.2 |
1.167 |
| Jun 29, 2024 |
1.14775233 |
1.14763031 |
1.2 |
1.15 |
| Jun 28, 2024 |
1.12271908 |
1.12258474 |
1.17 |
1.123 |
| Jun 27, 2024 |
1.122527 |
1.12241922 |
1.17 |
1.123 |
| Jun 26, 2024 |
1.09848388 |
1.0983668 |
1.15 |
1.099 |
| Jun 25, 2024 |
1.10528968 |
1.10517135 |
1.15 |
1.106 |
| Jun 24, 2024 |
1.15987429 |
1.15973471 |
1.17 |
1.16 |
I am quite surprised why round({col8}, 2 I get 1.2 for the athena value 1.16788816
Thank you
Have you considered editing the dataset and defining the Athena string column as Numeric or Decimal to the precision you require? You can do a Cast in Quicksight but it’s a bit painful. It seems to me that if you change the incoming data it will resolve the issue.
If you look at the field in the Dataset it has an ellipses menu (three vertical dots), click that and select “Choose data type”. It doesn’t allow for precision but maybe it will work?
The other way of doing it is in the transformation stage where you are getting the Athena data but I have no idea how your system works. Ours is SQLServer to Redshift with a process in between, written in Redshift, to transform the data. We then link Redshift to Quicksight. In the transformation, I set precision and data types etc.
1 Like
Hi @RichardAustin, did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!
Hi @Xclipse
Yes, please close this for now. I am working on transforming the data and see if that work.
Thank you
