Azure Data Lake – U-SQL – Join CSV and DB Table

Introduction

In my previous article we are going to combined read two CSV file and making grouping and finding the total quantity of Item.

Azure Data Lake – U-SQL – Using Wildcard or Virtual Columns
http://sqlknowledgebank.blogspot.in/2018/02/azure-data-lake-u-sql-using-wildcard-or.html

Another article that I create database, schema and table object.

Azure Data Lake – U-SQL – Creating Database and DB Object
http://sqlknowledgebank.blogspot.in/2018/02/azure-data-lake-u-sql-creating-database.html

Now it is the time to combine them both. Hope it will be interesting.

Scenario

Here we are going to combine a CSV file with a Database Table object. I mean to say that joining a CSV file with Database table object and retrieve some common information from both the CSV file and Table object.

 

U-SQL to Join

 

USE DATABASE MyUSQLDb;   // DATABASE keyword is optional

USE SCHEMA AbcPvtLtd; // SCHEMA keyword is required for schemas

@searchlog =

    EXTRACT ItemCode     int,

            ItemName     string,

            Qty          int,

            filename     string

    FROM "C:/Users/Joydeep/AppData/Local/USQLDataRoot/Input-1/{filename:}.csv"

    USING Extractors.Csv();

@filtering =

    SELECT a.ItemCode, b.ItemName, b.Total, a.ItemRate

    FROM   ItemRate AS a

           INNER JOIN

           (

            SELECT ItemCode,

                   ItemName,

                   SUM(Qty) AS Total

            FROM @searchlog

            GROUP BY ItemCode, ItemName

           ) AS b ON a.ItemCode == b.ItemCode;

OUTPUT @filtering 

    TO "C:/Users/Joydeep/AppData/Local/USQLDataRoot/output/Output-1/Result.csv"

    USING Outputters.Csv();

 

Job Graph:

 

 

 

Type of Join

U-SQL supports a number of join types – some may well be familiar to you, others may not.

ü  INNER JOIN

ü  FULL OUTER JOIN

ü  LEFT OUTER JOIN

ü  RIGHT OUTER JOIN

ü  CROSS JOIN

ü  LEFT SEMIJOIN

ü  RIGHT SEMIJOIN

ü  LEFT ANTISEMIJOIN

ü  RIGHT ANTISEMIJOIN

 

 

Hope you like it.

 

Posted by: MR. JOYDEEP DAS