Materialized views

Starburst Enterprise platform (SEP) supports materialized views with the Hive and Iceberg connectors.

Materialized views increase query performance by providing pre-computed results from complex, analytical queries. With SEP, you can run federated queries, create materialized views, and access catalogs of results through the Hive and Iceberg connectors.

Materialized views created in a Hive catalog may be automatically refreshed, as discussed in this document. For more information about Iceberg automatic refresh, see the Iceberg automatic refresh section.

Prerequisites

Your SEP platform administrator or a data engineer with administrative access to SEP has to first enable materialized views in one or more Hive or Iceberg catalogs. Check with them to learn which catalogs are enabled, and which schemas may be used.

You also must have the necessary access privileges to create data in the schema designated in each enabled catalog.

Create and use a materialized view

Any federated query that runs successfully in SEP can be used to create a materialized view. Materialized views in SEP are created in the same way as in other data platforms, with a CREATE MATERIALIZED VIEW statement. In the following example, the mysalescatalog has been configured to allow materialized views:

CREATE MATERIALIZED VIEW mysalescatalog.mysalesschema.mv_cust_tot_return AS
    SELECT
      sr_customer_sk ctr_customer_sk,
      sr_store_sk ctr_store_sk,
      sum(sr_return_amt) ctr_total_return
    FROM
    tpcds.sf1.store_returns,
    tpcds.sf1.date_dim
    WHERE ( (sr_returned_date_sk = d_date_sk) AND (d_year > 2000) )
    GROUP BY sr_customer_sk, sr_store_sk
;

Once a materialized view exists, you can query it like any regular table:

SELECT * FROM mycatalog.mysalesschema.mv_cust_tot_return;

When querying fresh materialized views within their grace period, access control checks on source tables are skipped by default. However, if the materialized view is configured with run_as_invoker=true and the materialized-views.allow-run-as-invoker property is enabled, queries execute as the invoking user and perform standard access control checks on source tables.

Automatically refresh materialized views in Hive

Hive catalogs in SEP configured to allow materialized views provide several WITH clause properties to configure refresh schedules and how new data is imported:

• refresh_interval and cron: Choose one method to specify a refresh frequency. The defined interval must be greater than or equal to five minutes.

• max_import_duration: Specifies how long to allow a refresh to complete before failing. Defaults to 120m.

• grace_period: Specifies the amount of time in-flight queries can run against an expiring snapshot.

• incremental_column: Specifies the column to be used to identify new data since the last refresh. If you do not use this field, SEP performs a full refresh.

• failed_refresh_limit: Specifies the maximum number of consecutive refresh failures allowed before refresh operations are temporarily disabled. By default, there is no set limit for consecutive refresh failures. The counter resets after a successful refresh, a change to the refresh limit value, or when the refresh is re-enabled at the next scheduled refresh time.

We suggest that you review the Hive connector documentation , which has more information about these properties.

In the following example, the refresh_interval property is used to automatically refresh the data every 24 hours from the time the CREATE statement initially runs:

CREATE MATERIALIZED VIEW myhive.mysalesschema.mv_cust_tot_return
WITH (
  refresh_interval = '24h',
  grace_period = '5m',
  max_import_duration = '30m',
  failed_refresh_limit = '2'
) AS
    SELECT
      sr_customer_sk ctr_customer_sk,
      sr_store_sk ctr_store_sk,
      sum(sr_return_amt) ctr_total_return
    FROM
    tpcds.sf1.store_returns,
    tpcds.sf1.date_dim
    WHERE ( (sr_returned_date_sk = d_date_sk) AND (d_year > 2000) )
    GROUP BY sr_customer_sk, sr_store_sk
;

In this example, the refresh runs for a maximum of 30 minutes. Unless the cron property is specified, the time at which data in a materialized view is refreshed is based on the moment the CREATE MATERIALIZED VIEW statement first runs, plus the refresh_interval. In addition, the refresh has a failed_refresh_limit set to 2. If the refresh fails two times, it is temporarily disabled.

You can run refreshes on a set schedule by using the cron property instead. The cron property uses normal cron expressions. Here is the same materialized view, created with a cron schedule and an incremental column:

CREATE MATERIALIZED VIEW myhive.mysalesschema.mv_cust_tot_return
WITH (
  cron = '30 2 * * *'
  grace_period = '5m',
  max_import_duration = '30m',
  incremental_column = 'sr_returned_date_sk'
) AS
    SELECT
      sr_customer_sk ctr_customer_sk,
      sr_store_sk ctr_store_sk,
      sum(sr_return_amt) ctr_total_return
    FROM
    tpcds.sf1.store_returns,
    tpcds.sf1.date_dim
    WHERE ( (sr_returned_date_sk = d_date_sk) AND (d_year > 2000) )
    GROUP BY sr_customer_sk, sr_store_sk
;

This causes the refresh to execute at 2:30 AM daily, and loads only new data as determined by the sr_returned_date_sk date column.

Enable Iceberg materialized views

Built-in access control (BIAC) must be configured to own table entities in order to enable materialized views in an Iceberg catalog.

See Using BIAC with Iceberg materialized views for more details.

Iceberg incremental column refresh

