topic Re: Spark dataframe performing poorly in Warehousing & Analytics

Spark dataframe performing poorly

qwerty3 — Mon, 23 Sep 2024 15:08:26 GMT

I have huge datasets, transformation, display, print, show are working well on this data when read in a pandas dataframe. But the same dataframe when converted to a spark dataframe, is taking minutes to display even a single row and hours to write the data in a delta table.

Re: Spark dataframe performing poorly

gchandra — Mon, 23 Sep 2024 15:39:09 GMT

Can you please share the code snippet?

Re: Spark dataframe performing poorly

qwerty3 — Mon, 23 Sep 2024 16:01:42 GMT

df.write.format("delta").saveAsTable("test_db.test_spark_df")

This took 3 mins to get completed with 5 rows and 4 columns

Actual datasets are not even getting written

Re: Spark dataframe performing poorly

qwerty3 — Mon, 23 Sep 2024 16:02:19 GMT

df.write.format("delta").saveAsTable("test_db.test_spark_df")

This took 3 mins to get completed with 5 rows and 4 columns

Actual datasets are not even getting written

Re: Spark dataframe performing poorly

gchandra — Mon, 23 Sep 2024 17:53:35 GMT

3 mins to write 5 rows is no good.

Are you running this on a shared cluster with so many other jobs? Will it be possible to test this on a personal cluster to isolate the issue?

try displaying the data frame in one cell display(df) and save the data frame in another cell.

Re: Spark dataframe performing poorly

qwerty3 — Tue, 24 Sep 2024 11:16:02 GMT

The cluster that I was using to execute this was not performing any other tasks, although the azure quota for this cluster family cpu was 83% at the time, I created a new cluster belonging to a family which had all the cores available, there spark is working well. But even at 83% utilization, should that cluster (the one used earlier, with high memory) perform so poorly?

Re: Spark dataframe performing poorly

gchandra — Tue, 24 Sep 2024 12:10:18 GMT

It's good to hear it worked on the new cluster family.

If the quota is already at 83%, the number of nodes your cluster needs is important. If Azure is not able to provision that many resources, it could result in suboptimal performance.

To find out this, please reduce the number of nodes so your cluster can start the job and complete it.

Re: Spark dataframe performing poorly

qwerty3 — Thu, 26 Sep 2024 09:40:07 GMT

Earlier I was using EA family of clusters which were memory optimized, now when I shifted to general purpose compute, the same data is getting written in seconds. Is it that the EA family of memory optimized clusters are not very performant for spark operations?

Re: Spark dataframe performing poorly

gchandra — Thu, 26 Sep 2024 10:31:38 GMT

For processing 5 rows, EA vs. Non-EA doesn't matter.

As you mentioned before, it could be non availability of the cluster in the quota.

Re: Spark dataframe performing poorly

qwerty3 — Thu, 26 Sep 2024 15:05:40 GMT

But even with General Purpose compute (256 GB memory, 64 cores, 8 max worker nodes, working solely on one task, i.e. one notebook) I am not able to write one dataframe as delta table, it contains geospatial data and must have data in lakhs

Re: Spark dataframe performing poorly

gchandra — Thu, 26 Sep 2024 15:39:41 GMT

It could be Skew, your partition anything.

Without looking at the script, and knowing the schema, number of rows, and output of Spark UI, it's hard to say what is wrong.

Re: Spark dataframe performing poorly

qwerty3 — Thu, 26 Sep 2024 15:43:02 GMT

Re: Spark dataframe performing poorly

gchandra — Thu, 26 Sep 2024 15:47:14 GMT

🙂 count() is just the action.

What are the transformations you are doing in the data frame? How many columns, how many rows you are anticipating approximately?

Re: Spark dataframe performing poorly

qwerty3 — Thu, 26 Sep 2024 15:52:21 GMT

I want to write that data in a table, but it always get stuck, it has 12 columns, the task was getting stuck, that is why I wanted to see count of data

Re: Spark dataframe performing poorly

gchandra — Thu, 26 Sep 2024 15:55:46 GMT

One last time, please share the entire script of the data frame so I can see how I can help.

Re: Spark dataframe performing poorly

qwerty3 — Thu, 26 Sep 2024 16:02:01 GMT

df = sparl.sql("SELECT df1.*, df2.*

FROM df1

JOIN df2

ON ST_Intersects(df1.geometry, df2.geometry)")

df.write.format("delta").mode("append").saveAsTable(table_name)

df1 has 1500 rows, df2 has 90 lakh rows.

Re: Spark dataframe performing poorly

gchandra — Thu, 26 Sep 2024 16:12:39 GMT

df1 has 1500 rows and df2 has 9 million rows, try broadcast join on df1

df= spark.sql("""SELECT /*+ BROADCAST(df1) */
df1.*, df2.* FROM df1 JOIN df2 ON ST_Intersects(df1.geometry, df2.geometry) """)

Re: Spark dataframe performing poorly

qwerty3 — Thu, 26 Sep 2024 16:31:09 GMT

But the issue is coming up while writing the df, sure broadcast might optimize the join, but how will it enhance the data write operation?

Re: Spark dataframe performing poorly

gchandra — Thu, 26 Sep 2024 16:46:40 GMT

The spark concept is Lazy Transformation. This means all Transformations will be processed when an Action is invoked. In your case, the SQL JOIN is the transformation, and Write is the Action.

So try the Broadcast join and check the result.

Re: Spark dataframe performing poorly

qwerty3 — Thu, 26 Sep 2024 18:50:18 GMT

Didn't work, still taking 10 minutes to write the df, which is a very long time, considering I 5000 such chunks to process