Queries and Expressions

Pixeltable Fundamentals

Section 3: Queries and Expressions

Welcome to Section 3 of the Pixeltable Fundamentals tutorial, Queries and Expressions.

In the previous section of this tutorial, Computed Columns, we saw how to issue queries over Pixeltable tables, such as:

pop_t.select(yoy_change=(pop_t.pop_2023 - pop_t.pop_2022)).collect()

We also saw how to define computed columns that become part of the table and are updated automatically when new rows are inserted:

pop_t.add_column(yoy_change=(pop_t.pop_2023 - pop_t.pop_2022))

Both these examples use the Pixeltable expression pop_t.pop_2023 - pop_t.pop_2022. We've seen a number of other expressions as well, such as the chain of image operations

t.source.convert('RGBA').rotate(10)

and the model invocation

detr_for_object_detection(
    t.source,
    model_id='facebook/detr-resnet-50',
    threshold=0.8
)

Expressions are the basic building blocks of Pixeltable workloads. An expression can be included in a select() statement, which will cause it to be evaluated dynamically, or in an add_column() statement, which will add it to the table schema as a computed column. In this section, we'll dive deeper into the different kinds of Pixeltable expressions and their uses. We'll:

• Understand the relationship between Pixeltable expressions and query execution
• Survey the different types of expressions
• Learn more about the Pixeltable type system

To get started, let's import the necessary libraries for this tutorial and set up a demo directory.

%pip install -qU pixeltable datasets

import pixeltable as pxt

pxt.drop_dir('demo', force=True)
pxt.create_dir('demo')

Connected to Pixeltable database at:
postgresql+psycopg://postgres:@/pixeltable?host=/Users/asiegel/.pixeltable/pgdata
Created directory `demo`.

<pixeltable.catalog.dir.Dir at 0x3274086a0>

In this tutorial we're going to work with a subset of the MNIST dataset, a classic reference database of hand-drawn digits. A copy of the MNIST dataset is hosted on the Hugging Face datasets repository, so wecan use Pixeltable's built-in Hugging Face data importer to load it into a Pixeltable table.

import datasets

# Download the first 50 images of the MNIST dataset
ds = datasets.load_dataset('ylecun/mnist', split='train[:50]')

# Import them into a Pixeltable table
t = pxt.io.import_huggingface_dataset('demo.mnist', ds)

Created table `mnist_tmp_69154325`.
Inserting rows into `mnist_tmp_69154325`: 50 rows [00:00, 15723.14 rows/s]
Inserted 50 rows with 0 errors.

t.head(5)

Column References

The most basic type of expression is a column reference: that's what you get when you type, say, t.image. An expression such as t.image by itself is just a Python object; it doesn't contain any actual data, and no data will be loaded until you use the expression in a select() query or add_column() statement. Here's what we get if we type t.image by itself:

t.image

This is true of all Pixeltable expressions: we can freely create them and manipulate them in various ways, but no actual data will be loaded until we use them in a query.

JSON Collections (Dicts and Lists)

Data is commonly presented in JSON format: for example, API responses and model output often take the shape of JSON dictionaries or lists of dictionaries. Pixeltable has native support for JSON accessors. To demonstrate this, let's add a computed column that runs an image classification model against the images in our dataset.

from pixeltable.functions.huggingface import vit_for_image_classification

t.add_column(classification=vit_for_image_classification(
    t.image,
    model_id='farleyknight-org-username/vit-base-mnist'
))

Computing cells: 100%|██████████████████████████████████████████| 50/50 [00:01<00:00, 41.26 cells/s]
Added 50 column values with 0 errors.

UpdateStatus(num_rows=50, num_computed_values=50, num_excs=0, updated_cols=[], cols_with_excs=[])

t.select(t.image, t.classification).head(3)

We see that the output is returned as a dict containing three lists: the five most likely labels (classes) for the image, the corresponding text labels (in this case, just the string form of the class number), and the scores (confidences) of each prediction. The Pixeltable type of the classification column is pxt.Json:

t

Pixeltable provides a range of operators on Json-typed output that behave just as you'd expect. To look up a key in a dictionary, use the syntax t.classification['labels']:

t.select(t.classification['labels']).head(3)

You can also use a convenient "attribute" syntax for dictionary lookups. This follows the standard JSONPath expression syntax.

t.select(t.classification.labels).head(3)

The "attribute" syntax isn't fully general (it won't work for dictionary keys that are not valid Python identifiers), but it's handy when it works.

