本页尚未翻译

本页内容仍为英文原文，源自官方文档 management/materialized-view。中文翻译正在分批进行中。

Materialized view

Create, open, list and drop a table whose contents are defined by a query.

Applies to: Embedded ❌, Enterprise ✅

A materialized view is a table whose contents are defined by a query over another table. The query is recorded in the view's schema, and a refresh brings its stored rows up to date with the source.

The result is an ordinary table: queryable, indexable, searchable, and present in table_names(). Nothing about reading it is special.

This page covers creating and managing one. Keeping it current is Refresh a materialized view.

说明

In SQL, see REFRESH MATERIALIZED VIEW and SHOW MATERIALIZED VIEWS.

Create a materialized view

A materialized view is defined by a SQL query:

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

See Create a materialized view for the full walkthrough, including joins and Functions in the query.

Open, list and drop views

	Python	TypeScript	SQL
create	create_materialized_view	createMaterializedView	CREATE MATERIALIZED VIEW
open	open_materialized_view	openMaterializedView	--
list	list_materialized_views	listMaterializedViews	SHOW MATERIALIZED VIEWS
refresh	refresh	refresh	REFRESH MATERIALIZED VIEW
drop	drop_materialized_view	dropMaterializedView	DROP MATERIALIZED VIEW

Enterprise Creating, refreshing and dropping a materialized view each have an async, job-returning counterpart -- create_materialized_view_async, view.refresh_async(), drop_materialized_view_async -- the same pattern as refresh_column_async: each returns a job you wait() on instead of blocking, with the same arguments as its synchronous counterpart, including auto_refresh_enabled and auto_refresh_interval_seconds; see Auto Refresh.

说明

On a local database, list_materialized_views reads table schemas to find views. On Enterprise, it asks the server for the view listing, as SHOW MATERIALIZED VIEWS does.

A view is dropped with drop_materialized_view, a dedicated operation, even though a materialized view is a table underneath. That also means dropping one removes stored rows, unlike dropping a view, which removes only a saved query.

Read the view and its definition

view.table is the underlying table, for querying, indexing, or search. view.definition has a single field, query -- the defining query, in the canonical spelling the server stores. Both are properties; on an async view, the definition is read with await view.definition().

Enterprise compatibility

Enterprise supports creating and refreshing materialized views on remote compute. Use the SDK version supported by your deployment, or the SQL interface.

Privileges

A materialized view is a table (see Dropping one), granted through the REST ACL API or SQL GRANT/REVOKE with the same privilege names as a table, but two of them mean something different here: the server refuses a direct INSERT, UPDATE or DELETE on a materialized view's rows -- only a refresh writes them. UPDATE is instead what authorizes calling refresh, and OWNERSHIP is what authorizes ALTER (auto-refresh settings):

GRANT SELECT, UPDATE ON MATERIALIZED VIEW daily_rollup TO ROLE analysts

That grants analysts read access and the ability to refresh daily_rollup, not row-level write access. See Privileges for the full statement syntax and every target it reaches.

Related guides

Refresh a materialized viewHow a refresh works, and what it writes.ViewsThe non-materialized counterpart, defined in SQL.