Extract the date hierarchy from previous data in Data Lake Partition

RahulK 101

Hi Team,

Would be of great help to get some clear assistance on the below query.

Data source is Dynamic365 & SAP Hana.

My requirement is to create a data lake partition(ADLS Gen2) with date hierarchy(SubjectArea-->RawData-->Year-->Month-->Day). Data available is from 2018-2020 and new data will be flowing-in daily.

Can you pls guide me to create this structure using ADF to copy multiple tables & extract the date format from previous data and store the current data as well.

Thanks in advance.

2 answers

ChiragMishra-MSFT 951 Reputation points

2020-06-08T06:36:53.427+00:00
Hi Pramod,

If you want to copy data daily and dynamically generate the folder hierarchy, you can set up a dynamic path in your sink dataset (ADLS Gen2) as shown below :

The expression I have used will create a structure like "base path"/<current year>/<current month>/<current date>/

@concat('base path' , formatDateTime(utcnow(), 'yyyy'), '/', formatDateTime(utcnow(), 'MM'), '/', formatDateTime(utcnow(), 'dd'), '/')

Hope this helps.
Please sign in to rate this answer.

1 person found this answer helpful.
RahulK 101 Reputation points

2020-06-09T20:02:03.703+00:00

Hi Chirag,

Thanks for the support. It was really helpful guidance in this regard.

Would be great if you could help me with the below one as well:

I have the data available from 2018 - 2020. I would like to create a folder structure in a way that the partition should read the data from 2018 and store in the Year>Month>Date hierarchy. Does ADF has the provision to create the partition to extract the date from previous data. If yes, pls suggest me the solution.

PFA the tentative structure of the data lake partition. Kindly, let me know if my query needs more clarification to elaborate.

Regards,
Pramod.
Sign in to comment
ChiragMishra-MSFT 951 Reputation points

2020-06-08T07:06:18.773+00:00
If you want to use existing folders in your sink to determine the next folder's structure, you can do the following :

use a GetMetadata activity (https://learn.microsoft.com/en-us/azure/data-factory/control-flow-get-metadata-activity#capabilities) and use the LastModified and ChildItems attributes to get the data.

Chain a set variable activity to it and use a dynamic expression to capture lastModified/childItems

Proceed as above, use a dynamic expression in your dataset and use your variable(s) and string functions if needed to create the hierarchy you want.

Hope this helps.
Please sign in to rate this answer.
ChiragMishra-MSFT 951 Reputation points

2020-06-11T06:01:01.66+00:00

@Pramod-7321 Does this answer your follow up question?

ChiragMishra-MSFT 951 Reputation points

2020-06-18T07:07:55.543+00:00

Hi @Pramod-7321,

Was the above answer helpful to you? If yes, please consider accepting it as answer as it would help other community members who stumble upon this thread for a similar/same issue.

Anonymous

2020-06-20T03:27:29.24+00:00

Hi Chirag,

Thanks for the solution. It has partially sufficed my need.

I have created a folder structure(2018/2019/2020) in ADLS Gen2 and I have added the dynamic content for the current data to be copied.

But my whole concern is how to dynamically make the previous years data i.e., 2018, 2019 to sit in the respective folders that I have created in DL Gen2. For ex: I have a table with date column of 2018, I want the process to copy the data to 2018 folder by reading the date column automatically.

Hope my query is clear.

Please suggest a workaround on this.

Thanks,
P.

ChiragMishra-MSFT 951 Reputation points

2020-06-25T07:39:37.837+00:00

Hi Pramod,

Are you trying to read from a DB source and sending the right row to a file in the right folder in Gen2 as a sink? If yes, you can also write a dynamic expression to come up with a query in the source that covers all years - 2018,2019, etc.

In the sink, you can parameterize again the same way as I suggested to write files in the correct folder.

Hope this helps!

Stay safe and take care !
Sign in to comment