Redash: [Feature Request] Add support for query parameters in the API

[yosit]

This is especially useful when hitting the api endpoint that can power custom dashboards

[arikfr]

It's a little tricky, because we currently invoke query execution in the background and the results will be available at some unknown point in time.

There are two ways to tackle this:

1. Enable the ability to ask for immediate execution of a query & wait for the result.
2. Have webhooks support (register with a webhook for when a query updates) + ability to trigger query execution.

Will webhooks support accommodate your use case?

[yosit]

To keep it simple I think that no.1 is easier on the calling side.

[arikfr]

True. Ok, let's try this approach.

The naive implementation will only work for short running queries, but we can start with this and later update.

[arikfr]

In QueryResultAPI need to add POST method, that will either: use redash.tasks.execute_query directly or create a new redash.tasks.QueryTask and wait on its response. The later has the benefit of deduplication of query runs, but the downside of possible need to wait for available Celery runner.

[divsky]

+1 on this. Dynamic Dashboards would be great.

[stanhu]

+1 for this feature too! I'm fine with the hybrid option to start if this is easier to implement:

http://redash.io/architecture/query_execution.html

@arikfr, do you have a branch working on this? Otherwise, I'm happy to start one.

[yosit]

@stanhu it's almost there. @arikfr implemented #321 and #340 so we're left with the need for a support in the editor to customize and test queries with values for parameters

[arikfr]

@stanhu to see an example of how it currently works, see: http://demo.redash.io/queries/146?p_action=create

Basically we use the query text as a template, and merge it with parameters from the URL. When I get to work on it, my next step is to implement a some sort of UI to select enter the variable values instead of using the URL.

The downside of current implementation (beside not having a UI), is that you can't use it from the API... depends on your use case, it might not be a problem.

[stanhu]

That's terrific! It's exactly the use case I had in mind. I assume you will also need to store the parameters and their respective values in the DB? Let me know how I can help move this along.

[arikfr]

What I was thinking about was to add a new object in redash: variable, that we define its type (date, integer, string, ...), (optionally) possible values, and a default value. Also, the list of possible values can be the result of another redash query.

Then this variable can be "attached" to queries or dashboards. Attaching variable to query can be done implicitly by just referencing its name in the query text.

Using the variable definition we will render the needed UI. And to make things simple, if there is no such variable predefined, we will just render a text input box.

WDYT?

[arikfr]

Btw, there is also filters feature:
http://demo.redash.io/queries/143
http://demo.redash.io/queries/144

[stanhu]

@arikfr, sounds like exactly what we need. This would add even more power to re:dash. Thanks!

[tomwolfgang]

+1 for Variables

[Danier-Evens]

@arikfr Add support for query parameters Have realized?

[arikfr]

@Danier-Evens there is beginning of support, see this example: http://demo.redash.io/queries/146?p_action=create

[sheva810712]

@arikfr The example of #146 supplies only one parameter,
what should I do if I want more than 1 parameters?

[arikfr]

Simple, you just add another one:
http://demo.redash.io/queries/718/source?p_action=create&p_min_date=2015-01-01

[ChiragKParmar]

@arikfr any update on UI part? Thanks

[arikfr]

UI for query parameters

[fabito]

Hi,

I'm willing to help on adding support for parameterized queries in embeds.
How complex would be to implement it. What are the challenges ?

tks

[arikfr]

@fabito see #1014 for some reference. We already support it (if enabled), but it's not safe (you open yourself to run _any query_ by an external user). Proper implementation will require changing both how the embed works (make it load the results with additional API call) but also fixing the security issue by sanitizing parameter values.

[devang-kredx]

My goal is to be able to be able to create custom dashboard for our users based on some their data, and embed it with in our website. While the embed option would have been great for this, the visualizations currently created on redash are not responsive.

Therefore, I was hoping to be able to do call redash API for getting the response back and create the visualizations myself. For this, it would be great to have the ability to be able to pass query params in API call which in turn can be passed to the query builder. Is there I can do this today in the current version of redash?

[arikfr]

