How best to copy files from onprem to azure using the file content to create partitions using ADF

Tinashe Chinyati 156

Hi, I have a folder with CSV files. I want to copy and create folder partitions in Azure datalake using the file content timestamp. How best should I approach this? I tried the lookup activity but the limitation of 5000 rows blocked me. Thanks for assisting

ShaikMaheer-MSFT 38,441 Reputation points Microsoft Employee

2021-06-14T07:13:35.243+00:00

Hello @Tinashe Chinyati ,

Thank you for Posting your query.

Could you please clarify on my understanding for the above ask?

I want to copy and create folder partitions in Azure datalake using the file content timestamp

Do you mean, if we have a file name as "abcd.csv" with timestamp column in it with value as "2021-06-14"(all rows will have same value) then in data lake you want to create below folder hierarchy.

I tried the lookup activity but the limitation of 5000 rows blocked me

Do you mean, your csv files may have rows with more than 5000 and we would like to know how to process them?
Tinashe Chinyati 156 Reputation points

2021-06-14T07:25:21.607+00:00

hi @ShaikMaheerMSFT-7578, so i have a date partitions (day 02) on prem and inside that date I have csv files (max count: 2000) but I want to copy these files to the datalake using the timestamp column inside each csv file.
So I need a hierarchy as 2021 (Year)-06 (month)-14(day)- RECORD THAT CORRESPONDS TO THAT PARTICULAR DATE
Tinashe Chinyati 156 Reputation points

2021-06-14T07:27:26.093+00:00

@ShaikMaheerMSFT-7578 I have checked file content its less than 5000 rows. So what you said is exactly what i need, creating partitions based on timestamp. column1: 2021-06-14 column2: test1 column3:24 so test1 with value 24 must be copied to the datalake under that date partition

Accepted answer

ShaikMaheer-MSFT 38,441 Reputation points Microsoft Employee

2021-06-14T10:18:39.423+00:00
Hello @Tinashe Chinyati ,

Thank you for Clarifications on Query.

Please check out the below detailed example, which implements dates hierarchy path.

For example, let's assume I have two files in folder as below.

Step 1: GetMetaData activity to get files from folder

Step 2: Pass GetMetaDataAcitivity outout childItems to ForEach activity

Step 3: Create date, month & date variables

Step 4: Lookup activity to get first row from file

Step 5: Set values in to 3 variables

Step 6: Copy activity to copy file to destination by creating path with date hierarchy

Step 7: Final results

Hope this helps. Do let us know if you any further queries.

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

Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.

Want a reminder to come back and check responses? Here is how to subscribe to a notification.
Please sign in to rate this answer.
Tinashe Chinyati 156 Reputation points

2021-06-15T05:52:39.633+00:00

Thanks @ShaikMaheer-MSFT , it works. How about if the csv files do not have a column header?

ShaikMaheer-MSFT 38,441 Reputation points Microsoft Employee

2021-06-16T07:52:53.857+00:00

Hi @Tinashe Chinyati

Glad to know that proposed solution works for you.

If your csv file doesn’t have column names defined in it then un-select First row as header inside dataset. ADF will add default schema to it as shown below.
So, on top of that default schema you can go head and implement above explained solution.

So, on top of that default schema you can go head and implement above explained solution. Hope this helps. Thank you.

Hope this helps. Thank you. Do let us know if you any further queries.
Sign in to comment

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

2 additional answers

Bedi,Bhawna 1 Reputation point

2021-06-17T11:19:53.173+00:00

Well explained!!! @ShaikMaheer-MSFT thankyou
Please sign in to rate this answer.
ShaikMaheer-MSFT 38,441 Reputation points Microsoft Employee

2021-06-17T11:27:33.847+00:00

Thank you @Bedi,Bhawna
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Tinashe Chinyati 156 Reputation points

2021-06-19T21:05:18.397+00:00

@ShaikMaheer-MSFT Thank you once again for the clear explain. following up on this discussion, we are able to create a date hierarchy using the file content itself, but it then copies the original file into that particular partition. How then do we ensure that we copy the corresponding details to that date. For example if we use Prop_0 column and partition using the date. I need to see test1 and 24 and all other details that fall within that date inside the partition. Another partition for 2021-07-02 will be created and Test 4 and 70 will be inside that partition and so on. Can you please assist with that. Thanks once again
Please sign in to rate this answer.
Kiran-MSFT 691 Reputation points Microsoft Employee

2021-06-20T00:06:19.247+00:00

You should use dataflow to solve involved use-cases with data. Its partition support can slice data across multiple files and directories.

Tinashe Chinyati 156 Reputation points

2021-06-20T05:09:41.747+00:00

Hi @Kiran-MSFT , thank you for your reply. I have tried the dataflow activity but it doesn't support connection to a file system on prem. So if it is possible to use the file content partition as we are now and then copy corresponding data under that date in the respective folder partition.

Tinashe Chinyati 156 Reputation points

2021-06-20T06:18:21.92+00:00

IHi @Kiran-MSFT thanks for that input, I think i have found a workaround that to include the dataflow activity. I will post my findings when done. Thanks
Sign in to comment

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

Share via

How best to copy files from onprem to azure using the file content to create partitions using ADF

2 additional answers

Your answer