Python UDF for dozer

[xudong963]

The General UDF Process

• User defines UDF: create xxx, function-related information will be stored in metadata.
• When the user uses the UDF, select func_name..., the parser detects the function, and the SQL layer should first determine whether the function is already supported by Dozer. If not, query the UDF in metadata.
• Perform expression processing on the UDF, such as func as a → a + 3, then the UDF will be processed into select a + 3…;
• Then it is the normal SQL processing flow.

Dozer Python UDF

Approach One

• The user provides a Python UDF script, and registration is not required (create udf func..).
• User Python UDF script → Middle layer → Dozer/other databases Dozer executes the function in the middle layer, which can be made into a separate crate.
• The middle layer should produce what results to feed the database to reduce the workload of the database: the middle layer should be able to automatically produce all functions in the py scripts as much as possible. Manually translating one function at a time is unacceptable.
• Dozer calls the corresponding function in the middle layer based on the func name in the SQL (if it's a UDF).

Example code

# Python script
def add(a, b):
    return a + b

// Middle layer
use pyo3::prelude::*;

#[pyfunction]
fn add_py(a: i32, b: i32) -> PyResult<i32> {
    Ok(a + b)
}

#[pymodule]
fn udf_py(_py: Python, m: &PyModule) -> PyResult<()> {
    m.add_wrapped(wrap_pyfunction!(add_py))?;
    Ok(())
}

// Dozer 
use pyo3::Python;

let gil = Python::acquire_gil();
let py = gil.python();
let udf = py.import("udf_py")?;
// udf can call `add_py` and so on

Approach Two

• The user does not need to provide a Python UDF script.
• Dozer supports create udf function with Python code, and UDF-related information is stored in the meta, such as: function name, UDF function code.
• Dozer calls the Python function using pyO3 at the appropriate time.

Comparison

	Pros	Cons
Approach One	1. No need to store UDF information to metadata. 2. No need to support create udf/delete udf or similar syntax.	1. Need to find an appropriate approach to automate the conversion of Python UDF scripts to the middle layer as much as possible. - parse python file line by line and convert to middle layer, too complicated, corner cases too much. - leverage some codegen crate.
Approach Two	1. Clear thinking and high feasibility. 2. Able to handle various corner cases.	1. Increase the pressure on metastore. 2. Require support for more SQL syntax.

Happy to receive any feedback!

[chubei]

I prefer approach 2, but not exactly sure how it works.

Problem with approach 1:

• User has to write and build Rust code (the middle layer, or glue code which is the term I usually use) to use their Python code in Dozer, which is probably too intimidating for most data science people.

Question about approach 2:

• User probably has to write more than a function. I'd picture it more like a class, which has initialization, schema validation and processing methods just like our processors.
• create udf function with Python code: In this example by Python code you mean actual code like def add(a, b): return a + b or a file name like add.py?
• Do we have a way to tell pyo3 to use the user's Python environment?

[snork-alt]

Ideally the user shoul be able to provide a Python notebook. Thta's what generally data scientists use. So, ideally I should be able to point to a function contained in a .ipynb file

[mediuminvader]

User probably has to write more than a function. I'd picture it more like a class, which has initialization, schema validation and processing methods just like our processors.

Right, need to figure out how to specify the "return type", like our expressions

[mediuminvader]

Do we have a way to tell pyo3 to use the user's Python environment?

@chubei about this, for my understanding, the way pyo3 uses to specify the virtual environment is just activating it before to run the rust application.
A virtual env should just be a folder and some ENV variables, maybe there is a way to use set it at runtime.

[chubei]

Oh, so Dozer will use whatever current Python environment. Cool

[xudong963]

with Python code should be changed to .ipynb file path.

[mediuminvader]

I also like approach 2.
Question: how do we manage the python environment?

[xudong963]

I am not very clear about this, do you have any suggestions? 🤔

[mediuminvader]

Would be interesting to test

• if it's possible to install packages after the dozer application started, something like [Installing python module within code]
• if it's possible to set the virtual env after the dozer application started

the alternative is to accept to activate the required virtual environment before to run dozer

[chloeminkyung]

I also prefer approach 2 - but agree on we need more than just a user provided function, and managing python env - maybe hygeia + pyo3 might work

[chubei]

https://github.com/nbigaouette/hygeia looks dead. Last release was two years agao and last commit was last April.

[xudong963]

In fact, I also like approach 2 🤣.

So let's do more detailed design by approach 2, thanks, guys!