# Exasol Udfs

> Exasol User Defined Functions (UDFs) and Script Language Containers (SLCs). Covers CREATE SCRIPT, SCALAR and SET functions, ExaIterator API, Python/Java/Lua/R scripts, BucketFS file access, GPU-accelerated UDFs, and building/deploying custom Script Language Containers with exaslct.

- **Type:** Skill
- **Install:** `agentstack add skill-exasol-labs-exasol-agent-skills-exasol-udfs`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [exasol-labs](https://agentstack.voostack.com/s/exasol-labs)
- **Installs:** 0
- **Category:** [AI & ML](https://agentstack.voostack.com/c/ai-and-ml)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [exasol-labs](https://github.com/exasol-labs)
- **Source:** https://github.com/exasol-labs/exasol-agent-skills/tree/main/plugins/exasol/skills/exasol-udfs

## Install

```sh
agentstack add skill-exasol-labs-exasol-agent-skills-exasol-udfs
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# 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(...)**, **default_output_columns**, 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 default_output_columns 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
# Python — load a pickled ML model
import pickle
with open('/buckets/bfsdefault/default/models/model.pkl', 'rb') as f:
    model = pickle.load(f)
```

```java
// 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

```sql
-- 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

```bash
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](https://github.com/exasol-labs)
- **Source:** [exasol-labs/exasol-agent-skills](https://github.com/exasol-labs/exasol-agent-skills)
- **License:** MIT

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

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-exasol-labs-exasol-agent-skills-exasol-udfs
- Seller: https://agentstack.voostack.com/s/exasol-labs
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.