Zeppelin Dynamic Form Drop Down value in SQL

Question

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?

Agonist

Answer 1

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))

alternative solution using %sql :

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

PS: instrument is set on B,2

Culottes

Is there any any specific reason why you use === instead of == ? Also, I'm trying to understand the difference between z.show() and df.show()? – Agonist

=== is used to create a column expression which you need to filter on. the == returns a boolean. As for df.show() and z.show() it is very clear. z is actually the zeppelin object which you should use to have "those pretty" displays. df.show is just the regular show function of a dataframe. – Culottes

z.show() is equivalent to Databricks display() method, I presume. Your code template does help but I am doing a lot of manipulation to the columns in the sql select statement. Hence, I would need a way to use the instrument variable in %sql – Agonist

@Veenit ok here you go ! – Culottes

where item="${item=A,A|B|C}" does facilitate what I am looking for. Would it be possible to provide label to those values A, B, C. For example, I want to display Item A in the dropdown but when we select that, A should be it's value. I can do this easily through Seq(("A", "1") as in my example in the question. – Agonist

Also, I would really appreciate if you can share the documentation link (if there is any) surrounding these features in Zeppelin. :-) – Agonist

The only documentation that I know about is the official Zeppelin doc but it's quite incomplete. The solution that I have provided is something I've just discovered while using zeppelin. I'm not sure about how you can label your selection in %sql interpreter. – Culottes

Do you know how to obtrain %sql result in %pyspark interpreter ? – Tricot

Answer 2

The other answers haven't really addressed the issue, the syntax you are looking for is:

where item = "${Select Item=,1(A)|2(B)|3(C)}"

Cheers.

Manatarms