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:

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:

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).

signature

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 }

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.

- 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]).

body_ref for longer bodies

Keep non-trivial SQL in its own file with proper editor support:

- name: haversine_km
  from: sql
  signature: { args: [...], returns: float64 }
  body_ref: ./queries/haversine.sql

body and body_ref are mutually exclusive; exactly one must be set when from: sql.

Remote (from: http://… | https://…)

The remote tier invokes an external HTTP endpoint, batching rows for scalar invocations and issuing a single request for table inputs. Powered by DataFusion's AsyncScalarUDFImpl, so the query scheduler overlaps remote calls with other work.

- name: classify_intent
  from: http://classifier.internal/v1/classify
  volatility: volatile
  signature:
    args: [{ name: prompt, type: utf8 }]
    returns: utf8
  params:
    timeout: 2s
    batch_size: 256
    batch_concurrency: 8
    auth_bearer: ${secrets:classifier_token}

Wire contract

Scalar functions without table arguments issue one HTTP request per batch:

• Request — POST <endpoint>, Content-Type: application/json, body:

  {"rows": [{"<arg_name>": <value>, ...}, ...]}

• Response — HTTP 200 with body:

  {"values": [<row_0_result>, <row_1_result>, ...]}

• values.length must equal rows.length. A mismatch is treated as an execution error.

Scalar functions with dynamic table arguments and table functions (kind: table) issue a single request per invocation with both scalar arguments and table inputs:

• Request body:

  {"args": {"<arg_name>": <value>, ...}, "tables": {"<table_name>": [{"<col>": <value>, ...}, ...]}}

  The tables field is omitted when no table inputs are declared. Each row in a table input is a JSON object whose keys match the declared tables[].columns.
• Response body — {"rows": [{"<output_col>": <value>, ...}, ...]} for table functions, {"values": [<result>]} for scalar.

Non-2xx responses surface as query errors, with the response body snippet (up to 256 chars, one line) included in the error for debugging. Argument and return values are encoded through Arrow's JSON reader/writer, so any Arrow type with a JSON representation (including list<…>, struct<…>, and timestamps) is permitted.

Remote params

WebAssembly (from: wasm)

The WebAssembly tier executes user code inside a sandboxed wasmtime module, exchanging Arrow IPC streams with the host. Both scalar and table kinds are supported; a single guest export serves both shapes.

- name: tokenize_lines
  from: wasm
  kind: table
  signature:
    tables:
      - name: input
        columns: [{ name: doc, type: utf8 }]
    returns:
      - { name: token, type: utf8 }
      - { name: position, type: int64 }
  params:
    module: ./modules/tokenizer.wasm
    entrypoint: spice_transform     # optional, defaults to spice_transform
    fuel: 100000000                 # default
    max_memory_bytes: 67108864      # default 64 MiB

WebAssembly params

The guest module must export memory, spice_alloc, spice_dealloc, and the configured entrypoint. The entrypoint receives (input_ptr, input_len, args_ptr, args_len) and returns a packed (output_ptr << 32) | output_len pointer to an Arrow IPC stream matching the declared return schema.

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:

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.

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

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:

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

SELECT * FROM scale_values(numbers, 3);
SELECT * FROM scale_values((SELECT value FROM numbers WHERE value > 0), 3);

Remote and WebAssembly table functions

Remote (from: http://…) and WebAssembly (from: wasm) table functions follow the same kind: table shape. Inputs and outputs are exchanged over the tier's native wire format — JSON for remote, Arrow IPC for WASM — as described in Execution Tiers.

Table functions are always SQL-only. They are not exposed to LLM tool-calling regardless of the as_tool setting.

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).

Return types are coerced implicitly where DataFusion knows how (e.g. Int32 → Int64, Float32 → Float64, Utf8 → LargeUtf8) — a literal like 6371 widening to Float64 is fine.

Tier notes:

• SQL tier lowers the body to a DataFusion physical expression, so any DataFusion-recognised type round-trips natively.
• Remote tier serializes arguments and results through Arrow's JSON reader/writer, so any Arrow type with a JSON representation is acceptable. Plan for wire size when passing nested structures or large lists.
• WebAssembly tier uses Arrow IPC streams as the host/guest ABI; the guest module must understand the declared input and output schemas.

Volatility

Volatility governs caching, constant-folding, and plan-time evaluation. Defaults to volatile — the safest choice.

Federation Deny-List

Every user function is automatically added to Spice's federation deny-list. This prevents the query planner from attempting to push a user function down into a federated data source (e.g. Postgres, MySQL, Databricks) — the remote source doesn't know about it, and a pushdown would either fail or, worse, silently call a different function with the same name.

Built-in Spice UDFs (ai, embed, bucket, identity SQL functions, etc.) are likewise denied. Functions are added on registration and removed on deregistration / hot-reload removal. No manual configuration is required.

Hot-Reload

When the spicepod file changes on disk, Spice diffs the functions: section against the live set:

• Removed functions are deregistered from DataFusion, removed from the deny-list, removed from USER_FUNCTION_INFO, and dropped from the tool registry.
• Changed functions are deregistered and re-registered (equality is structural — any field change triggers a re-register).
• Added functions are built and registered.

Failures to build a new or changed function are logged at ERROR and do not halt the diff — other functions still apply.

Introspection

SQL: list_udfs()

A built-in UDTF returns every function registered in the current session — Spice built-ins, DataFusion standard library, and user-declared functions together.

SELECT name, source, kind, volatility, "from", description
FROM list_udfs()
WHERE source = 'user';

HTTP: GET /v1/functions

Returns only the user-declared functions (not built-ins). Intended for operational tooling.

curl -H "Authorization: Bearer $API_KEY" http://localhost:8090/v1/functions

[
  {
    "name": "haversine_km",
    "kind": "scalar",
    "volatility": "immutable",
    "from": "sql",
    "description": "Haversine distance in kilometres"
  }
]

The OpenAPI spec exposes this under operation_id: list_functions, tag Functions.

LLM Tool Exposure

When as_tool: true (the default), every declared scalar function is also registered as an LLM tool. The tool invokes the function through a SELECT fn_name(arg0, …) AS result query over the runtime's DataFusion session, so all execution tiers dispatch transparently. Table functions (kind: table) are always SQL-only and are not surfaced to the LLM.

• Argument values are interpolated as typed SQL literals by the tool bridge — integers, floats, strings, booleans. No free-form SQL from the model is ever concatenated.
• The tool's JSON Schema parameters is derived from the function's Arrow signature. Types outside the JSON-encodable primitive set (int64, float64, utf8, boolean) are not eligible for tool exposure — the function still registers for SQL use, but tool registration is skipped with a WARN log.
• Name collisions with a previously-registered tool (built-in or tools: entry) are skipped with a WARN log. Rename one, or set as_tool: false on the function.

# SQL-callable only — not surfaced to the LLM.
- name: internal_hash
  from: sql
  signature: { args: [{ name: x, type: int64 }], returns: int64 }
  body: "x * 2654435761"
  as_tool: false

Exposing Tools as SQL UDFs

The inverse of LLM Tool Exposure: set as_sql: true on a tools: entry to register the tool as a SQL scalar UDF. The UDF dispatches to the tool per row, packing each row's arguments into a JSON object that matches the supplied signature.

runtime:
  functions:
    enabled: true

tools:
  - name: geocode
    from: http://geocode.internal/v1/lookup
    as_sql: true
    signature:
      args: [{ name: address, type: utf8 }]
      returns: utf8

SELECT geocode(address) FROM addresses;

Tool fields for SQL exposure

Behaviour

• A signature: block is required when as_sql: true — a tool's free-form JSON Schema does not uniquely map to a SQL signature, so argument and return Arrow types must be pinned explicitly.
• Supported Arrow types are the same primitive set as the Remote tier: int64, float64, utf8, boolean. Other types are rejected at build time with an Unsupported*Type error naming the tool.
• The derived UDF is always volatile. Constant-folding is disabled and per-row results are not memoized because the underlying tool is non-deterministic RPC.
• Per-query dispatch is capped at 16 concurrent in-flight calls, with row order preserved on output.
• Tool-backed UDFs are automatically added to the federation deny-list and are never pushed down to remote data sources.
• If runtime.functions.enabled is false, or signature: is missing, SQL registration is skipped with a WARN log and the tool remains callable as an LLM tool only.

Distributed Execution

In a Spice.ai Enterprise distributed cluster, UDFs declared on the scheduler are automatically propagated to executors as part of the GetAppDefinition bootstrap RPC — executors load the full Spicepod definition (datasets, catalogs, views, and UDFs) from the scheduler at startup, so query planning and execution use a consistent view of the function set across all nodes.

User functions are deny-listed from federation pushdown on every node, so there's no risk of a stray executor attempting to push a user function into a downstream source.

Limitations

Current (ALPHA) scope:

• kind: scalar and kind: table are implemented. Any other kind: value (e.g. aggregate, window) is rejected at parse time as an unknown variant and prevents the spicepod from loading.
• Supported from: schemes are sql, http://, https://, and wasm. The HTTP and WASM tiers are gated behind the http-functions and wasm-functions cargo features respectively (default-on in Enterprise and Cloud, opt-in for OSS builds). Other schemes (e.g. grpc://, flight://) parse but are rejected.
• LLM tool exposure (as_tool: true) is limited to scalar functions with primitive Arrow types in their signature (int64, float64, utf8, boolean). Functions with richer signatures remain callable from SQL.
• Remote tier issues batches in parallel with a default batch_concurrency of 4 (max 64). Retries, circuit-breakers, per-function rate limits, and http-arrow / Flight protocols are not yet implemented.
• No aggregate/window semantics, UDAF pushdown, or execution-engine delegation beyond what DataFusion's scalar UDF and table function paths provide.