Copy activity in Azure Data Factory and Azure Synapse Analytics

With a Linked Service, you can connect to your instance of Azure Database for PostgreSQL flexible server and use it within Azure Data Factory and Synapse Analytics activities.

The Copy Activity supports Copy Command, Bulk Insert, and Upsert. For more information, see Copy and transform data in Azure Database for PostgreSQL using Azure Data Factory or Synapse Analytics.

The next section has a step-by-step guide on how to manually create a copy activity and how to create a pipeline.

Prerequisites

Create a data copy activity via the portal

  1. In Azure Data Factory Studio, select the Author hub. Hover over the Pipelines section, select ... at the left, and select New pipeline to create a new pipeline.

    Screenshot that shows where to select author in Azure Data Factory.

    Screenshot that shows where to select new pipeline.

  2. Under Move and transform, drag and drop the Copy data activity into the pipeline.

    Screenshot that shows where to select Copy data.

  3. At the General tab, give a name to your pipeline.

    Screenshot that shows where to give a name to the pipeline at the general tab.

  4. At the Source tab, select or create a Source dataset. In this example, select an Azure Database for PostgreSQL table.

    Screenshot that shows where to select or create a source dataset at the source tab.

    Screenshot that shows a source dataset selected.

  5. At the Sink tab, select or create an Azure Database for PostgreSQL dataset as Sink dataset, and choose the Write method. For more information, see Azure Copy Activity and Write Method.

    Screenshot that shows where to select or create a sink datasource at sink tab.

  6. Select between Copy command, Bulk insert, and Upsert for the Write method.

    Screenshot that shows an example of copy command selected.

  7. If a custom mapping is required, configure your mapping in the Mapping tab.

  8. Validate your pipeline.

  9. Select Debug to run the pipeline manually.

  10. Set up a trigger for your pipeline.

For JSON payload examples, see Azure Database for PostgreSQL as sink.

Key columns behavior on upsert

When you upsert data with the Azure Database for PostgreSQL connector, you can specify optional fields called Key Columns.

Screenshot that shows optional key columns. There are three acceptable ways to use the Key Columns:

  1. Select New and add all the primary key columns of the sink datasource table

    Screenshot that shows an example with all key columns.

  2. Select New and add one or more unique columns of the sink datasource table

  3. Leave the Key columns empty. In this case, the connector finds the primary key columns and uses them as Key columns

    Screenshot that shows an example with no key columns selected.

Related content