Passing a dataframe to where clause + Spark SQL

[Divya Gehlot <di...@gmail.com>]

Hi,
//Loading all the DB Properties
val options1 = Map("url" ->
"jdbc:oracle:thin:@xx.xxx.xxx.xx:1521:dbname","user"->"username","password"->"password","dbtable"
-> "TESTCONDITIONS")
val testCond  = sqlContext.load("jdbc",options1 )
val condval = testCond.select("Cond")

testCond.show()
val options2 = Map("url" ->
"jdbc:oracle:thin:@xx.xxx.xxx.xx:1521:dbanme","user"->"username","password"->"password","dbtable"
-> "Test")
val test= sqlContext.load("jdbc",options2 )
test.select.where(condval ) //gives error as cannot convert sql.Column to
Dataframe

test.select().where(???)

My TestConditions table has only one row
which looks like year = 1965 and month = ;december'

Can I convert sql.Column to list and pass ?
I am new Spark and scala.


Will really appreciate the help.

Thanks,
Divya

Re: Passing a dataframe to where clause + Spark SQL

[Rishabh Wadhawan <ri...@gmail.com>]

Hi Divya
Considering you are able to successfully load both tables testCond and test as data frames.
As now taking your case:
when you do val condval = testCond.select(“Cond”) //Where Cond is a column name, here condval is a DataFrame.Even if it has one row, it is still a data frame
if you want to filter data in data frame (test) according to values in data frame condval, as of what I understand you are trying to do.

the appropriate syntax would be this in java (I am not sure about scala syntax but i am sure its easy to port)
DataFrame newDataFrame = test.join(test, test.col(“Cond”).equalTo(condval.col(“Cond”)), “inner”);

This would give you rows in test data frame that match the Cond column in condval.

Please tell me if that helps.

> On Feb 10, 2016, at 9:23 PM, Divya Gehlot <di...@gmail.com> wrote:
> 
> Hi,
> //Loading all the DB Properties
> val options1 = Map("url" -> "jdbc:oracle:thin:@xx.xxx.xxx.xx:1521:dbname","user"->"username","password"->"password","dbtable" -> "TESTCONDITIONS")
> val testCond  = sqlContext.load("jdbc",options1 )
> val condval = testCond.select("Cond")
> 
> testCond.show()
> val options2 = Map("url" -> "jdbc:oracle:thin:@xx.xxx.xxx.xx:1521:dbanme","user"->"username","password"->"password","dbtable" -> "Test")
> val test= sqlContext.load("jdbc",options2 )
> test.select.where(condval ) //gives error as cannot convert sql.Column to Dataframe
> 
> test.select().where(???)
> 
> My TestConditions table has only one row
> which looks like year = 1965 and month = ;december'
> 
> Can I convert sql.Column to list and pass ?
> I am new Spark and scala.
> 
> 
> Will really appreciate the help.
> 
> Thanks,
> Divya 


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@spark.apache.org
For additional commands, e-mail: user-help@spark.apache.org