Pixeltable Fundamentals¶

Section 1: Tables and Data Operations¶

Welcome to Section 1 of the Pixeltable Fundamentals tutorial, Tables and Data Operations. In this section, we'll learn how to:

• Create and manage tables: Understand Pixeltable's table structure, create and modify tables, and work with table schemas
• Manipulate data: Insert, update, and delete data within tables, and retrieve data from tables into Python variables
• Filter and select data: Use where(), select(), and order_by() to query for specific rows and columns
• Import data from CSV files and other file types

First, let's ensure the Pixeltable library is installed in your environment.

Tables¶

All data in Pixeltable is stored in tables. At a high level, a Pixeltable table behaves similarly to an ordinary SQL database table, but with many additional capabilities to support complex AI workflows. We'll introduce those advanced capabilities gradually throughout this tutorial; in this section, the focus is on basic table and data operations.

Tables in Pixeltable are grouped into directories, which are simply user-defined namespaces. The following command creates a new directory, fundamentals, which we'll use to store the tables in our tutorial.

Now let's create our first table. To create a table, we must give it a name and a schema that describes the table structure. Note that prefacing the name with fundamentals causes it to be placed in our newly-created directory.

To insert data into a table, we use the insert() method, passing it a list of Python dicts.

If you're inserting just a single row, you can use an alternate syntax that is sometimes more convenient.

We can peek at the data in our table with the collect() method, which retrieves all the rows in the table.

Pixeltable also provides update() and delete() methods for modifying and removing data from a table; we'll see examples of them shortly.

Filtering and Selecting Data¶

Often you want to select only certain rows and/or certain columns in a table. You can do this with the where() and select() methods.

Note the expressions that appear inside the calls to where() and select(), such as films_t.year. These are column references that point to specific columns within a table. In place of films_t.year, you can also use dictionary syntax and type films_t['year'], which means exactly the same thing but is sometimes more convenient.

In addition to selecting columns directly, you can use column references inside various kinds of expressions. For example, our revenue numbers are given in millions of dollars. Let's say we wanted to select revenue in thousands of dollars instead; we could do that as follows:

Note that since we selected an abstract expression rather than a specific column, Pixeltable gave it the generic name col_1. You can assign it a more informative name with Python keyword syntax:

Tables are Persistent¶

This is a good time to mention a few key differences between Pixeltable tables and other familiar datastructures, such as Python dicts or Pandas dataframes.

First, Pixeltable is persistent. Unlike in-memory Python libraries such as Pandas, Pixeltable is a database. When you reset a notebook kernel or start a new Python session, you'll have access to all the data you've stored previously in Pixeltable. Let's demonstrate this by using the IPython %reset -f command to clear out all our notebook variables, so that films_t is no longer defined.

The films_t variable (along with all other variables in our Python session) has been cleared out - but that's ok, because it wasn't the source of record for our data. The films_t variable is just a reference to the underlying database table. We can recover it with the get_table command, referencing the films table by name.

You can always get a list of all existing tables with the Pixeltable list_tables command.

Tables are Typed¶

The second major difference is that Pixeltable is strongly typed. Because Pixeltable is a database, every column has a data type: that's why we specified StringType, IntType, and FloatType for the three columns when we created the table. These type specifiers are mandatory when creating tables, and they become part of the table schema. You can always see the table schema with the describe() method.

In a notebook, you can also just type films_t to see the schema; its output is identical to films_t.describe().

Besides the column names and types, there's a third element to the schema, Computed With. We'll explain what this means in the next section of the tutorial, Computed Columns.

All of the methods we've discussed so far, such as insert() and get_table(), are documented in the Pixeltable API Documentation. The following pages are particularly relevant to this section of the tutorial:

• pixeltable package reference
• pxt.Table class reference
• API Cheat Sheet

A Real-World Example: Earthquake Data¶

Now let's dive a little deeper into Pixeltable's data operations. To showcase all the features, it'll be helpful to have a real-world dataset, rather than our toy dataset with four movies. The dataset we'll be using consists of Earthquake data drawn from the US Geological Survey: all recorded Earthquakes that occurred within 100 km of Seattle, Washington, between January 1, 2023 and June 30, 2024.

The dataset is in CSV format, and we can load it into Pixeltable with the handy import_csv() function, which creates a new Pixeltable table from the contents of a CSV file.

