Making Databricks Widgets Smarter
June 25, 2024//
Have you ever wished the Databricks widgets were a little more intelligent? When you change one, the others adjust?
That’s not too hard, but it’s a bit tricky. Here’s the secret: The widgets must be replaced as they change!
Dynamic Widget Replacement
For example:
Let’s say you have a date input, and whenever it changes, you want a second widget to change. That second widget needs to be suffixed with an increasing number as the contents change. Consider:
<span class="token keyword">if</span> interest_date <span class="token operator">!=</span> last_interest_date<span class="token punctuation">:</span>
<span class="token keyword">try</span><span class="token punctuation">:</span>
dbutils<span class="token punctuation">.</span>widgets<span class="token punctuation">.</span>remove<span class="token punctuation">(</span><span class="token string-interpolation"><span class="token string">f"issue</span><span class="token interpolation"><span class="token punctuation">{</span>index1<span class="token punctuation">}</span></span><span class="token string">"</span></span><span class="token punctuation">)</span>
<span class="token keyword">except</span><span class="token punctuation">:</span>
<span class="token keyword">pass</span>
index1 <span class="token operator">+=</span> <span class="token number">1</span>
dbutils<span class="token punctuation">.</span>widgets<span class="token punctuation">.</span>dropdown<span class="token punctuation">(</span><span class="token string-interpolation"><span class="token string">f"issue</span><span class="token interpolation"><span class="token punctuation">{</span>index1<span class="token punctuation">}</span></span><span class="token string">"</span></span><span class="token punctuation">,</span> <span class="token string">""</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span> You need the try/catch the first time thru because the remove will fail. Et Voila, changing widgets.
A Working Example
Here’s a functioning example:
interest_dates <span class="token operator">=</span> <span class="token punctuation">{</span>
<span class="token string">"20230730"</span><span class="token punctuation">:</span> <span class="token punctuation">[</span>
<span class="token punctuation">{</span><span class="token string">"issue_name"</span><span class="token punctuation">:</span> <span class="token string">"Issue 1"</span><span class="token punctuation">,</span> <span class="token string">"upcs"</span><span class="token punctuation">:</span> <span class="token punctuation">[</span><span class="token string">"1234"</span><span class="token punctuation">,</span> <span class="token string">"5678"</span><span class="token punctuation">]</span><span class="token punctuation">}</span><span class="token punctuation">,</span>
<span class="token punctuation">{</span><span class="token string">"issue_name"</span><span class="token punctuation">:</span> <span class="token string">"Issue 2"</span><span class="token punctuation">,</span> <span class="token string">"upcs"</span><span class="token punctuation">:</span> <span class="token punctuation">[</span><span class="token string">"9876"</span><span class="token punctuation">,</span> <span class="token string">"5432"</span><span class="token punctuation">]</span><span class="token punctuation">}</span><span class="token punctuation">,</span>
<span class="token punctuation">]</span><span class="token punctuation">,</span>
<span class="token string">"20240114"</span><span class="token punctuation">:</span> <span class="token punctuation">[</span>
<span class="token punctuation">{</span><span class="token string">"issue_name"</span><span class="token punctuation">:</span> <span class="token string">"Issue 3"</span><span class="token punctuation">,</span> <span class="token string">"upcs"</span><span class="token punctuation">:</span> <span class="token punctuation">[</span><span class="token string">"1111"</span><span class="token punctuation">,</span> <span class="token string">"2222"</span><span class="token punctuation">]</span><span class="token punctuation">}</span><span class="token punctuation">,</span>
<span class="token punctuation">{</span><span class="token string">"issue_name"</span><span class="token punctuation">:</span> <span class="token string">"Issue 4"</span><span class="token punctuation">,</span> <span class="token string">"upcs"</span><span class="token punctuation">:</span> <span class="token punctuation">[</span><span class="token string">"3333"</span><span class="token punctuation">,</span> <span class="token string">"4444"</span><span class="token punctuation">]</span><span class="token punctuation">}</span><span class="token punctuation">,</span>
<span class="token punctuation">]</span><span class="token punctuation">,</span>
<span class="token punctuation">}</span>
last_interest_date<span class="token punctuation">,</span> last_issue <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token boolean">None</span><span class="token punctuation">,</span> <span class="token boolean">None</span><span class="token punctuation">)</span>
index1<span class="token punctuation">,</span> index2 <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span>
dbutils<span class="token punctuation">.</span>widgets<span class="token punctuation">.</span>removeAll<span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">from</span> random <span class="token keyword">import</span> sample
<span class="token keyword">from</span> time <span class="token keyword">import</span> sleep
keys <span class="token operator">=</span> <span class="token builtin">sorted</span><span class="token punctuation">(</span><span class="token builtin">list</span><span class="token punctuation">(</span>interest_dates<span class="token punctuation">.</span>keys<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
dbutils<span class="token punctuation">.</span>widgets<span class="token punctuation">.</span>removeAll<span class="token punctuation">(</span><span class="token punctuation">)</span>
dbutils<span class="token punctuation">.</span>widgets<span class="token punctuation">.</span>dropdown<span class="token punctuation">(</span>
<span class="token string">"date"</span><span class="token punctuation">,</span> <span class="token string">""</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token string">""</span><span class="token punctuation">]</span> <span class="token operator">+</span> <span class="token punctuation">[</span><span class="token builtin">str</span><span class="token punctuation">(</span>k<span class="token punctuation">)</span> <span class="token keyword">for</span> k <span class="token keyword">in</span> <span class="token builtin">sorted</span><span class="token punctuation">(</span>interest_dates<span class="token punctuation">)</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token string">"1. Date"</span>
<span class="token punctuation">)</span>
interest_date <span class="token operator">=</span> dbutils<span class="token punctuation">.</span>widgets<span class="token punctuation">.</span>get<span class="token punctuation">(</span><span class="token string">"date"</span><span class="token punctuation">)</span>
<span class="token keyword">if</span> interest_date<span class="token punctuation">:</span>
<span class="token keyword">if</span> interest_date <span class="token operator">!=</span> last_interest_date<span class="token punctuation">:</span>
<span class="token keyword">try</span><span class="token punctuation">:</span>
dbutils<span class="token punctuation">.</span>widgets<span class="token punctuation">.</span>remove<span class="token punctuation">(</span><span class="token string-interpolation"><span class="token string">f"issue</span><span class="token interpolation"><span class="token punctuation">{</span>index1<span class="token punctuation">}</span></span><span class="token string">"</span></span><span class="token punctuation">)</span>
dbutils<span class="token punctuation">.</span>widgets<span class="token punctuation">.</span>remove<span class="token punctuation">(</span><span class="token string-interpolation"><span class="token string">f"upc</span><span class="token interpolation"><span class="token punctuation">{</span>index2<span class="token punctuation">}</span></span><span class="token string">"</span></span><span class="token punctuation">)</span>
<span class="token keyword">except</span><span class="token punctuation">:</span>
<span class="token keyword">pass</span>
index1 <span class="token operator">+=</span> <span class="token number">1</span>
index2 <span class="token operator">+=</span> <span class="token number">1</span>
last_interest_date <span class="token operator">=</span> interest_date
dbutils<span class="token punctuation">.</span>widgets<span class="token punctuation">.</span>dropdown<span class="token punctuation">(</span>
<span class="token string-interpolation"><span class="token string">f"issue</span><span class="token interpolation"><span class="token punctuation">{</span>index1<span class="token punctuation">}</span></span><span class="token string">"</span></span><span class="token punctuation">,</span>
<span class="token string">""</span><span class="token punctuation">,</span>
<span class="token punctuation">[</span><span class="token string">""</span><span class="token punctuation">]</span> <span class="token operator">+</span> <span class="token punctuation">[</span>k<span class="token punctuation">[</span><span class="token string">"issue_name"</span><span class="token punctuation">]</span> <span class="token keyword">for</span> k <span class="token keyword">in</span> interest_dates<span class="token punctuation">[</span>interest_date<span class="token punctuation">]</span><span class="token punctuation">]</span><span class="token punctuation">,</span>
<span class="token string">"2. Issue"</span><span class="token punctuation">,</span>
<span class="token punctuation">)</span>
issue <span class="token operator">=</span> dbutils<span class="token punctuation">.</span>widgets<span class="token punctuation">.</span>get<span class="token punctuation">(</span><span class="token string-interpolation"><span class="token string">f"issue</span><span class="token interpolation"><span class="token punctuation">{</span>index1<span class="token punctuation">}</span></span><span class="token string">"</span></span><span class="token punctuation">)</span>
<span class="token keyword">if</span> issue<span class="token punctuation">:</span>
<span class="token keyword">if</span> issue <span class="token operator">!=</span> last_issue<span class="token punctuation">:</span>
<span class="token keyword">try</span><span class="token punctuation">:</span>
dbutils<span class="token punctuation">.</span>widgets<span class="token punctuation">.</span>remove<span class="token punctuation">(</span><span class="token string-interpolation"><span class="token string">f"upc</span><span class="token interpolation"><span class="token punctuation">{</span>index2<span class="token punctuation">}</span></span><span class="token string">"</span></span><span class="token punctuation">)</span>
<span class="token keyword">except</span><span class="token punctuation">:</span>
<span class="token keyword">pass</span>
index2 <span class="token operator">+=</span> <span class="token number">1</span>
last_issue <span class="token operator">=</span> issue
current_issue <span class="token operator">=</span> <span class="token punctuation">[</span>
k <span class="token keyword">for</span> k <span class="token keyword">in</span> interest_dates<span class="token punctuation">[</span>interest_date<span class="token punctuation">]</span> <span class="token keyword">if</span> k<span class="token punctuation">[</span><span class="token string">"issue_name"</span><span class="token punctuation">]</span> <span class="token operator">==</span> issue
<span class="token punctuation">]</span><span class="token punctuation">.</span>pop<span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span>
dbutils<span class="token punctuation">.</span>widgets<span class="token punctuation">.</span>dropdown<span class="token punctuation">(</span>
<span class="token string-interpolation"><span class="token string">f"upc</span><span class="token interpolation"><span class="token punctuation">{</span>index2<span class="token punctuation">}</span></span><span class="token string">"</span></span><span class="token punctuation">,</span>
<span class="token string">""</span><span class="token punctuation">,</span>
<span class="token punctuation">[</span><span class="token string">""</span><span class="token punctuation">]</span> <span class="token operator">+</span> current_issue<span class="token punctuation">[</span><span class="token string">"upcs"</span><span class="token punctuation">]</span><span class="token punctuation">,</span>
<span class="token string">"3. UPC"</span><span class="token punctuation">,</span>
<span class="token punctuation">)</span>
upc <span class="token operator">=</span> dbutils<span class="token punctuation">.</span>widgets<span class="token punctuation">.</span>get<span class="token punctuation">(</span><span class="token string-interpolation"><span class="token string">f"upc</span><span class="token interpolation"><span class="token punctuation">{</span>index2<span class="token punctuation">}</span></span><span class="token string">"</span></span><span class="token punctuation">)</span>
<span class="token keyword">if</span> upc<span class="token punctuation">:</span>
displayHTML<span class="token punctuation">(</span><span class="token string-interpolation"><span class="token string">f"You selected: <b></span><span class="token interpolation"><span class="token punctuation">{</span>upc<span class="token punctuation">}</span></span><span class="token string"></b>"</span></span><span class="token punctuation">)</span>