How to implement the where not exists pattern in scala?

[tarente]

I have a dataframe with the following columns:

• Key1
• Key2
• Y_N_Col
• Col1
• Col2

For the key tuple (Key1, Key2), I have rows with Y_N_Col = "Y" and Y_N_Col = "N".

I need a new dataframe with all rows with Y_N_Col = "Y" (regardless of the key tuple), plus all Y_N_Col = "N" for which there are no Y_N_Col = "Y" for the same key tuple.

The dataframe is already calculated in a Scala notebook.

Thanks in advance,

Tiago R.

[Anonymous]

Hello, @Tiago Rente​! My name is Piper and I'm a moderator for Databricks. It's great to meet you, and thanks for your question! Let's see if your peers in the community have an answer to your question first. Or else I will follow up shortly with a response.

[-werners- · Accepted solution]

I'd use a left-anti join.

So create a df with all the Y, then create a df with all the N and do a left_anti join (on key1 and key2) on the df with the Y.

then a union of those two.

[tarente]

Hi werners,

Thanks for your answer.

I implemented your suggestion and the solution that I was seeking, but not sure which one is more performant.

The solution I was seeking is:

// My data is stored in the dfDups
// Create a Temp View
dfDups
  .createOrReplaceTempView("Dups")
 
// Create a new df without the "duplicates"
val dfNoDups = sqlContext.sql("""
  select *
    from Dups as Y
   where Y.Y_N_Col = 'Y'
   union all
  select *
    from Dups as N
   where N.Y_N_Col = 'N'
     and not exists (
                     select 1
                       from Dups as Y
                      where Y.Y_N_Col = 'Y'
                        and Y.Key1 = N.Key1
                        and Y.Key2 = N.Key2
                    )
  """)

Thanks,

Tiago R.

[-werners-]

I am not sure. In spark 2, the where not exists was actually planned using a left_anti join. In spark 3 I don't know if this has changed.

But you can display the query plan for both solutions (and try them both).

[Dan_Z]

Yes- Spark Catalyst optimizer is smart. It is possible that both query plans will actually have the same plan after the optimizers gets done with it. You can get the plan using:

dfNoDups.explain()

[tarente]

Thanks for your answer, I did not know of the explain.

I did some tests and they execute in similar times.

I ended up using the solution suggested by werners, because would easier to understand and maintain in the future.