Let's have a peek at our new dataset. The dataset contains 1823 rows, and we probably don't want to display them all at once. We can limit our query to fewer rows with the limit() method.

A different way of achieving something similar is to use the head() and tail() methods. Pixeltable keeps track of the insertion order of all its data, and head() and tail() will always return the earliest inserted and most recently inserted rows in a table, respectively.

Let's also peek at the schema:

Note that while specifying a schema is mandatory when creating a table, it's not always required when importing data. This is because Pixeltable uses the structure of the imported data to infer the column types, when feasible. You can always override the inferred column types with the schema_overrides parameter of import_csv().

The following examples showcase some common data operations.

Note that Pixeltable uses Pandas-like operators for filtering data: the expression

(eq_t.timestamp >= datetime(2023, 6, 1)) & (eq_t.timestamp < datetime(2023, 10, 1))

means both conditions must be true; similarly (say),

(eq_t.timestamp < datetime(2023, 6, 1)) | (eq_t.timestamp >= datetime(2023, 10, 1))

would mean either condition must be true.

You can also use the special isin operator to select just those values that appear within a particular list:

In addition to basic operators like >= and isin, a Pixeltable where clause can also contain more complex operations. For example, the location column in our dataset is a string that contains a lot of information, but in a relatively unstructured way. Suppose we wanted to see all Earthquakes in the vicinity of Rainier, Washington; one way to do this is with the contains() method:

Pixeltable also supports various aggregators; here's an example showcasing two fairly simple ones, max() and min():

We'll discuss the various Pixeltable functions and expressions more thoroughly in the next tutorial section, Computed Columns and Expressions, and they're exhaustively documented in the Pixeltable API.

Extracting Data from Tables into Python/Pandas¶

Sometimes it's handy to pull out data from a table into a Python object. We've actually already done this; the call to collect() returns an in-memory result set, which we can then dereference in various ways. For example:

collect() without a preceding limit() returns the entire contents of a query or table. Be careful! For very large tables, this could result in out-of-memory errors. In this example, the 1823 rows in the table fit comfortably into a dataframe.

Adding Columns¶

Like other database tables, Pixeltable tables aren't fixed entities: they're meant to evolve over time. Suppose we want to add a new column to hold user-specified comments about particular earthquake events. We can do this with the add_column() method:

Here, note is the column name, and pxt.StringType specifies the type of the new column. nullable=True means that it's an optional field. That nullable=True is mandatory for newly-added columns, because none of the existing rows have a comment (yet) - so it'd be inconsistent with the existing data to make note a required field.

An alternate syntax is sometimes convenient for adding columns:

Let's have a look at the revised schema.

Updating Rows in a Table¶

Table rows can be modified and deleted with the SQL-like update() and delete() commands.

update() can also accept an expression, rather than a constant value. For example, suppose we wanted to shorten the location strings by replacing every occurrence of Washington with WA. One way to do this is with an update() clause, using a Pixeltable expression with the replace() method.

Notice that in all cases, the update() clause takes a Python dictionary, but its values can be either constants such as '[email protected]', or more complex expressions such as eq_t.location.replace('Washington', 'WA'). Also notice that if update() appears without a where() clause, then every row in the table will be updated, as in the preceding example.

Batch Updates¶

The batch_update() method provides an alternative way to update multiple rows with different values. With a batch_update(), the contents of each row are specified by individual dicts, rather than according to a formula. Here's a toy example that shows batch_update() in action.

Deleting Rows¶

To delete rows from a table, use the delete() method.

Don't forget to specify a where() clause when using delete()! If you run delete() without a where() clause, the entire contents of the table will be deleted.

Table Versioning¶

Every table in Pixeltable is versioned: some or all of its modification history is preserved. If you make a mistake, you can always call revert() to undo the previous change to a table. Let's try it out: we'll use it to revert the successive delete() calls that we just executed.

Multimodal Data¶

In addition to the structured data we've been exploring so far in this tutorial, Pixeltable has native support for media types: images, video, audio, and unstructured documents such as pdfs. Media support is one of Pixeltable's core capabilities, and we'll have much more to say about it the upcoming Unstructured Data section of this tutorial. For now, we'll just give one example to show how media data lives side-by-side with structured data in Pixeltable.

Pixeltable will also embed image thumbnails in your notebook when you do a query: