Materialized Views

SQL - CREATE MATERIALIZED VIEW

Creates a new materialized view in the schema. A materialized view stores the result of a SQL SELECT query in a backing document type for fast reads. The defining query is executed immediately on creation to perform the initial data load.

Syntax

CREATE MATERIALIZED VIEW [IF NOT EXISTS] <name>
  AS <select-query>
  [REFRESH MANUAL | REFRESH INCREMENTAL | REFRESH EVERY <n> SECOND | MINUTE | HOUR]
  [BUCKETS <n>]

  • <name> Defines the name of the materialized view. It must not conflict with any existing type or materialized view.

  • IF NOT EXISTS Silently skips creation if a view with the same name already exists, instead of raising an error.

  • <select-query> A SQL SELECT statement that defines the data for the view. All source types referenced must exist at creation time.

  • REFRESH MANUAL (default) The view is only refreshed when triggered explicitly.

  • REFRESH INCREMENTAL The view is automatically refreshed after each committed transaction that modifies a source type.

  • REFRESH EVERY <n> SECOND | MINUTE | HOUR The view is refreshed on a schedule at the given interval.

  • BUCKETS <n> Sets the number of buckets for the backing document type.

Examples

  • Create a simple manual-refresh view:

CREATE MATERIALIZED VIEW ActiveUsers
  AS SELECT name, email FROM User WHERE active = true

  • Create a view that updates automatically after each relevant commit:

CREATE MATERIALIZED VIEW RecentOrders
  AS SELECT customer, amount FROM Order WHERE status = 'PENDING'
  REFRESH INCREMENTAL

  • Create a view that refreshes on a schedule:

CREATE MATERIALIZED VIEW HourlySummary
  AS SELECT product, sum(amount) AS total FROM Sale GROUP BY product
  REFRESH EVERY 1 HOUR

  • Skip creation if the view already exists:

CREATE MATERIALIZED VIEW IF NOT EXISTS ActiveUsers
  AS SELECT name FROM User WHERE active = true

  • Create a view with a custom bucket count for large result sets:

CREATE MATERIALIZED VIEW BigView
  AS SELECT * FROM HugeType
  BUCKETS 8

For more information, see:

SQL - ALTER MATERIALIZED VIEW

Changes the refresh mode of an existing materialized view.

ALTER MATERIALIZED VIEW is planned for a future release and is not yet implemented.

Syntax

ALTER MATERIALIZED VIEW <name>
  REFRESH MANUAL | REFRESH INCREMENTAL | REFRESH EVERY <n> SECOND | MINUTE | HOUR

  • <name> The name of the materialized view to alter.

  • REFRESH MANUAL Switches the view to manual refresh mode.

  • REFRESH INCREMENTAL Switches the view to incremental refresh mode (automatic after each relevant commit).

  • REFRESH EVERY <n> SECOND | MINUTE | HOUR Switches the view to periodic refresh mode at the specified interval.

For more information, see:

SQL - DROP MATERIALIZED VIEW

Removes a materialized view and its backing document type, including all stored data.

Syntax

DROP MATERIALIZED VIEW [IF EXISTS] <name>

  • <name> The name of the materialized view to remove.

  • IF EXISTS Suppresses errors when the view does not exist.

Examples

  • Drop a materialized view:

DROP MATERIALIZED VIEW ActiveUsers

  • Drop a view only if it exists:

DROP MATERIALIZED VIEW IF EXISTS NonExistentView
You cannot drop the backing document type of a materialized view directly using DROP TYPE. Drop the materialized view first.

For more information, see:

SQL - REFRESH MATERIALIZED VIEW

Triggers an immediate full refresh of a materialized view’s data. The operation truncates all existing data in the backing type and reloads it by re-executing the defining query. The refresh runs inside its own transaction.

This command is useful for MANUAL refresh mode views and for recovering a STALE view after a failed automatic refresh.

Syntax

REFRESH MATERIALIZED VIEW <name>

  • <name> The name of the materialized view to refresh.

Examples

  • Refresh a view immediately:

REFRESH MATERIALIZED VIEW HourlySummary

  • Refresh after a bulk import to bring a manual view up to date:

REFRESH MATERIALIZED VIEW SalesByProduct

For more information, see: