Add time of the day option when refreshing queries once a week/month

[MarcoMene]

It's nice to have top possibility to refresh queries once per week/month.
It would be nicer to have the possibilty to choose the day of the week/month and time of refresh.

[luke14free]

Since with celery we can achieve almost cron-like scheduling (http://docs.celeryproject.org/en/2.0-archived/getting-started/periodic-tasks.html#crontab-like-schedules), it would be nice to expose this to the end user, adding the possibility of running the query on certain days of the week or of the month.

For the days of the week we could show a tick-list of all the days of the week and then the user can chose when to refresh the query (usecase -> I don't want to refresh my dashboard on weekends).

For the days of the month we can have a simple text input asking the user to type, separated with a comma, all the days of the month in which he wants the query to be run.

Another, less user friendly idea, would be to let the user input a crontab-like string to decide when the queries can be updated.

[luke14free]

An idea of UI could be the following:

wdyt @arikfr? there would need to be some changes in the structure of the queries models.. etc

[arikfr]

Since with celery we can achieve almost cron-like scheduling (http://docs.celeryproject.org/en/2.0-archived/getting-started/periodic-tasks.html#crontab-like-schedules), it would be nice to expose this to the end user, adding the possibility of running the query on certain days of the week or of the month.

We don't use Celery's scheduler. The main reason being that our scheduler predates the use of Celery in Redash, but also because it's not clear how dynamic Celery's scheduler is. It seems to be improved to be more dynamic in Celery v4.0 (was recently released).

(I will comment about the UI next)

[arikfr]

Is "daily" the daily/weekly/cron options and periodic is "Every X"? If so, I would have 3 options there: "No Refresh", "Simple Schedule" ("Every X"), "Advanced Schedule" (the new one).

And why change in the structure of queries model? Maybe we can change the schedule string to store a cron style string? We should be able to represent any of this with a cron syntax, no?

[luke14free]

I agree with the labeling. Yes it would be a cool idea, so basically query.schedule becomes always a CRON-like string. One question tho is whether we are able to execute queries easily in the backend using celery + a cron string without using the scheduler (I still haven't studied well the way we run stuff)

[arikfr]

The way scheduling works is:

• Every 30 seconds, Celery's scheduler invokes the refresh_queries task.
• This in turn calls Query.outdated_queries method which returns the queries that need to be executed. Then refresh_queries enqueues a task for each of them.
• outdated_queries calls the should_schedule_next function with the schedule string and the retrieved_at time of the last query result of each query to determine if a query needs to be updated.

So basically you need to alter the should_schedule_next method to support CRON strings. There are several Python libs that parse the cron string.

[luke14free]

👍 thanks

[ranbena]

I believe this has been addressed in #2396.