exasol-udfs

Exasol UDFs and Scripts

Overview

Exasol lets you define custom functions and scripts in Python, Java, Lua, or R. These run inside the Exasol cluster nodes. Use them when:

• A computation is too complex to express in SQL.
• You need to call an external library, model or service.
• You want to process data in bulk without transferring it to the client.

The CREATE SCRIPT statement creates a UDF (also called "script").

Script Types

Type	Input	Output	Typical use
SCALAR	One row per call	One row (or multiple with EMITS)	Transform a single value
SET	All rows of a group	One row (or multiple with EMITS)	Aggregate or reshape a group

SET with EMITS can return a different number of rows than it receives (e.g., a custom window function). A SET script with RETURNS (not EMITS) acts as a custom aggregate — it receives all rows of a group and returns a single value. A SCALAR with EMITS can be used for splitting one row into many.

CREATE SCRIPT Syntax

CREATE OR REPLACE PYTHON3 SCALAR SCRIPT my_schema.my_script(
    input_col VARCHAR(100),
    factor DOUBLE
)
RETURNS DOUBLE AS

import math

def run(ctx):
    return math.sqrt(ctx.input_col) * ctx.factor

/

The script body ends with a bare / on its own line.

Language Tokens

Token	Language
PYTHON3	Python 3 (recommended)
PYTHON3_SME	Python 3 with Script Management Extensions
JAVA	Java
LUA	Lua
R	R

Return Declaration

• RETURNS type — scalar output (one value per call for SCALAR scripts).
• EMITS (col1 type1, col2 type2, ...) — tabular output (SET and SCALAR scripts that emit multiple rows).

Python SCALAR Script

CREATE OR REPLACE PYTHON3 SCALAR SCRIPT my_schema.upper_words(text VARCHAR(2000))
RETURNS VARCHAR(2000) AS

def run(ctx):
    return ctx.text.upper() if ctx.text else None

/

Call it like a regular SQL function:

SELECT my_schema.upper_words(description) FROM products;