Hello @thomashpunkt, this is something I have encountered as well and from what I understand, there is a limitation to the amount of time QuickSight will allow a query to run to build the column names. I want to say if it takes longer than 60 seconds to query the table format, then it will fail and not allow you to publish.
Are you running a lot of individual queries within the SQL statement and then joining them at the end?
It’s just 1 query, which runs once during the night as it takes 1,5 hrs to complete (into SPICE).
I could break joins up into different datasets, but it would make the whole thing unnecessarily complex. And even one joined SQL might in the end take more than 60 seconds.
I think it’s a valid use case to run long running SQLs once a night into SPICE. Quicksight should not disallow to save such statements after they take longer than 60 seconds. It feels broken to me.
Hello @thomashpunkt, sorry I think I worded this poorly. How complex is the SQL statement you are running? Do you have many select statements that query various tables/views from your database and then ultimately join them in the final select statement, or is a single select statement? Also, are you running many different calculations on the data?
I agree that this is rather cumbersome to say the least and has delayed updates for me personally on a few occasions, but it has forced me to write more efficient SQL statements. I’m mostly just curious if this is solely an issue of the quantity of data being ingested, or if there are changes I can suggest for how the SQL is written to ensure you can run the query.
Hi Dylan, no problem, I think I got your point anyway. There’s a lot of data, that needs to be aggregated and whatever I would change in the statement, I would never get below 60 seconds - so I guess we’re trapped here and can never again update the dataset.
Hello @thomashpunkt, the 60 seconds I mentioned above is solely for the table format, like all of the column names. It doesn’t need to ingest all of the data referenced in that amount of time. The one bummer is you will never be able to see the data being queried within the edit dataset page, as it won’t be able to pull all of the data in 60 seconds. I feel confident though that I could guide you on updating your dataset with a little more information, as I have run into this exact problem in the past.
Hey Dylan, the statement is 1 SELECT, which inner and left joins around 15 tables and subselects. I am pretty confident in optimizing the SQL, but even when I replace the SQL with a lighter SQL, the save & publish button stays disabled.
What have you done in the past to get around that?
Hello @thomashpunkt, okay, this is going to sound ridiculous, but for some reason it has fixed this issue in the past. Write the updated SQL in an editor like VSCODE or Notepad ++, whatever you prefer, and exit the edit dataset page. Once you have the query written how you want, re-enter the edit dataset page and as quickly as you can copy the updated SQL into the custom SQL editor on QuickSight and click apply.
For some weird reason lingering on the edit dataset custom SQL page when this issue occurs causes it to no longer accept any changes. Let me know if this trick fixes the problem on your end, I appreciate the information.
Hey Dylan, I’ve been in this business for a long time and such suggestions do not sound ridiculous at all by now. “Hacks” like that are just too common
However, in this case, it didn’t help.
I have even tried blocking the preview calls with an ad blocker, which blocked the calls successfully, but did not enable the “save & publish” button.
Then I tried to remove the button disabled attribute and trigger the click via the javascript console. No effect either.
Next thing would be checking the involved javascript, but that makes me feel uncomfortable, so I’ll call it day for now
What datasource is it in the backend / how much time a select limit 10 take to execute in the database ? . If the schema details are not retrieved within the timeout period , unfortunately the button is disabled.
- so I guess we’re trapped here and can never again update the dataset.