[EPIC] Complete datafusion-spark Spark Compatible Functions

@andygrove

Is your feature request related to a problem or challenge?

Many DataFusion users are using DataFusion to execution workloads originally developed for Apache Spark. Examples include

DataFusion Comet (@andygrove @comphead , etc)
LakeHQ / Sail (@shehabgamin )
Various internal pileines / engines (e.g. that @Omega359 and I think @Blizzara use)

They often do this for superior performance

Part of running Spark workloads is emulating Spark sematics
Emulating Spark semantics requires (among other things) functions compatible with Spark (which differs in semantics to the functions included in DataFusion)

Several projects are in the process of implementing Spark compatible function libraries using DataFusion's extension APIs. However. we concluded in #5600 that we could join forces and maintain a spark compatible funciton library in the core datafusion repo. @shehabgamin has implemented the first step in #15168 🙏

Describe the solution you'd like

This ticket tracks "completing" the spark function library started in #15168

Describe alternatives you've considered

datetime functions:

string functions:

[datafusion-spark] Implement Spark string function luhn_check #16612

math functions:

[datafusion-spark] Implement ceil function #15916

set functions:

map/array functions:

[datafusion-spark] Implement size function #5338

Infrastructure and Testing:

Related issues

Additional context

No response

@shehabgamin

@shehabgamin @alamb I created an epic in Comet for implementing our current expressions as ScalarUDFImpl rather than PhysicalExpr as a first step towards contributing them in the datafusion-spark crate: apache/datafusion-comet#1819

@linhr

@shehabgamin @alamb I created an epic in Comet for implementing our current expressions as ScalarUDFImpl rather than PhysicalExpr as a first step towards contributing them in the datafusion-spark crate: apache/datafusion-comet#1819

Nice! I’ll create an epic shortly after the DataFusion 48 release for Sail to contribute all of our implemented functions (and corresponding tests) to datafusion-spark.

Also, @linhr has some ideas around making sqllogictests easier to work with, for example so that test files no longer need to explicitly cast data types. I’ll let him share more on that.

@linhr

@linhr has some ideas around making sqllogictests easier to work with

Here is my idea to automate the test setup, without bringing Spark as a hard dependency.

We create a Python script to generate SLT files.
1. Define a set of input values for each data type, and optionally define custom input values for each function.
2. Use PySpark to execute the function, get the output, and write the SLT file. Make sure the values are formatted in the way that the SLT engine can understand. Input data type casting can also be done here.
For DataFusion developers who work on Spark functions, they run the Python script to update the SLT files whenever needed. PySpark is needed in a local virtualenv and we provide instructions for this setup.
For DataFusion developers who do not work on Spark functions, they run the DataFusion tests in the existing way. They do not need to be aware of how the Spark functions SLT files are generated.
We add a CI workflow that is triggered when Spark functions SLT files are changed, to make sure they are generated without unintended manual modification.

Using pyspark to generate expected input/output that gets checked in. sounds like a great idea to me

BTW i hope to devote some time next week to helping organize this effort a bit more, but have been totally bogged down in working on filtering in parquet

@linhr

This sounds like a great idea. Thanks @linhr.

@shehabgamin

I just had a chat with @shehabgamin

The current status is that we have not smoothed out the process to the point where contributors with minimal context can pick up a porting ticket and be successful porting the functions upstream. It seems writing / having tests tests are the last missing piece before we have a high likelihood of achieving this goal.

Here is the plan we came up with (please correct me if I am wrong):

@shehabgamin will create a script that creates expected .slt files from the sail gold master expected output (what @linhr describes on [EPIC] Complete datafusion-spark Spark Compatible Functions #15914 (comment)).
@shehabgamin will make a PR that checks in that pre-generated .slt files that are disabled somehow
We will then make a PR that shows a function being ported over and enabling the relevant .slt file (maybe it can be @irenjj 's [datafusion-spark] Implement ceil&floor function for spark #15958)

Then @alamb will create (copy/paste style) tickets for the remaining functions (based on #15916)

@linhr

We add a CI workflow that is triggered when Spark functions SLT files are changed, to make sure they are generated without unintended manual modification.

I am not quite sure where @linhr imagines this CI workflow running -- maybe we can start with running it manually and then run it in CI if/when we find problems with accidental changes

An update here is that we are waiting for one or two more good example PRs and then we'll turn the community on porting

If anyone wants to take a look / help out with #16612 / #16580 that would be super helpful

It might be something that codeing agents / claude code / etc can do easily 🤔

An update here is that we are waiting for one or two more good example PRs and then we'll turn the community on porting

If anyone wants to take a look / help out with #16612 / #16580 that would be super helpful

It might be something that codeing agents / claude code / etc can do easily 🤔

Would it be easier to pick a couple of functions that Sail has already implemented?

next_day and last_day are simple functions.

There are tests in DataFusion ready to go as well!

There are tests in DataFusion ready to go as well!

I filed two tickets to track this suggestion and marked them as good first issues

@alamb

Hi @alamb, I just wanted to clarify: if a Spark function appears in the sqllogictest tests, are we expected to implement it in DataFusion?

@Standing-Man

Hi @alamb, I just wanted to clarify: if a Spark function appears in the sqllogictest tests, are we expected to implement it in DataFusion?

@Standing-Man that is the intention. I haven't created individual tickets for all of them as it seems like it is more complicated to port than we initially thought so I wanted to get the process honed more (get some great examples) before we started porting things en-mass

However, since you have now done several others, perhaps you could try to port a few more (I suggest the non-string ones) and hopefully once we see the code flowing easily without much back and forth on the PRs we can start filing many more

Hi @alamb, I just wanted to clarify: if a Spark function appears in the sqllogictest tests, are we expected to implement it in DataFusion?

@Standing-Man that is the intention. I haven't created individual tickets for all of them as it seems like it is more complicated to port than we initially thought so I wanted to get the process honed more (get some great examples) before we started porting things en-mass

However, since you have now done several others, perhaps you could try to port a few more (I suggest the non-string ones) and hopefully once we see the code flowing easily without much back and forth on the PRs we can start filing many more

Got it, that makes sense. I’ll go ahead and try porting a few more non-string functions next. Hopefully this helps smooth out the process for scaling up later. Thanks for the guidance!