Spark cassandra : join table with condition on the query based on attribute from the primary RDD ("where tableA.myValue > tableB.myOtherValue")

Question

Is there a way to join 2 tables adding a condition on columns between the 2 tables ?

Example :

case class TableA(pkA: Int, valueA: Int)
case class TableB(pkB: Int, valueB: Int)


val rddA = sc.cassandraTable[TableA]("ks", "tableA")
rddA.joinWithCassandraTable[TableB]("ks", "tableB").where("tableB.valueB > tableA.valueA")

Is there a way to send the where("tableB.valueB > tableA.valueA") instruction ? ("tableB.value" being a clustering column)

score 0 · Answer 1 · answered Dec 06 '16 at 08:10

RDD.where() call just pass predicate to CQL. CQL is limited for fast and simple OLTP queries. More complicated queries could be done only with SparkSQL. For your case it could be something like this:

sqlContext.read.format("org.apache.spark.sql.cassandra")
    .options(Map( "table" -> "tableA", "keyspace"->"ks"))
    .load().registerTempTable("tableA")
sqlContext.read.format("org.apache.spark.sql.cassandra")
    .options(Map( "table" -> "tableB", "keyspace"->"ks"))
    .load().registerTempTable("tableB")
sqlContext.sql("select * from tableA join tableB on tableB.valueB > tableA.valueA").show

Spark cassandra : join table with condition on the query based on attribute from the primary RDD ("where tableA.myValue > tableB.myOtherValue")

1 Answers1