Thursday, 22 February 2018

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


1 comment:

  1. • Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time. Please keep updatingAzure Online Training Bangalore

    ReplyDelete