[EPIC] Complete `datafusion-spark` Spark Compatible Functions

[alamb]

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:

• [datafusion-spark] Implement Spark datetime function last_day #16774
• [datafusion-spark] Implement Spark date function next_day #16775

string functions:

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

math functions:

• [datafusion-spark] Implement ceil function #15916

Infrastructure and Testing:

• [DISCUSSION] Add separate crate to cover spark builtin functions #5600
• feat: Add datafusion-spark crate #15168
• [datafusion-spark] Example of using Spark compatible function library #15915
• feat: Support test spark runner in datafusion-spark for slt tests #17045

Related issues

• [datafusion-spark] Test integrating datafusion-spark code into comet datafusion-comet#1704
• [EPIC] Implement expressions as ScalarUDFImpl datafusion-comet#1819
• Spark-compatible CAST operation #11201
• SparkSha2 is not compliant with Spark and does not support Int32 type #16336
• Add xxhash algorithms in SQL and expression api #14367

Additional context

No response

[andygrove]

@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

[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

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.

1. 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.
2. 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.
3. 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.
4. 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.