input
Hello, world!
You can do a lot with Pixeltable UDFs.

input	longest_word
Hello, world!	Hello,
You can do a lot with Pixeltable UDFs.	Pixeltable

input	longest_word
Hello, world!	Hello,
You can do a lot with Pixeltable UDFs.	Pixeltable
Pixeltable updates tables incrementally.	incrementally.

input	longest_word	longest_word_2
Hello, world!	Hello,	Hello
You can do a lot with Pixeltable UDFs.	Pixeltable	Pixeltable
Pixeltable updates tables incrementally.	incrementally.	incrementally

input	longest_word	longest_word_2
Hello, world!	Hello,	Hello
You can do a lot with Pixeltable UDFs.	Pixeltable	Pixeltable
Pixeltable updates tables incrementally.	incrementally.	incrementally
Let's check that it still works.	works.	check

input	longest_word	longest_word_2	longest_word_3
Hello, world!	Hello,	Hello	Hello
You can do a lot with Pixeltable UDFs.	Pixeltable	Pixeltable	Pixeltable
Pixeltable updates tables incrementally.	incrementally.	incrementally	incrementally
Let's check that it still works.	works.	check	Let's

input	longest_word	longest_word_2	longest_word_3	longest_word_3_batched
Hello, world!	Hello,	Hello	Hello	Hello
You can do a lot with Pixeltable UDFs.	Pixeltable	Pixeltable	Pixeltable	Pixeltable
Pixeltable updates tables incrementally.	incrementally.	incrementally	incrementally	incrementally
Let's check that it still works.	works.	check	Let's	Let's

sum
1225

col_0	sum
0	45
1	145
2	245
3	345
4	445

sum_of_squares
40425

col_0	sum_of_squares
0	285
1	2185
2	6085
3	11985
4	19885

## What is a UDF? A Pixeltable UDF is just a Python function that is marked with the `@pxt.udf` decorator. ```python theme={null} @pxt.udf def add_one(n: int) -> int: return n + 1 ``` It’s as simple as that! Without the decorator, `add_one` would be an ordinary Python function that operates on integers. Adding `@pxt.udf` converts it into a Pixeltable function that operates on *columns* of integers. The decorated function can then be used directly to define computed columns; Pixeltable will orchestrate its execution across all the input data. For our first working example, let’s do something slightly more interesting: write a function to extract the longest word from a sentence. (If there are ties for the longest word, we choose the first word among those ties.) In Python, that might look something like this: ```python theme={null} import numpy as np def longest_word(sentence: str, strip_punctuation: bool = False) -> str: words = sentence.split() if ( strip_punctuation ): # Remove non-alphanumeric characters from each word words = [''.join(filter(str.isalnum, word)) for word in words] i = np.argmax([len(word) for word in words]) return words[i] ``` ```python theme={null} longest_word("Let's check that it works.", strip_punctuation=True) ```

  'check'

The `longest_word` Python function isn’t a Pixeltable UDF (yet); it operates on individual strings, not columns of strings. Adding the decorator turns it into a UDF: ```python theme={null} @pxt.udf def longest_word(sentence: str, strip_punctuation: bool = False) -> str: words = sentence.split() if ( strip_punctuation ): # Remove non-alphanumeric characters from each word words = [''.join(filter(str.isalnum, word)) for word in words] i = np.argmax([len(word) for word in words]) return words[i] ``` Now we can use it to create a computed column. Pixeltable orchestrates the computation like it does with any other function, applying the UDF in turn to each existing row of the table, then updating incrementally each time a new row is added. ```python theme={null} t.add_computed_column(longest_word=longest_word(t.input)) t.show() ```

  Computing cells: 100%|███████████████████████████████████████████| 2/2 \[00:00\<00:00, 138.28 cells/s]
  Added 2 column values with 0 errors.

