How to use NOT IN clause in filter condition in spark

user_5439546 0
Viewed 74884

I want to filter a column of an RDD source :

val source = sql("SELECT * from sample.source").rdd.map(_.mkString(","))
val destination = sql("select * from sample.destination").rdd.map(_.mkString(","))

val source_primary_key = source.map(rec => (rec.split(",")(0)))
val destination_primary_key = destination.map(rec => (rec.split(",")(0)))

val src = source_primary_key.subtractByKey(destination_primary_key)

I want to use IN clause in filter condition to filter out only the values present in src from source, something like below(EDITED):

val source = spark.read.csv(inputPath + "/source").rdd.map(_.mkString(","))
val destination = spark.read.csv(inputPath + "/destination").rdd.map(_.mkString(","))

val source_primary_key = source.map(rec => (rec.split(",")(0)))
val destination_primary_key = destination.map(rec => (rec.split(",")(0)))

val extra_in_source = source_primary_key.filter(rec._1 != destination_primary_key._1)

equivalent SQL code is 

SELECT * FROM SOURCE WHERE ID IN (select ID from src)

Thank you

apache-spark-sql apache-spark scala
3 Answers

You can try like--

df.filter(~df.Dept.isin("30","20")).show()

//This will list all the columns of df where Dept NOT IN 30 or 20

user_40418870

You can try something similar in Java,

ds = ds.filter(functions.not(functions.col(COLUMN_NAME).isin(exclusionSet)));

where exclusionSet is a set of objects that needs to be removed from your dataset.

user_74652060
Related