Is there a way to define pure SQL UDF's that still take advantage of all of Pyspark's optimizations?

Question

I'm repeating a lot of code that looks like the following:

trim(concat(ifnull(`field1`, ''), ' ', ifnull(`field2`, ''))) as my_field

It would be nice to be able to define a function called trim_and_concat that takes an arbitrary number of fields, concatenates them, and trims the result. I could write a UDF, but then I would lose out on all the PySpark optimization.

Is it possible to define a function that combines native SparkSQL methods such that the typical loss of opitimization associated with UDFs is avoided?

I know about the create_function syntax, but as far as I can tell this is just another way to create UDFs, and still requires that the functions be written in scala or Python.

user_3441510 · Answer

the easier way to go is defining a function with all transformation needed:

def trim_and_concat(columns):
    all_columns = []
    for col in columns:
        all_columns.append(ifnull(col))
        all_columns.append(lit(' '))
        
    return trim(concat(*all_columns))

df = df.withColumn('test', trim_and_concat(['field1', 'field2']))

Is there a way to define pure SQL UDF's that still take advantage of all of Pyspark's optimizations?

1 Answers