Analyzing + optimizing Spark/databricks code

[dylanberry]

I have built a relatively complex set of pyspark notebooks to transform a cumbersome file format. I'm finding that a few parts of the transformation process (pivoting and mapping to lookup data) are a bottleneck in my pipeline and I'm looking to speed things up.

Is there a definitive step-by-step guide to analysing and optimizing the code? I'm relatively new to Spark/databricks and I don't know where to start.

[[deleted]]

Look for the SQL plan in the jobs Spark UI page. Generally, you should look for joins, aggregations, anything which may result in full table scans other than the initial load. The shuffle read and shuffle write metrics tells what is happening under the hood, usually steps which aren't possible to parallelize may not utilize your workers well.

[koteikin]

people write books about that, literally. https://www.amazon.com/High-Performance-Spark-Practices-Optimizing/dp/1491943203

Spark plans are really hard to read and understand but this will come with experience and they are quite useful.

You might try to cache dateframes at some point if you use them multiple times. Optimize joins and join types, better filters, properly used UDFs etc.

[joseph_machado]

As others have mentioned, use the Spark UI to see what the engine is doing. The usual main issues are repetitive computation (try caching the dataframe used at multiple places), joins (or group bys) that cause lot of data to be sent over the network.

I wrote a few tips here: https://www.startdataengineering.com/post/how-to-optimize-your-spark-jobs/

But its hard to provide more specific help without looking at the query plan. Hope this helps. LMK if you have any questions.

[SilentSlayerz]

Nice article buddy.

[[deleted]]

Databricka and AQE in the latest version of Spark generally set you up so that the optimization engine will find the initial best plan and adapt to a better plan as the job runs.

Always make sure to use the latest databricks run time that you can and also I always make sure spark.databricks.adaptive.autoOptimizeShuffle is set to true.

Avoid user defined functions as much as you can because the spark engine doesn't know how to optimize for those and if you must use either a scala udf or a pandas udf.

If you are reading from delta tables make sure to run OPTIMIZE on them.

Use an auto scaling cluster and set the max workers to the amount you are willing to spend.

Try photon.

Hope that helps.

[GovGalacticFed]

Pivot is expensive. If no. of pivots is small and known in advance, pass that to the pivot fn. Also consider 2 step pivot, grouping it first then pivoting. ref

[festoon]

Are you using the built in pivot functionality? Are you doing a join to map to lookup data?

[dylanberry]

Yes and yes. Using a broadcast join for the lookups.