Skip to content

materializedview.md

Latest commit

 

History

History
172 lines (139 loc) · 5.97 KB

materializedview.md

File metadata and controls

172 lines (139 loc) · 5.97 KB
Raw

Materialized Views

Materialized Views in Synapse allow you to create queryable views that store the results of a Synapse SQL statement. These views are useful for combining, filtering, or transforming data from multiple tables into a single, queryable entity.

This tutorial will walk you through the basics of working with Materialized Views using the Synapse Python client.

Tutorial Purpose

In this tutorial, you will:

  1. Log in, get your project, and create tables with data
  2. Create and query a Materialized View
  3. Create and query a Materialized View with a JOIN clause
  4. Create and query a Materialized View with a LEFT JOIN clause
  5. Create and query a Materialized View with a RIGHT JOIN clause
  6. Create and query a Materialized View with a UNION clause

Prerequisites

  • This tutorial assumes that you have a Synapse project.
  • Pandas must also be installed as shown in the installation documentation.

1. Log in, get your project, and create tables with data

Before creating Materialized Views, we need to log in to Synapse, retrieve your project, and create the tables with data that will be used in the views.

You will want to replace "My uniquely named project about Alzheimer's Disease" with the name of your project.

--8<-- "docs/tutorials/python/tutorial_scripts/materializedview.py:setup"

2. Create and query a Materialized View

First, we will create a simple Materialized View that selects all rows from a table and then query it to retrieve the results.

--8<-- "docs/tutorials/python/tutorial_scripts/materializedview.py:basic_view"
The result of querying your Materialized View should look like: ``` Results from the materialized view: sample_id patient_id age diagnosis 0 S1 P1 70 Alzheimer's 1 S2 P2 65 Healthy 2 S3 P3 72 Alzheimer's 3 S4 P4 68 Healthy 4 S5 P5 75 Alzheimer's 5 S6 P6 80 Healthy ```

3. Create and query a Materialized View with a JOIN clause

Next, we will create a Materialized View that combines data from two tables using a JOIN clause and then query it to retrieve the results.

--8<-- "docs/tutorials/python/tutorial_scripts/materializedview.py:join_view"
The result of querying your Materialized View with a JOIN clause should look like: ``` Results from the materialized view with JOIN: sample_id patient_id age diagnosis gene expression_level 0 S1 P1 70 Alzheimer's APOE 2.5 1 S2 P2 65 Healthy APP 1.8 2 S3 P3 72 Alzheimer's PSEN1 3.2 3 S4 P4 68 Healthy MAPT 2.1 4 S5 P5 75 Alzheimer's APP 3.5 ```

4. Create and query a Materialized View with a LEFT JOIN clause

We can also create a Materialized View that includes all rows from one table and matches rows from another table using a LEFT JOIN clause and then query it to retrieve the results.

--8<-- "docs/tutorials/python/tutorial_scripts/materializedview.py:left_join_view"
The result of querying your Materialized View with a LEFT JOIN clause should look like: ``` Results from the materialized view with LEFT JOIN: sample_id patient_id age diagnosis gene expression_level 0 S1 P1 70 Alzheimer's APOE 2.5 1 S2 P2 65 Healthy APP 1.8 2 S3 P3 72 Alzheimer's PSEN1 3.2 3 S4 P4 68 Healthy MAPT 2.1 4 S5 P5 75 Alzheimer's APP 3.5 5 S6 P6 80 Healthy NaN NaN ```

5. Create and query a Materialized View with a RIGHT JOIN clause

Similarly, we can create a Materialized View that includes all rows from one table and matches rows from another table using a RIGHT JOIN clause and then query it to retrieve the results.

--8<-- "docs/tutorials/python/tutorial_scripts/materializedview.py:right_join_view"
The result of querying your Materialized View with a RIGHT JOIN clause should look like: ``` Results from the materialized view with RIGHT JOIN: sample_id patient_id age diagnosis gene expression_level 0 S1 P1 70.0 Alzheimer's APOE 2.5 1 S2 P2 65.0 Healthy APP 1.8 2 S3 P3 72.0 Alzheimer's PSEN1 3.2 3 S4 P4 68.0 Healthy MAPT 2.1 4 S5 P5 75.0 Alzheimer's APP 3.5 5 S7 NaN NaN NaN PSEN2 1.9 ```

6. Create and query a Materialized View with a UNION clause

Finally, we can create a Materialized View that combines rows from two tables using a UNION clause and then query it to retrieve the results.

--8<-- "docs/tutorials/python/tutorial_scripts/materializedview.py:union_view"
The result of querying your Materialized View with a UNION clause should look like: ``` Results from the materialized view with UNION: sample_id 0 S1 1 S2 2 S3 3 S4 4 S5 5 S6 6 S7 ```

Source Code for this Tutorial

Click to show me 
--8<-- "docs/tutorials/python/tutorial_scripts/materializedview.py"

References

  • [MaterializedView][materializedview-reference-sync]
  • [Column][column-reference-sync]
  • [Project][project-reference-sync]
  • [syn.login][synapseclient.Synapse.login]
  • query examples