Skip to content

Lab011.md

Latest commit

 

History

History
261 lines (208 loc) · 7.72 KB

Lab011.md

File metadata and controls

261 lines (208 loc) · 7.72 KB
Raw

Working with Data in a Data Lake - Data Discovery/Sandboxing

Discussion points:

  • query-in-place vs data loading

Data Exploration

  1. Launch Synapse Studio
  2. Look at our sale-small data in the lake using serverless. Let's use some examples:

I want to see all sales for 1/1/2018. First, find the data by drilling down in the workspace.

Note that:

  • we are using our serverless (on-demand) sql pool for data exploration
  • you will need to change the storage account in the queries below
  • note what we are changing in the queries as each requirement changes

Right click and choose SELECT TOP 100. The query should look like this:

SELECT
    TOP 100 *
FROM
    OPENROWSET(
        BULK 'https://asadatalakedavew891.dfs.core.windows.net/wwi-02/sale-small/Year=2018/Quarter=Q1/Month=1/Day=20180101/sale-small-20180101-snappy.parquet',
        FORMAT='PARQUET'
    ) AS [result]

Now modify that to tell me the total number of orders for that day:

SELECT
    count_big(*)
FROM
    OPENROWSET(
        BULK 'https://asadatalakedavew891.dfs.core.windows.net/wwi-02/sale-small/Year=2018/Quarter=Q1/Month=1/Day=20180101/sale-small-20180101-snappy.parquet',
        FORMAT='PARQUET'
    ) AS [result]

Now I want to see the total count of sales for all of 2018:

SELECT
    count_big(*)
FROM
    OPENROWSET(
        BULK 'https://asadatalakedavew891.dfs.core.windows.net/wwi-02/sale-small/Year=2018/*/*/*/*',
        FORMAT='PARQUET'
    ) AS [result]

Find the SUM and AVG Profit by day for each day in 2018 Q4

SELECT
    TransactionDate,
    CAST(SUM(ProfitAmount) AS decimal(18,2)) AS [(sum) Profit],
    CAST(AVG(ProfitAmount) AS decimal(18,2)) AS [(avg) Profit]
FROM
    OPENROWSET(
        BULK 'https://asadatalakedavew891.dfs.core.windows.net/wwi-02/sale-small/Year=2018/Quarter=Q4/*/*/*',
        FORMAT='PARQUET'
    ) AS [result]
GROUP BY TransactionDate
ORDER BY TransactionDate

Use Views for Your Sandbox

Querying this way works well for the savvy data analyst that understands data lakes. But this may not be what you want everyone to do.

Consider building a series of views when you have a SQL abstraction that works for you.

Take the previous query and wrap a VIEW around it like this:

CREATE VIEW sales2018Q4 AS 
SELECT
    TransactionDate,
    CAST(SUM(ProfitAmount) AS decimal(18,2)) AS [(sum) Profit],
    CAST(AVG(ProfitAmount) AS decimal(18,2)) AS [(avg) Profit]
FROM
    OPENROWSET(
        BULK 'https://asadatalakedavew891.dfs.core.windows.net/wwi-02/sale-small/Year=2018/Quarter=Q4/*/*/*',
        FORMAT='PARQUET'
    ) AS [result]
GROUP BY TransactionDate
--ORDER BY TransactionDate

Now execute the query using

select * from sales2018 order by transactiondate;

Note: consider providing SQL View abstractions over your data lake for users that may not be savvy enough to deal with complex SQL or data lake paradigms. Also consider leveraging, smartly, schemas to create your views. Each schema can map to an area of your datalake such as bronze or curated. By having a good naming convention you cut down on the confusion.

A Data Query Alternative Using Metadata

We looked at wildcarding above to look at date ranges (or any range that your data is partitioned over). Here's a different approach that may be easier, using metadata:

--this is my starter query, but note I changed the wildcard paths to be just for 2018
--note that I also made this into a VIEW because most users won't want to deal with the filepath syntax
--note the other items inline
USE sandbox;
GO
DROP VIEW IF EXISTS sales2018count;
GO

