Skip to main content
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 to create_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 A SELECT 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:
A query that joins tables works the same way, with no separate syntax for it. Continuing the join and chained Functions from Create a Function, producing jpeg from a separate videos table and captioning it in the same statement:
See Joins for the join itself, and Auto Refresh for what WITH sets here.

Automatic refresh at creation

Set auto_refresh_enabled and auto_refresh_interval_seconds in the WITH clause to schedule refreshes instead of calling refresh yourself:
Omit 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 defining SELECT. 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.
Opening the underlying table gives access to reads and indexes; it does not make independent schema changes part of the materialized-view definition. Changing automatic-refresh settings also does not change that definition.

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.