search

getredash / redash

Make Your Company Data Driven. Connect to any data source, easily visualize, dashboard and share your data.
http://redash.io/
BSD 2-Clause "Simplified" License
26.52k stars 4.39k forks source link

Scheduled query executions fail when a query uses dynamic date range parameters #4514

Open susodapop opened 4 years ago

susodapop commented 4 years ago

Issue Summary

Since dynamic date ranges are evaluated in the front-end, scheduled executions fail without warning or notice.

Steps to Reproduce

  1. Write a query with a date range parameter
  2. Configure the date range parameter to use a dynamic value
  3. Execute the query and note its last execution time
  4. Schedule the query to execute within a reasonable time (60 seconds e.g.)
  5. After the time allotted, observe that the last executed time has not updated

I expect the interface to warn about this behavior. Ideally, we can perform the calculation in the back-end so that scheduled executions can work.

Technical details:

wtfzambo commented 4 years ago

Yup, encountered this problem just today. Using a self hosted version of redash. Any idea of a potential workaround that I can easily implement to circumvent this?

susodapop commented 4 years ago

My workaround uses the API. The example here does it, including dynamic dates.

wtfzambo commented 4 years ago

@susodapop Hi, thanks for the reply. I've checked the repo you linked but I'm a bit lost.

Could you elaborate a bit more in depth how to implement the toolbelt? I'm working on a self hosted instance but the selfhosting itself was made by my company's developer, so I have zero clue what that implies.

susodapop commented 4 years ago

@wtfzambo I'm fixing a couple issues with that script. Will follow-up with you once completed.

susodapop commented 4 years ago

@wtfzambo I made an example using refresh_dashboard.py. It updates a whole dashboard of queries.

But you can borrow the logic to make a single query updater or whatever you need. This doesn't require you to have admin access to the Redash instance. All you need is your API key (as shown in the video).

wtfzambo commented 4 years ago

@susodapop yo, thanks a lot for taking the time to make this video, it was really explanatory. I am actually admin, but I thought I needed to edit some things in the docker and I don't have access to that (nor would I know tf to do anyway).

So, given that I host my redash inside a VPC in AWS, I guess to make this run on a daily basis I need to make a lambda function inside the same VPC and schedule a cloudwatch event to trigger said lambda. Would that work?

susodapop commented 4 years ago

@wtfzambo You could do that. I usually just add a cron job.

wtfzambo commented 4 years ago

@susodapop a cron job where? in the redash instance itself?

susodapop commented 4 years ago

@wtfzambo You can run the cron job on any box that has network access to your Redash instance. It can be your laptop, a raspberry pi, a docker container on your NAS or server, a VPS somewhere (kinda like your cloudwatch instance).

I usually run them on a raspberry pi. Entirely up to you.

You probably wouldn't run it in the Redash Docker container though, since updates down the road might blow away your settings.

wtfzambo commented 4 years ago

@susodapop aaaah ok! Sorry for the stupid question, I was confused at first because since I'm operating for a business, I would never have considered running a cron on my personal laptop or a raspberry pi :D