How to implement the ADF data flow to divide one row to two rows

Question

How to implement the ADF data flow to divide one row to two rows

Peter Ma 216 Microsoft Employee

Hi I have a file which is very large in data lake, the structure is like this:

Original Data(data lake):

What I want

Cosmos DB

Blob files which contains the Html(blob storage):

path/to/html/body1.html
path/to/html/body2.html
path/to/html/body3.html
path/to/html/body4.html

Who could give some suggestions for implement this logic in ADF

KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2021-06-29T18:43:10.003+00:00

Hi anonymous user-0048,

Thanks for using Microsoft Q&A forum and posting your query.

I'm not sure if I understood the requirement. Could you please elaborate a bit clear on the ask so that we can better assist on this. I am confused between source data and expected sink data.

Additional clarification (or a better sample/example) would be very helpful.

We look forward to your confirmation

Thanks
Peter Ma 216 Reputation points Microsoft Employee

2021-06-29T18:51:36.24+00:00

Sorry for the confusion, updated description!

Original Data(data lake):

ID | URL | Html(html content)
01 | a.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body1.html
02 | b.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body2.html
03 | b.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body3.html
04 | b.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body4.html

What I want

Cosmos DB

ID | URL | path_to_html_in_blob_storage
01 | a.com | path/to/html/body1.html
02 | a.com | path/to/html/body2.html
03 | a.com | path/to/html/body3.html
04 | a.com | path/to/html/body4.html

Blob files which contains the Html(blob storage):

path/to/html/body1.html
path/to/html/body2.html
path/to/html/body3.html
path/to/html/body4.html
Peter Ma 216 Reputation points Microsoft Employee

2021-06-29T18:53:09.52+00:00

Original Data(data lake):

ID | URL | Html(html content)
01 | a.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body1.html
02 | b.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body2.html
03 | b.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body3.html
04 | b.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body4.html

What I want

Cosmos DB

ID | URL | path_to_html_in_blob_storage
01 | a.com | path/to/html/body1.html
02 | a.com | path/to/html/body2.html
03 | a.com | path/to/html/body3.html
04 | a.com | path/to/html/body4.html

Blob files which contains the Html(blob storage):

path/to/html/body1.html
path/to/html/body2.html
path/to/html/body3.html
path/to/html/body4.html
Peter Ma 216 Reputation points Microsoft Employee

2021-06-29T18:55:56.773+00:00

Original Data(data lake):

ID | URL | Html(html content)
01 | a.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body1.html
02 | b.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body2.html
03 | b.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body3.html
04 | b.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body4.html

ID, URL, Html are only column names of original data. The html column data is very large and I want to put it to a file(each row has its own file) in blob storage and replace the column with a reference to blob storage.

What I want

Cosmos DB

ID | URL | path_to_html_in_blob_storage
01 | a.com | path/to/html/body1.html
02 | a.com | path/to/html/body2.html
03 | a.com | path/to/html/body3.html
04 | a.com | path/to/html/body4.html

Blob files which contains the Html(blob storage):

path/to/html/body1.html
path/to/html/body2.html
path/to/html/body3.html
path/to/html/body4.html

Answer accepted by question author

0 additional answers

Your answer

KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2021-06-29T18:43:10.003+00:00

Hi anonymous user-0048,

Thanks for using Microsoft Q&A forum and posting your query.

I'm not sure if I understood the requirement. Could you please elaborate a bit clear on the ask so that we can better assist on this. I am confused between source data and expected sink data.

Additional clarification (or a better sample/example) would be very helpful.

We look forward to your confirmation

Thanks
Peter Ma 216 Reputation points Microsoft Employee

2021-06-29T18:51:36.24+00:00

Sorry for the confusion, updated description!

Original Data(data lake):

ID | URL | Html(html content)
01 | a.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body1.html
02 | b.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body2.html
03 | b.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body3.html
04 | b.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body4.html

What I want

Cosmos DB

ID | URL | path_to_html_in_blob_storage
01 | a.com | path/to/html/body1.html
02 | a.com | path/to/html/body2.html
03 | a.com | path/to/html/body3.html
04 | a.com | path/to/html/body4.html

Blob files which contains the Html(blob storage):

path/to/html/body1.html
path/to/html/body2.html
path/to/html/body3.html
path/to/html/body4.html
Peter Ma 216 Reputation points Microsoft Employee

2021-06-29T18:53:09.52+00:00

Original Data(data lake):

ID | URL | Html(html content)
01 | a.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body1.html
02 | b.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body2.html
03 | b.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body3.html
04 | b.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body4.html

What I want

Cosmos DB

ID | URL | path_to_html_in_blob_storage
01 | a.com | path/to/html/body1.html
02 | a.com | path/to/html/body2.html
03 | a.com | path/to/html/body3.html
04 | a.com | path/to/html/body4.html

Blob files which contains the Html(blob storage):

path/to/html/body1.html
path/to/html/body2.html
path/to/html/body3.html
path/to/html/body4.html
Peter Ma 216 Reputation points Microsoft Employee

2021-06-29T18:55:56.773+00:00

Original Data(data lake):

ID | URL | Html(html content)
01 | a.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body1.html
02 | b.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body2.html
03 | b.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body3.html
04 | b.com | <html>...</html> // --> this html content will be save to blob storage: path/to/html/body4.html

ID, URL, Html are only column names of original data. The html column data is very large and I want to put it to a file(each row has its own file) in blob storage and replace the column with a reference to blob storage.

What I want

Cosmos DB

ID | URL | path_to_html_in_blob_storage
01 | a.com | path/to/html/body1.html
02 | a.com | path/to/html/body2.html
03 | a.com | path/to/html/body3.html
04 | a.com | path/to/html/body4.html

Blob files which contains the Html(blob storage):

path/to/html/body1.html
path/to/html/body2.html
path/to/html/body3.html
path/to/html/body4.html

Answer 1

Hi anonymous user-0048 ,

Sorry for the delayed response and thanks for clarification.

One of the way to achieve your requirement is use the Derived column to dynamically created the folder path for the HTML content files and followed by 2 sink transformations.

1st sink transformation is to write the HTML content to Blob location
2nd sink transformation is transform the data by replacing HTML content with its blob reference location/path and write to sink/destination as needed.

Sample transformation flow looks like below:

Hope this helps. Do let us know if you have further query.

----------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.

Share via

How to implement the ADF data flow to divide one row to two rows

Hi I have a file which is very large in data lake, the structure is like this:

What I want

Original Data(data lake):

What I want

0 additional answers

Your answer