Python user-defined functions

A Python user-defined function is a user-defined function that uses the Python programming language and statements for the definition of the function.

Python UDF declaration

Declare a Python UDF as inline or catalog UDF with the following steps:

• Use the FUNCTION keyword to declare the UDF name and parameters.
• Add the RETURNS declaration to specify the data type of the result.
• Set the LANGUAGE to PYTHON.
• Declare the name of the Python function to call with the handler property in the WITH block.
• Use $$ to enclose the Python code after the AS keyword.
• Add the function from the handler property and ensure it returns the declared data type.
• Expand your Python code section to implement the function using the available Python language.

The following snippet shows pseudo-code:

FUNCTION python_udf_name(input_parameter data_type)
  RETURNS result_data_type
  LANGUAGE PYTHON
  WITH (handler = 'python_function')
  AS $$
  ...
  def python_function(input):
      return ...
  ...
  $$

A minimal example declares the UDF doubleup that returns the input integer value x multiplied by two. The example shows declaration as Introduction to UDFs and invocation with the value 21 to yield the result 42.

Set the language to PYTHON to override the default SQL for SQL user-defined functions. The Python code is enclosed with $$ and must use valid formatting.

WITH
  FUNCTION doubleup(x integer)
    RETURNS integer
    LANGUAGE PYTHON
    WITH (handler = 'twice')
    AS $$
    def twice(a):
        return a * 2
    $$
SELECT doubleup(21);
-- 42

The same UDF can also be declared as Introduction to UDFs.

Refer to the Example Python UDFs for more complex use cases and examples.

Python language details

The Trino Python UDF integrations uses Python 3.13.0 in a sandboxed environment. Python code runs within a WebAssembly (WASM) runtime within the Java virtual machine running Trino.

Python language rules including indents must be observed.

Python UDFs therefore only have access to the Python language and core libraries included in the sandboxed runtime. Access to external resources with network or file system operations is not supported. Usage of other Python libraries as well as command line tools or package managers is not supported.

The following libraries are explicitly removed from the runtime and therefore not available within a Python UDF:

• bdb
• concurrent
• curses
• ensurepip
• doctest
• idlelib
• multiprocessing
• pdb
• pydoc
• socketserver
• sqlite3
• ssl
• subprocess
• tkinter
• turtle
• unittest
• venv
• webbrowser
• wsgiref
• xmlrpc

The following libraries are explicitly added to the runtime and therefore available within a Python UDF:

• attrs
• bleach
• charset-normalizer
• defusedxml
• idna
• jmespath
• jsonschema
• pyasn1
• pyparsing
• python-dateutil
• rsa
• tomli
• ua-parser

Type mapping

The following table shows supported Trino types and their corresponding Python types for input and output values of a Python UDF:

Trino type	Python type
ROW	tuple
ARRAY	list
MAP	dict
BOOLEAN	bool
TINYINT	int
SMALLINT	int
INTEGER	int
BIGINT	int
REAL	float
DOUBLE	float
DECIMAL	decimal.Decimal
VARCHAR	str
VARBINARY	bytes
DATE	datetime.date
TIME	datetime.time
TIME WITH TIME ZONE	datetime.time with datetime.tzinfo
TIMESTAMP	datetime.datetime
TIMESTAMP WITH TIME ZONE	datetime.datetime with datetime.tzinfo
INTERVAL YEAR TO MONTH	int as the number of months
INTERVAL DAY TO SECOND	datetime.timedelta
JSON	str
UUID	uuid.UUID
IPADDRESS	ipaddress.IPv4Address or ipaddress.IPv6Address

Time and timestamp

Python datetime and time objects only support microsecond precision. Trino argument values with greater precision are rounded when converted to Python values, and Python return values are rounded if the Trino return type has less than microsecond precision.

Timestamp with time zone

Only fixed offset time zones are supported. Timestamps with political time zones have the zone converted to the zone's offset for the timestamp's instant.