module03.md

Module 03 - Logical Data Warehouse

< Previous Module - Home - Next Module >

⏱️ Estimated Duration

10 minutes

🤔 Prerequisites

• Lab environment deployed
• Module 1D complete
• Module 2C complete

📢 Introduction

In this module, we will setup a Logical Data Warehouse (LDW), a relational layer, on top of the data files residing in the data lake. This will enable a broad range of business intelligence tools to query data from the Delta Lake tables using the serverless SQL endpoint.

flowchart LR
ds-.->v1
ds-.->v2
ds[(Data Lake\ncurated)]
bi["SQL Tools\n(e.g. Power BI)"]
subgraph db["Database (ldw)"]
subgraph schema["Schema (wwi)"]
v1[View\nwwi.customers]
v2[View\nwwi.orders]
end
end
v1-.->bi
v2-.->bi

Loading

🎯 Objectives

• Create a database.
• Create a schema.
• Create views on top of Delta Lake tables.

Table of Contents

1. Create a Database
2. Create a Schema
3. Create Views
4. Explore your data

1. Create a Database

A serverless SQL database is a logical container where users can create utility objects such as data sources, file formats and schemas, simplifying access to files placed in Azure storage (e.g. CSV, Parquet, Delta) through external tables and views. Note: The master database in serverless SQL pool does not support the creation of:

• External tables
• External data sources
• Database scoped credentials
• External file formats

In this step, we will create a user database with the name ldw and collation Latin1_General_100_BIN2_UTF8.

1. Navigate to the Develop hub

   

2. Click the [+] icon to add a new resource and select SQL script

   

3. Copy and paste the code snippet below and click Run

   CREATE DATABASE ldw COLLATE Latin1_General_100_BIN2_UTF8;

   

4. To the right of the Use database drop-down menu, click the Refresh icon

   

5. Set the Use database property to ldw

   

↥ back to top

2. Create a Schema

A schema can be used to logically group metadata such as tables and views within a user database. In this step, we will create a schema called wwi.

1. Copy and paste the code snippet below and click Run

CREATE SCHEMA wwi;

↥ back to top

3. Create Views

Views are virtual tables which encapsulate and enable reuse of serverless SQL pool queries. Once created, views can be consumed by SQL compatible tools such as Power BI. In this step, we will create views on top of our Delta Lake tables, customers and orders.

1. Copy and paste the code snippet below, replace YOUR_DATA_LAKE_ACCOUNT with the name of your Azure Data Lake Storage Gen2 account, and click Run

   CREATE VIEW wwi.customers
   AS SELECT *
   FROM
       OPENROWSET(
           BULK 'https://YOUR_DATA_LAKE_ACCOUNT.dfs.core.windows.net/03-curated/wwi/customers',
           FORMAT = 'DELTA'
       ) AS [result];

   

2. Copy and paste the code snippet below, replace YOUR_DATA_LAKE_ACCOUNT with the name of your Azure Data Lake Storage Gen2 account, and click Run

   CREATE VIEW wwi.orders
   AS SELECT *
   FROM
       OPENROWSET(
           BULK 'https://YOUR_DATA_LAKE_ACCOUNT.dfs.core.windows.net/03-curated/wwi/orders',
           FORMAT = 'DELTA'
       ) AS [result];

   

↥ back to top

4. Explore your data

The serverless SQL query service enables you to read data stored in the Delta Lake format using the OPENROWSET function. Since we have created virtual tables which wrap our serverless SQL queries utilizing the OPENROWSET function, we can simply refer to the views (e.g. SELECT * FROM wwi.customers).

1. Navigate to the Data hub

   

2. Click the web browser Refresh button to refresh the entire page

   

3. Under the Workspace tab, you should see a new SQL database called ldw

   

4. Expand the ldw database, navigate to Views

   

5. Right-click on wwi.customers and select New SQL script > Select TOP 100 rows

   

6. Click Run

   

7. Replace the existing SQL by copying and pasting the below code snippet, and click Run

SELECT
    orders.CustomerKey,
    customers.CustomerAddress,
    SUM(orders.Quantity) AS Quantity
FROM
    wwi.orders AS orders,
    wwi.customers AS customers
WHERE
    orders.CustomerKey = customers.CustomerSK
GROUP BY
    orders.CustomerKey,
    customers.CustomerAddress
ORDER BY
    orders.CustomerKey ASC

↥ back to top

🎉 Summary

You have successfully created a relational layer on top of Delta Lake tables residing in your Azure Data Lake Storage Gen2 account.

✅ Results

Azure Synapse Analytics

• 1 x Database (ldw)
• 1 x Schema (wwi)
• 2 x Views (wwi.customers, wwi.orders)

Continue >