Any ways to power a Databricks SQL dashboard widget with a dynamic query?

[bl12]

Hi, I'm using Databricks SQL and I need to power the same widget in a dashboard with a dynamic query. Are there any recommended solutions for this?

For more context, I'm building a feature that allows people to see the size of something. That size is determined by traits that the user selects. I was thinking of creating one query via API and then changing the same query every time the user chooses new traits also via API. However, the problem is that 2 users can't view different queries at the same time since there is only one widget.

Before when we used Redash, we passed entire queries as input to a parameter for the widget through url. However this is no longer possible with Databricks SQL and we had problems with HTTP GET limits. So basically I'm wondering if there's a way for multiple users to change the query behind the same widget? I haven't found any ways that would allow me to input a query as a parameter.

[AmanSehgal · Accepted solution]

I believe reDash isn't built that way within Databricks. It's still very limited in its capabilities.

I've two solutions for you. I haven't tried any but see if it works for you:

1. Use preset with DB SQL.
2. A hack - read below:

I'm assuming you have one widget in one dashboard. Modify the following implementation as per your use case.

For your dashboard, create a template with widgets and queries in your workspace.

When a user runs a query with different parameters, then using APIs you can clone the dashboard and put this new query with parameters. You'll receive the dashboard id and then you can pass that widget to the user. Once the user session expires, you can then put that in trash.

You'll have to design a sophisticated python code for this.

You can also use terraform for this. The experimental terraform exporter for databricks can help you as well.

[bl12]

Thank you for your answer! I had thought about the second idea but I wasn't able to use the API's to clone a dashboard with widgets. I know you said you haven't tried the solutions before but do you know how to clone a dashboard with a widget before? I'm using the "Create a new dashboard object" API but I can't get the widget from the template dashboard to be copied over.