Have you heard about Materialized Views in Amazon Redshift?
Tags: aws, beginners, sql, tutorial
Published: 2020-03-14 17:48:45

---
title: Have you heard about Materialized Views in Amazon Redshift?
published: true
description: Materialized Views in Amazon Redshift now Generally Available
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/t8mx2ucvvvnely3recx4.png
tags: aws, beginners, sql, tutorial
---

<em>Materialized Views</em> helps improve performance of analytical workloads such as <a href="https://aws.amazon.com/blogs/big-data/simplify-management-of-amazon-redshift-clusters-with-the-redshift-console">dashboarding</a>, queries from BI (Business Intelligence) tools, and <a href="https://www.wikiwand.com/en/Extract,_load,_transform">ELT</a> (Extract, Load, Transform) data processing. <em>Materialized Views</em> store the pre-computed results of queries and maintain them by incrementally processing latest changes from base tables. Future queries referencing these <em>Materialized Views</em> can then use the pre-computed results to <em>run</em>🏃🏻‍♀️<b>much</b> faster.

You can create <em>Materialized Views</em> based on one or more source tables by using filters, projections, inner joins, aggregations, grouping, functions, etc. (👉🏽<em>Read more in the docs, <a href="https://docs.aws.amazon.com/redshift/latest/dg/c_SQL_reference.html">here</a></em>.)
___

### What customer problem does <em>Materialized Views</em> solve?🧐
In a data warehouse (<em>system used for reporting and <b>data</b> analysis</em>) environment, applications often perform complex queries on large tables. A common example would be using a SELECT statement to perform multiple-table joins and aggregations (<em>process where data is collected and presented in summarized format</em>) on tables that contain billions of rows. Due to the complexity and large volume of data, processing these queries can be very time-consuming!

Enter <em>Materialized Views</em> in Amazon Redshift.🙌🏽

A <em>Materialized View stores</em> the result of the SELECT statement that defines the Materialized View.  You can then issue a <a href="https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_synopsis.html">SELECT statement</a> to query the <em>Materialized View</em>, in the same way that you query other tables or views in the <b>database</b>. When you query the <em>Materialized View</em>, you’re now querying that pre-computed result, that was based on an SQL query over one or more base tables. The difference is that now Amazon Redshift can process the query based on the pre-computed data stored in the Materialized View, <b>without having to process the base tables at all!</b>😅 This is a win🏆, because now query results are returned much faster compared to when retrieving the same data from the base tables.

___

### How can I create and manage <em>Materialized Views</em>?👩🏻‍💻📊
<mark>First</mark>, let me point you to the docs that detail SQL commands used to create and manage <em>Materialized Views</em>...

- <a href="https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-create-sql-command.html">CREATE</a> MATERIALIZED VIEW
- <a href="https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-refresh-sql-command.html">REFRESH</a> MATERIALIZED VIEW
- <a href="https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-drop-sql-command.html">DROP</a> MATERIALIZED VIEW

<mark>Second</mark>, let’s walk through a basic example on how to CREATE <em>Materialized Views</em> and REFRESH it after data ingestion. For the purpose of this blog post, I'm going to pretend you’ve already <a href="https://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-launch-sample-cluster.html">created</a> and <a href="https://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-connect-to-cluster.html">connected to your cluster</a>. Let’s run some queries on the AWS Management Console with the Redshift **query editor**...

I CREATE a simple table with the following SQL command, making sure I hit the **run** button for each individual query. (To see my results afterwards, I select the *table* **base_table**.)

```sql
CREATE table base_table (a int, b int);
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/z2c3p0442v8kplqp2t6t.png)


Now I want to INSERT more values into our **base_table**.

```sql
INSERT into base_table values (1,2);
```

Time⏳ to CREATE our first *Materialized View*!😅

```sql
CREATE MATERIALIZED VIEW mv_test as (select a from base_table);
```

Ok, let’s INSERT a few more values into our **base_table**...

```sql
INSERT into base_table values (2,3);
```

Let's SELECT all our content from **base_table**.

```sql
SELECT * FROM base_table; 
```

Now let's SELECT all our content from **mv_test**. (This command will retrieve our pre-computed results, which at this point are stale. The **base_table** has changed, but the changes have not yet been reflected.)

```sql
SELECT * FROM mv_test; 
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/t2bgo0r5aetuyeu9mdxa.png)

Let’s get this up to date. (A *Materialized View* stores the query result. When you query the *Materialized View*, you’re querying that pre-computed result.)

```sql
refresh materialized view mv_test;
```

Now let’s SELECT all our content from **mv_test** again...

```sql
SELECT * FROM mv_test; 
```

And now we see it’s *up to date!* And this is what we call *incremental maintenance*.💁🏻‍♀️

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/j19lmly9aw9usswg7xxz.png)
___



### What if I want to test a more complex scenario?👩🏻‍🔬🔬🥼🧪
Want to replicate a more detailed scenario to better appreciate how this new feature comes in handy?

Check out <a href="https://docs.aws.amazon.com/redshift/latest/gsg/getting-started.html">this example from our documentation</a>. It walks you through the process of creating tables, uploading sample data, and querying the database in your Amazon Redshift cluster.



#### And lastly...
This new feature is available at **no additional cost**, in *all regions* where <a href="https://aws.amazon.com/redshift/">Amazon Redshift</a> is available.☁️ 



¡Gracias por tu tiempo!
~Alejandra💁🏻‍♀️ y Canela🐾