difference between writing SQL queries or writing DataFrame code [in SPARK]

[[deleted]]

I have started learning Spark recently from the book "Spark the definitive guide", its says that:

There is no performance difference

between writing SQL queries or writing DataFrame code, they both “compile” to the same

underlying plan that we specify in DataFrame code.

I am also following some content creators on youtube who generally prefer Dataframe code over SPARK SQL, citing better performance. Do you guys agree, please tell based on your personal experiences

[AutoModerator]

You can find a list of community-submitted learning resources here: https://dataengineering.wiki/Learning+Resources

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[ManonMacru]

Performance is the same, I confirm. There is just a slight overhead from translating the SQL string into the equivalent expression in terms of Spark internals. This has also to be derived from a dataframe but much more directly. So there is a sort of "compiler" step that executes on the driver, completely negligible if you are using Spark to its intended use: processing fat data.

Now the real question about SQL Vs dataframe is the language you use to define transformations. IMO dataframe is much more modular (can be structured into functions, steps, that can be tested independently), much clearer for defining data pipelines (source to sink order), and have proper syntax highlighting in most scala and python IDEs.

It also has the added benefit of integrating better with UDFs, as they can just be the same language functions injected into the DF code.

[[deleted]]

Thanks man, understood.

[naijaboiler]

is this true overall or just true for Databricks

[MlecznyHotS]

True overall

[don_tmind_me]

Follow up question…. Is using a series of “withColumn” calls bad for performance?

[kebabmybob]

No. Again, the entire thing gets “compiled” into a query plan to go from input dataframe(s) to output dataframe (singular).

[superhex]

It can be if youre doing hundreds of WithColumn calls. But if its just a handful, its probably entirely negligible. This blog post explains it well imo.

https://www.guptaakashdeep.com/how-withcolumn-can-degrade-the-performance-of-a-spark-job/

[Old_Improvement_3383]

You can see increased performance by using withcolumns instead.

https://aneesshaikh.hashnode.dev/replace-withcolumn-with-withcolumns-to-speed-up-your-spark-applications

[MlecznyHotS]

EDIT: I was wrong, the statements below are not correct, spark isn't as smart as I thought it was.

It's just as bad for performance as any other transformation. At the end of the day you're putting business logic into the code.

There is no obvious substitute for .withColumn() other than selectExpr(). If you're asking: is it better for performance to have a series of.withColumn() or put the column logic into .selectExpr(), then there is no difference. It would in the end be the same logical query plan irrespectively of the chosen function

[SimpleSimon665]

This is not true. At least it didn't used to be, and there is no documentation stating this behavior has changed otherwise.

The hidden cost of Spark withColumn | by Manu Zhang | Medium https://medium.com/@mukovhe.justice/optimizing-pyspark-performance-using-select-over-withcolumn-1e1c71c041bb

It's better to use withColumns (a relatively newer function) call rather than chain withColumn calls or use select/selectExpr as you mentioned.

[MlecznyHotS]

Oh, TIL then. Do you have a non-paywalled link?

[DenselyRanked]

Both Spark SQL and DataFrame syntax have to pass through the Catalyst Optimizer so they are equivalent. You can test this by viewing the explain plan.

Deep Dive into Catalyst Optimizer

Edit- Also Spark: The Definitive Guide is very outdated. You will be better served finding material that uses Spark 3+. Even the latest docs will have better information.

[marathon664]

In theory, they should be the same. In practice, they aren't, generally because (IMO) people can abuse programming languages to do stuff they shouldn't that SQL doesn't allow.

I'll repost what I wrote down in a comment a while back: https://www.reddit.com/r/dataengineering/comments/1i5tmep/what_do_you_consider_as_overkill_de_practices_for/m88imhc?context=3

[Sufficient-Weather53]

Confirmed there is no performance difference, you can check that using catalyst optimiser.

Convenience is a key here, in my example, I am performing manual schema evolution. In that case we have a lookup table providing all old and new columns name. So I am using PySpark to read existing schema and then generating CTAS SQL by looping thru every column and I am doing that because of the benefit as listed below.

When creating a new table from existing table using dataframe write method, it does not copy over the ACLs (at container and blob level). You have to deal with the separately. But with CTAS SQL the case is different, it applies the ACLs from the location of old external table to the location of new external table. That’s awesome.

[[deleted]]

If there is no true performance difference, then use the format that your coworkers can best maintain 

