Using Lookup in ADF Data Flow

Introduction

SSIS developer, we all know the importance of Lookup Transform. Here in ADF, we have two types of Lookup. One is for control flow and other is for data flow. The behavior of both the lookup is not same. Control flow lookup provide only record sets but in Data flow we are using it as for looking up some records from other data sets.

In ADF data flow Lookup feature is more than the SSIS Lookup Transform. So, in this article we are thinking to demonstrate all the functionality of ADF Data Flow lookup with a simple example.

 

Hope it will be interesting.

 

Case scenario

We have a 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.

Now we have to use the Lookup Transform to find the Employee Name and Employee Email from our JSON file.

 

About Data Flow Lookup

Before moving to our actual data flow solution, we must need to understand every property of Data Flow Lookup.

 


 

Primary stream 

The incoming stream of data. This stream is equivalent to the left side of a join.

Lookup stream

The data that is appended to the primary stream. Which data is added is determined by the lookup conditions. This stream is equivalent to the right side of a join.

 

Match multiple rows 

If enabled, a row with multiple matches in the primary stream will return multiple rows. Otherwise, only a single row will be returned based upon the 'Match on' condition.

 

Match on 

Only visible if 'Match multiple rows' is not selected. Choose whether to match on any row, the first match, or the last match. Any row is recommended as it executes the fastest. If first row or last row is selected, you'll be required to specify sort conditions.

 

Lookup conditions: 

Choose which columns to match on. If the equality condition is met, then the rows will be considered a match. Hover and select 'Computed column' to extract a value using the data flow expression language.

 

Non-equi joins

To use a conditional operator such as not equals (!=) or greater than (>) in your lookup conditions, change the operator dropdown between the two columns. Non-equi joins require at least one of the two streams to be broadcasted using Fixed broadcasting in the Optimize tab.

 

 

 

ADF Solution Data Flow

Here we are not using any Sync as we are going to display output in Data Preview mode.

 


 

Source – 1

 


 

Source – 2

 


 

 

Flatten Transform

 


 

Lookup

 


 

Select with Data Preview

 


 


 

 

Hope you like it.

 


Comments

  1. SQL Knowledge Bank - Azure13 June 2020 at 09:41

    If you have any confusion between == and === operators, here I give you a simple explanation. == check only values and === checks values snd type. Take a simple example:
    If(12 == '12') :
    pint( 'Yes')
    else:
    print('No')
    ---> Yes

    If(12 === '12') :
    pint( 'Yes')
    else:
    print('No')
    ---> No

    Hope you understand that.

    ReplyDelete

Post a Comment

Popular Posts

Triggering Pipeline in ADF

Image
Introduction Pipeline is ready and we already publish it, now we need to trigger our pipeline in a schedule interval. For SSIS Developer, we have SQL Server Agent job to schedule and run the SSIS Project or Package.   Scheduling Jobs in SQL agent have some limitation. We have to logically check the SSIS Package execution is completed or not before proceeding to next run of same or dependent project. It also has some flexibility by using Steps. Suppose we need to run a SQL Script on Step-1, then SSIS package on Step-2. We are trying to say that dependent activity can run step by step. In Azure ADF triggering, we have some options that we not find in SQL Agent Job, so both have some advantage and Limitation also. In this blog post we are going to discuss ADF Add Trigger options. Hope it will be interesting.   Where we get the Add Trigger     When we open the Pipeline we can get the Add Trigger button beside the Debug button. After clicking this butt...
Read more

Working with Spark – Spark RDD

Image
Introduction In my previous blog post, we are discussing about Hadoop Map Reduce. Now in this blog post we are going to discuss about Spark Fundamentals concept like RDD. RDD stands for  Resilient Distributed  Dateset , these are the elements that run and operate on multiple nodes to do parallel processing on a cluster.  Hope it will be interesting. What is RDD According to Apache Spark documentation -  " Spark revolves around the concept of a resilient distributed dataset (RDD), which is a fault-tolerant collection of elements that can be operated on in parallel. There are two ways to create RDDs: parallelizing an existing collection in your driver program, or referencing a dataset in an external storage system, such as a shared filesystem, HDFS, HBase, or any data source offering a Hadoop InputFormat ". RDDs are immutable elements, which means once we create an RDD we cannot change it. RDDs are fault tolerant as well, hence in case of any failure, they ...
Read more

Master Child Table from Flat file by using ADF Data Flow

Image
Introduction In this blog post, we are trying to split a flat file and put the value in two different table of Azure SQL Server. I think if we have the knowledge in SSIS, using data flow is very easy. By using data flow, we got the ETL solution as we can use different transformation to process data and finally store it in destination. Hope it will be interesting. Case scenario We have a flat file contains data. We need to segregate records from it like master child table.   Destination ( Azure SQL Table)     ADF Solution We have a single Pipeline which has one Activity named Mapping Data Flow Activity which just calls our Data Flow.   Data Flows Structure     First data flow we are using for our Master table. It just store the distinct records in our master table named [Customer] Second data flow we are using for Transaction table named [SalesOrder]   Master Table Data Flow   Source     S...
Read more