I am moving the data from On-Premises SQL server to ADLS in CSV format. I have done the incremental load but don't know how to do the UPSERT in csv files using copy activity.

Hi Rakesh Kumar In ADF we cannot perform upsert to csv files directly. For table based sinks only, in copy activity and data flow you have the upsert option. As a workaround, you can have a table in Azure SQL and use that for upsert operation. Once the copy to table completes, you can have another copy activity to copy from table to a csv file. For more details on how do we implement upsert in copy activity, please refer to this video and for the data flow you may refer to this video. Please let us know if you need any other information or detail regarding this, Thank you

upsert the data from on premises Mysql to ADLS GEN2(format CSV files)

Accepted answer

Suba Balaji 11,206 Reputation points

2023-10-18T04:22:34.0066667+00:00

Hi Rakesh Kumar

In ADF we cannot perform upsert to csv files directly. For table based sinks only, in copy activity and data flow you have the upsert option.

As a workaround, you can have a table in Azure SQL and use that for upsert operation. Once the copy to table completes, you can have another copy activity to copy from table to a csv file.

For more details on how do we implement upsert in copy activity, please refer to this video and for the data flow you may refer to this video.

Please let us know if you need any other information or detail regarding this,

Thank you
Please sign in to rate this answer.
Rakesh Kumar 45 Reputation points

2023-10-18T05:03:31.05+00:00

@Suba Balaji Instead of creating new resource Azure SQL DB, can we use Databricks notebook for UPSERT in CSV files? If yes then how?

Suba Balaji 11,206 Reputation points

2023-10-18T16:19:37.33+00:00

If you already know how to connect to On Prem MySQL DB from databricks notebook, you can consider loading to delta tables where you can do a merge as given in this link.

phemanth 8,645 Reputation points Microsoft Vendor

2023-10-26T09:01:51.35+00:00

Hi Rakesh Kumar

if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Sign in to comment

Share via

upsert the data from on premises Mysql to ADLS GEN2(format CSV files)

0 additional answers