Filter Spark DataFrame based on another DataFrame that specifies denylist criteria
<p>I have a <code>largeDataFrame</code> (multiple columns and billions of rows) and a <code>smallDataFrame</code> (single column and 10,000 rows).</p>
<p>I'd like to filter all the rows from the <code>largeDataFrame</code> whenever the <code>some_identifier</code> column in the <code>largeDataFrame</code> matches one of the rows in the <code>smallDataFrame</code>.</p>
<p>Here's an example:</p>
<p>largeDataFrame</p>
<pre><code>some_idenfitier,first_name
111,bob
123,phil
222,mary
456,sue
</code></pre>
<p>smallDataFrame</p>
<pre><code>some_identifier
123
456
</code></pre>
<p>desiredOutput</p>
<pre><code>111,bob
222,mary
</code></pre>
<p>Here is my ugly solution.</p>
<pre class="lang-scala prettyprint-override"><code>val smallDataFrame2 = smallDataFrame.withColumn(&quot;is_bad&quot;, lit(&quot;bad_row&quot;))
val desiredOutput = largeDataFrame.join(broadcast(smallDataFrame2), Seq(&quot;some_identifier&quot;), &quot;left&quot;).filter($&quot;is_bad&quot;.isNull).drop(&quot;is_bad&quot;)
</code></pre>
<p>Is there a cleaner solution?</p>