EXECUTE Clause 🐍

Use execute to run raw Python over the rows a query already produced, inside the UCR (untrusted code runner) sandbox. Reach for it when the statistic you need has no OQL form — a recurrence that carries state forward, a frequency-domain measure, an all-pairs comparison — rather than as a general escape hatch.

Example use case: a latching fault state, where whether a bucket counts as faulted depends on whether the previous bucket did.

Note what is not on that list. OVER accepts any aggregate, percentiles included, so rolling medians, quantiles and IQR are ordinary OQL (p50(x) OVER w). Verbose is not the same as impossible: reach for execute when no window frame computes the answer at all.

execute is not apply. apply calls a named, deployed honk2science function (APPLY Clause); execute ships a Python body with the query. They are separate clauses — apply takes no Python, and execute takes no function name.

Key Points

Position: last clause in an aggregate query, after settings. Only valid on the top-level query, never inside a CTE. Nothing may follow it — see the trap below.
Body: captured verbatim, newlines and inner parentheses included, up to the final ). Define exactly one top-level function; its name is up to you.
Signature: my_function(df), or my_function(df, params) when the clause carries a parameter group.
In and out: the function receives a pandas.DataFrame of the query’s rows and must return a DataFrame. Its columns become the query’s columns.
Parameters reuse the apply key=value grammar (float, string, array) and arrive as a dict. Numeric literals stay floats — 1200.0 is 1200.0 in Python, not 1200.
No import. numpy as np, pandas as pd, scipy and a safe stdlib subset are pre-imported.

Syntax

aggregate ...
where time from ... to ...
group by ...
[settings ...]
execute [ (key=value, ...) ] ( <python> )

Example 1: A column derived from other columns

aggregate
    avg(metric('<metric-uuid>')) as melt_temp,
    line.id as line_id
where time from now()-1h to now()
group by line.id as line_id, bucket 1m as bucket
execute (
def add_fahrenheit(df):
    df["melt_temp_f"] = df["melt_temp"] * 9.0 / 5.0 + 32.0
    return df
)

Example 2: A recurrence OQL cannot express, with parameters

A Schmitt-trigger latch: the fault trips above one threshold and clears only below a lower one. In the band between them the answer depends on whether we were already latched — which depends on the bucket before that, and back to the last crossing.

No window frame computes this. A frame of any fixed size is a function of the raw values inside it, while the correct answer depends on history of unbounded length: a machine sitting in the dead band for longer than the frame gets the wrong state.

aggregate
    avg(metric('<metric-uuid>')) as vibration,
    line.id as line
where time from now()-6h to now()
group by line.id as line_id, bucket 1m as bucket
execute (trip_above=4.5, clear_below=2.0) (
def latch_fault(df, params):
    trip = params["trip_above"]
    clear = params["clear_below"]

    df = df.sort_values(["line", "bucket"]).reset_index(drop=True)
    latched = np.zeros(len(df), dtype=bool)

    for line in df["line"].unique():
        state = False
        for i in df.index[df["line"] == line]:
            value = df.at[i, "vibration"]
            # Already latched: stay latched until it falls below `clear`.
            # Not latched: trip only above `trip`.
            state = value > clear if state else value > trip
            latched[i] = state

    df["in_fault"] = latched
    return df
)

The trap: nothing may follow the block

ExecutePythonBody matches everything up to a ) that is followed only by whitespace and the end of the query. So a clause placed after the block does not produce a clause-order error — the parser simply never finds the body’s terminator:

-- WRONG: settings after execute
execute ( def flag(df): return df )
settings timezone = 'America/New_York'

[SYNTAX_PARSE_ERROR] parse error near ExecutePythonBody (line 9 symbol 0 - line 0 symbol 0)

That message names the python body, so it reads as a Python fault, and the natural response is to start rewriting the Python — flattening it, removing newlines, stripping indentation. None of that helps, because the Python was never the problem.

-- RIGHT: settings before execute
settings timezone = 'America/New_York'
execute ( def flag(df): return df )

If you see parse error near ExecutePythonBody, look at what comes after the closing parenthesis before you look at your Python.

Multi-line bodies with ordinary indentation are fine, and are the readable way to write this. Nothing about the clause requires a one-liner.

Sandbox behaviour to plan for

These change results silently rather than raising, so they are worth knowing before you write the body:

import is rejected. Use the pre-imported np, pd and scipy.
No timezone database. IANA zone names such as America/Chicago raise UnknownTimeZoneError. Pass the offset in as a number instead.
Timestamps arrive as tz-aware UTC. Arrow carries them that way, while OQL renders them session-local. Re-render if downstream consumers expect local time.
NULL becomes NaN, and coalesce does not survive the round trip. ClickHouse reads a coalesced 0; pandas rolling aggregates skip NaN. Restore the intent explicitly — df["uptime_seconds"] = df["uptime_seconds"].fillna(0.0) — or a gap reads as if the condition held throughout.

The rows arrive as one frame in no guaranteed order. Sort by your grouping key and timestamp before any windowed computation.

Errors

Message	Cause
`EXECUTE clause requires the UCR client, which is not configured`	The environment has no sandbox wired up.
`error preparing python in UCR`	The body failed to compile — a syntax error, or an `import`.
`error executing python in UCR`	The body raised at runtime.
`error converting UCR output to results`	The returned object was not a DataFrame, or held a dtype Arrow cannot carry.
`parse error near ExecutePythonBody`	Almost always a clause after the block, not a Python fault. See the trap above.