Friday, 23 February 2018

Azure Data Lake – U-SQL – Lab Session -1

Introduction
Here in this article, we are moving with a simple lab session which include all the article that we published before. By this article we get a good concept to work with U-SQL.

Case study
We have four CSV files and one U-SQL Table object named Sales Order

o   Customer.csv
o   ItemGroupMaster.csv
o   ItemMaster.csv
o   ItemStockMaster.csv

Meta data information are given bellow

Sales Order Table
Column Name
Data Type
SrlNo
Int
OrderNumber
String
CustID
String
ItemCode
String
SaleQty
int

ItemMaster.csv
Column Name
Data Type
ItemCode
Int
ItemName
String
GroupCode
Int

ItemGroupMaster.csv
Column Name
Data Type
ItemGroupCode
Int
ItemGroupName
String

Customer.csv
Column Name
Data Type
CustomerCode
String
CustomerName
String

ItemStockMaster.csv
Column Name
Data Type
ItemCode
Int
StockQty
Int

The output we needed by combining all those things are:

Column Name
ItemCode
ItemName
ItemGroupName
StockQty
OrderNumber
CustomerID
CustomerName
SaleQty
RemainingStockQty

U-SQL Statement

USE DATABASE MyUSQLDb
USE SCHEMA AbcPvtLtd;

@itemmaster=
    EXTRACT ItemCode int,
            ItemName string,
            GroupCode   int
    FROM "D:/USQL-Project/Project-1/Input/ItemMaster.csv"
    USING Extractors.Csv();

@joining =
  
    SELECT a.ItemCode,
           a.ItemName,
           b.ItemGroupName,
           c.StockQty,
           d.OrderNumber,
           d.CustID,
           e.CustomerName,
           d.SaleQty,
           (c.StockQty - d.SaleQty) AS stockQty
    FROM
    (
        SELECT ItemCode,
               ItemName,
               GroupCode
        FROM @itemmaster
    ) AS a
    INNER JOIN
    (
    SELECT ItemGroupCode,
           ItemGroupName
    FROM ItemGroupCode
          ) AS b
    ON a.GroupCode == b.ItemGroupCode
    INNER JOIN
    (
    SELECT ItemCode,
           StockQty
    FROM ItemStock
          ) AS c
    ON a.ItemCode == c.ItemCode
    LEFT JOIN
    (
    SELECT OrderNumber,
           CustID,
           ItemCode,
           SUM(SaleQty) AS SaleQty
    FROM salesOrder
    GROUP BY OrderNumber,
             CustID,
             ItemCode
         ) AS d
    ON a.ItemCode == d.ItemCode
    LEFT JOIN
    (
    EXTRACT CustomerId string,
            CustomerName string
    FROM "D:/USQL-Project/Project-1/Input/Customer.csv"
    USING Extractors.Csv()
         ) AS e
    ON d.CustID == e.CustomerId;    


OUTPUT @joining
    TO "D:/USQL-Project/Project-1/Output/Result.csv"
    USING Outputters.Csv();


Hope you like it.





Posted by: MR. JOYDEEP DAS

1 comment:

  1. Good example to practice hands on. Thanks Joydeep Da for this post.

    ReplyDelete