Setting a macro in Databricks SQL notebook to inspect year qualifier in table names

Question

I have a Databricks SQL notebook and I would like to set a macro at the start that will only run the subsequent notebook cells if particular filenames have a particular year qualifier.

For example, if the year qualifier is set at 'y2022' and the particular filename in the code is 'file1,' then the code would only run if it includes the filename 'file1_y2022.' I want to set the year qualifier and filenames in question in the first cell of the notebook. Any guidance?

For example:

Year_qualifier = 'y2022'

this would run:

Select * from file1_y2022

this would not run:

Select * from file1_y2021

Answer 1

You can try through widgets options . link

%python
dbutils.widgets.text("year_qualifier","")
year_qualifier = dbutils.widgets.get("year_qualifier")
var_file_name  = 'file1_'+year_qualifier
print(var_file_name)

%sql 
Select * from ${var_file_name}