Updated March 6, 2023
Introduction on Redshift Materialized View
The following article provides an outline for Redshift materialized views. Performing large and intensive queries like aggregation, Joins on large tables would decrease the performance, to overcome this in Redshift has introduced the materialized view concept in which the application would query the materialized view and get the precomputed result set (which could be repetitive). By this, the performance will also increase.
Redshift materialized view gets the precomputed result set of data without accessing the base tables, which makes the performance faster. If we consider a scenario, we have to get data from the base table and do some analysis on the data and populate it for the user in any dashboard or report format. Here the analysis could be done frequently, instead of analyzing and querying the base table again and again. We can create the materialized view, which could save the analytical data that we are planning to portray in the dashboard. When we create the materialized view, the redshift will run the user-specified query, get the data, and store the result.
Let us see more in the below discussion.
Syntax:
CREATE MATERIALIZED VIEW NAME_MV
[ BACKUP { YES | NO } ]
[ Mention_table_attributes ]
[ Auto refresh { YES | NO } ]
AS query
Backup: Backup is a clause to specify if the materialized view is included in the cluster snapshots. The default value is YES.
Table attributes: To specify how the data is distributed in the materialized view. For distribution style DISTSTYLE { EVEN | ALL | KEY } ]. For distribution key DISTKEY ( identifier ).
Auto-refresh: To specify whether the materialized view should be refreshed automatically with the base table’s latest data. The default value is NO.
How Materialized View work in Redshift?
Now let me show the pictorial representation of the materialized view, and later we go on to the creation, alteration, and auto-refresh.
Below is the Materialized view of the table Theatre and Price.
Materialized View
| Movie_name | Theatre_class | Theatre_ID | Total ( no_of_cust * price ) | Average ( no_of_cust / price ) |
| Iron Man | AC | 123 | 200 | 100 |
| Spider-Man | Normal | 345 | 200 | 100 |
Theatre Table ( Base table )
| Movie_name | Theatre_class | Theatre_ID |
| Iron Man | AC | A_123 |
| Iron Man | AC | A_123 |
| Spider-Man | Normal | N_345 |
| Spider-Man | Normal | N_345 |
Price Table ( Base Table )
| Theatre_ID | No_of_cust | Price |
| A_123 | 100 | 100 |
| N_345 | 200 | 100 |
Examples:
Create a table as below and insert data into it.
Create table Theatre
(
Movie_name varchar(20),
Theatre_class varchar(20),
Theatre_id varchar(15)
);
Screenshot for the same:
Insert data as below:
Insert into theatre values ( 'Iron Man', 'AC', 'A_123' );
Insert into theatre values ( 'Iron Man', 'Normal', 'N_123' );
Insert into theatre values ( 'Spider Man', 'AC', 'A_345' );
Insert into theatre values ( 'Spider Man', 'Normal', 'N_345' );
Insert into theatre values ( 'Frozen', 'AC', 'A_456' );
Insert into theatre values ( 'Frozen', 'Normal', 'N_456' );
Insert into theatre values ( 'Bat Man', 'AC', 'A_777' );
Insert into theatre values ( 'Bat Man', 'Normal', 'N_777' );
Insert into theatre values ( 'X-Man', 'AC', 'A_888' );
Insert into theatre values ( 'X-Man', 'Normal', 'N_888' );
Screenshot for the same:
select * from theatre;
Table creation and inserting of data.
Create table PRICE
(
Theatre_id varchar(15),
No_of_cust INT,
Price INT
);
Screenshot for the same:
Insert into PRICE values ( 'A_123' , 50, 150 );
Insert into PRICE values ( 'N_123' , 60, 100 );
Insert into PRICE values ( 'A_345' , 50, 250 );
Insert into PRICE values ( 'N_345' , 50, 150 );
Insert into PRICE values ( 'A_456' , 40, 200 );
Insert into PRICE values ( 'N_456' , 50, 150 );
Insert into PRICE values ( 'A_777' , 50, 300 );
Insert into PRICE values ( 'N_777' , 50, 200 );
Insert into PRICE values ( 'A_888' , 50, 250 );
Insert into PRICE values ( 'N_888' , 50, 150 );
Screenshot for the same: –
Select * from price;
Creating the Materialized view:
Create materialized view MV_theatre_analysis AS
Select t.movie_name,
T.theatre_class ,
T.Theatre_id,
P.No_of_cust * p.price as Total_Price,
AVG (p.PRICE) as Average_Price
From theatre t , price p
Where t.theatre_id = p.theatre_id
group by 1,2,3,4 ;
Screenshot for the same: –
Select data from the materialized view using the below statement: –
Select * from MV_theatre_analysis;
Output:
| movie_name | theatre_class | theatre_id | total_price | average_price |
| Iron Man | Normal | N_123 | 6000 | 100 |
| Frozen | AC | A_456 | 8000 | 200 |
| Bat Man | Normal | N_777 | 10000 | 200 |
| Spider-Man | Normal | N_345 | 7500 | 150 |
| X-Man | AC | A_888 | 12500 | 250 |
| Spider-Man | AC | A_345 | 12500 | 250 |
| Frozen | Normal | N_456 | 7500 | 150 |
| Iron Man | AC | A_123 | 7500 | 150 |
| Bat Man | AC | A_777 | 15000 | 300 |
| X-Man | Normal | N_888 | 7500 | 150 |
Alter the materialized view:
Alter materialized view MV_theatre_analysis AUTO REFRESH YES ;
Screenshot for the same:
Refresh the materialized view:
REFRESH materialized view MV_theatre_analysis ;
Screenshot for the same:
Recommended Articles
This is a guide to Redshift materialized views. Here we discuss How Materialized View works in Redshift with the pictorial representation of the materialized view. You may also have a look at the following articles to learn more –
