How widgets query DataFrames

A table widget can show a DataFrame of millions of rows, scroll through it, filter and sort it, although the browser never receives the whole table. This page explains how, starting from the questions you meet when a table grows or when your data lives somewhere Pret does not know

• What does a widget ask Python for, and where does the filtering run?
• Can a small table be sent to the browser once, instead of page by page?
• Why does a Spark or Ibis table show only 500 rows, and how to page through all of them?
• How to connect another kind of source, such as an API or a database Pret does not support?

Start with Work with DataFrames if you have not added a DataFrame to a Store yet.

A widget asks for pages

Every DataFrame has a provider, the Python object that runs queries on its rows. A widget never reads the rows directly. It sends a query describing the rows it needs, and the provider returns them as an Arrow table. You can send the same query yourself

from pret import DataFrame, create_store

store = create_store(
    {
        "todos": DataFrame(
            [
                {"todo_id": 1, "text": "Buy bread", "done": True},
                {"todo_id": 2, "text": "Call the plumber", "done": False},
                {"todo_id": 3, "text": "Water the plants", "done": False},
                {"todo_id": 4, "text": "Book a table", "done": False},
            ]
        )
    }
)
result = store["todos"].provider.query(
    {
        "filter": {"kind": "predicate", "column": "done", "operator": "eq", "value": False},
        "sort": [{"column": "text", "direction": "asc"}],
        "offset": 0,
        "limit": 2,
    }
)
print("Matching rows", result["total_count"])
result["table"].to_pandas()

Matching rows 3

The query asks for the first two unfinished todos, sorted by text. The reply holds those two rows, and total_count, the number of rows matching the filter, which the widget uses to size its scrollbar. Each row also has __pret_row_id, the ID a widget sends back when the row is edited.

The filter and the sort run where the rows live, before the page is cut

While the table scrolls, the browser requests pages of 256 rows aligned on multiples of 256, so that two overlapping scroll positions share the same request. It keeps at most 2048 rows in its cache. It already knows the count after the first query, so later pages pass include_count: false, which lets Spark and Ibis skip counting the rows again. When a source rejects a page as too large, the browser halves the page size until it fits, or reports the error if a single row is too large.

Send a small table to the browser once

Each page is a round trip to Python. For a small saved Store, load_store can instead send the Store tables to the browser once

from pret import load_store

store = load_store("todos.pret", dataframe_sync="full", dataframe_max_bytes=10_000_000)

The browser loads the tables into its own SQLite database, so filtering, sorting and scrolling no longer wait for Python. Edits still go to Python, which checks and saves them, and the browser copy receives the saved rows. dataframe_max_bytes is required, and loading fails rather than sending more than that many bytes of Arrow data. This is not an offline mode, a page still needs its notebook kernel or server to save edits.

The three ways rows reach the browser

A Store without synchronization suits small examples and static pages, such as this documentation. Use a connected Store for large tables or for Spark and Ibis queries.

Spark and Ibis queries show a preview

DataFrame(query) does not page through a Spark or Ibis source. It runs the query once, with a limit of 500 rows and no ordering, and widgets work on these loaded rows, the preview. Filters and sorts typed in a widget apply to the preview in the browser. To run the query again on the source with these filters, Metanno tables offer a query button, see source_requery=True in DataFrames. The widget shows the number of rows in the preview, not a total.

Counting and ordering a large Spark query can take as long as running it, a preview answers "what does this data look like" without paying that cost. When you need every row, page through the source instead

In both cases, __pret_row_id is the position of a row in the current result, not a lasting ID. This is why these DataFrames are read-only. To edit or annotate rows, make an editable copy first.

This paged example needs pip install 'ibis-framework[duckdb]'

import ibis

from pret.dataframe_external import IbisDataFrameProvider

query = ibis.memtable({"todo_id": [1, 2, 3], "text": ["Buy bread", None, "Book a table"]})
paged = DataFrame(provider=IbisDataFrameProvider(query, paging=True))
result = paged.provider.query({"sort": [{"column": "text", "direction": "asc"}], "offset": 0, "limit": 2})
print("Matching rows", result["total_count"])
result["table"].to_pandas()

Matching rows 3

Pages are sorted by the requested columns, then by every other scalar column, so that the same row does not appear on two pages. Ascending sorts put nulls first, descending sorts put them last. To keep a preview but change its size, pass IbisDataFrameProvider(query, row_limit=2000) instead.

A raw Spark DataFrame needs pip install 'ibis-framework[pyspark]'. Pret registers it as a temporary Spark view, dropped when the DataFrame is released, and leaves the Spark session open with its settings. Rows are transferred as Arrow data, which keeps nullable integers and timestamp timezones.

Show a DataFrame in your own component

Metanno's table widget is a Pret component, and your components can read DataFrames the same way with use_dataframe_view(dataframe, filter, primary_key, sort, load), with arguments passed by position. The hook returns

• rowSource, with rowCount and getRows(start, end, signal), which fetches the rows of a range as described above
• previousRowSource, the previous row source while a new filter or sort is loading, to keep showing rows
• description, the column names and types, and loading and error messages to display

The row source works the same for the three Store modes above. The hook releases its query when the component unmounts.

All widgets showing one DataFrame in a page start from the same rows, its scope. For a Store table, the scope is a filter and the number of rows matching it. For a Spark or Ibis preview, it also holds the loaded rows. Each widget then adds its own filter and sort. When no scope is set, the hook loads the whole table.

Related tables need another start. A table of entities that follows a table of notes must show the entities of the listed notes, and change with them when the notes query changes. Pass load as False, and set the scopes from an effect of your component with two methods of the DataFrame

async def load_notes_and_entities():
    notes_scope = await notes.fetchScope(None)
    entities_scope = await entities.fetchScope(
        {
            "kind": "relation",
            "column": "note_id",
            "resource_id": notes.resource_id,
            "parent_column": "note_id",
            "filter": notes_scope.filter,
        }
    )
    notes.setScope(notes_scope)
    entities.setScope(entities_scope)

fetchScope(filter) queries a scope without showing it. The relation filter keeps the entities whose note_id appears in the notes matching the notes filter. setScope(scope) shows a scope in every widget of that DataFrame. Fetching both scopes before setting them keeps the two tables consistent, the entities table never shows the entities of notes that are no longer listed. Metanno's DataWidgetFactory loads its related tables this way.

Connect another source

Pret already queries the databases supported by Ibis, through DataFrame(ibis_table). For another kind of source, such as a search engine or an HTTP API, write a provider. Subclass pret.DataFrameProvider and implement two methods

• describe returns the columns, their Arrow types and the operations the source supports, for widgets to configure their fields
• query receives the query fields above and returns the requested page as an Arrow table, with the total_count of matching rows, or None when include_count is false

Both methods may be synchronous or asynchronous. The provider should run the filter, the sort and the paging on its source, so that only the requested page reaches Python. Widgets also send a few more query fields. They read the row IDs of a range with "columns": ["__pret_row_id"], fetch selected rows with an in filter on these IDs, and send locate to scroll to a selected row. Query fields and return types are described in DataFrameProvider, and Pret's Ibis provider, dataframe_external.py, is a complete example.

Two optional methods add more. materialize collects every row, it lets save_store turn the source into an editable copy. apply_edit applies row edits, with "edit": True in the capabilities returned by describe. Without them, saving the Store and editing the rows raise an error.