timsaucer commented on code in PR #17:
URL: https://github.com/apache/datafusion-site/pull/17#discussion_r1714531391
##########
_posts/2024-08-06-datafusion-python-udf-comparisons.md:
##########
@@ -0,0 +1,595 @@
+---
+layout: post
+title: "Comparing approaches to User Defined Functions in Apache Datafusion
using Python"
+date: "2024-08-06 00:00:00"
+author: timsaucer
+categories: [tutorial]
+---
+
+<!--
+{% comment %}
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to you under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+{% endcomment %}
+-->
+# Writing User Defined Functions in Apache Datafusion using Python
+
+## Personal Context
+
+For a few months now I’ve been working with Apache DataFusion, a fast query
engine written in rust.
+From my experience the language that nearly all data scientists are working in
is Python. In
+general, for in memory work people often stick to pandas and pyspark for
larger tasks that cannot
+fit into memory. Polars is also growing extremely fast.
+
+Personally, I would love a single query approach that is fast for both in
memory usage and can
+extend to large batch processing to exploit parallelization. I think
DataFusion, coupled with
+Ballista, may provide this solution.
+
+As I’m testing, I’m primarily limiting my work to the datafusion-python
project, a wrapper around
+the rust library. This wrapper gives you the speed advantages of keeping all
of the data in the
+rust implementation and the ergonomics of working in python. Personally, I
would prefer to work
+purely in rust, but I also recognize that since the industry works in python
we should meet the
+people where they are.
+
+## User Defined Functions
+
+The focus of this post is User Defined Functions. The DataFusion library gives
a lot of useful
+functions already for doing dataframe manipulation. These are going to be
similar to those you
+find in other dataframe libraries. You’ll be able to do simple arithmetic,
create substrings of
+columns, or find the average value across a group of rows. These cover most of
the use cases
+you’ll need in a DataFrame.
+
+However, there will always arise times when you want a custom function. By
using user defined
+functions (UDFs) you open the world of possibilities of your code. Sometimes
there simply isn’t an
+easy way to use built in functions to achieve your goals.
+
+In the following, I’m going to demonstrate two example use cases. These are
based on real world
+problems I’ve encountered. Also I want to demonstrate the approach of “make it
work, make it work
+well, make it fast” that is a motto I’ve seen thrown around in data science.
+
+I will demonstrate three approaches to writing UDFs. In order of increasing
performance they are
+
+- Writing a pure python function to do your computation
+- Using the pyarrow libraries in python to accelerate your processing
+- Writing a UDF in rust and exposing it to python
+
+Additionally I will demonstrate two variants of this. The first will be nearly
identical to the
+pyarrow library approach to simplicity of understanding how to connect the
rust code to python. The
+second version we will do the iteration through the input arrays ourselves to
give even greater
+flexibility to the user.
+
+Here are the two example use cases, taken from my own work but generalized.
+
+### Use Case 1: Scalar Function
+
+I have a DataFrame and a list of tuples that I’m interested in. I want to
filter out the dataframe
+to only have values that match those tuples from certain columns in the
dataframe. For example,
+suppose I have a table of sales line items. There are many columns, but I am
interested in three: a
+part key, supplier key, and return status. I want only to return a dataframe
with a specific
+combination of these three values.
+
+Probably the most ergonomic way to do this without UDF is to turn that list of
tuples into a
+dataframe itself, perform a join, and select the columns from the original
dataframe. If we were
+working in pyspark we would probably broadcast join the dataframe created from
the tuple list since
+it is tiny. In practice, I have found that with some dataframe libraries
performing a filter rather
+than a join can be significantly faster. This is worth profiling for your
specific use case.
+
+### Use Case 2: Aggregate or Window Function
+
+I have a dataframe with many values that I want to aggregate. I have already
analyzed it and
+determined there is a noise level below which I do not want to include in my
analysis. I want to
+compute a sum of only values that are above my noise threshold.
+
+This can be done fairly easy without leaning on a User Defined Aggegate
Function (UDAF). You can
+simply filter the dataframe and then aggregate using the built in `sum`
function. Here, we
+demonstrate doing this as a UDF primarily as an example of how to write UDAFs.
We will use the
+pyarrow compute approach.
+
+## Pure Python approach
+
+The fastest way (developer time, not code time) for me to implement the scalar
problem solution
+was to do something along the lines of “for each row, check the values of
interest contains that
+tuple”. I’ve published this as [an
example](https://github.com/apache/datafusion-python/blob/main/examples/python-udf-comparisons.py)
+in the [datafusion-python
repository](https://github.com/apache/datafusion-python). Here is an
+example of how this can be done:
+
+```python
+values_of_interest = [
+ (1530, 4031, "N"),
+ (6530, 1531, "N"),
+ (5618, 619, "N"),
+ (8118, 8119, "N"),
+]
+
+def is_of_interest_impl(
+ partkey_arr: pa.Array,
+ suppkey_arr: pa.Array,
+ returnflag_arr: pa.Array,
+) -> pa.Array:
+ result = []
+ for idx, partkey in enumerate(partkey_arr):
+ partkey = partkey.as_py()
+ suppkey = suppkey_arr[idx].as_py()
+ returnflag = returnflag_arr[idx].as_py()
+ value = (partkey, suppkey, returnflag)
+ result.append(value in values_of_interest)
+
+ return pa.array(result)
+
+
+is_of_interest = udf(
Review Comment:
Great idea. I've added it to the issue list
https://github.com/apache/datafusion-python/issues/806
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org
For additional commands, e-mail: github-h...@datafusion.apache.org
