Exasol Udfs

Exasol UDFs & Script Language Containers

Trigger when the user mentions UDF, user defined function, CREATE SCRIPT, ExaIterator, SCALAR, SET EMITS, BucketFS, script language container, SLC, exaslct, custom packages, GPU UDF, ctx.emit, ctx.next, variadic script, dynamic parameters, EMITS(...), defaultoutputcolumns, or any UDF/SLC-related topic.

When to Use UDFs

Use UDFs to extend SQL with custom logic that runs inside the Exasol cluster:

• Per-row transforms (cleaning, parsing, hashing)
• Custom aggregation across grouped rows
• ML model inference (load model from BucketFS, score rows)
• Calling external APIs from within SQL
• Batch processing with DataFrames

SCALAR vs SET Decision Guide

| | SCALAR | SET | |---|--------|-----| | Input | One row at a time | Group of rows (via GROUP BY) | | Output | RETURNS (single value) | EMITS (col1 TYPE, ...) (zero or more rows) | | Row iteration | Not needed | ctx.next() loop required | | SQL usage | SELECT udf(col) FROM t | SELECT udf(col) FROM t GROUP BY key | | Use case | Per-row transforms | Aggregation, ML batch predict, multi-row emit |

Language Selection

| Language | Startup | Best For | Expandable via SLC? | |----------|---------|----------|---------------------| | Python 3 (3.10 or 3.12) | ~200ms | ML, data science, pandas, string processing | Yes | | Java (11 or 17) | ~1s | Enterprise libs, type safety, Virtual Schema adapters | Yes | | Lua 5.4 | The script has dynamic return arguments. Either specify the return arguments in the query via EMITS or implement the method defaultoutputcolumns in the UDF.

ExaIterator API Quick Reference

Python

| Method/Property | SCALAR | SET | Description | |----------------|--------|-----|-------------| | ctx. | yes | yes | Access input column value | | return value | yes | no | Return single value (RETURNS) | | ctx.emit(v1, v2, ...) | no | yes | Emit output row (EMITS) | | ctx.emit(dataframe) | no | yes | Emit DataFrame as rows | | ctx.next() | no | yes | Advance to next row; returns False at end | | ctx.size() | no | yes | Number of rows in current group | | ctx.reset() | no | yes | Reset iterator to first row | | ctx.get_dataframe(num_rows, start_col) | no | yes | Get rows as pandas DataFrame |

Important: There is no emit_dataframe() method — use ctx.emit(dataframe) to emit a DataFrame.

Java

| Method | Description | |--------|-------------| | ctx.getString("col") | Get string value | | ctx.getInteger("col") | Get integer value | | ctx.getDouble("col") | Get double value | | ctx.getBigDecimal("col") | Get decimal value | | ctx.getDate("col") | Get date value | | ctx.getTimestamp("col") | Get timestamp value | | ctx.next() | Advance to next row (SET only) | | ctx.emit(v1, v2, ...) | Emit output row (SET only) | | ctx.size() | Row count in group (SET only) | | ctx.reset() | Reset to first row (SET only) |

BucketFS File Access

All languages can read files from BucketFS at /buckets///:

# Python — load a pickled ML model
import pickle
with open('/buckets/bfsdefault/default/models/model.pkl', 'rb') as f:
    model = pickle.load(f)

// Java — reference JARs via %jar directive
%jar /buckets/bfsdefault/default/jars/my-library.jar;

Performance tip: Load models/resources once (outside the row loop or in a module-level variable), not per-row.

GPU Acceleration (Exasol 2025.2+)

Exasol supports GPU-accelerated UDFs via CUDA-enabled Script Language Containers:

• Use template-Exasol-8-python-3.{10,12}-cuda-conda flavors
• Requires NVIDIA driver on the Exasol host
• Install GPU libraries (PyTorch, TensorFlow, RAPIDS) via conda in the SLC
• Standard UDF API — no code changes needed beyond importing GPU libraries

Script Language Containers (SLC) Overview

UDFs run inside Script Language Containers — Docker-based runtime environments. The default SLC includes standard libraries. When you need additional packages (e.g., scikit-learn, PyTorch, custom JARs), build a custom SLC.

When You Need a Custom SLC

• Installing pip/conda packages not in the default container
• Adding system libraries (apt packages)
• Using a different Python version (3.10 vs 3.12)
• Enabling GPU/CUDA support
• Adding R packages from CRAN

Quick Activation

-- Activate for current session
ALTER SESSION SET SCRIPT_LANGUAGES='PYTHON3=localzmq+protobuf://////?lang=python#buckets/////exaudf/exaudfclient_py3';

-- Activate system-wide (requires admin)
ALTER SYSTEM SET SCRIPT_LANGUAGES='...';

Install the Build Tool

pip install exasol-script-languages-container-tool

Performance Tips

• Load once, use many: Load models/resources outside the row loop
• Use SET for batching: Collect rows into a list/DataFrame, process in bulk
• Lua for low latency: Avoids JVM/Python startup overhead
• Parallelism is automatic: UDFs run on all cluster nodes simultaneously

Detailed References

• Python patterns — context API, DataFrame pattern, type mapping, testing: [references/udf-python.md](references/udf-python.md)
• Java & Lua patterns — ExaMetadata API, JARs, adapters, Lua libraries: [references/udf-java-lua.md](references/udf-java-lua.md)
• Building custom SLCs — exaslct CLI, flavors, customization, deployment, troubleshooting: [references/slc-reference.md](references/slc-reference.md)

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: exasol-labs
• Source: exasol-labs/exasol-agent-skills
• License: MIT

Install and usage instructions live in the source repository linked above.