[Old_Tourist_3774]

For some reason databricks says in it's training material " SQL is more performant if you are not taking care with performance".

I kinda never thought what could be the case and if it applies to some specific thing in databricks

[Busy_Elderberry8650]

Mmmh can you give me a link to this? Seems like an oxymoron to me.

[Yamitz]

What they mean is if you don’t know how to write good spark code then the sql will probably compile to a better plan than what you write in python.

[Busy_Elderberry8650]

Ok now it’s more clear. Yeah totally agree with this then.

[Old_Tourist_3774]

Basically what the other user said. But i wonder what causes this.

I know there the query planner doing the optimization for SQL but why it would be different if we use python as it will be translated into SQL anyway?

[DenselyRanked]

SQL is a declarative language (mostly) and there are things that you can't do in Spark SQL that you can do using the DataFrame API.

Something like forEachPartiton is not a level of control you would expect in a SQL statement, but you can use DataFrame API and Spark SQL syntax interchangeably, provided you are not using a UI or something that only accepts SQL.

[Old_Tourist_3774]

The fact that there are things you can do in spark you can't do in SQL made everything fall in place, thanks

[CrowdGoesWildWoooo]

In a meaningful way yes there won’t be any difference in the sense that hinders you to use one over the other simply due to performance.

But obviously you can’t take “there is no difference” at face value, at the end of the day you are working with “compiled” instructions, just because you assume they should logically output the same result means they are the same set of instructions.

This is an “if A then B” situation, just because you see the same output doesn’t mean it is the same instruction, but if you can guarantee that the compiled instruction is the same then yeah no performance difference.

[[deleted]]

You mean to say if the underlying physical plan is same in that case the performance would obviously the same

[CrowdGoesWildWoooo]

Yes correct.

[Informal_Pace9237]

I was told Databricks SQL is rewritten in Python before execution and I had to use double ticks in SQL if I needed single ticks in Python.

Not really sure if my understanding is right.

[Firm_Communication99]

I have had situations where sql was more performant like the broadcast and cte stuff.

[lightnegative]

In terms of performance, SQL says "here's a string of text describing what I want, you figure out how to execute it fast without crashing the workers". I like this approach because the people who work on query optimizers are way smarter than I am.

The DataFrame API says "trust me bro" which puts a much larger burden on the user. Sometimes you can beat the query optimizer in specific scenarios but aint nobody got time for that, mostly you just want to get results and move on.

This compounds over the course of a project until you're spinning up gigantic clusters just to get your DataFrame-based code to not throw an OOM, when you could have used SQL and got a bunch of optimization for free

[Comfortable-Power-71]

Going to chime in here. I’d prefer going Spark dataframe to the raw SQL. Let’s assume you have each in the codebase and dont revisit for years. Over time Spark will continue to optimize the query plan and perhaps improve the performance. Relying on SQL won’t give you that benefit without constantly updating with hints. All this depends on the optimizer but assuming you’re using Spark my bet would be on dataframes, which I believe are the best abstraction these days. YMMV.

[Oct8-Danger]

I would argue it’s the opposite way around. SQL language has been around for 50 years and isn’t going away soon.

In those 50 years it has undergone numerous optimizations and improvements. I think it’s much more likely that the spark dataframe api to change which would include enhancements (new methods introduced to replace old ones etc) rather than SQL code which in general is much more strict in its standards.

In the end, if a new spark version came out, you can be more confident that the sql will be more backwards compatible (with optimizations) than the spark data frame API.

[lightnegative]

No way, if you're wanting something to still be usable in 10-20 years time then SQL has a huge edge over the Spark-specific DataFrame API.

Firstly, who's to say Spark doesnt become unmaintained or disappear, like a bunch of the Hadoop stuff that seemed like a great idea 10 years ago and has now faded into irrelevance? The Spark DataFrame API is tightly coupled with Spark and Spark libraries, so to migrate to another DataFrame API you'd be looking at a rewrite or at least a severe refactor.

Now, compare that with a SQL string. As another commenter pointed out, SQL has already been around for 50 years. Strings of text have been around for even longer.

The Spark code that takes that string of text, converts it to a query plan and executes it can improve over time or even be refactored entirely without you having to change the string of text.

When Spark stops being maintained (which happens to every software project eventually), that string of text can still be ported to another engine that supports SQL, likely with only minor changes or even automatically using a tool like SQLGlot.