t.classification.labels is another Pixeltable expression; you can think of it as saying, "do the 'labels' lookup from every dictionary in the column t.classification, and return the result as a new column." As before, the expression by itself contains no data; it's the query that does the actual work of retrieving data. Here's what we see if we just give the expression by itself, without a query:

t.classification.labels

<pixeltable.exprs.json_path.JsonPath at 0x3c0730220>

Similarly, one can pull out a specific item in a list (for this model, we're probably mostly interested in the first item anyway):

t.select(t.classification.labels[0]).head(3)

Or slice a list in the usual manner:

t.select(t.classification.labels[:2]).head(3)

Pixeltable is resilient against out-of-bounds indices or dictionary keys. If an index or key doesn't exist for a particular row, you'll get a None output for that row.

t.select(t.classification.not_a_key).head(3)

As always, any expression can be used to create a computed column.

# Use label_text to be consistent with t.label, which was given
# to us as a string

t.add_column(pred_label=t.classification.label_text[0])
t

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

Finally, just as it's possible to extract items from lists and dictionaries using Pixeltable expressions, you can also construct new lists and dictionaries: just package them up in the usual way.

custom_dict = {
    # Keys must be strings; values can be any expressions
    'ground_truth': t.label,
    'prediction': t.pred_label,
    'is_correct': t.label == t.pred_label,
    # You can also use constants as values
    'engine': 'pixeltable',
}

t.select(t.image, custom_dict).head(5)

UDF Calls

UDF calls are another common type of expression. We've seen them throughout the tutorial; in fact, we used one a moment ago when we added a model invocation to our workload:

vit_for_image_classification(
    t.image,
    model_id='farleyknight-org-username/vit-base-mnist'
)

This calls the vit_for_image_classification UDF in the pxt.functions.huggingface module. Note that vit_for_image_classification is a Pixeltable UDF, not an ordinary Python function. (UDFs were first discussed in the Tables and Data Operations section of this tutorial.) You can think of a Pixeltable UDF as a function that operates on columns of data, iteratively applying an underlying operation to each row in the column (or columns). In this case, vit_for_image_classification operates on t.image, running the model against every image in the column.

Notice that in addition to the column t.image, this call to vit_for_image_classification also takes a constant argument specifying the model_id. Any UDF call argument may be a constant, and the constant value simply means "use this value for every row being evaluated".

You can always compose Pixeltable expressions to form more complicated ones; here's an example that runs the model against a 90-degree rotation of every image in the sample and extracts the label. Not surprisingly, the model doesn't perform as well on the rotated images.

rot_model_result = vit_for_image_classification(
    t.image.rotate(90),
    model_id='farleyknight-org-username/vit-base-mnist'
)

t.select(t.image, rot_label=rot_model_result.labels[0]).head(5)   

There are a large number of built-in UDFs that ship with Pixeltable; you can always refer back to the API Documentation for details.

Method Calls

Many built-in UDFs allow a convenient alternate syntax. The following two expressions are exactly equivalent:

a = t.image.rotate(90)
b = pxt.functions.image.rotate(t.image, 90)

a and b can always be used interchangeably in queries, with identical results. Just like in standard Python classes, whenever Pixeltable sees the method call t.image.rotate(90), it interprets it as a function call pxt.functions.image.rotate(self, 90), with (in this case) self equal to t.image.

Any method call can also be written as a function call, but (just like in standard Python) not every function call can be written as a method call. For example, the following won't work:

t.image.vit_for_image_classification(
    model_id='farleyknight-org-username/vit-base-mnist'
)

That's because vit_for_image_classification is part of the pxt.functions.huggingface module, not the core module pxt.functions.image. Most Pixeltable types have a corresponding core module of UDFs that can be used as method calls (pxt.functions.image for Image; pxt.functions.string for String; and so on), described fully in the API Documentation.

Arithmetic and Boolean Operations

Expressions can also be combined using standard arithmetic and boolean operators. As with everything else, arithmetic and boolean expressions are operations on columns that (when used in a query) are applied to every row.

t.select(
    t.image,
    t.label,
    t.label == '4',
    t.label < '5',
).head(5)

When you use a where clause in a query, you're giving it a Pixeltable expression, too (a boolean-valued one).

t.where(t.label == '4').select(t.image).show()

The following example shows how boolean expressions can be assigned to variables and used to form more complex expressions.

# Reuse `rot_model_result` from above, extracting
# the dominant label as a new expression

rot_label = rot_model_result.label_text[0]

# Select all the rows where the ground truth label is '5',
# and the "rotated" version of the model got it wrong
# (by returning something other than a '5')

t.where((t.label == '5') & (rot_label != '5')).select(
    t.image, t.label, rot_label=rot_label
).show()

Notice that to form a logical "and", we wrote

(t.label == '5') & (rot_label != '5')

using the operator & rather than and. Likewise, to form a logical "or", we'd use | rather than or:

(t.label == '5') | (rot_label != '5')

For logical negation:

~(t.label == '5')

This follows the convention used by other popular data-manipulation frameworks such as Pandas, and it's necessary because the Python language does not allow the meanings of and, or, and not to be customized. There is one more instance of this to be aware of: to check whether an expression is None, it's necessary to write (say)

t.label == None

rather than t.label is None, for the same reason.

Arrays

In addition to lists and dicts, Pixeltable also has built-in support for numerical arrays. A typical place where arrays show up is as the output of an embedding.

from pixeltable.functions.huggingface import clip_image

t.add_column(clip=clip_image(t.image, model_id='openai/clip-vit-base-patch32'))
t.select(t.image, t.clip).head(5)

Computing cells: 100%|██████████████████████████████████████████| 50/50 [00:01<00:00, 41.41 cells/s]
Added 50 column values with 0 errors.

The underlying Python type of pxt.Array is an ordinary NumPy array (np.ndarray), so that an array-typed column is a column of NumPy arrays (in this example, representing the embedding output of each image in the table). As with lists, arrays can be sliced in all the usual ways.

t.select(t.clip[0], t.clip[5:10], t.clip[-3:]).head(5)

Ad hoc UDFs with apply

We've now seen the most commonly encountered Pixeltable expression types. There are a few other less commonly encountered expressions that are occasionally useful.

You can use apply to map any Python function onto a column of data. You can think of apply as a quick way of constructing an "on-the-fly" UDF for one-off use.

import numpy as np

t.select(t.clip.apply(np.ndarray.dumps, col_type=pxt.String)).head(2)

Note, however, that if the function you're applying doesn't have type hints (as in the example here), you'll need to specify the output column type explicitly.

Column Properties

Some ColumnRef expressions have additional useful properties. A media column (image, video, audio, or document) has the following two properties:

• localpath: the media location on the local filesystem
• fileurl: the original URL where the media resides (could be the same as localpath)

t.select(t.image, t.image.localpath).head(5)

Any computed column will have two additional properties, errortype and errormsg. These properties will usually be None. However, if the computed column was created with on_error='ignore' and an exception was encountered during column execution, then the properties will contain additional information about the exception.

To demonstrate this feature, we're going to deliberately trigger an exception in a computed column. The images in our example table are black and white, meaning they have only one color channel. If we try to extract a channel other than channel number 0, we'll get an exception. Ordinarily when we call add_column, the exception is raised and the add_column operation is aborted.

t.add_column(channel=t.image.getchannel(1))

Computing cells:  32%|█████████████                            | 16/50 [00:00<00:00, 905.19 cells/s]



---------------------------------------------------------------------------

ValueError                                Traceback (most recent call last)

File ~/Dropbox/workspace/pixeltable/pixeltable/pixeltable/exprs/row_builder.py:314, in RowBuilder.eval(self, data_row, ctx, profile, ignore_errors)
    313 start_time = time.perf_counter()
--> 314 expr.eval(data_row, self)
    315 if profile is not None:


File ~/Dropbox/workspace/pixeltable/pixeltable/pixeltable/exprs/function_call.py:429, in FunctionCall.eval(self, data_row, row_builder)
    427 if isinstance(self.fn, func.CallableFunction) and not self.fn.is_batched:
    428     # optimization: avoid additional level of indirection we'd get from calling Function.exec()
--> 429     data_row[self.slot_idx] = self.fn.py_fn(*args, **kwargs)
    430 elif self.is_window_fn_call:


File /opt/miniconda3/envs/pxt/lib/python3.9/site-packages/PIL/Image.py:2555, in Image.getchannel(self, channel)
   2553         raise ValueError(msg) from e
-> 2555 return self._new(self.im.getband(channel))


ValueError: band index out of range


The above exception was the direct cause of the following exception:


Error                                     Traceback (most recent call last)

Cell In[25], line 1
----> 1 t.add_column(channel=t.image.getchannel(1))


File ~/Dropbox/workspace/pixeltable/pixeltable/pixeltable/catalog/table.py:414, in Table.add_column(self, type, stored, print_stats, on_error, **kwargs)
    412 new_col = self._create_columns({col_name: col_schema})[0]
    413 self._verify_column(new_col, set(self._schema.keys()), set(self._query_names))
--> 414 status = self._tbl_version.add_column(new_col, print_stats=print_stats, on_error=on_error)
    415 FileCache.get().emit_eviction_warnings()
    416 return status


File ~/Dropbox/workspace/pixeltable/pixeltable/pixeltable/catalog/table_version.py:503, in TableVersion.add_column(self, col, print_stats, on_error)
    501 self.schema_version = self.version
    502 with Env.get().engine.begin() as conn:
--> 503     status = self._add_columns([col], conn, print_stats=print_stats, on_error=on_error)
    504     _ = self._add_default_index(col, conn)
    505     self._update_md(time.time(), conn, preceding_schema_version=preceding_schema_version)


File ~/Dropbox/workspace/pixeltable/pixeltable/pixeltable/catalog/table_version.py:578, in TableVersion._add_columns(self, cols, conn, print_stats, on_error)
    576     # we need to re-initialize the sqlalchemy schema
    577     self.store_tbl.create_sa_tbl()
--> 578     raise exc
    579 finally:
    580     plan.close()


File ~/Dropbox/workspace/pixeltable/pixeltable/pixeltable/catalog/table_version.py:561, in TableVersion._add_columns(self, cols, conn, print_stats, on_error)
    559 plan.open()
    560 try:
--> 561     num_excs = self.store_tbl.load_column(col, plan, value_expr_slot_idx, conn, on_error)
    562 except sql.exc.DBAPIError as exc:
    563     # Wrap the DBAPIError in an excs.Error to unify processing in the subsequent except block
    564     raise excs.Error(f'SQL error during execution of computed column `{col.name}`:\n{exc}') from exc


File ~/Dropbox/workspace/pixeltable/pixeltable/pixeltable/store.py:265, in StoreBase.load_column(self, col, exec_plan, value_expr_slot_idx, conn, on_error)
    263 value_exc = result_row.get_exc(value_expr_slot_idx)
    264 if on_error == 'abort':
--> 265     raise excs.Error(
    266         f'Error while evaluating computed column `{col.name}`:\n{value_exc}'
    267     ) from value_exc
    268 # we store a NULL value and record the exception/exc type
    269 error_type = type(value_exc).__name__


Error: Error while evaluating computed column `channel`:
band index out of range

But if we use on_error='ignore', the exception will be logged in the column properties instead.

t.add_column(channel=t.image.getchannel(1), on_error='ignore')

Computing cells: 100%|████████████████████████████████████████| 50/50 [00:00<00:00, 1718.16 cells/s]
Added 50 column values with 50 errors.

UpdateStatus(num_rows=50, num_computed_values=50, num_excs=50, updated_cols=[], cols_with_excs=['mnist_tmp_69154325.channel'])

Notice that the update status informs us that there were 50 errors. If we query the table, we see that the column contains only None values, but the errortype and errormsg fields contain details of the error.

t.select(t.image, t.channel, t.channel.errortype, t.channel.errormsg).head(5)

More details on Pixeltable's error handling can be found in the Working with External Files how-to guide.

The Pixeltable Type System

We've seen that every column and every expression in Pixeltable has an associated Pixeltable type. In this section, we'll briefly survey the various Pixeltable types and their uses.

Here are all the supported types and their corresponding Python types:

The Python type is what you'll get back if you query an expression of the given Pixeltable type. For pxt.Json, it can be any of str, int, float, bool, list, or dict.

Several types can be specialized to constrain the allowable data in a column.

• pxt.Image can be specialized with a resolution and/or an image mode:
  • pxt.Image[(300,200)] - images with width 300 and height 200
  • pxt.Image['RGB'] - images with mode 'RGB'; see the PIL Documentation for the full list
  • pxt.Image[(300,200), 'RGB'] - combines the above constraints
• pxt.Array must be specialized with a shape and a dtype:
  • pxt.Array[(64,64,3), pxt.Float] - 3-dimensional arrays with dtype pxt.Float and 64x64x3 shape

If we look at the structure of our table now, we see examples of specialized image and array types.

t

t.clip has type pxt.Array[(512,), pxt.Float], since the output of the embedding is always a 1x512 array. t.channel has type Image['L'], since it's always an 'L' mode (1-channel) image.