SQL Language Guide > SQL Language Guide > Elements of SQL Statements > Scalar User-defined Functions (UDFs) > Python UDFs
Was this helpful?
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
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.
Analytics Engine data types map to Python and NumPy data types as follows:
Analytics Engine Type
Python Type
NumPy Input Type
NumPy Output Type
CHAR
str
NPY_UNICODE
NPY_STRING or NPY_UNICODE
VARCHAR
str
• str, if size > 1024
• NPY_UNICODE, if size <= 1024
NPY_STRING or NPY_UNICODE
NCHAR
str
NPY_UNICODE
NPY_STRING or NPY_UNICODE
NVARCHAR
str
• str, if size > 1024
• NPY_UNICODE, if size <= 1024
NPY_STRING or NPY_UNICODE
INTEGER1
int
NPY_BYTE
NPY_BYTE
INTEGER2
int
NPY_SHORT
NPY_SHORT
INTEGER4
int
NPY_INT
NPY_INT
INTEGER8
int
NPY_LONGLONG
NPY_LONGLONG
DECIMAL
Decimal
Decimal
Decimal*
FLOAT8
float
NPY_DOUBLE
NPY_DOUBLE
FLOAT4
float
NPY_FLOAT
NPY_FLOAT
ANSIDATE
datetime.date
datetime.date
NPY_DATETIME
TIME WITHOUT TIMEZONE
datetime.time
datetime.time
NPY_DATETIME
TIME WITH TIMEZONE
timezone‑aware datetime.time
timezone‑aware datetime.time
timezone‑aware datetime.time*
TIME WITH LOCAL TIMEZONE
datetime.time
datetime.time
NPY_DATETIME
TIMSTAMP WITHOUT TIMEZONE
datetime.datetime
datetime.datetime
NPY_DATETIME
TIMESTAMP WITH TIMEZONE
timezone‑aware datetime.datetime
timezone‑aware datetime.datetime
timezone‑aware datetime.datetime*
TIMESTAMP WITH LOCAL TIMEZONE
datetime.datetime
datetime.datetime
NPY_DATETIME
INTERVAL YEAR TO MONTH
int
NPY_INT
NPY_INT
INTERVAL DAY TO SECOND
int
int
int*
MONEY
float
NPY_DOUBLE
NPY_DOUBLE
IPv4
int
NPY_INT
NPY_INT
IPv6
int
int
int*
UUID
int
NPY_ULONGLONG
NPY_LONGLONG
Vector(TINYINT)
list of int
2D NumPy array of the appropriate NPY_* integer type
2D NumPy array of the appropriate NPY_* integer type
Vector(SMALLINT)
list of int
2D NumPy array of the appropriate NPY_* integer type
2D NumPy array of the appropriate NPY_* integer type
Vector(INTEGER)
list of int
2D NumPy array of the appropriate NPY_* integer type
2D NumPy array of the appropriate NPY_* integer type
Vector(BIGINT)
list of int
2D NumPy array of the appropriate NPY_* integer type
2D NumPy array of the appropriate NPY_* integer type
Vector(FLOAT4)
list of float
2D NumPy array of NPY_FLOAT or NPY_DOUBLE
2D NumPy array of NPY_FLOAT or NPY_DOUBLE
Vector(FLOAT)
list of float
2D NumPy array of NPY_FLOAT or NPY_DOUBLE
2D NumPy array of NPY_FLOAT or NPY_DOUBLE
Vector(DECIMAL)
list of decimal.Decimal objects
(not supported)
(not supported)
BOOLEAN
bool
NPY_BOOL
NPY_BOOL
BYTE
VARBYTE
bytearray
• bytearray, if size > 1024
• NPY_VOID, if size <= 1024
NPY_VOID
*Python data type (not a NumPy array) is expected for this result type.
Last modified date: 09/11/2026