How to load delta files from ADLS to SQL in ADF

Question

How to load delta files from ADLS to SQL in ADF

Prapul Kumar Dongari 116

Hi have requirement to load delta files from adls container to sql but delta files are partitioning so load all latest partitioned files using adf how to achive this.

Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2024-02-27T05:39:57.66+00:00

Hi Prapul, could you please share some sample file /folder structure?
Prapul Kumar Dongari 116 Reputation points

2024-02-27T05:45:41.76+00:00
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2024-02-28T01:57:27.37+00:00

You can load based on last modified date to pick up latest ones. You can store the last modified date in a table or lookup file after every load. On the next load, you can look up the value from table/file and use it in the copy activity to pick up files whose modified dateime > last modified datetime in the table or file. Here is a sample video. https://youtu.be/QZn9D6APsek?si=HiJeAenzpYCDhgne please check and revert if you have questions.
Prapul Kumar Dongari 116 Reputation points

2024-02-28T02:10:12.4933333+00:00

But delta files are partitioned if the file size is large it will create many partitions and there is some time difference from one partition file to other so in that case how we can handle lastmodified date
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2024-02-29T10:27:29.6066667+00:00

Hi, for example, if your process runs today, and loads files upto 29th Feb 2024 at 4PM, you can store this in the table or lookup file. Which ever files are modified after this timestamp, they will all be loaded irrespective of number of files. But here, Out of all the loaded files, you might have to find a logic to pick up the latest timestamp so that , it can be used for logging. One way to do this is to loop through files one by one and compare it with the timestamp in the lookup file or table. If the stored timestamp is lesser than the one being loaded, the stored timestamp will be updated. So typical flow would be, get file names using get metadata activity--> loop through -->find out timestamp using one more get metadata activity -->parallely find out the stored timestamp using lookup activity--> use if condition to decide whether the stored timestamp needs an update and the file needs to be copied or not. Hope it makes sense. Please let us know if you have further questions. Thanks.

Your answer

Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2024-02-27T05:39:57.66+00:00

Hi Prapul, could you please share some sample file /folder structure?
Prapul Kumar Dongari 116 Reputation points

2024-02-27T05:45:41.76+00:00
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2024-02-28T01:57:27.37+00:00

You can load based on last modified date to pick up latest ones. You can store the last modified date in a table or lookup file after every load. On the next load, you can look up the value from table/file and use it in the copy activity to pick up files whose modified dateime > last modified datetime in the table or file. Here is a sample video. https://youtu.be/QZn9D6APsek?si=HiJeAenzpYCDhgne please check and revert if you have questions.
Prapul Kumar Dongari 116 Reputation points

2024-02-28T02:10:12.4933333+00:00

But delta files are partitioned if the file size is large it will create many partitions and there is some time difference from one partition file to other so in that case how we can handle lastmodified date
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2024-02-29T10:27:29.6066667+00:00

Hi, for example, if your process runs today, and loads files upto 29th Feb 2024 at 4PM, you can store this in the table or lookup file. Which ever files are modified after this timestamp, they will all be loaded irrespective of number of files. But here, Out of all the loaded files, you might have to find a logic to pick up the latest timestamp so that , it can be used for logging. One way to do this is to loop through files one by one and compare it with the timestamp in the lookup file or table. If the stored timestamp is lesser than the one being loaded, the stored timestamp will be updated. So typical flow would be, get file names using get metadata activity--> loop through -->find out timestamp using one more get metadata activity -->parallely find out the stored timestamp using lookup activity--> use if condition to decide whether the stored timestamp needs an update and the file needs to be copied or not. Hope it makes sense. Please let us know if you have further questions. Thanks.

Share via

How to load delta files from ADLS to SQL in ADF

Your answer