Materialized views #

Starburst Galaxy supports the creation of materialized views for any object storage catalog. The materialized view always uses the Iceberg table format even for views of data in other table formats. Materialized views increase query performance by providing pre-computed results from complex, analytical queries.

Create a materialized view #

Any federated query that runs successfully in Starburst Galaxy can be used to create a materialized view in an object storage data source. Materialized views in Galaxy are created in the same way as in other data platforms, with a CREATE MATERIALIZED VIEW statement:

CREATE MATERIALIZED VIEW salescatalog.salesschema.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;

See also #

• SHOW CREATE MATERIALIZED VIEW
• REFRESH MATERIALIZED VIEW
• DROP MATERIALIZED VIEW