COPY ACTIVITY FROM SOURCE LIKED REST TO DESTINATION LOINED REST SERVICE

Rishabh Srivastava 60 Microsoft Employee

Want to copy from source table to destination table in the same log analytics workspace. Using Azure Data Factory.

My configuration:
Source Linked REST Service to read data from Log Analytics workspace w1 table t1
Destination Linked REST service to write to log analytics workspace w1 table t2

I am using log ingestion API to write to w1 table t2.

Now the issue is how do I map hierarchical data from source to destination as ADF doesn't support this?
How can I do mapping?

Smaran Thoomu 10,405 Reputation points Microsoft Vendor

2024-05-03T12:02:27.48+00:00
Hi @Rishabh Srivastava

Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

I understand you want to copy data from a source table (t1) in one Log Analytics workspace (w1) to a destination table (t2) in the same workspace using Azure Data Factory (ADF). You're using Linked REST Services for both source and destination, and the challenge lies in mapping hierarchical data since ADF doesn't natively support it.

To map hierarchical data from a source to a destination in Azure Data Factory, you can use Data Flows along with Copy Activity. Here are the steps to achieve this:

Create a Data Flow in Azure Data Factory.

In the Data Flow, add a Source transformation and configure it to read data from the Source REST Data Set using the REST linked service to connect to Log Analytics workspace w1 table t1.

Add a Sink transformation and configure it to write data to the Destination REST Data Set using the REST linked service to connect to Log Analytics workspace w1 table t2.

In the Data Flow, add a Derived Column transformation to map the hierarchical data from the source to the destination. You can use the Derived Column transformation to create new columns or modify existing columns based on expressions.

Save and publish the Data Flow.

In the Copy Activity, select the Data Flow as the source or sink dataset, depending on your requirements.

Configure the Copy Activity to use the Data Flow as the source or sink dataset, depending on your requirements.

Run the Copy Activity to copy data from the source to the destination, with the hierarchical data mapped using the Derived Column transformation in the Data Flow.

For more information, please refer the below articles:

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-transformation-overview

https://www.sqlshack.com/transform-data-using-a-mapping-data-flow-in-azure-data-factory/

I hope this helps! Let me know if you have any further questions.
Smaran Thoomu 10,405 Reputation points Microsoft Vendor

2024-05-05T16:00:34.37+00:00

@Rishabh Srivastava We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Rishabh Srivastava 60 Reputation points Microsoft Employee

2024-05-05T18:23:05.46+00:00

As suggested I am using REST Linked service in source data flow now what should be the sink in data flow and how do I put the output of data flow to copy activity? how do I use Copy activity with data flows ?

Like How do I mat the response of source linked REST service to request body of destination REST linked service using Data flows?
Smaran Thoomu 10,405 Reputation points Microsoft Vendor

2024-05-07T11:51:00.63+00:00
@Rishabh Srivastava To use Data Flows with Copy Activity in Azure Data Factory, you can use a Sink transformation in the Data Flow to write data to the destination table. In your case, you can use another REST linked service as the Sink in the Data Flow to connect to the Log Analytics workspace and write data to the destination table.

Here are the steps to use Copy Activity with Data Flows to map the response of the Source Linked REST Service to the request body of the Destination Linked REST Service:

Create a Data Flow in Azure Data Factory.

In the Data Flow, add a Source transformation and configure it to read data from the Source REST Data Set using the REST linked service to connect to Log Analytics workspace w1 table t1.

Add a Sink transformation and configure it to write data to the Destination REST Data Set using the REST linked service to connect to Log Analytics workspace w1 table t2.

In the Data Flow, add a Derived Column transformation to map the hierarchical data from the source to the destination. You can use the Derived Column transformation to create new columns or modify existing columns based on expressions.

Save and publish the Data Flow.

In the Copy Activity, select the Data Flow as the source dataset and the Destination REST Data Set as the sink dataset.

Configure the Copy Activity to use the Data Flow as the source dataset and the Destination REST Data Set as the sink dataset.

In the Copy Activity, map the columns from the source dataset to the columns in the destination dataset. You can use the Mapping tab in the Copy Activity to map the columns.

Run the Copy Activity to copy data from the source to the destination, with the hierarchical data mapped using the Derived Column transformation in the Data Flow.

To map the response of the Source Linked REST Service to the request body of the Destination Linked REST Service, you can use the Derived Column transformation in the Data Flow to create new columns or modify existing columns based on expressions. You can then map these columns to the columns in the Destination REST Data Set in the Copy Activity.

For more information you can follow the below links:

https://learn.microsoft.com/en-us/azure/data-factory/control-flow-execute-data-flow-activity

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-transformation-overview

I hope this helps! Let me know if you have any further questions.
Rishabh Srivastava 60 Reputation points Microsoft Employee

2024-05-07T20:51:18.4333333+00:00

I dont see this option

In the Copy Activity, select the Data Flow as the source dataset and the Destination REST Data Set as the sink dataset.
Smaran Thoomu 10,405 Reputation points Microsoft Vendor

2024-05-09T09:20:54.5966667+00:00

@Rishabh Srivastava Apologies for the confusion. I mean to say after you have done the desired transformations in the Data Flow, you need to connect the source in the Copy Activity.

Share via

COPY ACTIVITY FROM SOURCE LIKED REST TO DESTINATION LOINED REST SERVICE