CREATE VIEW sales2018count
AS
SELECT
    * ,
    --metadata cols
    result.filename() as [source_filename],
    result.filepath() as [source_filepath],
    --these work positionally
    result.filepath(1) AS [Quarter],
    result.filepath(2) AS [Month],
    result.filepath(3) AS [Day]
FROM
    OPENROWSET(
        --for reference, this is the original
        --BULK 'https://asadatalakedavew891.dfs.core.windows.net/wwi-02/sale-small/Year=2018/Quarter=Q4/Month=12/Day=20181203/sale-small-20181203-snappy.parquet',
        BULK 'https://asadatalakedavew891.dfs.core.windows.net/wwi-02/sale-small/Year=2018/Quarter=*/Month=*/Day=*/*',
        FORMAT='PARQUET'
    ) AS [result]
GO

--get a quick look at what the above query is actually doing
select top 10 * from sales2018count;
go

--now show me the count for 2018
--327542724
select count(*) 
from sales2018count;
GO

--example metadata queries

--show me data only from a particular file
--1421319
select count(*) 
from sales2018count 
where source_filename = 'sale-small-20181203-snappy.parquet'

--examples of filepath fun
select count(*) from  sales2018count where Quarter = 'Q2'
select count(*) from sales2018count where Month = 12;
select count(*) from sales2018count where Day = '20181201';

CSV files

Let's take a look at a CSV file. Do a SELECT TOP 100 against wwi-02/data-generators/generator-product/generator-product.csv. The code should look like this:

-- This is auto-generated code
SELECT
    TOP 100 *
FROM
    OPENROWSET(
        BULK 'https://asadatalakedavew891.dfs.core.windows.net/wwi-02/data-generators/generator-product/generator-product.csv',
        FORMAT = 'CSV',
        PARSER_VERSION='2.0'
    ) AS [result]

Notice the column headers aren't right. We can fix that like this:

-- This is auto-generated code
SELECT
    TOP 100 *
FROM
    OPENROWSET(
        BULK 'https://asadatalakedavew891.dfs.core.windows.net/wwi-02/data-generators/generator-product/generator-product.csv',
        FORMAT = 'CSV',
        PARSER_VERSION='2.0',
        FIRSTROW = 1)
        WITH (
            ProductID INT,
            Seasonality INT,
            Price DECIMAL(10,2),
            Profit DECIMAL(10,2)
        ) as csv

Much better.

Note that we can also chart this data.

JSON data

Do a SELECT TOP 100 against product-json/json-data/product-1.json. The code should look like this:

-- This is auto-generated code
SELECT TOP 100
    jsonContent
/* --> place the keys that you see in JSON documents in the WITH clause:
       , JSON_VALUE (jsonContent, '$.key1') AS header1
       , JSON_VALUE (jsonContent, '$.key2') AS header2
*/
FROM
    OPENROWSET(
        BULK 'https://asadatalakedavew891.dfs.core.windows.net/wwi-02/product-json/json-data/product-1.json',
        FORMAT = 'CSV',
        FIELDQUOTE = '0x0b',
        FIELDTERMINATOR ='0x0b',
        ROWTERMINATOR = '0x0b'
    )
    WITH (
        jsonContent varchar(MAX)
    ) AS [result]

Yuk. Horrible.

Rather than explain how to fix it, here's the pattern you can use and here's what we changed:

  • we want to query all json files, not just that one. Use a wildcard.
  • add the CROSS APPLY statement to declare the schema
  • use an alias and subsequently change the SELECT projection
  • note that you'll get 5 rows...one for each file
SELECT products.*
FROM
    OPENROWSET(
        BULK 'https://asadatalakedavew891.dfs.core.windows.net/wwi-02/product-json/json-data/*.json',
        FORMAT = 'CSV',
        FIELDQUOTE = '0x0b',
        FIELDTERMINATOR ='0x0b',
        ROWTERMINATOR = '0x0b'
    )
    WITH (
        jsonContent varchar(MAX)
    ) AS [result]
CROSS APPLY OPENJSON(jsonContent)
WITH (
    ProductId INT,
    Seasonality INT,
    Price DECIMAL(10,2),
    Profit DECIMAL(10,2)
) AS products