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.
%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
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]
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: ...
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:
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:
intfloatstrlist (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
Pixeltable Basics 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.
@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]
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
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()
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()
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 behaviorupdate() - updates the internal state of the aggregator with a new
valuevalue() - 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()
