Use multiple column to partition data in ADF copy activity

Question

Hi,

I have some data and two columns that I would like to use as partition cols i.e d_type and d_date.

Iam using copy activity but it only takes one column to partition. Is it possible to partition using three cols i.e d_type, year(d_date) and month(d_date).

Something like this:

The idea actually is to save the data so that we can read data for any d_type independently and d_date inside d_type independently like this:

lets say d_type uniques are 1,5,10
and d_date are 2022-01-01 to 2022-03-30

then I want to store parquets like this:

d_type_1/2022-01/data.parquet
d_type_1/2022-02/data.parquet
d_type_1/2022-03/data.parquet

d_type_5/2022-01/data.parquet
d_type_5/2022-02/data.parquet
d_type_5/2022-03/data.parquet

d_type_10/2022-01/data.parquet
d_type_10/2022-02/data.parquet
d_type_10/2022-03/data.parquet

Answer 1

Hi @Obaid UrRehman ,

Thank you for posting query in Microsoft Q&A Platform.

I believe you are using Azure SQL Database as the source as source here. Please correct me if I am wrong.

I have gone through documentation of Azure SQL Database connector, It looks partitionColumnName field allows to take only single column. It does not allow multiple columns as partition. Click here to know more about Source setting documentation.

When are you trying with two column names then are you getting any error? Try using column names as array. for example, as ["id","dateCol"] and let us know how it behaves.

If that does not work, then its confirm that its not allowed at this moment. I encourage you to have a feedback item created for this using below link. Product team closely monitor feedbacks and consider them for future releases.
https://feedback.azure.com/d365community/forum/1219ec2d-6c26-ec11-b6e6-000d3a4f032c

Hope this helps. Please let me know if any further queries.

-------------

Please consider hitting Accept Answer button. Accepted answers help community as well.