Zeppelin Dynamic Form Drop Down value in SQL

van_d39 picture van_d39 · Aug 5, 2016 · Viewed 8.4k times · Source

I have a dropdown element in my Zeppelin notebook

val instrument = z.select("Select Item", Seq(("A", "1"),("B", "2"),("C", "3")))

I want to use the value of this variable instrument in my sql. For e.g., my next paragraph in the notebook contains

%sql select * from table_name where item='<<instrument selected above>>'

Is this possible? If yes, what would the syntax look like?

Answer

eliasah picture eliasah · Aug 5, 2016

This is completely possible and here is an example with both %spark and %sql interpreters :

cell 1:

val df = Seq((1,2,"A"),(3,4,"B"),(3,2,"B")).toDF("x","y","item")
df.registerTempTable("table_name")
val instrument = z.select("Select Item", Seq(("A", "1"),("B", "2"),("C", "3")))

cell 2:

z.show(df.filter($"item"===instrument))

dynamic_spark

alternative solution using %sql :

%sql select * from table_name where item="${item=A,A|B|C}" 

dynamic_sql

PS: instrument is set on B,2