UDFs in Krishiv: Python, pandas and Arrow, and Rust
When no built-in function does what you need, a Krishiv UDF runs your Python (or Rust) code inside the query. In Python there are three shapes: @udf gets one value at a time, @pandas_udf gets a pandas Series, @arrow_udf gets an Arrow array. On 200,000 rows the pandas shape ran about 4 times and the Arrow shape about 8 times faster than the row shape; the Arrow shape was within about 15% of the equivalent built-in expression and the row shape about 9 times slower. Use a UDF for logic you cannot write in SQL, and the batch shapes when you do.
Key facts | Fact | Value | |---|---| | Krishiv version | 0.2.0 from PyPI; every Python number is from that install | | Python forms |
krishiv.sql.functions.udf,pandas_udf,arrow_udf;spark.udf.register(name, fn, type)for SQL | | Where it runs | in your process, on a thread pool; no worker process to start | | Rust | nativeScalarUdfandAggregateUdftraits (cratekrishiv-udf-sdk), registered withSession::register_udf| | Task | multiplyamountby 1.2 and sum it, over the 200,000-row orders file | | Machine | one laptop, 12 threads; two passes of 7 runs, run inside the benchmark lock; timings move between runs |
How do I write a Python UDF?
Declare the return type and decorate a function:
=
=
return * 1.2
[Row(sum(g)=60049707.768000044)]
To call it from SQL, register it by name:
[Row(round(sum(with_tax_sql(amount)), 2)=60049707.77)]
What are the batch forms?
@pandas_udf receives a pandas.Series per batch and returns one; @arrow_udf receives and returns a pyarrow.Array. Your function is called once per batch instead of once per row:
return * 1.2
return
How much slower is a UDF than a built-in?
Same task, four ways, two passes of seven runs each; the median is the middle of the two passes' medians, in milliseconds:
| Form | Median | Min to max |
|---|---|---|
built-in F.col("amount") * 1.2 | 4.0 ms | 3.6 to 4.7 ms |
@udf (row at a time) | 38 ms | 32.8 to 49.5 ms |
@pandas_udf | 9 ms | 8.6 to 12.7 ms |
@arrow_udf | 4.5 ms | 4.2 to 5.4 ms |
The sums agree to about 15 significant digits (they differ in the last digits because the engine adds in a different order). The row form pays a Python call per row, about 0.17 microseconds each here ((38 − 4) ms over 200,000 rows); the batch forms pay per batch. The built-in runs without Python at all and the engine can optimize around it. Rule of thumb from this table: write the batch form when you need Python, and the built-in when one exists.
What happens when my UDF fails?
The Python exception comes back as an error with the traceback and SQLSTATE 38000:
return 1 / 0
SparkRuntimeException: [PYTHON_EXCEPTION] An exception was thrown from the Python worker: Traceback (most recent call last):
...
File "bad.py", line 7, in bad
return 1 / 0 if x > 499 else x
ZeroDivisionError: division by zero SQLSTATE: 38000
The class PYTHON_EXCEPTION is Spark's, so error handling that groups by class sees the same name.
How do I write a UDF in Rust?
A Rust UDF is a type that implements the ScalarUdf trait, a function over Arrow arrays that the executor calls once per batch. Because it is compiled Rust, there is no interpreter in the loop. This is shortened from the krishiv-udf-sdk crate's own example (compiled and run on the krishiv 0.2.0 crate: it returned -1, -2, -3 for 1, 2, 3), a UDF that negates an integer column. With only krishiv in Cargo.toml it needs these imports:
use ;
use ;
use ;
The UDF:
;
Register it on a session with session.register_udf(Negate)? and call negate_i32(x) from SQL or a DataFrame. The signature tells the analyzer which implicit casts to insert, so the UDF never casts its own arguments, and plans refer to a UDF by name and version only. Aggregate UDFs implement AggregateUdf with a GroupsAccumulator and register with register_udaf. Rust UDFs are listed as supported in the 0.2 compatibility table.
Which should I use?
If a built-in function exists, use it: EXPLAIN shows what it does and the optimizer can push it down. If you need Python, write a batch UDF. If you need it fast and you write Rust, implement ScalarUdf. See also Arrow, pandas and PyArrow for moving data in and out of a query.
Enjoyed this post? Star Krishiv on GitHub to follow along.
Star on GitHub