Custom SQL not working in Quick Sight — 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.

3

Hi @vihaan.s,

Just checking back in since we haven’t heard from you in a bit. I wanted to see if the guidance shared earlier helped resolve your question, or if you found a solution in the meantime.

If you still have any additional questions related to your initial post, 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.

4

Hi there,

Thanks for checking in! I actually managed to figure it out shortly after posting.

It turned out the issue wasn’t with QuickSight itself, but with my SQL query. I had assumed that because I selected the catalog in the UI, I didn’t need to explicitly mention it in my code. That wasn’t the case!Once I updated my query to explicitly include the catalog name, everything worked perfectly.

Thanks again for following up. You can go ahead and close this out or mark it as resolved!