cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

How to overcome missing query parameters in Databricks SQL?

dimsh
Contributor

Hi, there!

I'm trying to build up my first dashboard based on Dataabricks SQL. As far as I can see if you define a query parameter you can't skip it further. I'm looking for any option where I can make my parameter optional.

For instance, I have a table (start_date, end_date, country_code, unique_ids) and want by default to show the data for the last 7 days. But at the same time, I want to add two inputs where a user can specify his / her arbitrary date range.

When I don't provide any value I get a "Missing values for start_date, end_date parameters" error. So if I get a null in this case I can process it via SQL, but my query just fails.

Is there any chance to overcome this limitation?

With BR,

Dmytro

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Axel Schwanke​ , Would you like to raise a feature request for the same?

Hi @DavideCagnoni (Customer)​ , The Ideas Portal lets you influence the Databricks product roadmap by providing feedback directly to the product team.

Use the Ideas Portal to:

  • Enter feature requests.
  • View, comment, and vote up other users’ requests.
  • Monitor the progress of your favorite ideas as the Databricks product team goes through their product planning and development process.

For a quick tutorial on submitting an idea, watch this video:

View solution in original post

13 REPLIES 13

Hubert-Dudek
Esteemed Contributor III

What about widgets?

dbutils.widgets.dropdown("start_date", default_value, values)

dbutils.widgets.dropdown("end_date", default_value, values)

dimsh
Contributor

@Hubert Dudek​ 

Thanks. But I'm using Databricks SQL (not Data Engineering) and I'm only limited with SQL syntax.

https://docs.databricks.com/sql/user/queries/query-parameters.html

not usable in my case

iwtLegacyAnbieterId like '%{{providerId}}%'

I simply want the option of getting all records by leaving the parameter empty

Also, the mere fact that {{}} exists in the SQL will cause Databricks console to error it. While debugging I had actually moved the parameter {{}} part to a SQL comment -- {{}} and the console errors it even though it's only in a comment so obviously the above won't work.

Anonymous
Not applicable

@Dmytro Imshenetskyi​ - Does this last answer help?

dimsh
Contributor

@Kyle Hale​ 

Thanks. It works, but from my perspective, it looks like a workaround.

Probably it will be better to consider empty values as nulls, and then process them via COALESCE SQL function.

austin_ford
New Contributor III
New Contributor III

@Dmytro Imshenetskyi​ When you are building a DBSQL dashboard, you can set the default for parameters so users don't have to select such a parameter: https://docs.databricks.com/sql/user/queries/query-parameters.html#query-parameter-mapping-in-dashbo...

Anonymous
Not applicable

@Dmytro Imshenetskyi​ - Checking on you. How have these more recent answers worked out?

Axel_Schwanke
Contributor

any solution for my problem of using a parameter in a 'like' where clause, e.g.

iwtLegacyAnbieterId like '%{{providerId}}%'

and getting ALL records by leaving the parameter empty?

Hi @Axel Schwanke​ , Would you like to raise a feature request for the same?

Hi @DavideCagnoni (Customer)​ , The Ideas Portal lets you influence the Databricks product roadmap by providing feedback directly to the product team.

Use the Ideas Portal to:

  • Enter feature requests.
  • View, comment, and vote up other users’ requests.
  • Monitor the progress of your favorite ideas as the Databricks product team goes through their product planning and development process.

For a quick tutorial on submitting an idea, watch this video:

Anonymous
Not applicable

@Axel Schwanke​ - Let me see if I can find someone to help you. Thank you for your patience.

DavePete_79593
New Contributor II

I have a similar problem and none of the above proposals work. I'm trying to do an array_overlap(column_array, user_input_array). The user_input_array should be optional. So I can do something like select case when cardinality(column_array) = 0 or cardinality(user_input_array) > 0 then true else array_overlap(column_array, user_input_array) end as matched. However this won't work because the SQL UI won't allow empty input parameters.

DavePete_79593
New Contributor II

And I can't submit a feature request because when I select feedback I get: HTTP ERROR 403

Problem accessing /sso/ideas. Reason:

Invalid or missing CSRF token

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.