topic Re: pyspark dataframe empties after it has been saved to delta lake. in Data Engineering

pyspark dataframe empties after it has been saved to delta lake.

Christine — Wed, 25 May 2022 06:42:57 GMT

Hi,

I am facing a problem that I hope to get some help to understand.

I have created a function that is supposed to check if the input data already exist in a saved delta table and if not, it should create some calculations and append the new data to the table.

When I run the code without saving the data, it is possible to display the dataframe (display(dataframe)), however, after appending the data from the dataframe to the delta table, a new run of display(dataframe) suddently indicates that the dataframe is empty. Can somebody help me understand why the dataframe is displayed as empty, when the only change is that the data has been saved to the delta table? Does "display" somehow run the join function again?

Thank you!

Simplified code

#Load existing delta table

deltaDF = spark.read.format('delta').load(filePath)

#Remove any row that is identical to already existing data

condition = [<relevant column comparisons>]

noexistingDF = DF.join(deltaDF, on=condition, how="left_anti")

#Performing some additional columns to the data based on the already present data

display(noexistingDF ) #successfullly displays data

#Saving data to delta table

noexistingDF.write.format("delta").mode("append").save(fileDestination)

display(noexistingDF ) #Suddenly the dataframe is empty

Re: pyspark dataframe empties after it has been saved to delta lake.

-werners- — Wed, 25 May 2022 07:02:41 GMT

yes. Spark is lazy evaluated meaning that it will execute code only on actions. display() is such an action, write too.

So Spark will first execute the query for the write (read, transform, write), and then do the same a second time for the display (read, transform, display). The left_anti join will return nothing as the data is added to the delta table.

if you would read/write from different tables, the df would not be empty (as the read table has not changed).

Re: pyspark dataframe empties after it has been saved to delta lake.

Christine — Wed, 25 May 2022 07:25:46 GMT

Okay, thank you! Do you know if there is a way to copy the table to work around it so display does not transform based on the read table but displays the data as it was before saving?

Re: pyspark dataframe empties after it has been saved to delta lake.

-werners- — Wed, 25 May 2022 07:33:42 GMT

There are several ways.

But they all come down to the same: writing the df to disk.

So if you write noexistingDF to disk (by spark.write or checkpoint) and then read it, you're there.

Copying the delta table itself seems overkill (althoug it can be done).

Re: pyspark dataframe empties after it has been saved to delta lake.

Anonymous — Wed, 01 Jun 2022 06:57:17 GMT

Hi @Christine Pedersen please let us know if @Werner Stinckens answered helped you in mitigating the issue or do you need any further help on this?

Re: pyspark dataframe empties after it has been saved to delta lake.

Christine — Wed, 01 Jun 2022 13:49:27 GMT

Hi @Chetan Kardekar, the replies did answer my question, so I do not need more information, thank you.

Re: pyspark dataframe empties after it has been saved to delta lake.

Anonymous — Fri, 22 Jul 2022 15:09:47 GMT

Hey there @Christine Pedersen

Hope everything is going great!

Would you be happy to circle back and mark an answer as best? It would be really helpful for the other members to find the solution more quickly.

Cheers!

Re: pyspark dataframe empties after it has been saved to delta lake.

Christine — Mon, 01 Aug 2022 06:45:56 GMT

Hey @Vartika Nain

Yes of course.

Cheers!

Re: pyspark dataframe empties after it has been saved to delta lake.

SharathE — Sat, 23 Sep 2023 18:04:59 GMT

Hi,im also having similar issue ..does creating temp view and reading it again after saving to a table works??

Re: pyspark dataframe empties after it has been saved to delta lake.

mayur_05 — Thu, 09 May 2024 18:18:51 GMT

But what if i want to do some transformation after writing noexisting df into tables and use that df into my code