How to Create a Materialized View in Redshift?

Many organizations are using Redshift as their data warehouse house and they are using Redshift for reporting or for dashboard queries. Imagine if applications query often must perform complex queries on large tables. For example, consider a reporting SELECT statement that performs multi-table joins and aggregations on tables that contain billions of records. You can address this issue using materialized views in Amazon Redshift. In this article, we will check how to create a materialized view in Amazon Redshift.

What are Materialized Views in Redshift?

A materialized view contains a precomputed result set, based on an complex SQL query over one or more base tables. You can query a materialized view, in the same way that you can query other tables or views in the database. As a materialized view holds precompiled result and the same will be returned without having to get results from complex query. From the user standpoint, the query results are returned much faster compared to when retrieving the same data from the base tables.

Materialized views are very useful for speeding up your queries such as reporting or dashboard queries that are predictable and repeated ver and over again.

Why You Should Use Materialized Views?

A Materialized View is the Physical copy of the complex query that join large tables and performs complex aggregation. Materialized view holds the precompiled result for your complex and repeated query. Materialized views (MVs) generally give an amazing performance boost. Once you create one based on your query, the Amazon Redshift database can get the results directly from the MV instead of executing the query itself. This can make your SQL significantly faster especially when the query processes billions of rows and result of query rarely changes.

What is the Difference Between View and Materialized View?

Following is the basic difference between view and Materialized view:

Normal Views	Materialized Views
Query results are not physically stored.	Query results are physically stored. You can query materialized views instead of querying query over and over.
View executes underlying query every time you use a view in your query	Materialized view holds precomputed results. When used materialized view return result that is stored instead of query execution.
The query execution is slow.	The query execution is significantly faster when you use materialized view.
The Views are virtual only and run the query definition each time they are accessed.	Materialized views are disk based and are updated periodically based upon the query definition.

Redshift Materialized View Syntax

You can creates a materialized view based on one or more Amazon Redshift tables or external tables that you can create using Spectrum.

Following is the Redshift Materialized view syntax:

CREATE MATERIALIZED VIEW mv_name
[ BACKUP { YES | NO } ]
[ table_attributes ]   
[ AUTO REFRESH { YES | NO } ]
AS query

Where,

BACKUP clause specifies whether the materialized view is included in automated. Default values is ‘YES’.
table_attributes allows you to define distkey and sortkey.
query is a valid SELECT statement that defines the materialized view and its content.
AUTO REFRESH defines whether the materialized view should be automatically refreshed. Default value is ‘NO’.

Redshift Materialized Views Examples

Now, let us create sample materialized view in Amazon Redshift.

CREATE MATERIALIZED VIEW rollup_mv AS
	SELECT ss_item_sk, sum(ss_ext_sales_price) AS total1
	FROM ds_tbl_db.store_sales
	GROUP BY ss_item_sk;
	SELECT * FROM ds_tbl_db.store_sales
	UNION ALL
	SELECT null, sum(ss_ext_sales_price) AS total1 
	FROM store_sales;

How to Refresh Redshift Materialized Views?

You can use following command to refresh materialized view manually.

REFRESH MATERIALIZED VIEW rollup_mv

How to Drop Materialized Views in Redshift?

You can use following command to drop materialized views:

DROP MATERIALIZED VIEW  IF EXISTS  rollup_mv

Redshift Materialized Views Limitations

Following are the some of the Redshift Materialized views Limitations:

Materialized view cannot refer standard views, or system tables and views.
You cannot use temporary tables in materialized view.
User-defined functions are not allowed in materialized views.
The ORDER BY, LIMIT, or OFFSET clause are not allowed in materialized views.
Leader node-only functions: CURRENT_SCHEMA, CURRENT_SCHEMAS, HAS_DATABASE_PRIVILEGE, HAS_SCHEMA_PRIVILEGE, HAS_TABLE_PRIVILEGE are not allowed in materialized views.
You can’t use the AUTO REFRESH YES option when the materialized view definition includes mutable functions or external schemas.