@devang-kredx if you always need a new value, you can use the refresh API to trigger query execution with the parameters.

But there is another question here -- are you planning on fetching this data on the client side or the server side?

[devang-kredx]

@arikfr We are open to both, fetching data from client side directly, or calling the api and fetch data from server side and pipe it to client.

Will try the refresh API and with providing new params as needed.

Quick question- if two different calls are made to the same query with _different_ params, is it guaranteed that the job result will pass the result of the query with its own param, ie, there is no race condition?

[solutionrooms]

I had the same requirement a few months back, I needed a synchronous way to call an api to get json data back using query parameters (including maxAge, and query caching).

So I created myself a version of the embed html that returns json data. I now use this regularly in "production". the json is used to populate amcharts visualizations.

As @arikfr points out, there may well be problems with this approach, however for my requirement it works great.

@devang-kredx - using the modified embed approach I initiate 10-20 api calls at the same time, and no race condition appears, each one returns the correct data.

@arikfr - any chance that this could find its way into a release? or do you think it is too risky?

@devang-kredx - I am happy to share this with you if you are ok with the risks and effort of maintaining your own modifications.

[devang-kredx]

@solutionrooms Would really appreciate if you could share your solution, would be a great help for us!

[devang-kredx]

@solutionrooms If still available, would love to see your solution for this

[solutionrooms]

Sure, @devang-kredx
This is currently working on 11.1.b2095
There are two additional files to copy to current/redash/handlers, these are embedjson.py and metajson.py, both of these are modded versions of embed.py
then, you need to update the existing current/handlers/__init__.py

near the end to include these handlers...

from redash.handlers import embed, queries, static, authentication, admin, embedjson, metajson

embedjson.py and metajson.py can be found here

https://db.tt/tnM4fPg6
and
https://db.tt/qLjMYhJG

to use -
http://yourredash/embedjson/query/1?api_key=yourapikey&p_your_parameter=value etc

embedjson will produce a json from a standard redash query.

However if you want to build up a json string (e.g. in postgres) then you can use metajson (just replace embedjson with metajson in the above url). It assumes that you have a column called "json" in your query results and renders whatever is in there as standard json.

[devang-kredx]

@solutionrooms Thanks! Will try it out 👍

[MaxBer]

+1 for embedding support.

[dheerajrav]

@solutionrooms I tried your workaround, I am still not able to get the query to refresh. I am getting the old query_result.

[ziahamza]

@arikfr Passing custom params to embedded iframe seems to have broken in the current master.

It seems to have come as part of the frontend refactor as you moved to using API to get visualisation from client JS: https://github.com/getredash/redash/pull/1376

specifically: https://github.com/getredash/redash/pull/1376/commits/0a06f950d51b7a776aef07e7b4ffc55fab8bce3e#diff-d8568f012ecebae9994e0e588a76725bL72

[arikfr]

@ziahamza I know. As it was something I never recommended on using, I felt it was ok to merge the refactor with this functionality broken. I do intend to fix it, but not sure it will be done before the v1 release.

If someone wants to make a relevant pull request, he's welcome, although I'll be happy if they consult with me first.

[ziahamza]

@arikfr I made an attempt, and made a work in progress PR here: https://github.com/getredash/redash/pull/1524

It works, along with caching queries through maxAge. Its enabled by default for testing, but if this approach makes sense, then I can polish it up.

[Aleyasen]

I am wondering is this feature ready to use?
I want to pass parameters to chart in iFrame.

[morganizeit]

Hi folks! Any news on sorting out the test failures in https://github.com/getredash/redash/pull/1524 ?

[asafmaor]

👍 For adding this feature.

[TsubasaK111]

👍 I'm looking forward to the day that this is added as a feature.

[arikfr]

Actually this is already possible with the refresh API. See this for example:
https://gist.github.com/arikfr/e3e434d8cfd7f331d499ccf351abbff9

[TsubasaK111]

@arikfr Good to know, thank you!
However there's no documentation on this, I'd recommend that it be added to the official docs (or let me PR something, but I'm presuming the docs are not managed on GH)