Iceberg materialized views support a predicate-based incremental refresh using the incremental_column property. On each refresh, only rows whose column value exceeds the current maximum in the storage table are appended, rather than replacing all data.

This is distinct from the Hive incremental_column property, which works with a scheduler. Iceberg incremental refresh is triggered manually with REFRESH MATERIALIZED VIEW and requires enabling iceberg.materialized-view-incremental-column-refresh.enabled=true in the catalog configuration.

Iceberg materialized views can still be refreshed on a schedule independently of incremental_column, using the refresh_schedule property. See Automatic refresh.

See Incremental column refresh in the Iceberg connector documentation for configuration details, supported column types, and query restrictions.

Automatic materialized view substitution

Note

Automatic materialized view substitution is a public preview feature.

Automatic materialized view substitution is a query-time acceleration feature that uses pre-computed materialized views to execute part or all of a query, without requiring the query to reference the materialized view directly. When a query reads from base tables that are also covered by an eligible materialized view, SEP identifies the materialized view and substitutes it into the query plan, avoiding the need to scan the base tables and repeat the computation at query time.

Materialized views in Iceberg and Hive catalogs can be eligible for substitution. The source tables of a materialized view eligible for substitution can use the following connectors:

• BigQuery

• Cassandra

• ClickHouse

• Cosmos DB

• Db2

• Delta Lake

• DuckDB

• DynamoDB

• Elasticsearch

• Exasol

• Generic JDBC

• Google Sheets

• Greenplum

• HBase

• Hive

• Hudi

• Iceberg

• Kafka

• KDB+

• Lakehouse

• MariaDB

• MaxCompute

• MongoDB

• MySQL

• Netezza

• OpenSearch

• Oracle

• Pinot

• PostgreSQL

• Prometheus

• Redis

• Redshift

• SAP HANA

• SingleStore

• Snowflake

• Splunk

• SQL Server

• Stargate

• Synapse

• Teradata, including Teradata Direct

• Thrift

• TPC-DS

• TPC-H

The teradata_direct_legacy connector doesn’t support automatic materialized view substitution.

The materialization is always stored in the same type of catalog as the materialized view, regardless of the source of the data:

• Materialized views in an Iceberg catalog are stored as Iceberg tables.

• Materialized views in a Hive catalog are stored as Hive tables, which the cache service creates and refreshes.

The definition of a materialized view eligible for automatic substitution can only contain:

• Table scans

• Projections of a subset of columns

A materialized view is only considered for substitution while its content is not stale, based on the grace period configured for the view.

Users need SELECT privileges on the materialized view, including the columns the query reads, for it to be substituted into their queries. If a user does not have access, the query reads from the base tables instead.

Requirements

Automatic materialized view substitution requires the following:

• Starburst Control Plane is installed and accessible. Control Plane hosts the materialization metastore used to track materialized views eligible for substitution.

• The Starburst Control Plane and SEP cluster versions match.

Substitution for materialized views in a Hive catalog also requires the following:

• The cache service is deployed.

• The Hive catalog is configured to allow materialized views.

Configuration

Automatic materialized view substitution requires the materialization metastore, which Starburst Control Plane enables by default with the materialization.metastore.enabled property.

The coordinator uses the following properties to control automatic materialized view substitution. Both properties are enabled by default when the coordinator is configured to use Starburst Control Plane:

Automatic materialized view substitution configuration properties

Property name	Description	Default value
materialized-view-substitution.support.enabled	Allows a materialized view to be marked as eligible for substitution with the substitution_enabled property.	true
materialized-view-substitution.enabled	Enables substitution on the query side.	true

Each materialized view must also be enabled individually. A materialized view is only considered for substitution when its substitution_enabled property is set to true. The property is only available when the materialized-view-substitution.support.enabled coordinator property is true.

Set the property when you create the materialized view:

CREATE MATERIALIZED VIEW mycatalog.myschema.mv_orders
WITH (substitution_enabled = true) AS
    SELECT orderkey, custkey, orderstatus, totalprice
    FROM postgresql.public.orders;

To enable or disable substitution for an existing materialized view, use ALTER MATERIALIZED VIEW:

ALTER MATERIALIZED VIEW mycatalog.myschema.mv_orders
SET PROPERTIES substitution_enabled = false;

Note

A materialized view is only substituted after it has been refreshed. If you enable substitution on a materialized view that has never been refreshed, run REFRESH MATERIALIZED VIEW to start using it. If the materialized view is already populated, enabling the property takes effect immediately.

Use SHOW CREATE MATERIALIZED VIEW to check the current value. For materialized views in a Hive catalog, setting substitution_enabled to false removes the property, so it does not appear in the SHOW CREATE MATERIALIZED VIEW output.

Automatic materialized view substitution can be disabled for an individual session using the materialized_view_substitution_enabled session property:

SET SESSION materialized_view_substitution_enabled = false;

Limitations

The following operations are not currently eligible for automatic materialized view substitution:

• Filters

• Arbitrary expressions, including SQL functions

• Aggregations, including window functions

• Joins

• Subqueries

Enabling substitution on a materialized view that includes any of these operations in its definition fails with an error, whether you set substitution_enabled in CREATE MATERIALIZED VIEW or ALTER MATERIALIZED VIEW.