CODEWITHSUNDEEP
azure-data-factoryazure-mapping-data-flowreconciliation
Anand
Anand

Reputation: 21338

Data reconcilliation in Azure Data factory

I am new to ADF. My requirement is that I need to reconcile (compare) the data between two datasets - Azure SQL table and HTTP API (external).

I was thinking of using ADF Data Flows where I can add both the above sources but HTTP is not supported as a source in Data Flows (can't use REST connector due to some limitation).

Workaround that I think is to load the API data to some Blob storage/ADLS and then use it as a source in Data Flows.

Is the above workaround fine? Or any other way to reconcile the two datasets that is possibel in ADF?

Upvotes: 0

Views: 149

Answers (1)

Rakesh Govindula
Rakesh Govindula

Reputation: 11514

Workaround that I think is to load the API data to some Blob storage/ADLS and then use it as a source in Data Flows

If your API data is huge then this workaround might be the better option to achieve your requirement as dataflow has join and union transformations which you can use for your comparisons.

If the API data is less than 5000 rows, you can try the below approach in this case.

First use a lookup activity with your source HTTP JSON dataset and uncheck the First Row only option.

The lookup activity will give the data as an array of JSON. Now, use a copy activity with your SQL table dataset as source and your target as sink. In the source of the copy activity, select query option.

In the query, you can use openjson() and with to join or union it with your table as per your requirement.

Here is a sample query on joining the SQL table and API data using openjson().

declare @json1 nvarchar(max) = N'@{activity('Lookup1').output.value}';

SELECT t.* FROM  
 OPENJSON (@json1)  
WITH (   
              albumId int '$.albumId' ,  
              id int '$.id',  
              title varchar(max) '$.title',
              url varchar(max) '$.url',
              thumbnailUrl varchar(max) '$.thumbnailUrl'
 ) t inner join table1 on t.id=table1.id;;

enter image description here

Upvotes: 0

Related Questions