Limit the data in SPICE from Athena table

[rishaja]

Hi folks,

Our datasource has 100GB of data (and growing) and it take a very long time to be loaded in quicksight with direct query (more than 60seconds). My environment is this

S3 → Glue → Athena → Quicksight.

We started using SPICE initially, where after 100GB of data, it is started giving timeout with failed to load.
After that we shifted direct query to Athena with partition on month, day, year using dataset parameter Using dataset parameters in Amazon QuickSight - Amazon QuickSight
. but it is turning out to be pretty slow.

For our usecase - we need only last 8 weeks at a time. How i can limit SPICE to load latest data only with last 8 weeks of data where it is refresh automatically daily?

[apjvinod]

Hi @rishaja

Welcome to QuickSight Community .

Either you can write custom sql in QuickSight to limit the data to last 8 weeks as shown below or you can have view in Athena with similar filter conditions to limit the data in SPICE.

 SELECT * FROM "cid_cur"."cur"  where bill_billing_period_end_date > ( current_date + interval '56' day)

Please check the below community post for additional details.

Setting data retention period on incremental refresh dataset?

Is it possible to set a data retention period on a SQL dataset with incremental refresh enabled? Or even manually delete old data from it? I’m trying to prevent my dataset from growing too large and really only need it to retain the previous 2 weeks of data at most… It adds about 3.5M rows of data daily so the size will grow out of hand quickly if I can’t control the retention period.

Thanks
VInod