What is the best way to Divide up a Table for access to ADF due to Look Up Limitation

Question

What is the best way to Divide up a Table for access to ADF due to Look Up Limitation

Mike Kiser 1,536

Hello!
@Nasreen Akter @MartinJaffer-MSFT @KranthiPakala-MSFT

I have a DB Table the size of the following. We must load ALL employees however my Lookups will only take about 3000 rows (huge number of columns). What is the best way for me to break this table of 8452 rows into batches of 3000 rows so I can run my pipelines.
Thanks!
Mike

Mark Kromer MSFT 1,146 Reputation points

2021-06-05T02:29:45.797+00:00

What is your objective? You seem to be doing an operation that would likely work better inside a data flow, not a pipeline lookup.
Mike Kiser 1,536 Reputation points

2021-06-05T16:17:28.527+00:00

Thanks!

The objective is to get the DB Table reduced to where the Lookup Activity doesn't fail due to its limitations.
Mike
MarkKromer-MSFT 5,226 Reputation points Microsoft Employee Moderator

2021-06-06T07:18:14.167+00:00

I meant, what is the goal of your pipeline? If you are looking up 1000s of wide rows, you should look at performing that operation inside a data flow which utilizes Spark compute and does have those limitations. The Lookup pipeline activity is not intended to work on large datasets in that way.

1 answer

Your answer

Mark Kromer MSFT 1,146 Reputation points

2021-06-05T02:29:45.797+00:00

What is your objective? You seem to be doing an operation that would likely work better inside a data flow, not a pipeline lookup.
Mike Kiser 1,536 Reputation points

2021-06-05T16:17:28.527+00:00

Thanks!

The objective is to get the DB Table reduced to where the Lookup Activity doesn't fail due to its limitations.
Mike
MarkKromer-MSFT 5,226 Reputation points Microsoft Employee Moderator

2021-06-06T07:18:14.167+00:00

I meant, what is the goal of your pipeline? If you are looking up 1000s of wide rows, you should look at performing that operation inside a data flow which utilizes Spark compute and does have those limitations. The Lookup pipeline activity is not intended to work on large datasets in that way.

Answer 1

HimanshuSinha-msft 19,491 Microsoft Employee Moderator

Hello @Mike Kiser ,
Thanks for the ask and using the Microsoft Q&A platform .
As @MarkKromer-MSFT suggested you can use ADF mapping data flow , but in case if you want to use ADF pipeline you can try to use the the waterrmark logic .

Create a watermark table at the Source . Let the table have the field may be a employeeid /somedate ( depends on which field you want to use )
Read the value of the watermark table using a lookup .
In your pipeline add a for loop .
Add a lookup activity , *** The select statement should be dynamic something on the lines 'SELECT top 5000 fieldnames where watermark = LookupActivityinStep2.value"
Your lookup should have all the values for 5000 records . Now you can perform your logic here . If you intend to use an inner loop here ( which is not supported ) please use a execute pipeline and implement the FE In the child pipeline
Now get the max values of the watermark field and update the watermark table .
In the next iteration the dynamic expression which only pick rows greater the value which is in the watermark table .
Hope this helps

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

Mark Kromer MSFT 1,146 Reputation points

2021-06-16T16:59:40.517+00:00

Just to be clear ... this is a "pipeline" approach vs. a "data flow" approach. BOTH are ADF features.
HimanshuSinha-msft 19,491 Reputation points Microsoft Employee Moderator

2021-06-16T17:06:33.647+00:00

Thanks @Mark Kromer MSFT for correcting me on that . I have updated the intial post .

Thanks
Himanshu
HimanshuSinha-msft 19,491 Reputation points Microsoft Employee Moderator

2021-06-23T23:16:56.543+00:00

Hello @Mike Kiser ,,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu

Share via

What is the best way to Divide up a Table for access to ADF due to Look Up Limitation

1 answer

Your answer