I have a data flow from S3, Glue, Athena to Quicksight.
When I do the edit dataset, I’m able to see the data fine but if I go to the Analysis, always get this error for all columns : Your database generated a SQL exception. This can be caused by query timeouts, resource constraints, unexpected DDL alternations before or during a query, and other database errors.
I did give quicksight access to S3 Bucket, Athena and write permission for Athena Workgroup to the S3 bucket that the data comes from.
I’m also able to see the data if I do preview table in Athena as well
Hi Vivian,
Welcome to the QuickSight community.
Can you please clarify whether you are using Athena “Direct Query” or SPICE. It seem to me you are using “direct query”. I suspect issue may be due to data type mismatch error. Can you please check integer/decimal datatype has appropriate numeric values. I also see “aenergy” column with larger string, please also check whether this column may have few records with string value more 2047 character in length causing exception.
To troubleshoot (as a quick test) you can change direct query to SPICE and start ingestion. SPICE ingestion will also likely fail due to SQL exception and provide you specific erroneous records in dataset from SPICE refresh page. SPICE ingestion error codes - Amazon QuickSight
I would also recommend filing a case with AWS Support where we can dive into the details so that we can help you further. Here are the steps to open a support case. If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf. Hope this helps!
Additionally please also check time for your query execution and status in Athena Console query execution logs.
If your query times out, you can try these options to resolve your problem.
If the failure was generated while working on an analysis, remember that the Amazon QuickSight timeout for generating any visual is two minutes. If you’re using a custom SQL query, you can simplify your query to optimize running time.
If you are in direct query mode (not using SPICE), you can try importing your data to SPICE. However, if your query exceeds the Athena 30-minute timeout, you might get another timeout while importing data into SPICE. For the most current information on Athena limits, see Amazon Athena Limits
Hi, it’s a direct query.
The aenergy does not exceed 2047 characters.
Regarding the troubleshoot solution you suggested, I changed from Direct Query to Spice and kept getting “Last refresh failed” error.
On SPICE refresh page. can you please check the error. You will also able to download sample records having issues during SPICE refresh, if issue is related to data type mismatch.
I don’t have any error, just simply a text says refresh is failed. That’s why I couldn’t figure out the issue. But now when I go back to athena to query the data without LIMIT, I got this error:
HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException- which looks like the one of my rows are not the proper JSON object. Is it possible to make the changes within edit query in Quicksight via Direct Query (maybe by split or substring?)
You cannot change data in edit query page in QuickSight. Now as you got the json parsing error at Amazon Athena, please fix the issue in the source data.
