I recieved a mail from quicksight that scheduled data reload has failed. Why does it happen and how can one avoid these errors
Hi @Tanu_13
Please refer the following post to resolve the timeout issue.
1 Like
Hi @Tanu_13 - Validate the data load from Quick, something may break.
2 Likes
Does full refresh and incremental refresh affect it?
Hi @Tanu_13
What kind of dataset is it? Is it from a relational database or some other source?
If it is relational you can check the query on the database layer to see if it is running long and causing a timeout. You may need to tweak the query to improve performance of the query; introduce indexes required etc.
Regards,
Giri
The datasource is RDS Mysql, and the query execution time is high due to joins
Hi @Tanu_13
If you have a database administrator for your mySQL he may be able to look at the query execution plan and determine if adding an index or organizing the query differently may make it run faster.
Net, this issue should be resolved from your backend. If the query runs in reasonable amount of time and uses the right amount of resources it will feed into QuickSight correctly.
Regards,
Giri
Yes, incremental load should be faster compare to full load, if you are having huge data, data transfer to QS spice may take time, always go for incremental.
@Sanjeeb2022 we are executing this every 12 hr and for some refresh the duration is 9 hrs.
Hi @Tanu_13
9 hrs is too long a time. I think this definitely needs to be looked at from the database level to see what ails the query execution plan.
Regards,
Giri
1 Like
Agree with @Giridhar.Prabhu .. Please tune your sql and if possible do an incremental load.
@Sanjeeb2022 The data volume is the reason for long duration of execution. If I opt for inrcremental refresh please explain how it works
Hi @Tanu_13 ,
Here are the instructions for incremental refresh.
Kindly try this and let us know.
Hi @Vaidy, we tried imcremental refresh for 24 hrs window size which was supposed to ingest some 4500 records. But even that failed after 10 hrs execution time.
@Sanjeeb2022 @Giridhar.Prabhu @Xclipse @Vaidy I need your opinion on this SPICE JOIN limit Amazon QuickSight launches a 20x higher limit for SPICE JOIN - AWS
The SPICE capacity of my account is as follows - Please let me know if the failure issue is related to this
Hi @Tanu_13
If joining multiple datasets from the same RDS MySQL source within Quick, create the join directly in MySQL. This avoids complex joins in Quick that can generate Cartesian products or heavy SQL with large row counts, which often cause timeouts. Use the joined SQL query as a single dataset in Quick.
As shared by @Giridhar.Prabhu, check with your database admin to increase query timeout settings if needed. Add indexes to the tables (especially join/filter columns). Review the query execution plan to identify slow parts of the query.
Check your RDS MySQL query logs to identify slow performing queries. Look for long running SELECT statements during Quick refresh times. Identify queries with high execution time or full table scans.
Common timeout causes could be multiple dataset joins creating cartesian products or heavy SQL or slow RDS instance (low CPU, insufficient instance).
Refer the documentation for Quick joining limitations.
I have one more doubt, I qm seeing this error in the visual which uses the dataset. Is it expected or should it show the data if filter pf date is selected before the date of issue refresh failure we have been facing
The error from your screenshot Data source/dataset deleted or became unavailable while QuickSight was ingesting data into SPICE indicates that either, the data source (RDS MySQL connection) was deleted, or the dataset was deleted/modified during the SPICE import process.
Check if the data source or dataset was accidentally deleted/modified.
Hi @Tanu_13,
Just checking back in since we haven’t heard from you in a bit. Were you able to see Xclipse’s reply and/or find a solution yourself in the meantime? If you still have any additional questions related to your initial post, please feel free to share them. Otherwise, any update you’re able to provide within the next 3 business days would be helpful for the community.
Thank you!
Hi @Tanu_13,
Since we haven’t received any further updates from you, I’ll treat this inquiry as complete for now. If you have any additional questions, feel free to create a new post in the community and link this discussion for context.
Thanks!