I am using Athena as data source in quicksight.
How to apply filter on a column so that it scans only those ?
I have created a table in Athena as table1. And I have used partition projection on (event_date date) . The partition projection is working in Athena and data is populating there.
But when I used Quicksight to create a dashboard. I applied a filter on event_date, It fires a query to Athena and in where clause, It gives the event_date as
event_date=date_trunc('day', FROM_UNIXTIME(TO_UNIXTIME(CAST(FROM_UNIXTIME(TO_UNIXTIME(CAST(PARSE_DATETIME('2023-03-01 00:00:00', 'yyyy-MM-dd HH:mm:ss')
and It scans all the partition in the table because trunc_date function truncates the given timestamp to '2023-03-01 00:00:00.000'. And gives the error
HIVE_EXCEEDED_PARTITION_LIMIT: Query over table 'sample_db.table1' can potentially read more than 1000000 partitions
But when event_date string then in that case while applying the filter in quicksight it scans only given event_date.
How to fix this issue. So that It scans only given number of partition in case of event_date as date also?.
In this case , we can use Quicksight Dataset Parameter to control the query scanning.
The query will look like this:- First we have to create two parameter fromDate and toDate in dataset.
select * from table where date between <<$frmomDate>> and <<$toDate>>
After this , we have to map this dataset parameter to analysis parameter.