# User-Defined Functions Spice.ai supports **user-defined functions** declared declaratively in a spicepod's `functions:` section. Each function can be a **scalar** UDF (one value per input row) or a **table** UDTF (a relation returned in a SQL `FROM` clause), and is dispatched through one of three execution tiers: * **SQL** (`from: sql`) — in-process function whose body is a DataFusion SQL expression (scalar) or query (table). * **Remote** (`from: http://…` or `from: https://…`) — async function that invokes a remote HTTP endpoint with a JSON request and receives a JSON response. * **WebAssembly** (`from: wasm`) — function backed by a sandboxed WASM module invoked with Arrow IPC batches. Functions are automatically registered into the SQL session, exposed via the `list_udfs()` UDTF and the `GET /v1/functions` HTTP endpoint, and (when scalar) surfaced to LLM tool-calling. They hot-reload when the spicepod changes on disk. ## Quickstart Declare a function in `spicepod.yaml`: ```yaml version: v2 kind: Spicepod name: my_app runtime: functions: enabled: true functions: - name: haversine_km from: sql description: Haversine distance in kilometres. volatility: immutable signature: args: - { name: lat1, type: float64 } - { name: lon1, type: float64 } - { name: lat2, type: float64 } - { name: lon2, type: float64 } returns: float64 body: | 6371 * acos( cos(radians(lat1)) * cos(radians(lat2)) * cos(radians(lon2) - radians(lon1)) + sin(radians(lat1)) * sin(radians(lat2)) ) ``` Use it in SQL: ```sql SELECT haversine_km(lat1, lon1, lat2, lon2) FROM trips; ``` ## Schema Reference Each entry in `functions:` is a `Function` object. Fields are strictly validated (`deny_unknown_fields` is enforced). | Field | Type | Required | Description | | ------------- | --------- | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `name` | string | yes | Identifier the function is registered under. Referenced by that name in SQL. | | `from` | string | yes | Source URI selecting the execution tier. `sql`, `http://…`, `https://…`, `wasm`. | | `enabled` | bool | no | Defaults to `true`. Set to `false` to keep the declaration in the spicepod without registering it for SQL, tool exposure, `list_udfs()`, or `/v1/functions`. | | `description` | string | no | Free-form description surfaced in `list_udfs()` and `GET /v1/functions`. | | `kind` | enum | no | `scalar` (default) or `table`. Both are wired today. `aggregate` and `window` are reserved and rejected at registration with a clear error. | | `volatility` | enum | no | `immutable`, `stable`, `volatile` (default). See [Volatility](#volatility). | | `signature` | object | yes | Typed signature. See below. | | `body` | string | tier-dep | Inline SQL expression (scalar) or `SELECT` query (table). **Required** for `from: sql` unless `body_ref` is set. **Optional** for `from: wasm` to supply a table input from SQL. **Forbidden** for `from: http*`. Mutually exclusive with `body_ref`. | | `body_ref` | string | tier-dep | Path to a file whose contents are the function body. Resolved relative to the runtime's CWD. Same tier rules as `body`. Mutually exclusive with `body`. | | `metadata` | map | no | Free-form metadata surfaced alongside the declaration. | | `params` | map | no | Tier-specific knobs. Supports `${ secrets:KEY }` / `${ env:KEY }` interpolation. See [Remote params](#remote-params) and [WebAssembly params](#webassembly-params). | | `dependsOn` | string\[] | no | Names of spicepod components that must load before this function. Inferred from SQL bodies and `params.input_table` when omitted. | | `metrics` | object | no | Per-function metrics configuration. | | `as_tool` | bool | no | Expose the function as an LLM tool. Defaults to `true` for scalar functions; table functions are always SQL-only. See [LLM Tool Exposure](#llm-tool-exposure). | ### `signature` ```yaml signature: tables: # optional; table inputs for the function - name: input columns: - { name: value, type: int64 } args: # positional scalar arguments - { name: x, type: int64 } returns: int64 # scalar: a single Arrow type # returns: # OR — table: a list of named output columns # - { name: value, type: int64 } # - { name: doubled, type: int64 } ``` | Field | Description | | ------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `tables` | Optional list of declared table inputs. When present, table inputs are passed before scalar arguments at call sites. Used by [table functions](#table-functions) and scalar functions that consume a relation. | | `tables[].name` | Logical input name exposed to the backend. | | `tables[].columns` | Arrow schema declared for that input. | | `args` | Positional scalar argument list. Empty for niladic functions. | | `args[].name` | Argument name. Referenced by name in SQL bodies. | | `args[].type` | Arrow logical-type string (e.g. `float64`, `utf8`, `list`, `decimal(38, 10)`, `timestamp(us, utc)`). See [Supported types](#supported-types). | | `returns` | For `kind: scalar`, a single Arrow type string. For `kind: table`, a list of named output columns (each with `name` and `type`). | ## Execution Tiers ### SQL (`from: sql`) The body is parsed into a DataFusion logical expression against a schema derived from the argument list, then lowered to a physical expression at build time. The standard DataFusion scalar functions (math, string, datetime), Spark built-ins, and `datafusion-functions-json` are all available in bodies. ```yaml - name: shout from: sql volatility: immutable signature: args: [{ name: s, type: utf8 }] returns: utf8 body: "upper(s)" ``` The body runs entirely in-process with no sandbox. Prefer SQL-tier UDFs for anything that can be expressed in a SQL expression — they're fastest, type-checked at startup, and don't leave the runtime. SQL functions support the full set of Arrow logical types accepted by DataFusion, including primitives, `list<…>`, `large_list<…>`, `struct<…>`, `decimal(p, s)`, `decimal256(p, s)`, and `timestamp(unit[, timezone])`. ## Table Functions Set `kind: table` to register a user-defined table function (UDTF). Table functions return a relation instead of a single value and are invoked in a SQL `FROM` clause: ```sql SELECT * FROM split_lines('hello\nworld'); ``` The `signature.returns` field becomes a list of output columns. The function may take any combination of scalar arguments and declared `signature.tables` inputs; table inputs always precede scalar arguments at call sites. ### SQL table functions The body is a single `SELECT` query. Scalar arguments are visible through the reserved `args` table; declared `signature.tables` inputs are visible by their declared names. ```yaml functions: - name: emit_pair from: sql kind: table volatility: immutable signature: args: [{ name: x, type: int64 }] returns: - { name: value, type: int64 } - { name: doubled, type: int64 } body: | SELECT x AS value, x * 2 AS doubled FROM args UNION ALL SELECT x + 1 AS value, (x + 1) * 2 AS doubled FROM args ``` ```sql SELECT * FROM emit_pair(4); -- value | doubled -- 4 | 8 -- 5 | 10 ``` To pass a relation into a table function, declare a `signature.tables` entry and reference the input table inside the body. The argument at the call site can be a table name or an inline subquery: ```yaml functions: - name: scale_values from: sql kind: table signature: tables: - name: input columns: [{ name: value, type: int64 }] args: [{ name: factor, type: int64 }] returns: - { name: scaled, type: int64 } body: | SELECT input.value * args.factor AS scaled FROM input CROSS JOIN args ``` ```sql SELECT * FROM scale_values(numbers, 3); SELECT * FROM scale_values((SELECT value FROM numbers WHERE value > 0), 3); ``` ### Supported Types All tiers share a single Arrow type parser. Type names are case-insensitive and accept shorthand aliases (`string` ↔ `utf8`, `bool` ↔ `boolean`, `int` ↔ `int32`, `double` ↔ `float64`). | Arrow type | Notes | | ----------------------------------------------------------------- | ------------------------------------------------------------------- | | `int8`, `int16`, `int32`, `int64` | | | `uint8`, `uint16`, `uint32`, `uint64` | | | `float32`, `float64` | | | `utf8` / `string`, `large_utf8` | | | `boolean` / `bool` | | | `binary`, `large_binary` | | | `date32`, `date64` | | | `timestamp(s)`, `timestamp(ms)`, `timestamp(us)`, `timestamp(ns)` | Optional second argument for a timezone, e.g. `timestamp(us, utc)`. | | `decimal(p, s)`, `decimal128(p, s)`, `decimal256(p, s)` | | | `list`, `large_list` | | | `struct` | Field names may be unquoted or quoted (`"name":T` / `'name':T`).