Joining multi Flat files - JSON File and Table in ADF Data Flow

Introduction

In this blog post, we are just going to Join and Join but in logical manners to understand ADF Data Flow. Here in ADF Data flow we are using Flatten, Select and Join Transform.

Hope it will be interesting.

 

Case scenario

We have some Flat file which contains Employee Code, Salary and Bonus Information. There are multiple Flat files in our Azure BLOB Container.

Now we have a JSON file which contains information about Employee Code, Employee Name and Employee Email. We also have table object named Employee State. Which contain Employee Code and State name.

 


 

Now we have to join all using ADF data flow. First we used Get Meta Data activity to collect all file information. By using ForEach we are moving one by one file. Within the for Each we have Mapping Data Flow activity which actually call our data low. Rest are done by Data Flow.

 

ADF Solution

 

Control Flow Structure

 


 

Data Flows Structure

 

 

 

Here we are not going to describe related to Control Flow and Parameterized Data sets. You can easily capture it from my previous blog post.

 

From Control Flow, we are going to show only Mapping Data Flow.

 

Mapping Data Flow of Control Flow

 


 

Data Flow

 

Source 1

 


 

Source 2

 


 

Source 3

 


 

Flatten Transform

 


 

JOIN Transform

 


 

 

Sink and Output

 


 

 

 

Hope you like it.


Comments

Post a Comment

Popular Posts

Simultaneously Loading Multiple CSV files to Different Destination Table

Image
Introduction In this blog post, we are trying to load a bunch of CSV file from Azure Blob Storage to Different Destination Table of Azure SQL Server. Here we are not using any one to one mapping. All activities must be done in a single pipeline and single Copy Data Activity. Hope it will be interesting. Case Scenario In our Azure Blob storage, we have different CSV file with different metadata. We need to store them in our Azure SQL Server table. One file is for one table. The table is created automatically, so there is no column mapping over here. Source Azure Blob Files   ADF Solution       First we need two Data Sets. One for retrieving all CSV files information from Blob Storage and Second is for copy the each file contain in destination. With this destination Data sets create table automatically for each CSV file. Data Sets-1 (Used to Retrieve all File From Blob Storage)     Data Sets-2 (Destination Data Sets for SQL Table...
Read more

ADF using Parameterized Stored Procedure

Image
Introduction Here in this article, we are trying to demonstrate the Stored Procedure Activity of ADF and how we pass the parameters in it. Hope it will be interesting. Case Scenario We have a table named [ ProductInfo ]. It contains all the product name.   CREATE TABLE ProductInto (       ProductID    INT ,       ProductName VARCHAR ( 50 ) ) GO INSERT INTO ProductInto VALUES ( 1 , 'Tooth Past' ),( 2 , 'Tooth Brush' ) Now, we have another Table named [ OrderInfo ], contains all the order information. CREATE TABLE OrderInfo (    OrderID               INT ,    ProductID             INT ,    Qty                   INT ,    Rate   ...
Read more

Working with Python -- File Handling

Image
Introduction File Handling is a very important portion for any application. In Python, we have several function to Create, Read, Update and Delete file. In this blog post, we are trying to discuss about Python File handling. Hope it will be interesting. Open Function This is the key function to working with file. The Open function takes two parameters. One is File name and second is mode. "r"   -  Read   - Default value. Opens a file for reading, error if the file does not exist "a"  -  Append  - Opens a file for appending, creates the file if it does not exist "w"  -  Write  - Opens a file for writing, creates the file if it does not exist "x"  -  Create  - Creates the specified file, returns an error if the file exists In addition of that, we can specify that the file can be treated as Binary or as Text file. "t"  -  Text  - Default value. Text mode "b"  -  Binary  - Binary mode (e.g. images) Exa...
Read more