I have two DataFrames: a and b. This is how they look like:
a
-------
v1 string
v2 string
roughly hundreds of millions rows
b
-------
v2 string
roughly tens of millions rows
I would like to keep rows from DataFrame a where v2 is not in b("v2").
I know I could use left join and filter where right side is null or SparkSQL with "not in" construction. I bet there is better approach though.
You can achieve that using the except method of Dataset, wich "Returns a new Dataset containing rows in this Dataset but not in another Dataset"
Use PairRDDFunctions.subtractByKey:
def subtractByKey[W](other: RDD[(K, W)])(implicit arg0: ClassTag[W]): RDD[(K, V)]
Return an RDD with the pairs from this whose keys are not in other.
(There are variants that offer control over the partitioning. See the docs.)
So you would do a.rdd.map { case (v1, v2) => (v2, v1) }.subtractByKey(b.rdd).toDF.
subtractByKey and go back to DataFrames.
except is the perfect answer! Want to post it as a separate answer?Consider your dataframe a looks like below.
+----+
|col1|
+----+
| v1|
| v2|
+----+
Consider your dataframe b looks like below.
+----+
|col1|
+----+
| v2|
+----+
APPROACH 1:
-------------------
You can use dataframe's join method and use the type of join as left_anti to find out the values that are in dataframe a but not in dataframe b. The code is given below :
a.as('a).join(b.as('b),$"a.col1" === $"b.col1","left_anti").show()
Please find the result below :
APPROACH 2:
-------------------
You can use sql which is similar to Sql server/Oracle etc to do this. For this, first you have to register your dataframe as temp table (which will reside in spark's memory) and then write the sql on top of that table.
a.registerTempTable("table_a")
b.registerTempTable("table_b")
spark.sql("select * from table_a a where not exists(select 1 from table_b b where a.col1=b.col1)").show()
Please find the result below :
