CREATE FUNCTION

Creates an external function that calls a remote handler over Flight (typically Python or other services).

Supported Languages

• Determined by the remote server (commonly Python, but any language can be used as long as it implements the Flight endpoint)

Syntax

CREATE [ OR REPLACE ] FUNCTION [ IF NOT EXISTS ] <function_name>
    AS ( <input_param_types> ) RETURNS <return_type> LANGUAGE <language_name>
    HANDLER = '<handler_name>' ADDRESS = '<udf_server_address>'
    [DESC='<description>']

Examples

This example walks through a complete end-to-end setup for an external function that calculates the greatest common divisor (GCD) of two integers.

Step 1: Set Up the Python UDF Server

Install the tidbcloudlake-udf package:

pip install tidbcloudlake-udf

Create a file udf_server.py with the following content:

from tidbcloudlake_udf import udf, UDFServer

@udf(
    input_types=["INT", "INT"],
    result_type="INT",
    skip_null=True,
)
def gcd(x: int, y: int) -> int:
    while y != 0:
        (x, y) = (y, x % y)
    return x

if __name__ == '__main__':
    server = UDFServer("0.0.0.0:8815")
    server.add_function(gcd)
    server.serve()

Start the server:

python udf_server.py

Step 2: Register the Function in TiDB Cloud Lake

CREATE FUNCTION gcd AS (INT, INT)
    RETURNS INT
    LANGUAGE python
    HANDLER = 'gcd'
    ADDRESS = 'https://udf.example.com';

Step 3: Call the Function

SELECT gcd(48, 18);
-- Returns: 6

SELECT gcd(100, 75);
-- Returns: 25