I have chained queries where the primary query fetches data from an external database and returns a “long” table of results and then simple separate queries that return subsets of that based on a dropdown parameter, e.g. SELECT * from cached_query_123 WHERE area = '{{ area }}'. This latter query doesn’t seem to be updating. I have set a refresh schedule of one hour, but the query table doesn’t show a latest execution time at all and dashboards (which have a global parameter) show old data. The query does execute fine if I run it manually or hit the refresh button on the dashboard, but it just doesn’t update in the background and not on dashboard page load either. What’s wrong here? How or why does parametrization affect the updating?
This is on a self-hosted Redash 7 running via Docker.
When you set a schedule for a parameterized query, the scheduler will only execute the query with the default parameter value. This execution won’t be saved in as the latest_query_data_id, but will be available if you open the query.
The list of queries shows “Last Executed At” blank for all parametrized queries
The query itself shows “Updated X hours ago” where X is greater than the defined update schedule
Visualizations on the dashboard also show “(refresh icon) X hours ago” where X is greater than the defined update schedule
Both the query and dashboard have the same default parameter value.
Is latest_query_data_id what the dashboard uses? I’d expect if a user opens a dashboard, the data it shows should be up to date without needing to hit refresh.
We’ve been seeing the same thing. I believe it started when we upgraded to 7.0, but it’s possible it was happening with 6.0 as well. Most of our queries are set to update everyday, but now we have to regularly tell people to hit “Refresh” on dashboards to view updated data. The majority of our queries are also using parameters, we have over 600 queries and 90 dashboards (if this matters). Our main data source is a PostgreSQL database.
From the looks of things, the queries are just not updating at all, going multiple days without running. Looking at the ‘query_results’ table, you don’t see any results either.
Hi @vnnw, can you provide more information about what isn’t working on your end? Which version of Redash are you using? Self-hosted or not? Which data sources don’t appear to be updating?
I can’t reproduce this. Here’s what I tried:
Create a scheduled, parameterized query that hits Postgres
Use the query results data source to select a portion of the returned data from step 1.
Monitor the results of step 2 to confirm that the query from step 1 runs regularly and the query result is successfully updating.
Nothing unexpected happens with this setup. Also @otsaloma to see if the OP ever resolved this on their end.
Thanks for the reply.
I am running a self-hosted Redash using the master branch from Github (8.0.0 beta).
I added a query with a Date-Range parameter like Last 7 days, and Redash won’t refresh the query.
I know this release is premature but hope to find a workaround to get over it as this problem happened before.
Can you give more detail? What happens when you refresh the query manually? Does it hang forever or will it return a result? Is it just the scheduled query that fails?
What happens when schedule a query without a parameter?
I still see this. I can reproduce it by doing the following.
Add a SQL query against a database, e.g. SELECT * FROM table LIMIT 1000;. Set to update every five minutes.
Add a parametric query, e.g. SELECT * FROM cached_query_123 WHERE area = '{{ area }}' where 123 is the query number of the previous step. Replace “area” with whatever data you have available. Set to update every minute.
Add a dashboard showing the table from the previous step. Wait more than five minutes and you’ll see “6 minutes ago” (and ever more as you wait longer) at the bottom of the dashboard widget. Reloading the page doesn’t change the value. Hit the refresh button at the top right of the page and you’ll see “0 minutes ago” and correspondigly the latest data.
You have the right idea. @arikfr showed how to circumvent this limitation using the API here. Which means it’s possible to refresh parameterized queries.
But I’m not certain if the present behavior is a bug or is intentional. In the first case we can open a bug report. In the second case we can label this thread a feature request for the community to consider.
I was glad to see parametrized queries supported in the shared dashboards, as passing a new parameter value seems to be the only way to force a dashboard to update ahead of the schedule.
I could suggest to perform scheduled updates with all parameters set to default values, so that we could open a dashboard with default parameters values to see the updated results.
P.S. personally, this does not look like a new feature request. This is more a result of a new feature (parameterized queries) being implemented without taking into account another working feature (scheduled updates).
This is correct. We do want to implement support for it, but it didn’t make the cut into the original Pull Request. We decided to release this feature without it, as parmaeterized queries are rarely scheduled so it was still better to have this than waiting further.
The GitHub link is broken, so I’m not sure what code you were referring to, but I think the relevant part is:
Because most parameterized queries won’t have a reference to latest_query_data, retrieved_at will get the value of now, which in turn result in it not being scheduled. Not sure what was the original idea for setting it this way, but I do realize now that without having a reliable timestamp of previous execution we can’t schedule the next one.
Also, it’s worth noting that scheduled queries will work only for the default value so the its usefulness is limited anyway.
Even if it was a non parameterized query, Redash won’t refresh the shown data, unless you enable the dashboard refresh feature. Once enabled it will not only trigger refresh of the data, but also update the view with any new data. It also has the nice side effect of not triggering refreshes when the dashboard is not open.
Is there a reason you didn’t use the dashboard refresh on interval option?
Ideally, I need a dashboard which works much like Metabase - whenever a user opens it, it fetches the most recent data from the data source, and shows the updated data to the user.
As far as I see, with Redash it is only achievable if I add a dummy numeric parameter to each underlying query, randomly generate a number to the user, and send the link.
E.g. /dashboard/test?p_Update_param=123456, where 123456 - is the randomly generated number.
This parameter is inserted in the query to WHERE expression in the following manner: where {{Update_param}} <> -1, which always evaluates to True, regardless of the parameter value.
This way, Redash considers it a different query, executes it immediately, and fetches the fresh data from the database.
One could argue that users have “refresh” button on the dashboard, but there is no “refresh” button on publicly shared dashboard. Yet, the data there needs to be updated as well.
Alternatively, I could refresh all queries in the selected dashboard via API, but this way I need to somehow call it every time a user opens a dashboard.
Is there a reason you didn’t use the dashboard refresh on interval option?
I just posted how to reproduce the bug. We’re not currently running any info displays, we don’t need the interval refresh feature. Again, my expectation, and that of our users, is that the data would update in the background and on page load you see the data of the latest update. For us, parameters are used for different business segments, with the default in most cases being “all.” Thus, most times users don’t even change the parameter at all, but as far as I can tell, using parameters is the best way to allow that drill-down for users while avoiding duplication under the hood – we don’t want to write separate queries and configure separate visualizations and dashboards for the same metrics of each business segment.
Queries with parameters aren’t refreshing on schedule. For this there is: #4182, which was recently opened. #1909 can be a quick fix for this, although in the long term we would like to rewrite our scheduler to address this and other issues it currently has.
Dashboards always using cached results if available. Query page actually accepts a maxAge query string parameter, that can trigger a refresh (depends on the value it receives). We can look into applying a similar thing to the dashboard page. This actually should be somewhat simple and can address @dtroyan’s issue (and partially @otsaloma’s if the queries are fast to execute).
I tried the scheduler part, which did not work.Then I created the Dashboard for same query and scheduled it for 10 mins, it worked but the issue is it only works till that window is open. As soon as the tab is closed, the query is not refreshed again. Also, if the laptop is closed nothing is refreshed. I need that the data is refreshed automatically without any manual work. Can you please help with any solution?
