1

I want to have a more interactive dashboard. like reading the data from database , giving it to select box, onchange of select box send the value and run the query.

i want to achieve this using zeppelin bcz on selected value i have to display the analytics.

what would be the way to achieve this and is this possible to achieve through zeppelin.

i tried with select box, but i couldnot save the selected value and send it to next query and execute that. something like

select age, count(1) value 
from bank 
where marital="${marital=single,single|divorced|married}" 
group by age 
order by age

i didnt get how to store this parameter and send selected parameters from one paragraph to another

or something like handling all these things from UI, lets say javascript html and sending that selected value as parameter to the zeppelin? something like this while using the url

<iframe src="http://myipaddress:8080/#/notebook/2BWCNP7V8/paragraph/20160831-115204_1774035770?asIframe&param1=value1&param2=value2" width="500" height="300"  scrolling="no" frameBorder="0" id="iframe1" style="text-align:center;" >Browser not compatible.</iframe>

and using these param1=value1&param2=value2 in my zeppelin paragraph? technically doable or not i dont understand. please help me how to achieve this? thanks in advance :)

Priyanka D L
  • 171
  • 1
  • 8
  • please check if this is your want, http://stackoverflow.com/questions/38335170/how-to-put-a-variable-into-z-zeppelincontext-in-javascript-in-zeppelin/38353337#38353337 – Rockie Yang Sep 01 '16 at 21:29
  • i need something like `select name,nameid from table1` , this will be my first select box, on select of something my second select box ie `select data,dataid from table2 where nameid='PreviouslySelectedNameID'` and then pass the second selected value into my next query to get the result.. :( – Priyanka D L Sep 02 '16 at 05:26
  • You can create that. in `z.select`, the `Seq` need created from your select statement. – Rockie Yang Sep 02 '16 at 05:29
  • i can do z.select , but how to give `where nameid='PreviouslySelectedNameID'` in second select? how to retain selected value?do i need to store it ? – Priyanka D L Sep 02 '16 at 05:49

1 Answers1

1

We can get all the maritals with following code

val maritals = bank.select("marital").distinct.collect.map(_.getString(0))

And convert to seq ZeppelinContext wanted

val seq = mairitals.zipWithIndex.map{case (x,y) => (y.toString, x)}.toSeq

Then we can select it like this

val index = z.select("marital", "1", seq)
val marital = seq(index.toString.toInt)._2

And the marital can use be used for further processing. like.

val sql = s"""select distinct job from bank where marital=="$marital""""

sqlContext.sql(sql).show

Or like when I use spark-highcharts. In this case I wanna plot average balance over age for certain marital status.

highcharts(bank.filter(col("marital") === marital)
  .series("x" -> "age", "y" -> avg(col("balance")))
  .orderBy(col("age"))).plot()

NOTE: Only the paragraph with the select will be executed automatically when value changed.

Rockie Yang
  • 4,725
  • 31
  • 34
  • thanks for the detailed answer, when i do `val maritals = bank.select("marital").distinct.collect.map(_.getString(0))` it is saying `:30: error: value select is not a member of org.apache.spark.rdd.RDD[Bank] bank.select("marital").distinct.collect.map(_.getString(0))`... what is the problem here? what do i need to do? – Priyanka D L Sep 17 '16 at 08:14
  • The error message says `select is not a member of RDD`. Which means your `bank` is a RDD not a DataFrame. Check the Zeppelin Tutorial how bank is create helps. – Rockie Yang Sep 17 '16 at 12:10
  • `val sql = s"""select distinct job from bank where marital=="$marital"""" sqlContext.sql(sql).collect` gives `java.lang.RuntimeException: [1.33] failure: identifier expected select * from bank where marital=="single" ^` but if i do ` %sql select * from bank where marital=="single"` it is working. and when i give raw query to test like `sqlContext.sql("select * from bank where marital=='single'")`.show it is throwing `java.lang.RuntimeException: Table Not Found: bank `.... where is the problem? i had tried the answer provided here but no luck http://stackoverflow.com/a/30276247 – Priyanka D L Sep 19 '16 at 08:39
  • The error message usually contains really useful information. Follow the error message will let you found the solution. It says `Table NOT found`. – Rockie Yang Sep 20 '16 at 05:44
  • yes i read that, but if table wasnot there ,then how it will execute `%sql select * from bank where marital=="single"`?.. i could execute this query in `%sql` but not as `sqlContext.sql()` , this is where i am confused.. and to execute `bank.select("marital").distinct.collect.map(_.getString(0))` it has to be DF as you mentioned in the previous comment.. this is working means no problem with DF right? – Priyanka D L Sep 20 '16 at 05:51
  • It must be something wrong in your environment. Try to check logs and restart to zeppelin to see if it helps – Rockie Yang Sep 20 '16 at 07:29