```python theme={null} t.insert([{'input': 'Pixeltable updates tables incrementally.'}]) t.show() ```

  Computing cells:   0%|                                                    | 0/3 \[00:00\



Oops, those trailing punctuation marks are kind of annoying. Let’s add
another column, this time using the handy `strip_punctuation` parameter
from our UDF. (We could alternatively drop the first column before
adding the new one, but for purposes of this tutorial it’s convenient to
see how Pixeltable executes both variants side-by-side.) Note how
*columns* such as `t.input` and *constants* such as `True` can be freely
intermixed as arguments to the UDF.

```python  theme={null}
t.add_computed_column(
    longest_word_2=longest_word(t.input, strip_punctuation=True)
)
t.show()
```

  Computing cells: 100%|███████████████████████████████████████████| 3/3 \[00:00\<00:00, 252.91 cells/s]
  Added 3 column values with 0 errors.




## Types in UDFs

You might have noticed that the `longest_word` UDF has *type hints* in
its signature.

```python  theme={null}
def longest_word(sentence: str, strip_punctuation: bool = False) -> str: ...
```

The `sentence` parameter, `strip_punctuation` parameter, and return
value all have explicit types (`str`, `bool`, and `str` respectively).
In general Python code, type hints are usually optional. But Pixeltable
is a database system: *everything* in Pixeltable must have a type. And
since Pixeltable is also an orchestrator - meaning it sets up workflows
and computed columns *before* executing them - these types need to be
known in advance. That’s the reasoning behind a fundamental principle of
Pixeltable UDFs:

* Type hints are *required*.

You can turn almost any Python function into a Pixeltable UDF, provided
that it has type hints, and provided that Pixeltable supports the types
that it uses. The most familiar types that you’ll use in UDFs are:

* `int`
* `float`
* `str`
* `list` (can optionally be parameterized, e.g., `list[str]`)
* `dict` (can optionally be parameterized, e.g., `dict[str, int]`)
* `PIL.Image.Image`

In addition to these standard Python types, Pixeltable also recognizes
various kinds of arrays, audio and video media, and documents.

## Local and module UDFs

The `longest_word` UDF that we defined above is a *local* UDF: it was
defined directly in our notebook, rather than in a module that we
imported. Many other UDFs, including all of Pixeltable’s built-in
functions, are defined in modules. We encountered a few of these in the
10-Minute Tour tutorial: the `huggingface.detr_for_object_detection` and
`openai.vision` functions. (Although these are built-in functions, they
behave the same way as UDFs, and in fact they’re defined the same way
under the covers.)

There is an important difference between the two. When you add a module
UDF such as `openai.vision` to a table, Pixeltable stores a *reference*
to the corresponding Python function in the module. If you later restart
your Python runtime and reload Pixeltable, then Pixeltable will
re-import the module UDF when it loads the computed column. This means
that any code changes made to the UDF will be picked up at that time,
and the new version of the UDF will be used in any future execution.

Conversely, when you add a local UDF to a table, the *entire code* for
the UDF is serialized and stored in the table. This ensures that if you
restart your notebook kernel (say), or even delete the notebook
entirely, the UDF will continue to function. However, it also means that
if you modify the UDF code, the updated logic will not be reflected in
any existing Pixeltable columns.

To see how this works in practice, let’s modify our `longest_word` UDF
so that if `strip_punctuation` is `True`, then we remove only a single
punctuation mark from the *end* of each word.

```python  theme={null}
@pxt.udf
def longest_word(sentence: str, strip_punctuation: bool = False) -> str:
    words = sentence.split()
    if strip_punctuation:
        words = [
            word if word[-1].isalnum() else word[:-1] for word in words
        ]
    i = np.argmax([len(word) for word in words])
    return words[i]
```

Now we see that Pixeltable continues to use the *old* definition, even
as new rows are added to the table.

```python  theme={null}
t.insert([{'input': "Let's check that it still works."}])
t.show()
```

  Computing cells:   0%|                                                    | 0/5 \[00:00\



But if we add a new *column* that references the `longest_word` UDF,
Pixeltable will use the updated version.

```python  theme={null}
t.add_computed_column(
    longest_word_3=longest_word(t.input, strip_punctuation=True)
)
t.show()
```

  Computing cells: 100%|███████████████████████████████████████████| 4/4 \[00:00\<00:00, 348.89 cells/s]
  Added 4 column values with 0 errors.




The general rule is: changes to module UDFs will affect any future
execution; changes to local UDFs will only affect *new columns* that are
defined using the new version of the UDF.

## Batching

Pixeltable provides several ways to optimize UDFs for better
performance. One of the most common is *batching*, which is particularly
important for UDFs that involve GPU operations.

Ordinary UDFs process one row at a time, meaning the UDF will be invoked
exactly once per row processed. Conversely, a batched UDF processes
several rows at a time; the specific number is user-configurable. As an
example, let’s modify our `longest_word` UDF to take a batched
parameter. Here’s what it looks like:

```python  theme={null}
from pixeltable.func import Batch


@pxt.udf(batch_size=16)
def longest_word(
    sentences: Batch[str], strip_punctuation: bool = False
) -> Batch[str]:
    results = []
    for sentence in sentences:
        words = sentence.split()
        if strip_punctuation:
            words = [
                word if word[-1].isalnum() else word[:-1]
                for word in words
            ]
        i = np.argmax([len(word) for word in words])
        results.append(words[i])
    return results
```

There are several changes:

* The parameter `batch_size=16` has been added to the `@pxt.udf`
  decorator, specifying the batch size;
* The `sentences` parameter has changed from `str` to `Batch[str]`;
* The return type has also changed from `str` to `Batch[str]`; and
* Instead of processing a single sentence, the UDF is processing a
  `Batch` of sentences and returning the result `Batch`.

What exactly is a `Batch[str]`? Functionally, it’s simply a `list[str]`,
and you can use it exactly like a `list[str]` in any Python code. The
only difference is in the type hint; a type hint of `Batch[str]` tells
Pixeltable, “My data consists of individual strings that I want you to
process in batches”. Conversely, a type hint of `list[str]` would mean,
“My data consists of *lists* of strings that I want you to process one
at a time”.

Notice that the `strip_punctuation` parameter is *not* wrapped in a
`Batch` type. This because `strip_punctuation` controls the behavior of
the UDF, rather than being part of the input data. When we use the
batched `longest_word` UDF, the `strip_punctuation` parameter will
always be a constant, not a column.

Let’s put the new, batched UDF to work.

```python  theme={null}
t.add_computed_column(
    longest_word_3_batched=longest_word(t.input, strip_punctuation=True)
)
t.show()
```

  Computing cells: 100%|███████████████████████████████████████████| 4/4 \[00:00\<00:00, 353.90 cells/s]
  Added 4 column values with 0 errors.




As expected, the output of the `longest_word_3_batched` column is
identical to the `longest_word_3` column. Under the covers, though,
Pixeltable is orchestrating execution in batches of 16. That probably
won’t have much performance impact on our toy example, but for GPU-bound
computations such as text or image embeddings, it can make a substantial
difference.

## UDAs (aggregate UDFs)

Ordinary UDFs are always one-to-one on rows: each row of input generates
one UDF output value. Functions that aggregate data, conversely, are
many-to-one, and in Pixeltable they are represented by a related
abstraction, the UDA (User-Defined Aggregate).
Pixeltable has a number of built-in UDAs; if you’ve worked through the
Fundamentals tutorial, you’ll have already encountered a few of them,
such as `sum` and `count`. In this section, we’ll show how to define
your own custom UDAs. For demonstration purposes, let’s start by
creating a table containing all the integers from 0 to 49.

```python  theme={null}
import pixeltable as pxt

t = pxt.create_table('udf_demo/values', {'val': pxt.Int})
t.insert({'val': n} for n in range(50))
```

  Created table \`values\`.
  Inserting rows into \`values\`: 50 rows \[00:00, 9267.95 rows/s]
  Inserted 50 rows with 0 errors.
  UpdateStatus(num\_rows=50, num\_computed\_values=0, num\_excs=0, updated\_cols=\[], cols\_with\_excs=\[])


If we wanted to compute their sum using the built-in `sum` aggregate,
we’d do it like this:

```python  theme={null}
import pixeltable.functions as pxtf

t.select(pxtf.sum(t.val)).collect()
```



Or perhaps we want to group them by `n // 10` (corresponding to the tens
digit of each integer) and sum each group:

```python  theme={null}
t.group_by(t.val // 10).order_by(t.val // 10).select(
    t.val // 10, pxtf.sum(t.val)
).collect()
```



Now let’s define a new aggregate to compute the sum of squares of a set
of numbers. To define an aggregate, we implement a subclass of the
`pxt.Aggregator` Python class and decorate it with the `@pxt.uda`
decorator, similar to what we did for UDFs. The subclass must implement
three methods:

* `__init__()` - initializes the aggregator; can be used to
  parameterize aggregator behavior
* `update()` - updates the internal state of the aggregator with a new
  value
* `value()` - retrieves the current value held by the aggregator

In our example, the class will have a single member `cur_sum`, which
holds a running total of the squares of all the values we’ve seen.

```python  theme={null}
@pxt.uda
class sum_of_squares(pxt.Aggregator):
    def __init__(self):
        # No data yet; initialize `cur_sum` to 0
        self.cur_sum = 0

    def update(self, val: int) -> None:
        # Update the value of `cur_sum` with the new datapoint
        self.cur_sum += val * val

    def value(self) -> int:
        # Retrieve the current value of `cur_sum`
        return self.cur_sum
```

```python  theme={null}
t.select(sum_of_squares(t.val)).collect()
```



```python  theme={null}
t.group_by(t.val // 10).order_by(t.val // 10).select(
    t.val // 10, sum_of_squares(t.val)
).collect()
```




Built with [Mintlify](https://mintlify.com).