Enterprise only
A materialized view stores the result of a query as a table. Use it to keep a
filtered or transformed dataset that you can query, index and search
repeatedly, instead of recomputing the query every time you read it.
In SQL this is
CREATE MATERIALIZED VIEW.Create a view
A materialized view is defined by a SQL query, so it is created with one. From Python, pass the defining query tocreate_materialized_view on an
Enterprise connection:
create_materialized_view waits for creation and initial population, then
returns the view handle. Use create_materialized_view_async to submit a job
and wait for it separately. Both methods accept query= on synchronous and
asynchronous connections. query cannot be combined with source, select,
where, or limit; put those clauses in the query itself.
The query defines the view: here, the frames table’s frame_id,
video_id, ts and caption columns, filtered to the frames selected for
captioning. There is no separate, more limited form for defining one —
joins and Functions in the select list work the same way any
other SELECT does, within the query shapes your deployment’s LanceDB
Enterprise version and configuration accept; see the
SQL reference.
Creating the view computes its rows, so it is ready to read immediately.
db.open_materialized_view gets a
handle back for reading, refreshing or inspecting it from Python; the view
itself is also an ordinary table, queryable by name like any other.
Your administrator manages stable row IDs and other source-table
requirements for materialized views on the server; confirm support for your
source table’s shape before creating a view over it.
Selecting a Function’s output
Enterprise ASELECT expression is SQL text, so it can call a registered
Function the same way a computed column’s
expression can. Continuing the
caption Function registered in Create a
Function, against a frames table that already has a jpeg column:
jpeg from
a separate videos table and captioning it in the same statement:
WITH sets here.
Automatic refresh at creation
Setauto_refresh_enabled and auto_refresh_interval_seconds in the WITH
clause to schedule refreshes instead of calling
refresh yourself:
auto_refresh_enabled to let the server decide whether to enable it,
and omit auto_refresh_interval_seconds to let the server decide the
pacing. See Auto Refresh for how scheduling
behaves once the view exists.
Working with the view
view.table is the underlying table, for querying, indexing, or search.
view.definition has a single field, query — the recorded query, in the
canonical spelling the server stores. Use it to inspect the query without
inferring it from the rows. Both are properties; on an async view, the
definition is read with await view.definition().
Creation, refresh and drop also have async, job-returning SDK methods; see
Materialized view.
Add derived values to a view
A materialized view’s output schema belongs to its definingSELECT. Do not
use view.table.add_columns(...) to attach another computed or Function column
to that output. In particular, adding a Function binding is rejected: the
column would not be produced by the next view refresh.
Choose where the calculation belongs:
- Include the expression or Function call in the view’s
SELECT, as in the caption example. To change an existing definition, create a replacement view under a new name, check its results, indexes and grants, and move readers to it before dropping the old view. - Add a Function column to the ordinary source table, refresh it, then select that stored column in a view. This lets you manage column refresh separately from view refresh.
- Create an ordinary table from a query when you need independently editable outputs. That table is a snapshot; it does not inherit the view’s refresh relationship to the source.
Related guides
Refresh a materialized view
Bring the view up to date with its source.
Versioning
Inspect the table history and read a particular version.