[ADF] Azure Data Factory Copy Activity number of files

Aminouvic 1

We've a simple ADF pipeline that copies data from an on premise sql server database (throught one IR node) to ADLS Gen 2

We are trying to optimize job duration/performance by writing multiple files sink side but the parallel copy parameters / DIU seems to have no effect (the pipeline write one big file)

How can we configure the number of files written to ADLS in this case ?

2 answers

HarithaMaddi-MSFT 10,136 Reputation points

2020-08-06T11:25:56.163+00:00

Hi @Aminouvic ,

Welcome to Microsoft Q&A Platform.

Azure Data Factory has provided ability to run foreach activity in parallel by configuring below properties in it. If isSequential is set to false, the activity iterates in parallel with a maximum of 20 concurrent iterations. Documentation for the same can be found here.

Below are the sample runs I have done for couple of files with batch size of 1 and 2 and the execution run time variation depicts parallel loading of files.

Hope this helps! Please let us know for further queries and we will be glad to assist.
Please sign in to rate this answer.
Aminouvic 1 Reputation point

2020-08-06T15:22:15.743+00:00

Hi,

Thank you for the response and details on the Foreach activity.

Actually we are already using it with the provided configuration and it works fine.

What we want to do is that each inner activity (copie action) writes data to ADLS in parallel for the same source table.

But currently the Copy action generates one big file per table.

Exemple :
Currently
Input SQL table : my_source_table_1
The copy action will write one file into ADLS : /raw/data/my_source_table_1/file_uuid.csv

We wish to write multiple files in parallel to speed up the process :
Input SQL table : my_source_table_1
Output ADLS :

/raw/data/my_source_table_1/file_1.csv

/raw/data/my_source_table_1/file_2.csv

/raw/data/my_source_table_1/file_3.csv
......

/raw/data/my_source_table_1/file_XXXX.csv

Thank you for help!

HarithaMaddi-MSFT 10,136 Reputation points

2020-08-07T09:29:07.567+00:00

Hi @Aminouvic ,

Thanks for sharing more details.

As per my knowledge, there is no dynamic way to split the files in a copy activity in data factory. However, the same can be achieved by defining rules - specific year range or specific set of records in the table identified by a column value. These rules can be maintained in a config table and Lookup activity can be used to retrieve them prior to sending this information to a foreach activity that has copy activity. The query parameterization at source can be used to filter the data from single SQL table according to config table rules and loads into multiple files parallelly. Please let me know if this approach works, meanwhile I will work with Product team internally for any alternative approach.

Aminouvic 1 Reputation point

2020-08-10T13:41:25.267+00:00

Hi @HarithaMaddi-MSFT ,

Thanks for the recommandation.

The described mechanism will indeed write one file per partition/ filtered query, but it would be nice to be able to dynamically/choose the number of target files especially for large tables/partitions (>100G in our case).

Best regards,

HarithaMaddi-MSFT 10,136 Reputation points

2020-08-11T08:40:01.453+00:00

Hi @Aminouvic ,

Thanks for sharing more details. I discussed with Azure data factory product team and they mentioned that this requirement aligns perfectly with their ongoing work item - "New property maxRowsPerFile to split and write to multiple smaller files". Please send a mail to AzCommunity [at] microsoft [.] com with below details and then I can loop you with corresponding team members to get early trial on the feature.

Subject of the mail: <Attn - Haritha : [ADF] Azure Data Factory Copy Activity number of files>

Thread URL:

Looking forward for your mail. Thanks for your patience.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
HarithaMaddi-MSFT 10,136 Reputation points

2020-09-16T08:58:10.18+00:00

Hi @Aminouvic ,

Want to share the new update from ADF team which we were discussing in August. Please check this link that gives more details on this property and kindly let us know if it helps in implementing your requirement.

Thanks for your patience!
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

[ADF] Azure Data Factory Copy Activity number of files

2 answers

Your answer