Custom SQL not working in QuickSight — dataset fails to create/refresh

Q&A
,
1

Hi all,

I’m trying to build a dataset in Amazon QuickSight using custom SQL, but it isn’t working. My data source is Athena, and a normal table/visual import works fine; the problem only appears when I switch to custom SQL.

What happens: when I enter my query and click Save & publish, I get error message:
Your database generated a SQL exception. This can be caused by query timeouts, resource constraints, unexpected DDL alterations before or during a query, and other database errors. Check your database settings and your query, and try again.
What I’ve already checked: the same query runs successfully directly in Athena , the data source connection is valid, and I have SPICE selected.
Questions:

  1. Is there a known limitation or syntax restriction with custom SQL on this data source in QuickSight?

  2. Could this be a permissions issue (e.g. the QuickSight service role missing access to the underlying tables/S3)?

  3. Anything I should check in the dataset/SPICE settings?

Any pointers appreciated. Thanks!

2

Hi @vihaan.s and welcome to the Quick Community!
To address your questions;

  1. It could potentially be in response to the SQL, have you tried simplifying and running to see if it works? Then add by section to see where the error starts occurring?
    Additionally, I saw this article that seems it may be discussing a similar scenario?

  2. Yes this could be possible as well; I believe the service role does not access to S3 as well

  3. Nothing additional in the SPICE settings to check; you could try switching it to Direct Query to test out but I do not see that affecting the outcome.