topic Re: How to implement the where not exists pattern in scala? in Data Engineering

How to implement the where not exists pattern in scala?

tarente — Fri, 08 Oct 2021 17:04:23 GMT

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.

Re: How to implement the where not exists pattern in scala?

Anonymous — Fri, 08 Oct 2021 20:43:48 GMT

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.

Re: How to implement the where not exists pattern in scala?

-werners- — Mon, 11 Oct 2021 10:21:51 GMT

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.

Re: How to implement the where not exists pattern in scala?

tarente — Tue, 12 Oct 2021 10:34:00 GMT

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.

Re: How to implement the where not exists pattern in scala?

-werners- — Tue, 12 Oct 2021 11:08:32 GMT

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).

Re: How to implement the where not exists pattern in scala?

Dan_Z — Wed, 20 Oct 2021 21:47:54 GMT

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()

Re: How to implement the where not exists pattern in scala?

tarente — Thu, 21 Oct 2021 08:06:41 GMT

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.