UDFs in Pixeltable - Pixeltable Documentation

This documentation page is also available as an interactive notebook. You can launch the notebook in Kaggle or Colab, or download it for use with an IDE or local Jupyter installation, by clicking one of the above links.

Pixeltable comes with a library of built-in functions and integrations, but sooner or later, you’ll want to introduce some customized logic into your workflow. This is where Pixeltable’s rich UDF (User-Defined Function) capability comes in. Pixeltable UDFs let you write code in Python, then directly insert your custom logic into Pixeltable expressions and computed columns. In this how-to guide, we’ll show how to define UDFs, extend their capabilities, and use them in computed columns. This notebook uses pxt.create_table() so you can run cells without a project. An app puts the same columns on a TableModel in app.py and creates the tables with pxt schema update. To start, we’ll install the necessary dependencies, create a Pixeltable directory and table to experiment with, and add some sample data.

%pip install -qU pixeltable

import pixeltable as pxt

# Create the directory and table
pxt.drop_dir('udf_demo', force=True)  # Ensure a clean slate for the demo
pxt.create_dir('udf_demo')
t = pxt.create_table('udf_demo/strings', {'input': pxt.String})

# Add some sample data
t.insert(
    [
        {'input': 'Hello, world!'},
        {'input': 'You can do a lot with Pixeltable UDFs.'},
    ]
)
t.show()

Connected to Pixeltable database at: postgresql+psycopg://postgres:@/pixeltable?host=/Users/asiegel/.pixeltable/pgdata
Created directory `udf_demo`.
Created table `strings`.
Inserting rows into `strings`: 2 rows [00:00, 763.99 rows/s]
Inserted 2 rows with 0 errors.

What is a UDF?

A Pixeltable UDF is just a Python function that is marked with the @pxt.udf decorator.

@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:

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]

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:

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

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.

t.insert([{'input': 'Pixeltable updates tables incrementally.'}])
t.show()

Computing cells:   0%|                                                    | 0/3 [00:00<?, ? cells/s]
Inserting rows into `strings`: 1 rows [00:00, 255.24 rows/s]
Computing cells: 100%|███████████████████████████████████████████| 3/3 [00:00<00:00, 364.69 cells/s]
Inserted 1 row with 0 errors.

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.

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.

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

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

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

Computing cells:   0%|                                                    | 0/5 [00:00<?, ? cells/s]
Inserting rows into `strings`: 1 rows [00:00, 242.01 rows/s]
Computing cells: 100%|███████████████████████████████████████████| 5/5 [00:00<00:00, 623.99 cells/s]
Inserted 1 row with 0 errors.

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

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:

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.

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.

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:

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:

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.

@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

t.select(sum_of_squares(t.val)).collect()

t.group_by(t.val // 10).order_by(t.val // 10).select(
    t.val // 10, sum_of_squares(t.val)
).collect()

Ship it as an app

A UDF used as a column must be defined in this file (or another module under the project root):

import pixeltable as pxt

TableModel = pxt.model_base()


@pxt.udf
def reverse(s: str) -> str:
    return s[::-1]


class Strings(TableModel, name='strings'):
    input: pxt.String
    reversed = reverse(input)

pxt schema update app.py udf_demo

​What is a UDF?

​Types in UDFs

​Local and module UDFs

​Batching

​UDAs (aggregate UDFs)

​Ship it as an app

What is a UDF?

Types in UDFs

Local and module UDFs

Batching

UDAs (aggregate UDFs)

Ship it as an app