CODEWITHSUNDEEP
azure-data-lakeu-sql
Ahsan Abbas
Ahsan Abbas

Reputation: 165

How to Avoid adding duplicates to U-SQL Tables

Is there a way to avoid adding duplicate records in U-SQL tables other than adding/truncating partitions. Let's assume only unique identifiers are different for both records.

Upvotes: 2

Views: 461

Answers (2)

Arron
Arron

Reputation: 1164

You can also use ROWNUMBER() in the U-SQL query

@transactions =
   SELECT *,
   ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Id DESC) AS RowNumber
   FROM @searchlog;

@result =
   SELECT Id,Name,Description,Age
   FROM @transactions
   WHERE RowNumber == 1;

This will get the unique record in a file.

Upvotes: 3

Michael Rys
Michael Rys

Reputation: 6684

Since U-SQL tables do not provide UNIQUE constraints due to the limited scalability, you will have to make sure that you use ANTI SEMIJOIN on the unique columns to filter out the possible duplicates on the new data before you insert it.

Upvotes: 2

Related Questions