Python UDFs
Python UDFs must be configured before use. For more information, see the chapter “Configuring User-defined Functions” in the System Administrator Guide.
NumPy Requirement
When using non-containerized UDFs, NumPy version 1.26 must be available in the Python 3.10 installation. Use the following pip3 install command to install the required NumPy version:
pip3 install numpy==1.26
When using containerized UDFs, NumPy is pre-installed in the container and does not require a separate install.
Using Imports
Python imports are located using PYTHONPATH. The /opt/Actian/vectorwise-udf/import directory is automatically added to PYTHONPATH when using containerized UDFs.
NumPy UDF
The speed of numerical data processing is greatly improved using NumPy arrays versus using conventional Python data types. In addition, many machine learning libraries use NumPy array processing allowing database data to be directly provided to these libraries without conversion to or from Python objects.
Although NumPy UDFs are written in Python (you must select Python as the udf_languages), there are several important differences between NumPy and Python UDFs.
NumPy Import
The NumPy Python import will be imported automatically as “np” so your UDF is not required to add a NumPy import statement.
UDF names must be prefixed with numpy__ (two underscore characters).
When you prefix a Python UDF name with numpy__, such as “numpy__myudf”, argument processing is changed to take advantage of NumPy’s fast array-based APIs and UDFs receive database data a vector at a time rather than a tuple at a time.
Best with Numeric Data
NumPy’s key strength is processing vectors of numeric data. Although other data types such as strings and dates are supported in NumPy UDFs, many of these types revert to the underlying Python data type rather than a NumPy array.
To realize the benefit of NumPy array processing, data must be accessed with NumPy APIs. Converting to Python objects will negate any performance benefit and may even be slower.
UDFs will receive and return vectors rather than a tuple at a time.
NumPy UDF arguments and return value differ from basic Python UDFs. When writing NumPy UDFs, you must handle arguments and the return value as follows:
1. Every non-constant argument is either a NumPy or Python array of the same size.
2. Constant argument values will be an array of size 1. Your code should assume any argument can be a constant value and check the size.
3. A variable called vector_size is automatically provided to the UDF code that contains the current vector size. Note that the vector size can range from 1 to the currently set vector size. (Default size is 1024.)
4. The UDF must return a single NumPy or Python array that is exactly the size of the provided vector_size variable.
Below is a simple NumPy UDF that adds two arguments and returns the result. It does not allow constants (single value arrays) as an example of the vector_size variable. However, the UDF will function properly if those checks are removed, with the behavior of adding the constant value to all array items.
create or replace function numpy__add(a integer, b integer) return (integer) as language python source='
if len(a) < vector_size:
raise Exception("Argument 1 is a constant")
if len(b) < vector_size:
raise Exception("Argument 2 is a constant")
return np.add(a,b)
',commutative=true;\g
Nulls Not Supported
NumPy UDFs do not support nullskip=’ignore’ and do not support NULL argument or return values.
Data Types Supported by NumPy
Vector data types map to NumPy types as shown in the following table.
Supported NumPy types must be returned as a NumPy array. However, the UDF can also return a plain Python array with the expected Python data type. For example, UDFs that are defined to return a timestamp are expected to return a datetime Python type or a type that Python can convert to a datetime.
Data types not supported by NumPy will send Python objects.
Last modified date: 12/19/2024