> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lancedb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Create a materialized view

> Store the result of a query as a table, ready to query, index and search.

**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.

> [!NOTE]
>
> In SQL this is [`CREATE MATERIALIZED VIEW`](https://docs.lancedb.com/sql/ddl/materialized-view#create).

## 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](https://docs.lancedb.com/basics/catalog.md#enterprise)
with [`execute_query`](https://docs.lancedb.com/basics/sql#sql-from-python); the SQL is the same
either way:

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
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")
```

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
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](#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](https://docs.lancedb.com/sql/ddl/materialized-view.md#create).

Creating the view computes its rows, so it is ready to read immediately.
[`db.open_materialized_view`](https://docs.lancedb.com/management/materialized-view#manage) 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](https://docs.lancedb.com/curation/create-function.md) the same way a computed column's
expression can. Continuing the
[`caption` Function](https://docs.lancedb.com/curation/create-function#chain) registered in Create a
Function, against a `frames` table that already has a `jpeg` column:

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
db.execute_query("""
    CREATE MATERIALIZED VIEW frames_captioned AS
    SELECT frame_id, jpeg, caption(jpeg) AS caption
    FROM frames
    WHERE selected = true
""").read_all()
```

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
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](https://docs.lancedb.com/curation/create-function.md#chain), producing `jpeg` from
a separate `videos` table and captioning it in the same statement:

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
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()
```

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
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](https://docs.lancedb.com/curation/join.md) for the join itself, and
[Auto Refresh](https://docs.lancedb.com/enterprise/auto-refresh.md) 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`](https://docs.lancedb.com/feature-engineering/refresh-materialized-view) yourself:

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
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()
```

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
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](https://docs.lancedb.com/enterprise/auto-refresh.md) 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](https://docs.lancedb.com/management/materialized-view.md#manage).

## Related guides

> [card] [Refresh a materialized view](https://docs.lancedb.com/feature-engineering/refresh-materialized-view.md)
>
> Bring the view up to date with its source.

> [card] [Versioning](https://docs.lancedb.com/management/table.md#versioning)
>
> Inspect the table history and read a particular version.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.