CODEWITHSUNDEEP
azureazure-sql-databaseazure-data-factory
newdeveloper
newdeveloper

Reputation: 1451

ADF copy data activity - check for duplicate records before inserting into SQL db

I have a very simple ADF pipeline to copy data from local mongoDB (self-hosted integration environment) to Azure SQL database.

My pipleline is able to copy the data from mongoDB and insert into SQL db. Currently if I run the pipeline it inserts duplicate data if run multiple times.

I have made _id column as unique in SQL database and now running pipeline throws and error because of SQL constraint wont letting it insert the record.

How do I check for duplicate _id before inserting into SQL db?

should I use Pre-copy script / stored procedure? Some guidance / directions would be helpful on where to add extra steps. Thanks

Upvotes: 1

Views: 10216

Answers (4)

gaurav modi
gaurav modi

Reputation: 39

To remove the duplicates you can use the pre-copy script. OR what you can do is you can store the incremental or new data into a temp table using copy activity and use a store procedure to delete only those Ids from the main table which are in temp table after deletion insert the temp table data into the main table. and then drop the temp table.

Upvotes: 0

newdeveloper
newdeveloper

Reputation: 1451

Currently I got the solution using a Stored Procedure which look like a lot less work as far this requirement is concerned.

I have followed this article: https://www.cathrinewilhelmsen.net/2019/12/16/copy-sql-server-data-azure-data-factory/

I created table type and used in stored procedure to check for duplicate.

my sproc is very simple as shown below: 

SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spInsertIntoDb]
    (@sresults dbo.targetSensingResults READONLY)
AS

BEGIN

MERGE dbo.sensingresults AS target
USING @sresults AS source
ON (target._id = source._id)

WHEN NOT MATCHED THEN
    INSERT (_id, sensorNumber, applicationType, place, spaceType, floorCode, zoneCountNumber, presenceStatus, sensingTime, createdAt, updatedAt, _v)
    VALUES (source._id, source.sensorNumber, source.applicationType, source.place, source.spaceType, source.floorCode,
     source.zoneCountNumber, source.presenceStatus, source.sensingTime, source.createdAt, source.updatedAt, source.updatedAt);
END

I think using stored proc should do for and also will help in future if I need to do more transformation.

Please let me know if using sproc in this case has potential risk in future ?

Upvotes: 2

Leon Yue
Leon Yue

Reputation: 16411

Azure Data Factory Data Flow can help you achieve that:

enter image description here

You can follow these steps:

  1. Add two sources: Cosmos db table(source1) and SQL database table(source2).

  2. Using Join active to get all the data from two tables(left join/full join/right join) on Cosmos table.id= SQL table.id. enter image description here

  3. AlterRow expression to filter the duplicate _id, it not duplicate then insert it. enter image description here

  4. Then mapping the no-duplicate column to the Sink SQL database table.

Hope this helps.

Upvotes: 5

PowerStar
PowerStar

Reputation: 895

You Should implement your SQL Logic to eliminate duplicate at the Pre-Copy Scriptenter image description here

Upvotes: 2

Related Questions