Lab055.md

Lab 055: Writing a SQL Script to copy data from one data lake zone to another

Business Objective

We want to load the sale-small data for 2018 Q1 only to the gold area of our datalake. From there we can do further analysis on the data or prepare it for loading to a possible EDW. We will load the remaining data later.

Implementation

• We use gold to denote data that is ready to be loaded into our EDW (if we decide we need one).
• in this case our output should be a folder structure that is in the correct format for our fact table. We want to do all of the ETL outside of Synapse SQL POOLS and do as much as possible in SQL SERVERLESS

Why?

• Our fact table will likely look something like this so we need our ETL/ELT script to build something accordingly.

CREATE TABLE [warehouse].[factSales]
  (
    [TransactionId] [uniqueidentifier]  NOT NULL,
    [CustomerId] [int]  NOT NULL,
    [ProductId] [smallint]  NOT NULL,
    [Quantity] [tinyint]  NOT NULL,
    [Price] [decimal](9,2)  NOT NULL,
    [TotalAmount] [decimal](9,2)  NOT NULL,
    [TransactionDateId] [int]  NOT NULL,
    [ProfitAmount] [decimal](9,2)  NOT NULL,
    [Hour] [tinyint]  NOT NULL,
    [Minute] [tinyint]  NOT NULL,
    [StoreId] [smallint]  NOT NULL
  )

HERE

Discussion points:

• query-in-place vs data loading
• physical modeling

Data Exploration

1. Launch Synapse Studio
2. Look at our sale-small data in the lake using serverless

• Now how do I look at just one month

Load data from the lake into Synapse

Assuming the data looks right, I want to load that into Synapse natively. We don't think that using EXTERNAL TABLES will be fast enough for our needs. In the interest of time, let's only load 2018 data.

So, how should we think about the physical modeling for this table. Let's assume it will actually have 3 billion rows. Consider:

• CCI
• hash distributed on CustomerId
• partitioned by TransactionDate

Steps:

• create a new schema called warehouse

Create a new SQL Script in Synapse Workspace

    CREATE TABLE [warehouse].[FactSales]
    (
    [TransactionId] [uniqueidentifier]  NOT NULL,
    [CustomerId] [int]  NOT NULL,
    [ProductId] [smallint]  NOT NULL,
    [Quantity] [tinyint]  NOT NULL,
    [Price] [decimal](9,2)  NOT NULL,
    [TotalAmount] [decimal](9,2)  NOT NULL,
    [TransactionDateId] [int]  NOT NULL,
    [ProfitAmount] [decimal](9,2)  NOT NULL,
    [Hour] [tinyint]  NOT NULL,
    [Minute] [tinyint]  NOT NULL,
    [StoreId] [smallint]  NOT NULL
    )
    WITH
    (
    DISTRIBUTION = HASH ( [CustomerId] ),
    CLUSTERED COLUMNSTORE INDEX,
    PARTITION
    (
        [TransactionDateId] RANGE RIGHT FOR VALUES (
        20180101, 20180201, 20180301, 20180401, 20180501, 20180601, 20180701, 20180801, 20180901, 20181001, 20181101, 20181201,
        20190101, 20190201, 20190301, 20190401, 20190501, 20190601, 20190701, 20190801, 20190901, 20191001, 20191101, 20191201)
    )
    );

There are multiple ways to load the data:

• data flows
• copy data tool
• script

Let's look at a few

We need an Integration Dataset that points to the source data:

• Create a new Dataset by selecting Data from the left menu, expanding the + button on the Data blade and selecting Integration Dataset. We will be creating a dataset that will point to the root folder of the sales data in the data lake.

• In the New integration dataset blade, with the All tab selected, choose the Azure Data Lake Storage Gen2 item. Select Continue.

• In the Select format screen, choose the Parquet item. Select Continue.

  Field	Value
  Name	Enter asamcw_sales_parquet.
  Linked service	asadatalake{SUFFIX}
  File path - Container	Enter wwi-02.
  File path - Folder	Enter sale-small.
  Import schema	From connection/store

Now do the same thing to point to the destination dataset table created above in your synapse pool.

Field	Value
Name	Enter asamcw_sale_asa.
Linked service	SQLPool01
Table name	wwi_mcw.SaleSmall
Import schema	From connection/store

Since we want to filter on multiple sale year folders (Year=2018 and Year=2019) and copy only the 2018 and 2019 sales data, we will need to create a data flow to define the specific data that we wish to retrieve from our source dataset. To create a new data flow, start by selecting Develop from the left menu, and in the Develop blade, expand the + button and select Data flow.

Be sure to turn on data flow debug NOW.

You can do this on your own.

wildcard paths on Source options should be: sale-small/Year=2018/\*/\*/\*/\*

18. Select the Mapping tab and toggle the Auto mapping setting to the off position. You will need to select Input columns for the following:

    Input column	Output column
    Quantity	Quantity
    TransactionDate	TransactionDateId
    Hour	Hour
    Minute	Minute

    

    TODO TODO TODO