Create a materialized view

Applies to: Embedded ❌, Enterprise ✅ 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, run the statement on an Enterprise connection with execute_query; the SQL is the same either way:

db.execute_query("""
    CREATE MATERIALIZED VIEW frames_captioned AS
    SELECT frame_id, video_id, ts, caption
    FROM frames
    WHERE selected = true
""").read_all()
view = db.open_materialized_view("frames_captioned")

CREATE MATERIALIZED VIEW frames_captioned AS
SELECT frame_id, video_id, ts, caption
FROM frames
WHERE selected = true

execute_query returns once the result stream is readable, not once the statement has finished, so .read_all() waits out the full creation before the next line opens it. 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:

db.execute_query("""
    CREATE MATERIALIZED VIEW frames_captioned AS
    SELECT frame_id, jpeg, caption(jpeg) AS caption
    FROM frames
    WHERE selected = true
""").read_all()

CREATE MATERIALIZED VIEW frames_captioned AS
SELECT frame_id, jpeg, caption(jpeg) AS caption
FROM frames
WHERE selected = true

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:

db.execute_query("""
    CREATE MATERIALIZED VIEW frames_captioned
    WITH (auto_refresh_enabled = false, auto_refresh_interval_seconds = 5)
    AS
    SELECT
        f.frame_id,
        extract_frame(v.video, f.ts) AS jpeg,
        caption(extract_frame(v.video, f.ts)) AS caption
    FROM videos v
    JOIN frames f ON v.id = f.video_id
    WHERE f.selected = true
""").read_all()

CREATE MATERIALIZED VIEW frames_captioned
WITH (auto_refresh_enabled = false, auto_refresh_interval_seconds = 5)
AS
SELECT
    f.frame_id,
    extract_frame(v.video, f.ts) AS jpeg,
    caption(extract_frame(v.video, f.ts)) AS caption
FROM videos v
JOIN frames f ON v.id = f.video_id
WHERE f.selected = true

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:

db.execute_query("""
    CREATE MATERIALIZED VIEW frames_captioned
    WITH (auto_refresh_enabled = true, auto_refresh_interval_seconds = 300)
    AS
    SELECT frame_id, video_id, ts, caption
    FROM frames
    WHERE selected = true
""").read_all()

CREATE MATERIALIZED VIEW frames_captioned
WITH (auto_refresh_enabled = true, auto_refresh_interval_seconds = 300)
AS
SELECT frame_id, video_id, ts, caption
FROM frames
WHERE selected = true

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.

Introduction

Getting Started

Concepts

Data Ingestion

Search

Data Curation

Feature Engineering

Training

Management

Maintenance

Security

Deployment

SQL Reference

Create a materialized view

Create a view

Selecting a Function’s output

Automatic refresh at creation

Working with the view

Related guides

Refresh a materialized view

Versioning

​Create a view

​Selecting a Function’s output

​Automatic refresh at creation

​Working with the view

​Related guides

Refresh a materialized view

Versioning

Create a view

Selecting a Function’s output

Automatic refresh at creation

Working with the view

Related guides