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