question

JayaVenkatasubramanian-2440 avatar image
0 Votes"
JayaVenkatasubramanian-2440 asked ·

How to update Azure sql database table from Azure Datafactory in a foreach loop?

I have a database table that will provide a large number of ids (500k). For each id I will need to make a REST api call (to Yammer), extract a value from the response JSON and then update the source table against the id with the extracted value.
id1 : value1
id2 : value2
id3 : value3
What kind of activities and configurations will I need to use to make it simple, fast and efficient? I am fairly new to Azure and learning about lookup and foreach activities. I see that lookup activity has a 5000 row limit.
How can I chunk the data read from the database?
How can I update the row in the table for each id? Is stored procedure activity the only way? Is it efficient to call a stored procedure for thousands of times?

azure-data-factory
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered ·

Hello @JayaVenkatasubramanian-2440 ,
Thanks for the ask and using the Microsoft Q&A platform .
Your assesment is correct that lookup has a limit of 5K rows at this time . Since you have mentioned that source is a SQL table and with 500K records I am assuming that you must be having a primary key , if thats the case you can use the watermark logic and it should work . Read about the logic here
Logic .
1. Create a Watermark table to store the max Primary key .
2. Fetch the top 5K from the table and pass that to the foreach ( FE) loop .
3. Try to grab the max of the Primary key and now the query should be something like ( SELECT * from somesource table where primarykey > max(primarykey )

Let me know if you have any queries on this

Please do let me know how it goes .
Thanks
Himanshu
Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members


· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello Himanshu,

Appreciate your response. While I look into this, I would really appreciate your feedback on my second part of the question that I am mainly worried about:

How can I update the row in the source table for each id? Is stored procedure activity (OR copy activity having Sink as stored procedure) the only way? Is it efficient to call a stored procedure for thousands of times in the foreach loop?
The stored procedure will have a simple update line : update <table> set value = <extracted value from API call> where id = <id used from source table for the API>
Should I use any table type here?
Are there ways to accumulate the "id : extracted value" data after the REST api call happening in the foreach loop in order to update the database in a single shot or lesser number of times? Or do I not have to worry about that?

Thanks much!

0 Votes 0 ·
HimanshuSinha-MSFT avatar image HimanshuSinha-MSFT JayaVenkatasubramanian-2440 ·

Hello @JayaVenkatasubramanian-2440 ,
I have responded to the ask here
Thanks
Himanshu


0 Votes 0 ·